October 24, 2008 at 3:17 pm
Lynn Pettis (10/24/2008)
Then I'd try this:
select
obitno,
oaorsc
from
hsdet
inner join hshed
on (obhssq = oahssq
and oainvd > 20050100)
where
oaorsc = right('0' + cast(@wh as varchar), 2)
😎
That was awesome. Thanks. Works like a charm.
October 24, 2008 at 3:17 pm
Can you do this?
DECLARE @wh char(2)
SET @wh = '1'
SET @WH = CASE WHEN LEN(@WH) = 1 THEN '0' + @WH ELSE @WH END
select obitno, oaorsc
from hsdet inner join hshed on obhssq=oahssq
where oainvd>20050100
and oaorsc=@wh
Granted, it doesn't answer your initial question as to why it didn't work, but it should make it work.
October 24, 2008 at 3:18 pm
Garadin (10/24/2008)
Can you do this?
DECLARE @wh char(2)SET @wh = '1'
SET @WH = CASE WHEN LEN(@WH) = 1 THEN '0' + @WH ELSE @WH END
select obitno, oaorsc
from hsdet inner join hshed on obhssq=oahssq
where oainvd>20050100
and oaorsc=@wh
Granted, it doesn't answer your initial question as to why it didn't work, but it should make it work.
Yours works too. Thanks alot.
Yes, I would still like to know why I was getting that conversion error, but I'll not lose any sleep over it.
October 24, 2008 at 3:19 pm
Lynn Pettis
Then I'd try this:select
obitno,
oaorsc
from
hsdet
inner join hshed
on (obhssq = oahssq
and oainvd > 20050100)
where
oaorsc = right('0' + cast(@wh as varchar), 2)
Drat! I knew I should have posted that like 6 posts ago. :hehe:
October 24, 2008 at 3:22 pm
Me too! Our problem was we were focused on trying to figure out why the query wasn't working instead of finding an alternative that would.
😎
October 24, 2008 at 3:26 pm
Yeah, and it'll still bug me until I figure out why it's not working. I thought it might have been similar to the tally issue from yesterday, but so far testing of that nature is yielding no results. It seems like an important thing to know as I'm just starting to work with CTE's.
October 24, 2008 at 3:27 pm
Okay, freaky!! That's where my thoughts were also!
😎
October 24, 2008 at 3:38 pm
select obitno, oaorsc
from hsdet inner join hshed on obhssq=oahssq
where oainvd>20050100
and convert(int,oaorsc)=@wh
The reason that this does not work is because the order that clauses appear in the WHERE section does NOT determine their order of evaluation at execution time. The optimizer determines that.
This is pretty much standard in all programming languages that have an optimizer (which is to say, virtually all modern compiled languages, including SQL).
As already shown, one way around this is to make a derived table source to filter out the invalid records before it gets to the WHERE clause.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
October 24, 2008 at 3:42 pm
Another note: Although AND branches are not order sensitive in SQL (thus leading to this problem), CTE clauses are order sensitive (because later clauses can only refer to earlier clauses).
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
October 24, 2008 at 3:53 pm
Just so I can try and understand more...
If I rewrite it to the following:
declare @wh varchar(2)
set @wh='10';
select obitno, oaorsc
from
(select obitno, oaorsc
from hsdet inner join hshed on obhssq=oahssq
where oainvd>20050100) as DerivedHistTable
where convert(int,oaorsc)=@wh
I still get a conversion error. Am I doing something wrong with this script?
October 24, 2008 at 4:04 pm
rbarryyoung (10/24/2008)
select obitno, oaorsc
from hsdet inner join hshed on obhssq=oahssq
where oainvd>20050100
and convert(int,oaorsc)=@wh
The reason that this does not work is because the order that clauses appear in the WHERE section does NOT determine their order of evaluation at execution time. The optimizer determines that.
This is pretty much standard in all programming languages that have an optimizer (which is to say, virtually all modern compiled languages, including SQL).
As already shown, one way around this is to make a derived table source to filter out the invalid records before it gets to the WHERE clause.
That much I understand. What I didn't get is why the CTE example didn't work after he added in the ISNUMERIC = 1. I think of CTE's as prettymuch derived tables that live in memory for the duration of the query (this could be completely off, I haven't actually read that anywhere, that's just what they seem to do).
So, to test it out, I did the following:
DECLARE @a Table(
AIDint identity(1,1),
WHchar(2))
INSERT INTO @a(WH)
SELECT 'XX' UNION ALL
SELECT 'YY' UNION ALL
SELECT 'ZZ' UNION ALL
SELECT '01' UNION ALL
SELECT '02' UNION ALL
SELECT ' ' UNION ALL
SELECT '03'
;WITH AI(AID, WH)
AS (SELECT AID, WH
FROM @a
WHERE AID > 3 AND ISNUMERIC(WH) = 1)
SELECT *
FROM AI
WHERE Convert(int,WH) = 3
This actually worked fine, and returned no errors. However, doing it exactly the same way the OP did it (without the CTE) worked fine in this case as well. (Even without the ISNUMERIC). That can probably be explained by the optimizer choosing to evaluate my > first with such a small data set, but the question with CTE's remains.
October 24, 2008 at 4:11 pm
adams.squared (10/24/2008)
If I rewrite it to the following:
declare @wh varchar(2)set @wh='10';
select obitno, oaorsc
from
(select obitno, oaorsc
from hsdet inner join hshed on obhssq=oahssq
where oainvd>20050100) as DerivedHistTable
where convert(int,oaorsc)=@wh
I still get a conversion error. Am I doing something wrong with this script?
Nope, my bad: I forgot that in SQL, the optimizer can work across table source boundaries also. So technically, even Lynn's solution is not guaranteed to work either.
So, let me check ... yes, the SOP way to address this in SQL Server is to hide it behind a CASE function:
declare @wh varchar(2)
set @wh='10';
Select obitno, oaorsc
From hsdet inner join hshed on obhssq=oahssq
Where oainvd>20050100
And @wh = (Select Case oainvd>20050100
Then convert(int,oaorsc)
Else NULL End)
Hmm, you might be able to improve that some ... 🙂
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
October 24, 2008 at 4:18 pm
Garadin (10/24/2008)
rbarryyoung (10/24/2008)
The reason that this does not work is because the order that clauses appear in the WHERE section does NOT determine their order of evaluation at execution time. The optimizer determines that....
As already shown, one way around this is to make a derived table source to filter out the invalid records before it gets to the WHERE clause.
That much I understand. What I didn't get is why the CTE example didn't work after he added in the ISNUMERIC = 1. I think of CTE's as prettymuch derived tables that live in memory for the duration of the query (this could be completely off, I haven't actually read that anywhere, that's just what they seem to do).
This is also due to the same mistake I mentioned above: The optimizer can cross table source boundaries also.
So my statement about the order of CTE's clauses was only half true: syntactically order is important, but semantically, the optimizer doesn't care and can reorder derived table lookups and other table source retrieval work as it sees fit.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
October 24, 2008 at 4:29 pm
Ahhhh. I get it. I QUIT... but I get it. (I don't even understand that commercial and I just used it. :ermm: )
So... to summarize:
Performing an operation that the optimizer HAS to take first, such as selecting all the data into a temp table or table variable would have avoided it.
However, anything that is done in a single operation(ie. Derived Tables or CTE's) can fall prey to this issue depending on how the optimizer chooses to run.
October 24, 2008 at 4:49 pm
Garadin (10/24/2008)
So... to summarize:Performing an operation that the optimizer HAS to take first, such as selecting all the data into a temp table or table variable would have avoided it.
However, anything that is done in a single operation(ie. Derived Tables or CTE's) can fall prey to this issue depending on how the optimizer chooses to run.
Close. One thing that you can be sure off in SQL Server is that the Optimizer never crosses procedural boundaries. This is why user-defined functions (other than in-line TVF's) cannot be optimized, and it is a big part of what makes RBAR so RBAR.
However, the optimizer does not have complete free reign within a single statement, there are some boundaries that it must respect. The two that I can think of of the top of my head are:
1) The Major SELECT Clauses: The major clauses of the SELECT statement (FROM, WHERE, ORDER BY, etc...) have a formally defined order of execution, which the optimizer must "respect".
2) CASE Function Clauses: The WHEN's in a CASE statement do have to be evaluated in the order that they appear. Further, the THEN clauses can only be executed if the corresponding WHEN clause matches. The optimizer must also "respect" these orders.
Now when I say that the optimizer must "respect" these orderings, I do not mean that it cannot not reorder them at all: it can, but only insofar as it can be certain that it will still return the exact same functional results, including whether an error is generated or not. (Note that performance is an operational result, and not a functional result, so obviously the optimizer can reorder things to change the performance)
So, while the optimizer can reorder AND clauses irrespective of whether or not that may cause data errors, it cannot execute calculations from the SELECT column list before the WHERE clause filtering if that might cause a similar data error or change the contents of the result set in any way.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 15 posts - 16 through 30 (of 35 total)
You must be logged in to reply to this topic. Login to reply