July 4, 2011 at 1:42 am
Hi All,
I have a Main_Detail table which contains 1526013586 rows. I am running following query
select top 10 * from dbo.Main_Detail order by UNQKEY asc,LINE_NBR asc which is taking too long time to return the result.
There is a clustered index on unqkey col and non clustered index on line_nbr col in ascending order
IF I remove the order by cluase from above query it runs fast.
If i keep only one col in order by cluase then also it runs fast.
But if i use above query with two cols in order by clause then it takes too long time.
The execution plan shows CI scan.
I observed the same query in DTA it suggests non clusetured index on both the above cols and suggest all col name as included colmn for the index.
Any help or suggestion as the no. of rows returned are just 10 in no. but still it going to take long time
What is the effective way to use the order by clause with multiple cols
Thanks in advance
July 4, 2011 at 2:03 am
Probably this could because of LIN_NBR sorting. Whats the datatype of the columns. Please provide the table structure.
However to me it looks like the exepected behaviour that you are doing two SORT operations on a large table.
July 4, 2011 at 2:04 am
It's not the ORDER BY by itself causing the long duration.
It's the combination of TOP x ORDER BY.
In order to find the top 10 rows all rows need to be ordered matching the ORDER BY clause.
It runs fast if you only use one of the two since one of the two indexes you have will support the sort operation.
There are two solutions I can think of:
a) if UNQKEY is a column holding unique values, then I'd skip the LINE_NBR column in the ORDER BY since it won't change the result anyway.
b) you could add another index with UNQKEY asc,LINE_NBR asc. But unlike the DTA suggestion I would strongly vote against including all columns. I'd rather ask if you really need all columns or if the number of columns returned can be (significantly?) reduced.
As a side note: when dealing with such large tables, DTA should not be considered as a valid tool for query tuning...
A very deep understanding of performance tuning / indexing is strongly recommended. Either in-house or via consulting.
July 4, 2011 at 2:30 am
No don't use the DTA.
Give me the table structure and execution plan.
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
July 4, 2011 at 3:04 am
Hi All,
Datatype for both the cols are int and allow null as it is a source for the ETL developement. UNQKEY will remail unique i.e. not null once the developement is done. For ALL the execution plan is attached for ur reference.
I hope it is enough for all to put on light on the post.
Thanks
July 4, 2011 at 3:59 am
vinod.saraswat (7/4/2011)
Hi All,Datatype for both the cols are int and allow null as it is a source for the ETL developement. UNQKEY will remail unique i.e. not null once the developement is done. For ALL the execution plan is attached for ur reference.
I hope it is enough for all to put on light on the post.
Thanks
If UNQKEY will be unique, then remove LINE_NBR from the ORDER BY ( = my recommendation (a) ).
Instead of trying to get better results from bad data, fix the data!
July 4, 2011 at 4:25 am
creating non-clustered index on main_detail(unqkey,line_nbr) should help..
July 4, 2011 at 7:21 am
arvind.1611 (7/4/2011)
creating non-clustered index on main_detail(unqkey,line_nbr) should help..
Are you aware that we're talking about a table with more than a billion rows and such an index would only be required during development?
July 4, 2011 at 11:11 pm
Thanks for all your valuable responses i agree with Lutz will fix the data.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply