June 11, 2010 at 3:51 pm
scott.pletcher (6/11/2010)
Will need the original requestor to spell out specific rules on that to be truly accurate, since many variations are possible.
:laugh: I think that's kinda been the point since the start of the thread! :laugh:
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
June 11, 2010 at 4:03 pm
I think that's kinda been the point since the start of the thread!
For max variance only though.
I believe it is still possible to find the NextMonth before getting additional specs.
Scott Pletcher, SQL Server MVP 2008-2010
June 11, 2010 at 7:00 pm
scott.pletcher (6/11/2010)
For max variance only though. I believe it is still possible to find the NextMonth before getting additional specs.
It might be - to be honest I can't be bothered to think about it any more. 🙂
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
June 14, 2010 at 4:23 am
bteraberry (6/11/2010)
scott.pletcher (6/11/2010)
>> but when you post junior level code (non-SARG-able queries anyone?) <<Yes, I took the short-cut, because as *I* pointed out the code will result in full scan anyway.
When Lynn posted *exactly* the same comment about the scan *after* I did, you praised her for it.
You're just upset that I out-did you several times. Geez, get over it.
The only reason I'm upset is because I think you're a tool who is misleading people who are trying to learn SQL.
For example, you proposed a solution that included a CASE in a WHERE clause in that other thread and I called you on it. It's amateur coding because it makes it non-SARG-able. Your response was that it didn't matter because the query would result in a scan no matter how it was done. I pointed out how you were wrong and gave a specific example that resulted in a seek. This is just one example.
What is a problem with non-SARGable query? It is absolutely fine if you fully understand implication of its use. For example it is mostly "true" (still not always) that single index seek is much faster than full table scan (do you want an example when it is not - easy: table with a single row!), however if your query intention is select number of records over the tipping point of the related index, scan most likely will be more efficient. For example, you need the query to find all people with the surname ending 'ov' in the database from slavic country (it's very-very common ending of slavic surnames). You might have the clustered index on this column, which would make finding a person in the table very fast. But try to enforce the index seek on the required query, which most likeley would have " WHERE Surname LIKE '%ov' ". You will find that number of requried seeks will make the query slower than using single full table/index scan.
The performance tuning of the relational databases never was and never will be straight forward thing. There are some common tips, however almost always you will find some exclusions and some situations where even rules-of-thumb should not be applied.
All advanced performance tuning is done on "by-query" basis.
June 14, 2010 at 4:50 am
Eugene Elutin (6/14/2010)
What is a problem with non-SARGable query?
Interesting question.
By definition, SARGable means an index seek could be used. The key word there is 'could': a SARGable predicate gives the optimiser the choice of using a seek or scan. Assuming that a good cardinality estimate is available, the optimiser will typically make the right choice (though in practice it does tend to favour scans, for reasons which aren't important to my point).
As a general rule, it does seem preferable to use an equivalent SARGable construction where one is available, if only to maximise the options available to the optimiser.
I'm not certain that the example of a LIKE clause with a leading-wildcard was a great choice to demonstrate the point you were making. While it is certainly possible to arrange things so that logical expression might produce an index seek, it isn't straightforward by any means.
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
June 14, 2010 at 5:11 am
Paul White NZ (6/14/2010)
Eugene Elutin (6/14/2010)
What is a problem with non-SARGable query?...
I'm not certain that the example of a LIKE clause with a leading-wildcard was a great choice to demonstrate the point you were making.
....
Paul
The example was used just to demonstrate that queries intended to select large chunks of rows from a table might not benefit from using index seek.
What about other example:
Let say in the ETL process there is a temp table which is populated with a lot of data to be inserted into the final destination table. It might be the case that just a few rows in this table should be excluded based on some complicated business rule which could not be applied (for one or another reason) during the population of temp table. In this case, using CASE WHEN in SELECT, will be absolutely fine as we intend to select most of the records of temp table for inserting into destination table. Trying to enforce index seek here (even, if it's possible) would be quite stupid.
June 14, 2010 at 5:29 am
Eugene Elutin (6/14/2010)
The example was used just to demonstrate that queries intended to select large chunks of rows from a table might not benefit from using index seek.
Yup, I got that - it's just you chose a poor example, since leading-wildcard LIKE comparisons are not normally seekable anyway. A trailing-wildcard example would have worked better! (e.g. Surname LIKE 'Smith%' in many countries).
What about other example:
Let say in the ETL process there is a temp table which is populated with a lot of data to be inserted into the final destination table. It might be the case that just a few rows in this table should be excluded based on some complicated business rule which could not be applied (for one or another reason) during the population of temp table. In this case, using CASE WHEN in SELECT, will be absolutely fine as we intend to select most of the records of temp table for inserting into destination table. Trying to enforce index seek here (even, if it's possible) would be quite stupid.
Actually, I'm not sure that's a brilliant example either. SSIS is the natural choice for ETL, rather than temporary tables and T-SQL.
There's nothing too wrong with the point you're trying to make - a scan can be preferable to a seek in some circumstances.
My point is simply that a SARGable expression does not preclude a scan, and the optimiser is very good at spotting when a scan might be more efficient than a seek. Given a choice between writing a SARGable and non-SARGable expression, all things being equal I would choose the SARGable form.
Paul
edit: omitted the vital word 'not' :rolleyes:
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
June 14, 2010 at 5:58 am
Paul White NZ (6/14/2010)
... SSIS is the natural choice for ETL, rather than temporary tables and T-SQL....
I would say that "SSIS is the Microsoft choice for ETL" 😀
Depending on source, there are sometimes much better options for "E".
Depending on what is required in "T", T-SQL with temp tables can easly outplay SSIS.
If, somehow, your transformed source data available in memory - memory based bulk copy for "L".
Paul White NZ (6/14/2010)
...There's nothing too wrong with the point you're trying to make - a scan can be preferable to a seek in some circumstances.
...
Actually my point is:
If you understand what you're doing, writing non-sargable query is absolutely fine based on the fact that a scan can be preferable to a seek in some circumstances.
I do agree with you that writing SARGable query is a preferred way in most of everyday cases.
P.S. Actually, how to make use of index if you need the list of people with surname ending with something ('%ov')? - Have another indexed column where the surname is stored in the reveresed way so you could write LIKE 'vo%' 😀
June 14, 2010 at 7:10 am
Eugene Elutin (6/14/2010)
I would say that "SSIS is the Microsoft choice for ETL"
Mine too, most of the time.
...how to make use of index if you need the list of people with surname ending with something ('%ov')? - Have another indexed column where the surname is stored in the reveresed way so you could write LIKE 'vo%' 😀
That's one way, yes (indexed non-persisted computed column). There's an article on here somewhere that does exactly that.
There are other 'creative' ways to achieve similar effects.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 9 posts - 61 through 68 (of 68 total)
You must be logged in to reply to this topic. Login to reply