Indexes
-
mohaiv (9/8/2009)
I don't belive it...The server can use index when the first part of the index is in the where clause.
I try it and MS SQL server realy use index on query 2,3,4 and in case number 1 don't (because no index with ch in the first part). And I only use this seven records from the sample.
Regards,
Moha
I didn't either, that's why I got it wrong. Here's the execution plan from running the query (with the replacement of "bool" type with CHAR(1)) in SQL2005
-
Sorry, I try on SQL 2000. Here is the plan:
But I understand now what happend on SQL2005 so thanks for you answer.
Now I see that why your choice is correct.
Moha
-
'Correct' is a relative term with this question...but the discussion was fascinating in places.
A good question, from that point of view 🙂
I wouldn't worry about it too much - it's not as if there are cash prizes, just 2 SSC points!
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi -
Aren't SSC points better than a cash prize?! 😀
-
I agree with you and i sorry my 2 points too 🙂
-
mohaiv (9/8/2009)
I don't belive it...The server can use index when the first part of the index is in the where clause.
I try it and MS SQL server realy use index on query 2,3,4 and in case number 1 don't (because no index with ch in the first part). And I only use this seven records from the sample.
Regards,
Moha
This was my initial thought too, for the same reason, that the index starts with ID which is not in the WHERE clause. It seems odd to me also that query 4 causes a table scan, because you would think that the query engine could utilize the ID and CH criteria from the WHERE clause against the index to limit the number of rows it has to scan from the table itself, since ID and CH columns seem to have high selectivity.
-
Yep .. that's exactly what I thought 🙂
-
But, On SQL Server 2005 sp2 I see table scan in both query 1 and query 4 in execution plan.
I think I had to go through all the discussion to get to the cause of why query 4 only.
Lost 2 points.
SQL DBA.
-
It can and will do just that when it decides that is the least amount of work. But remember, whenever you use an index and have to get data from the primary table, they have to be "joined" (by a nested loop etc) which adds to the workload of using an index.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills -
Sorry I am jumping on this one late... I replaced the BOOL data type with Char and received the correct answer which was 4. That is the only one that performed a table scan for me. Was there ever a BOOL data type in SQL??
-
As you all pointed out Bool does not exist in SQL Server, and I replaced it with a char(1). In my environment (SQL server 2008 on a server with 4 quad processors 32Gb memory and a fast SAN) I actually got two table scans, query 1 and 4. My experience is that the query plan depends on :
- The environment
- The indexes
- The statistics
- The workload of the server
- The query of course
- The data
- The cache
And this makes it hard to predict the actual query plan since SQL will always try to use the most optimized query plan, and it is a good idea to leave it that way and not trying to force a query plan with hints. If you force a query plan that you think is the most optimized, you may end up with a slow query when anyhting changes, like the amount of data in the table. In this case query 4 may end up with a index seek and a key lookup.
/Håkan Winther
MCITP:Database Developer 2008
MCTS: SQL Server 2008, Implementation and Maintenance
MCSE: Data Platform -
hakan.winther (9/22/2009)
My experience is that the query plan depends on :- The cache
:blink: How?
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi -
Spend enough cache and you can buy a better query plan?
(Sorry, Emperor Paulpatine. I just couldn't resist.)
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills -
Bob Hovious 24601 (9/22/2009)
Spend enough cache and you can buy a better query plan?(Sorry, Emperor Paulpatine. I just couldn't resist.)
:laugh: You get worse Bob! :laugh:
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi -
My reaction on looking at this was that there is so little data in the table that it would be a bug in the optimiser if it did anything other than a table scan for any of them. Then I saw the choices available for answer, which didn't include all four queries, so I had to take a guess. I decided to base my guess on what I thought it would do if there were a lot more data in the table. Q2 and Q3 would obviously be partial index scans if there were a non-trivial amount of data in the table, so that left Q1 and Q4 and Q4 could be done using the index to get candidate rows and and then filtering those looking at the table data, and if the index data specified were sufficiently selective it seemed pretty certain the optimiser would do that. Q1 could be done with a partial index scan as well, and that too seemed the reasonable approach - so I mentally tossed a coin and picked Q1. And found the answer given was Q4. So I stuck it on my mick-mouse laptop, corrected the SQL in one of the several possible ways (what's "bool"? should it be "bit" or should it be "char(1)" or should it be "tinyint" or should it be something else) and ran it. Didn't get a table scan for 4, even after adding extra rows and rebuilding the index. So I think there are some environmental things issuing the result (like which RDBMS you are using - the one that understands "bool" apparently gives different results from the one I am using).
Tom
Viewing 15 posts - 31 through 45 (of 46 total)
You must be logged in to reply to this topic. Login to reply