September 6, 2002 at 12:13 pm
I have two tables (we'll call them tblA and tblB) Each table has a column called LinkID.
tblB also has a column called UserID.
tblB has a clustered index on UserID, a non clustered index on LinkID, and a Primary key index on LinkID, UserID and a few other indexes (including one with five columns which has the first two as UserID, LinkID). tblA has a clustered index (which is also the Primary Key) on LinkID.
I join the two tables like this:
SELECT *
FROM tblA
INNER JOIN tblB ON tblA.LinkID = tblB.LinkID
WHERE tblb.currentinfo >= 10000
When I look at the execution plan a see that both clustered indexes were used. The query takes about 6 seconds to run.
Why would the clustered index for tblB get used instead of the non clustered index that only contains the column LinkID? The column UserID is not used anywhere in the query.
Further, if I use an Index hint to force SQL Server to use the non clustered index then the same query takes only 3 seconds to run.
Can anyone give me reasons why SQL Server chooses the wrong index?
Robert Marda
SQL Server will deliver its data any way you want it
when you give your SQL Programmer enough developing time.
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems
September 6, 2002 at 1:39 pm
You may want to see if changing your sql to (below) is any better
SELECT *
FROM tblA
INNER JOIN tblB ON tblA.LinkID = tblB.LinkID
and tblb.currentinfo >= 10000
Steven
September 6, 2002 at 2:04 pm
That fixed it. Thanks!
Now, does anyone know why that would fix this problem?
Robert Marda
SQL Server will deliver its data any way you want it
when you give your SQL Programmer enough developing time.
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems
September 6, 2002 at 6:46 pm
There are dozens of factors that cause the query engine to make certain decissions. Generally it looks at the index with the most unique values and may consider the WHERE clause before it considers the ON to try and determine how to minimalize datasets. Is currentinfo part of the clustered index (doesn't have to be the first item), if so then the statistics for the clustered index may appear more unique, however it is not looking at the first column so the search takes longer. Now when you move it to the ON it may not even consider the currentinfo when deciding on what index to use. This is juts my thoughts and as I said it makes doazens of decisions to pick indexes and it could be some other reason. I usually try various ways of writing the query until I get the performance I expect.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
September 7, 2002 at 3:59 am
If I remember correctly (don't quote me)
adding 'and' to the 'on' statement is more ansi-92 compliant, which sql server is optimised for.
Steven
September 7, 2002 at 10:53 pm
The clustered index only has one column.
Robert Marda
SQL Server will deliver its data any way you want it
when you give your SQL Programmer enough developing time.
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems
September 8, 2002 at 9:37 pm
While all the above is fair and valid, I'm sure we have all struck instance where
the dynamic optimisation done by SQL is just not appropriate.
Yes, I am saying the query optimiser is not perfect - more to the point, you can over-ride it if you want.
Use the table hint (NDEX =) as in
SELECT * from table_a (index = ind_tableA)
etc.
May seem heavy handed, but certainly works and can be less hit and miss that reqwriting queries
September 9, 2002 at 8:54 am
I agree with you. SQL Server doesn't always choose the right execution plan and when I can't find a way to get it to choose the correct one then I have no problem with using any hint that will help.
Robert Marda
SQL Server will deliver its data any way you want it
when you give your SQL Programmer enough developing time.
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply