E2 is eval. 1st in

Question

E2 is eval. 1st in

DavidReynolds

SSC Journeyman

Points: 86
More actions
November 26, 2003 at 2:44 am

#101774

in the following select with two expressions
... Where (E1) and (E2) ...
i'm finding that E2 is evaluating first, however its imperative that E1 is evaluated first.
Any ideas on how to force the evaluation sequece.
Additional info:
The full TSQL is:
SELECT T_Item.UD3
From T_Item inner join T_DocumentType on T_Item.DocumentTypeID = T_DocumentType.[Id]
WHERE ( T_DocumentType.[Id] = '{8511C9BA-8D2E-4D8C-A58E-B1016F4A9977}')
AND ( CAST(UD3 AS Integer) = 123456 )
------
UD3 contains numeric content only on records that have the specified document type. This is why the CAST must not be done first. In the SQL above i've simplified the expression used with the cast to an equals but its actually used to test that the value lies within a certain numeric range.
David Reynolds
Technical Lead
imagin' solutions
Scotland. UK.
Edited by - DavidReynolds on 11/26/2003 04:41:30 AM

David Reynolds
Technical Lead
imagin' solutions
Scotland. UK.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

mia Right there with Babe Points: 749 More actions · Answer 1

Could there be something about operator precedence that is making E2 evaluate before E1? The expressions should evaluate left to right unless operator precedence dictates otherwise.

Cheers,

mia

Life moves pretty fast. If you don't stop and look around once in a while, you could miss it.

Cheers,
mia

Life moves pretty fast. If you don't stop and look around once in a while, you could miss it.

Frank Kalis SSC Guru Points: 111183 More actions · Answer 2

AFAIK, there is no hint for you to force SQL Server to do this.

I also think this is the job of the optimizer.

Why is this a problem?

Couldn't you just rewrite the query?

Frank

http://www.insidesql.de

http://www.familienzirkus.de

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

DavidReynolds SSC Journeyman Points: 86 More actions · Answer 3

UD3 is an indexed column and is being scanned as the first step in SQL Server's execution plan. In effect causing E2 to execute before E1.

To get around this i've created a function that accepts a varchar and converts it to an integer. This has caused SQL Server to give it a higher cost.

The execution plan is now E1 and E2 -> what I was after.

mia -> thanks for pointing me in the right direction.

David Reynolds

Technical Lead

imagin' solutions

Scotland. UK.

David Reynolds
Technical Lead
imagin' solutions
Scotland. UK.

David Burrows SSC Guru Points: 65142 More actions · Answer 4

One way is to use CASE but there may be performance issues.

WHERE (CASE 

WHEN T_DocumentType.[Id] = '{8511C9BA-8D2E-4D8C-A58E-B1016F4A9977}') THEN 

(CASE WHEN CAST(UD3 AS Integer) = 123456 THEN 1 ELSE 0 END) 

ELSE 0 END) = 1

Far away is close at hand in the images of elsewhere.
Anon.

Frank Kalis SSC Guru Points: 111183 More actions · Answer 5

Just curious what is the rationale for this?

Sorry, too quick, I meant, why do you want it to be this way?

Frank

http://www.insidesql.de

http://www.familienzirkus.de

Edited by - Frank kalis on 11/26/2003 05:54:12 AM

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

DavidReynolds SSC Journeyman Points: 86 More actions · Answer 6

I've seen a few posts asking 'why' questions. After i posted the problem i realised i'd not given sufficient details and so 'edit'ed the entry. As i'm not sure if the edit has been broadcast i'm posting the expanded problem again FYI (cf below) if this still doesn't answer the Qs then let me know.

The reply with the solution that i posted recently includes the additional note that UD3 is an indexed field and this is scanned as the first step in the execution plan.

Thanks again to those who've contributed.

-------=-=-=-=-=----------

in the following select with two expressions

... Where (E1) and (E2) ...

i'm finding that E2 is evaluating first, however its imperative that E1 is evaluated first.

Any ideas on how to force the evaluation sequece.

Additional info:

The full TSQL is:

SELECT T_Item.UD3

From T_Item inner join T_DocumentType on T_Item.DocumentTypeID = T_DocumentType.[Id]

WHERE ( T_DocumentType.[Id] = '{8511C9BA-8D2E-4D8C-A58E-B1016F4A9977}')

AND ( CAST(UD3 AS Integer) = 123456 )

------

UD3 contains numeric content only on records that have the specified document type. This is why the CAST must not be done first. In the SQL above i've simplified the expression used with the cast to an equals but its actually used to test that the value lies within a certain numeric range.

David Reynolds

Technical Lead

imagin' solutions

Scotland. UK.

David Reynolds
Technical Lead
imagin' solutions
Scotland. UK.

Jeremy Kemp SSCertifiable Points: 6754 More actions · Answer 7

I remember once coming across a similar problem when I used functions in where clauses.

If I remember correctly (it was a long time ago) I needed to convert the parameter that was passed to the procedure into upper case. I initially wrote the query with the UPPER function in the where clause but this ran like a three legged dog. When I looked at the execution plan I realised that the query was not using the index (Frank can probably tell you the technical reason why).

I changed the proc to change the parms to upper case with SET statements outside of the query and then the query ran as expected.

If something similar is happening here, then you might try something like this:

SELECT T_Item.UD3

From T_Item inner join T_DocumentType on T_Item.DocumentTypeID = T_DocumentType.[Id]

WHERE ( T_DocumentType.[Id] = '{8511C9BA-8D2E-4D8C-A58E-B1016F4A9977}')

AND UD3 between <low_value> and <high_value>

and isNumeric(UD3) = 1

I've changed the UD3 test to separate steps to try and force it to use an index. If that doesn't work have you tried a subquery?

But then again, I could be barking up the wrong tree.

Jeremy

PS Apologies to all dog lovers out there.

DavidReynolds SSC Journeyman Points: 86 More actions · Answer 8

Hi Jeremy,

Thanks for your interest.

The "Between .. and .." perform an implicit conversion on the varchar data, which bumps up the cost and is therefore done after the 'isnumeric' operation.

Both the "isnumeric" and "between and" are performed as the first step in the exec. plan by scanning the index.

That's a good solution. I like the isnumeric test to avoid problems with bad data (not that there ever will be!).

David.

David Reynolds

Technical Lead

imagin' solutions

Scotland. UK.

David Reynolds
Technical Lead
imagin' solutions
Scotland. UK.