select first row take too much time why and how to solve it ?

Question

select first row take too much time why and how to solve it ?

ahmed salah 3,216

I work on sql server 2019 i face issue when select top 1 it take too much time
although it one record but it take too much time may be 3 minutes

this table extractreports.[dbo].[FinalTechnologyGeneration] have 32000 rows 32 thousand rows
and table [Technology].[PartsGeneratedRules] have 600000 thousand rows 600 thousand rows

i only make select top 1 where message rule string field =generategrouprule string field

so how to make select top 1 more faster and take time less than3 minutes

 select top 1 1 from extractreports.dbo.FinalTechnologyGeneration f with(nolock)   
    inner join [Technology].[PartsGeneratedRules] r with(nolock) on f.MessageRule=r.GeneratedGroupRule

so why select is very slow although i select top 1 only

CREATE TABLE [dbo].[FinalTechnologyGeneration](  
[TechnologyId] [bigint] NULL,  
[PartID] [int] NULL,  
[PLID] [int] NULL,  
[CodeRulesId] [int] NULL,  
[GroupCode] [nvarchar](5) NOT NULL,  
[MessageRule] [varchar](800) NULL,  
[GroupCodeRank] [nvarchar](5) NULL  
) ON [PRIMARY]  
  
CREATE TABLE [Technology].[PartsGeneratedRules](  
[PartId] [int] NOT NULL,  
[CodeRulesId] [int] NULL,  
[GeneratedGroupCode] [nvarchar](10) NULL,  
[GeneratedGroupRule] [nvarchar](500) NULL,  
[ModifiedDate] [datetime] NULL,  
[Modifiedby] [int] NULL,  
[PLID] [int] NULL,  
[TechnologyId] [int] NULL,  
[GroupCodeRank] [int] NULL,  
PRIMARY KEY CLUSTERED   
(  
[PartId] ASC  
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)   
)

Accepted answer

2 additional answers

Your answer

Answer 1

LiHong-MSFT 10,056

Hi @ahmed salah
Try forcing a hash join:

 select top 1 column_list from extractreports.dbo.FinalTechnologyGeneration f with(nolock)   
     inner hash join [Technology].[PartsGeneratedRules] r with(nolock) on f.MessageRule=r.GeneratedGroupRule  
order by order_by_column

Best regards,
LiHong

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

ahmed salah 3,216 Reputation points

2022-06-21T15:55:42.457+00:00

only one question please i need answer for it
why you use
inner hash join
instead of
inner join

what benfit from adding hash join
are will make join and query run faster or what
Erland Sommarskog 121.8K Reputation points MVP Volunteer Moderator

2022-06-21T20:54:53.21+00:00

A join can physically be implemented in three ways: Nested loops, Merge join or Hash join. With INNER HASH JOIN, you are forcing that join to be implemented as a hash join. Furthermore, you are also implicitly forcing the join order.

Or for how LiHong arrived the conclusion that this was the best solution, Li will have to answer. But given the scant information you had shared, I'm tempted to say that it was a shot in the dark.

And more importantly, this is a not a general go-faster button. Next time you try this, you may find that performance drops.
LiHong-MSFT 10,056 Reputation points

2022-06-22T01:56:45.29+00:00

At first, I was tending to recommend you apply suitable index. However you commented that you tried it and it is still slow.
Therefore i have a guess that the optimizer probably thought a loop was going to be better with top 1.
Could you please share the execution plans of the two queries with and without 'top 1'?

Answer 2

Yitzhak Khabinsky 26,586

Hi @ahmed salah ,

While using TOP() clause you need to add an ORDER BY clause to the SELECT.
Otherwise, TOP has no meaning.

Additionally, indexes would help to improve performance:

on f.MessageRule
on r.GeneratedGroupRule
on ORDER BY column(s)

ahmed salah 3,216 Reputation points

2022-06-19T07:49:22.777+00:00

I do all that and util now slow still exist
are there are any recommendation must done
or any notes advice

Answer 3

You claim that there are indexes, but what is your collation?

I note that MessageRule is varchar(800), while GeneratedGroupRule is nvarchar(500). When you join columns, they should preferably have the same data type. What happens in this query is that MessageRule will be converted to nvarchar. Depending on your collation, this can render any index on that column useless.

Then again, even with the best of indexes, the query can take a lot of time. Assume, for the sake of the example, that there is only one matching row in the entire set. Adding TOP 1 in this situation is likely to make the query slower, because SQL Server will apply a strategy where it quickly finds a match if there are plenty of them. But that strategy will be very slow when there are very few matches.

If you don't have TOP 1, SQL Server knows from the start that it has to scan the whole lot, and plans execution accordingly.

What performance do you get without TOP 1?

Share via

select first row take too much time why and how to solve it ?

2 additional answers

Your answer