SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Short circuit SQL query


Short circuit SQL query

Author
Message
ken.trock
ken.trock
SSC Eights!
SSC Eights! (809 reputation)SSC Eights! (809 reputation)SSC Eights! (809 reputation)SSC Eights! (809 reputation)SSC Eights! (809 reputation)SSC Eights! (809 reputation)SSC Eights! (809 reputation)SSC Eights! (809 reputation)

Group: General Forum Members
Points: 809 Visits: 1730
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 Crazy 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
Lowell
Lowell
SSC-Dedicated
SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)

Group: General Forum Members
Points: 35813 Visits: 40244
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

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

GilaMonster
GilaMonster
SSC Guru
SSC Guru (114K reputation)SSC Guru (114K reputation)SSC Guru (114K reputation)SSC Guru (114K reputation)SSC Guru (114K reputation)SSC Guru (114K reputation)SSC Guru (114K reputation)SSC Guru (114K reputation)

Group: General Forum Members
Points: 114187 Visits: 45475
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, MVP, M.Sc (Comp Sci)
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


ken.trock
ken.trock
SSC Eights!
SSC Eights! (809 reputation)SSC Eights! (809 reputation)SSC Eights! (809 reputation)SSC Eights! (809 reputation)SSC Eights! (809 reputation)SSC Eights! (809 reputation)SSC Eights! (809 reputation)SSC Eights! (809 reputation)

Group: General Forum Members
Points: 809 Visits: 1730
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
Evil Kraig F
Evil Kraig F
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10657 Visits: 7660
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
Evil Kraig F
Evil Kraig F
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10657 Visits: 7660
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. Smile 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
GilaMonster
GilaMonster
SSC Guru
SSC Guru (114K reputation)SSC Guru (114K reputation)SSC Guru (114K reputation)SSC Guru (114K reputation)SSC Guru (114K reputation)SSC Guru (114K reputation)SSC Guru (114K reputation)SSC Guru (114K reputation)

Group: General Forum Members
Points: 114187 Visits: 45475
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, MVP, M.Sc (Comp Sci)
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


WayneS
WayneS
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12189 Visits: 10601
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 Crazy 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
Author - SQL Server T-SQL Recipes
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

ChrisM@home
ChrisM@home
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2715 Visits: 10539
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
ken.trock
ken.trock
SSC Eights!
SSC Eights! (809 reputation)SSC Eights! (809 reputation)SSC Eights! (809 reputation)SSC Eights! (809 reputation)SSC Eights! (809 reputation)SSC Eights! (809 reputation)SSC Eights! (809 reputation)SSC Eights! (809 reputation)

Group: General Forum Members
Points: 809 Visits: 1730
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search