Click here to Skip to main content
15,867,308 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
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

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

sample ddl

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) 
 ) 


What I have tried:

select top 1 1 from extractreports.dbo.FinalTechnologyGeneration f with(nolock)
    inner join [Technology].[PartsGeneratedRules] r with(nolock) on f.MessageRule=r.GeneratedGroupRule
Posted
Updated 19-Jun-22 23:46pm
Comments
[no name] 19-Jun-22 11:46am    
Let us know why you don't have a "where" clause; or why a Cartesian join on 800 char columns makes sense.
ahmed_sa 19-Jun-22 16:29pm    
let me know
what i put on where
and why and use where
and what you mean by
Cartesian join on 800 char columns
can you tell me

1 solution

You are defining the relationship between the tables with [MessageRule] [varchar](800) NULL and [GeneratedGroupRule] [nvarchar](500) NULL. What do you think will happen if there are any rows in the tables where those values are NULL?
You will essentially get a Cross Join (a Cartesian Join). In general, do not use columns that can contain NULL as a way of defining the join.

Without any sample data it's impossible to tell, but at first glance it would appear that [GroupCode] [nvarchar](5) NOT NULL and [GeneratedGroupCode] [nvarchar](10) NULL would be better options for the join

You are joining on columns of different types and potential sizes - what if you have a MessageRule longer than 500 characters? Were you expecting it to match?

Whichever way you join, you don't have any indexes defined that include the columns you are using to join the tables. You've been told before about using appropriate indexing to improve performance.

All of these things will impact the performance of your query.

You may find these resources help
Join varchar to nvarchar performance - Transact-SQL - SQLTeam.com Forums[^]
Joining SQL Server tables using large character type columns[^]
https://www.sqlshack.com/query-optimization-techniques-in-sql-server-tips-and-tricks/[^]
Trick to Optimize TOP clause in SQL Server[^]
 
Share this answer
 
v2
Comments
Richard Deeming 20-Jun-22 6:07am    
The select top 1 1 from ... is technically correct, since he's only testing whether the record exists. Removing the second 1 would result in an invalid query, since you haven't selected any columns. :)
CHill60 20-Jun-22 7:35am    
D'oh. I'll update the solution. Thank you!
ahmed_sa 20-Jun-22 10:09am    
thank you for reply and interset
what you mean by following

You are defining the relationship between the tables with [MessageRule] [varchar](800) NULL and [GeneratedGroupRule] [nvarchar](500) NULL. What do you think will happen if there are any rows in the tables where those values are NULL?
You will essentially get a Cross Join (a Cartesian Join). In general, do not use columns that can contain NULL as a way of defining the join.

what you mean by that please
i need to understand this point

all point i understand and release it
CHill60 20-Jun-22 12:13pm    
Firstly, I am demonstrating that I have not kept in touch with latest developments. I tried to demonstrate the problem with an example, but my example completely ignored the rows containing null, whereas I was expecting every row with a null in that column to match to every other row with null. I.e. instead of getting 2 rows returned I would get 4.

This article describes the problem far better than I can - Join SQL Server tables where columns include NULL values[^]

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900