Can't remember the term 1

[TysonLPrice]

I've seen postings on and am trying to research tuning stored procedures by using what I thought was called scalibility in where clauses. I can't remember an exact scenario but it's something like if you use '01/01/2010' versus GetDate(). It's along the lines that the compiler doesn't know what the value will be and can't create an effective execution plan. Any thoughts?

 

[gmmastros]

If you're talking about innefficient execution plans, you probably mean "parameter sniffing".

Or, you could be talking about "sargable" queries.

Let me know of you wante to explain either (or both) of these things.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[TysonLPrice]

Or, you could be talking about "sargable" queries

That was it...I'm going to Google it but I would appreciate your thoughts on it.

 

[gmmastros]

In another thread, I wrote...

Whenever you have a where clause that performs a function on column data, you will get a table/index scan. This means that each row will have to be evaluated to see if it should be filtered out or included in the results.

When the query engine can use an index, it will identify the records that need to get deleted a lot faster.

For further reading.... SQL Server Sargable

Think of it this way. Suppose someone handed you a dictionary and told you to highlight (with a marker) all words that begin with 'tr'. What would you do? You would flip through the pages (very quickly) and find the first word that starts with TR. You would start highlighting them until you get to the first word that does NOT start with TR.

Now, imagine someone hands you a dictionary and says, highlight each word that contains TR. The only way to do this would be to start at the beginning and examine EVERY word. Obviously this would take a lot longer to accomplish.

By making the query sargable, it will use an existing index to identify the records faster, thus causing the entire operation to take less time. This ONLY works if there is an index.

For example, if someone hands you a 'normal' book and told you to highlight the words that start with TR, you would again have to examine every word. Again... this would take a long time to accomplish.

Make sense?

Brent Ozar also explains it here:

http://www.brentozar.com/archive/2010/06/sargable-why-string-is-slow/

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

SQLDenis also has this to say about it:

http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/only-in-a-database-can-you-get-1000-impr

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[TysonLPrice]

Thanks for the start...

 

[markros]

Just an addition - use ISO format for dates.
Check these two links
The ultimate guide to the datetime datatypes
Bad habits to kick : mis-handling

date / range queries

PluralSight Learning Library