Top n Used with Order by & performance
-
Performance problem is there when i used like this
Select top 100 field1, field2
from table1 t1
inner join table2 t2 on t1.field1 = t2.field1
order by t2.field1
In table1, tabl2 i have more than 1 million records
If i remove order by clause then it works very fast
Can i specify which index is to be used to fetch data from particular table
as in above case 1st it fetch data from both the table & apply order by clause & then it takes top 100 records
Is there any other way to specify clustered / non clustered index to the same
Thanks in advance
-
Yes, you can add an INDEX HINT to your query
WITH (INDEX (ix_blablabla))
N 56°04'39.16"
E 12°55'05.25" -
However, unless you're 100% sure you know what you're doing, you may make the query even slower.
Take a look at the exec plan, see what indexes are been used, and what order the operations are been done in.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Is field1 AND field2 indexed? The ORDER by requires a tempdb operation, as does the TOP 100. If they're indexed, then the optimizer can easily shortcut the first matching 100 records.
Be sure your TEMPDB isn't overloaded or on a slow disk, etc. If they're indexed, I wouldn't expect the performance to be too slow.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com -
Can you tell me what is meant by TEMPDB isn't overloaded ?
(1)
select * from Manish_Mst_item WITH (INDEX =PK_Mst_Item)
INNER join Manish_Trn_Item ON Sp_Item = It_No
When i run above query it gives record sequence of the Manish_Trn_Item table (i.e. Index Sp_No Which is primary key)
(2)
select * from Manish_Mst_item WITH LEFT join Manish_Trn_Item ON Sp_Item = It_No
When i run above query it gives record sequence of Manish_Item (i.e. Index On It_No which is primary key)
What to do if i want everytime
You can find screenshot of it at below link.
http://www.keepandshare.com/doc/view.php?id=155413&da=y
Thanks for your reply
Thanks in advance
-
In this query:
Select top 100 field1, field2
from table1 t1
inner join table2 t2 on t1.field1 = t2.field1
order by t2.field1
.. you are requesting the top 100 *ordered* rows, which means that SQL Server needs to first scan and sort both tables in full (there is no search argument present). Indexes may help in that only indexpages needs to be scanned, but still.. The semantics of the query implies a scan followed by a sort, then return the top 100 according to the requested ordering.
If you remove the 'order by t2.field1', then the request is much simpler:
'Just bring me the first 100 rows you can grab, I don't care which ones'
There's no sorting or ordering involved, thus way less overhead for the server to be able to fulfill the query.
From a performance point of view, sort operations are among the most expensive.
/Kenneth
-
Thanks for your reply
Is there any option in Query where i can use Index which is on field2 so i don't have to write order by clause in query & thus performance will improve
And if you see my above post where i have given 2 query. 1st query does not return data with sequence on it_id field & 2nd query does. Only difference b/w them is in 1st i have written INNER JOIN & 2nd LEFT JOIN .
So is there any way where i can use index in SELECT QUERY
Thanks in advance
-
You can use an index hint, however, I can guarentee to you that forcing the index on field2 will make the query worse.
Your sort and your join are both on field1. Why would you want to tell SQL to use the index on field2? It's useless to the query as it stands.
Which table is field2 from? If you don't have an index on field1, add one.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Thanks for your reply
My Table Structure
Table1
Field1 - This is my primary key field
Table2
Field1 - Foreign Key
Field2 - Primary Key Field
Field3 - Transaction data
Now when i join above tables with INNER JOIN sequence of records is of Table2, i want sequence of Table1
Now if i LEFT JOIN sequence of records is of Table1
But i want to use INNER JOIN & if i include order by clause then my query became taking multi times then not including order by clause.
I want only 500 rows of this query but this query should be in order of Field1 of Table1.
As you told it will degrade performance if i include query hint.
Is there any other way i can achieve this.
It will be great if someone solve this please.
Hope this will make you understand my problem.
Thanks in advance.
-
Index on Field1 in Table2, including Field3 if it is necessary to return that. You say that Field2 is the pk. Is it the clustered index as well?
An order by statement ORDER BY t2.Field1 (since it's the foreign key to t1 its the same as ordering by t1.Field1. If you leave out the order by, there's no guarentee what order the rows will be returned and the order that you get rows may change from one execution to another.
If that's still too slow, post the execution plan you get (by running SET SHOWPLAN_ALL ON before the query) and we can look at it some more.
Is there anything that you're retrieving from Table1? If not, here doesn't seem to be a need for it in the query, since you can order by the foreign key field instead.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Thanks for your reply
You can find execute plan in following link which comes after i write SET SHOWPLAN_ALL ON
http://www.keepandshare.com/doc/view.php?id=159907&da=y
Data are in Sheet1 & Sheet2
I have given you table1 & table2 just to make you understand my problem
In real scenario query actually connecting to around 10-12 tables & i have to include order by clause on one of field of that table
Field on which i am doing orderby is Primary key & it is cluster index
I have checked with query plan & all the tables are joining either by Index Scan OR Clustered Index Scan OR Cluster Index seek OR Index seek
-
Waiting for reply
-
I do have a full time job, as does just about everyone else who answers questions here. I'll look at your stuff when I have a few minutes free
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Ok... let's stop pussyfooting around... your original post, with the correct indexes on the columns, runs as fast with the Order By as without... it screams.
Now you throw the curve ball of having 10-12 table joins which will obviously change the whole shooting match.
Provided that it's not hundreds of lines long, post the code that you're having problems with... include the code for the indexes that are available on the tables.
And, be patient... like Gail said, we're helping out of the goodness of our hearts... we don't get paid to fix your stuff and we all have to make a living. Posting to a forum is not the same as having your own private consultant.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
OK, having had a careful look at your execution plan, the actualquery doesn't much resemble the earlier examples. As far as I can see, the actual query that you're having problems with is as follows (table names mangled)
SELECT TOP 500 AView.*, FirstTable.*
FROM AView
INNER JOIN FirstTable ON AView.aID = FirstTable.Value
INNER JOIN SecondTable ON SecondTable.TID = FirstTable.TID
LEFT JOIN ThirdTable ON FirstTable.CID = ThirdTable.UID
WHERE FirstTable.DID=5
A few points.
There's no reason for Table3 to be there at all. Its a left join, so isn't limiting the result set. You're not returning anything from that table, nor are you filtering on anything in that table. Take it out.
There's no order by statement. Without an order by, SQL will return the data in whatever order it feels like. Yout top 500 may be a different 500 at different times because of that. If you need an order in your result set, you must specify order by
Do you need every field in the view and the first table? That makes it very difficult to create a covering index and, from the exec plan, the bookmark lookups on Table1 and some of the tables in the view are a large portion of the cost of the query.
I saw very high subtree costs on some of the parallelism operations. If you add OPTION (MAXDOP 1) to the end of your query, does it run any faster?
Do you have indexes on the columns involved in the join ([sa_Thread].[sa_Index], [sa_Status].[sa_dbIndexValue], [archive_data].[archiveImageID]) If not, add and see if it helps. SQL's doing those joins as a many-to-many merge which doesn't look optimal to me.
Also, check the fragmentation of the clustered index on archive_image and on sa_thread.
If you have any success, let me know. I don't know how much mre help I ca offer without been able to play with the query.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply