Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Short circuit SQL query Expand / Collapse
Author
Message
Posted Monday, December 6, 2010 11:07 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, December 8, 2014 1:29 PM
Points: 323, Visits: 1,475
Hello. I pull data from a SQL 2000 environment via SSIS into a 2008 DB of ours.

Select Distinct RCD.Variable4 
FROM Route_Call_Detail RCD
WHERE RCD.[DateTime] > '12/4/10' AND RCD.[DateTime] < '12/5/10'
AND SUBSTRING(RCD.Variable4, 1, 6) NOT LIKE '%[^0-9]%' --Thanks to Jeff Moden for this
--AND CONVERT(INT, SUBSTRING(RCD.Variable4, 1, 6)) BETWEEN 5011 AND 7999

This works fine, purely numeric values returned for Variable4 1st 6 chars. Uncomment that last line and I get this error:

Syntax error converting the varchar value 'AZ' to a column of data type int.

There are rows where Variable4 = 'AZ' but I thought those would be short circuited by the statement above. It works in test Anyhow, I tried putting this in a case statement like some people have offered but no luck. Keep in mind the query runs in 2000 before getting over to us (we're fortunately not the keepers of the 2000 data).

Thanks,
Ken
Post #1030743
Posted Monday, December 6, 2010 11:19 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 9:42 AM
Points: 12,957, Visits: 32,491
i think you'll just have to wrap a section of the query into a subquery, since the data has to be filtered on the LIKE statement first;

because the second part uses a function (CONVERT)...it's not SARG-able, so a short circuit base don the index seeks cannot occur, right?

SELECT * FROM (
Select
Distinct RCD.Variable4
FROM Route_Call_Detail RCD
WHERE RCD.[DateTime] > '12/4/10'
AND RCD.[DateTime] < '12/5/10'
AND SUBSTRING(RCD.Variable4, 1, 6) NOT LIKE '%[^0-9]%' --Thanks to Jeff Moden for this
) MyAlias
WHERE CONVERT(INT, SUBSTRING(RCD.Variable4, 1, 6)) BETWEEN 5011 AND 7999





Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1030745
Posted Monday, December 6, 2010 11:48 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 8:36 AM
Points: 40,615, Visits: 37,080
SQL makes no guarantee as to what order expressions will be evaluated in and, as such, one cannot assume that any form of 'short-circuiting' will always occur, especially when dealing with data columns and not just constant expressions.


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1030756
Posted Monday, December 6, 2010 11:54 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, December 8, 2014 1:29 PM
Points: 323, Visits: 1,475
Lowell (12/6/2010)
i think you'll just have to wrap a section of the query into a subquery, since the data has to be filtered on the LIKE statement first;

because the second part uses a function (CONVERT)...it's not SARG-able, so a short circuit base don the index seeks cannot occur, right?

SELECT * FROM (
Select
Distinct RCD.Variable4
FROM Route_Call_Detail RCD
WHERE RCD.[DateTime] > '12/4/10'
AND RCD.[DateTime] < '12/5/10'
AND SUBSTRING(RCD.Variable4, 1, 6) NOT LIKE '%[^0-9]%' --Thanks to Jeff Moden for this
) MyAlias
WHERE CONVERT(INT, SUBSTRING(RCD.Variable4, 1, 6)) BETWEEN 5011 AND 7999





Makes sense but surprisingly it didn't change anything.

Ken
Post #1030762
Posted Monday, December 6, 2010 11:55 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Monday, December 15, 2014 2:26 PM
Points: 5,466, Visits: 7,647
You've run into a common issue, and it's a natural one to make. The Where clause limits the data, then we do the rest...

Sadly, this isn't true. You can't make the optimizer run the WHERE first. Not directly. You have no control after you declare the results you want without hinting. Note, this is key. You declare the results you want, you don't give SQL a process, not directly.

The two ways out of this are to build a subquery with the where clause before you do any conversion and then use the OPTION (FORCE ORDER) tag (which I don't recommend without thorough knowledge of the impact), or to drop the data you're going to process to a #TMP and then do conversions after that.



- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Post #1030763
Posted Monday, December 6, 2010 11:57 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Monday, December 15, 2014 2:26 PM
Points: 5,466, Visits: 7,647
ken.trock (12/6/2010)
Lowell (12/6/2010)
i think you'll just have to wrap a section of the query into a subquery, since the data has to be filtered on the LIKE statement first;

because the second part uses a function (CONVERT)...it's not SARG-able, so a short circuit base don the index seeks cannot occur, right?

SELECT * FROM (
Select
Distinct RCD.Variable4
FROM Route_Call_Detail RCD
WHERE RCD.[DateTime] > '12/4/10'
AND RCD.[DateTime] < '12/5/10'
AND SUBSTRING(RCD.Variable4, 1, 6) NOT LIKE '%[^0-9]%' --Thanks to Jeff Moden for this
) MyAlias
WHERE CONVERT(INT, SUBSTRING(RCD.Variable4, 1, 6)) BETWEEN 5011 AND 7999





Makes sense but surprisingly it didn't change anything.

Ken


Heh, what I get for leaving the window open a bit. :) Use OPTION (FORCE ORDER) at the tail of that, last piece of the statement.



- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Post #1030764
Posted Monday, December 6, 2010 12:00 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 8:36 AM
Points: 40,615, Visits: 37,080
Or, as an (untested) alternative.
Select 
Distinct RCD.Variable4
FROM Route_Call_Detail RCD
WHERE RCD.[DateTime] > '12/4/10'
AND RCD.[DateTime] < '12/5/10'
AND CASE WHEN SUBSTRING(RCD.Variable4, 1, 6) NOT LIKE '%[^0-9]%' THEN CONVERT(INT, SUBSTRING(RCD.Variable4, 1, 6)) ELSE NULL END BETWEEN BETWEEN 5011 AND 7999
)




Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1030767
Posted Monday, December 6, 2010 12:41 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:58 AM
Points: 5,370, Visits: 9,010
ken.trock (12/6/2010)
Hello. I pull data from a SQL 2000 environment via SSIS into a 2008 DB of ours.

Select Distinct RCD.Variable4 
FROM Route_Call_Detail RCD
WHERE RCD.[DateTime] > '12/4/10' AND RCD.[DateTime] < '12/5/10'
AND SUBSTRING(RCD.Variable4, 1, 6) NOT LIKE '%[^0-9]%' --Thanks to Jeff Moden for this
--AND CONVERT(INT, SUBSTRING(RCD.Variable4, 1, 6)) BETWEEN 5011 AND 7999

This works fine, purely numeric values returned for Variable4 1st 6 chars. Uncomment that last line and I get this error:

Syntax error converting the varchar value 'AZ' to a column of data type int.

There are rows where Variable4 = 'AZ' but I thought those would be short circuited by the statement above. It works in test Anyhow, I tried putting this in a case statement like some people have offered but no luck. Keep in mind the query runs in 2000 before getting over to us (we're fortunately not the keepers of the 2000 data).

Thanks,
Ken


Ken,

A LIKE that starts with a '%' won't be able to utilize an index on the column, which may make a noticeable impact. Since you want the first six characters to be digits, you might want to change it to:
AND RCD.Variable4 LIKE '[0-9][0-9][0-9][0-9][0-9][0-9]%'

which is SARGable and thus will utilize an index.

This might also fix the issue you were having... if sql is already doing a scan on the table because of this, it would just go ahead and compare the other column at the same time. You would probably still need to put it into a CTE or sub-query as Lowell showed you.


Wayne
Microsoft Certified Master: SQL Server 2008
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links: For better assistance in answering your questions, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings
Post #1030797
Posted Monday, December 6, 2010 12:49 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 10:20 AM
Points: 1,037, Visits: 7,023
You're checking the first six characters are digits, so why not:
WHERE LEFT(RCD.Variable4, 4) BETWEEN '5011' AND '7999'
which can potentially make use of an index?



Low-hanging fruit picker and defender of the moggies





For better assistance in answering your questions, please read this.




Understanding and using APPLY, (I) and (II) Paul White

Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Post #1030799
Posted Monday, December 6, 2010 1:02 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, December 8, 2014 1:29 PM
Points: 323, Visits: 1,475
GilaMonster (12/6/2010)
Or, as an (untested) alternative.
Select 
Distinct RCD.Variable4
FROM Route_Call_Detail RCD
WHERE RCD.[DateTime] > '12/4/10'
AND RCD.[DateTime] < '12/5/10'
AND CASE WHEN SUBSTRING(RCD.Variable4, 1, 6) NOT LIKE '%[^0-9]%' THEN CONVERT(INT, SUBSTRING(RCD.Variable4, 1, 6)) ELSE NULL END BETWEEN BETWEEN 5011 AND 7999
)



Yes, this works! I was trying to do something like this myself but couldn't quite get there. Thanks everyone who replied.

Ken
Post #1030807
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse