Query on 16 millions rows -> 20 ms. vs Query on 4 millions rows -> 1 ms.

Question

Post reply

Query on 16 millions rows -> 20 ms. vs Query on 4 millions rows -> 1 ms.

Viewing 7 posts - 16 through 21 (of 21 total)

You must be logged in to reply to this topic. Login to reply

Luke L SSC-Dedicated Points: 35545 More actions · Answer 1

OK so I was wondering, how is this query being executed? The only reason why I ask is the implicit conversions that are occurring. I understand the datetime one. But I don't understand the Implicit conversion to an Int for Param 1.

Does your query handle that as

where stat_no = 11

Or

where stat_no = '11'

Granted it shouldn't make that much of a difference, but every little bit helps right? When I use an integer column as a predicate, I only see the conversion with the second example because it's being passed as a character string instead of an integer.

-Luke.

To help us help you read this[/url]For better help with performance problems please read this[/url]

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 2

Hi Carl,

I hope I haven't wasted your time here, I've got some test data on my machine and I'm gonna try and play around but I think what you have is ok.

Here is a good article that Gail wrote about Index Seeks and Index Scans and how they can be misleading sometimes, I haven't actually checked to see if this applies to your code yet , but it might be worth you checking it out:

http://sqlinthewild.co.za/index.php/2009/03/05/when-is-a-seek-actually-a-scan/

----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley

:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]

SQL-4-Life

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 3

OK time for some sleep, I've lost my marbles... Sorry Carl, I'll try have another look at this next week if you still need.

(Bookmark lookups don't happen with Clustered Indexes[hence there isn't one here] - Please tell me I'm right 🙂 )

Have a good week-end

----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley

:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]

SQL-4-Life

Lynn Pettis SSC Guru Points: 442447 More actions · Answer 4

Christopher Stobbs (5/22/2009)

OK time for some sleep, I've lost my marbles... Sorry Carl, I'll try have another look at this next week if you still need.

(Bookmark lookups don't happen with Clustered Indexes[hence there isn't one here] - Please tell me I'm right 🙂 )

Have a good week-end

Correct. The leave nodes of the Clustered Index are the datapages, not need to "go to" them to access the data.