Where clause in sql request should be resolved last

Question

I've got a SQL request that takes very long time to execute. So I want to make it better but don't known how to do it. Here is an example: My request:

SELECT * from T1
Inner join T2 on T1.a = T2.b
Inner join T3 on T2.c = T3.d
WHERE 1=1
AND T2.e = 'a certain value'  --I call i the Clause1
and dbo.MyUDF(T1.id) = 1      --I call i the Clause2

It seems that the problem comes from the UDF call. Running the request without Clause1 and Clause2 will give me 2500rows taking 7 sec.

Running the request without Clause2 will give me 16 rows taking 9 sec.

running the request with all 2 clauses will give me 15 rows taking 1:45 min.

but calling MyUdf 16X in a cursor will take 9Seconds.

declare curs cursor
for SELECT T1.id from T1
Inner join T2 on T1.a = T2.b
Inner join T3 on T2.c = T3.d
WHERE 1=1
AND T2.e = 'a certain value'
open curs
fetch next from curs into @fid
while(@@FETCH_STATUS = 0)
            BEGIN
                select dbo.MyUdf(@fid)
                fetch next from curs into @fid
            END

close curs
deallocate curs

So it seems that de SQL Engine test de all 2500 rows with the UDF and the runs Clause1. And I would like that it does the other so the UDF will be called only on 16 rows.

any idea ?

--Edit-- Having a look at the execution plan, It tells that my UDF will not use very mutch. So I think it always take it first. So I need to tell sql server that this part of the request is the worst and that it must take it at last steap. Do you know how to do that ?

You need to post the contents of `dbo.MyUDF`. SQL is Set based; modular/OO programming concepts (more often than not) do not apply. — OMG Ponies, Jun 22 '10 at 16:08
Ben S: I got 1=1 in each query because the queries are generated by an engine and it the easy way to not check if there is already a condition. — Pitming, Jun 22 '10 at 16:19
The UDF is used to filter result. It contains the rules of confidentiality for every items. And it sometimes do recursion with the same UDF. — Pitming, Jun 22 '10 at 16:20

score 4 · Answer 1 · answered Jun 22 '10 at 16:10

a function in a WHERE clause is not SARGable, the optimizer will do a scan since it can't determine what the function returns

If possible duplicate the code from the function in your where clause and it should run much faster

it is the same reason that something like this

WHERE YEAR(DateColumn) = 2008

is much slower than

WHERE DateColumn >= '20080101'
AND DateColumn <'20090101'

The first one will cause a scan, the second one could cause a seek (if you have indexes)

See also Only In A Database Can You Get 1000% + Improvement By Changing A Few Lines Of Code

Thanks but I know that. But the code in my udf is full of rules and I can't copy past it here. Also because it's used by other part of code/DB and don't want to have an ugly maintenance to do — Pitming, Jun 22 '10 at 16:24

Thomas · Answer 2 · 2010-06-22T21:08:23.177

2

First, as others have said, I would not try to encapsulate business logic into a UDF since there are many times where you will be tempted to use the UDF in an ON or WHERE clause as you have in your OP. However, one solution would be to encapsulate the faster portion of the query into a CTE table like so:

With FasterResults As
    (
    Select T1.id, ...
    From T1
        Join T2 
            On T2.b = T1.a
        Join T3 
            On T3.d = T2.c
    Where 1=1
        And T2.e = 'a certain value'
    )
Select
From FasterResults As F
Where dbo.MyUDF(F.id) = 1

Another solution (although far from ideal) would be to use the FORCE ORDER query hint:

Select T1.id, ...
From T1
    Join T2 
        On T2.b = T1.a
            And T2.e = 'a certain value'
    Join T3 
        On T3.d = T2.c
    Join T1 As T12
        On T12.a = T1.a
            And dbo.MyUDF(F.id) = 1
Where 1=1
OPTION (FORCE ORDER)

edited Jun 22 '10 at 21:08

answered Jun 22 '10 at 16:36

Thomas

63,911
12
95
141

I *think* you're right. I was just about to post this, but I was going to try it first. Are you sure that the query optimizer is going to run the two filters sequentially, and not optimize it all into one? I was thinking that the reason that UDF's aren't allowed side effects is to allow the optimizer to do things like that. – Chris Wuestefeld Jun 22 '10 at 16:44
1

There is no guarantee that your first solution will work long term. You cannot make such assumptions – A-K Jun 22 '10 at 18:47
Also "FORCE ORDER Specifies that the join order", so it only applies to join order, not to order of predicates evaluation. – A-K Jun 22 '10 at 18:48
@AlexKuznetsov - Yep I see that. However, you can easily work around that by putting the criteria in another join which is last. Still, it isn't even remotely a good solution. Refactoring the UDF into an inline UDF or view would be a better solution. – Thomas Jun 22 '10 at 21:09

score 1 · Answer 3 · answered Jun 22 '10 at 16:21

1

Short term, use this:

SELECT * from T1
Inner join T2 on T1.a = T2.b
Inner join T3 on T2.c = T3.d
WHERE 1=1
AND T2.e = 'a certain value'  --I call i the Clause1
and CASE WHEN T2.e = 'a certain value' 
  THEN dbo.MyUDF(T1.id) 
  ELSE 1 
END = 1

Long term, consider using inline UDF instead of a scalar one.

answered Jun 22 '10 at 16:21

A-K

16,804
8
54
74

The problem is that my request is generated by an engine and it's very hard to write something like your example. – Pitming Jun 22 '10 at 16:25

score 0 · Answer 4 · edited May 23 '17 at 12:11

Not sure but could you but in an embedded if statement within the WHERE clause. This was already asked on stackoverflow here.

This is just pseudocode but maybe something like:

SELECT * from T1
Inner join T2 on T1.a = T2.b
Inner join T3 on T2.c = T3.d
WHERE 1=1
AND 
  IF T2.e = 'a certain value'
    THEN 
        T2.e = 'a certain value'  --I call i the Clause1
        and dbo.MyUDF(T1.id) = 1      --I call i the Clause2

score 0 · Answer 5 · answered Jun 22 '10 at 16:10

I would change the query around to this... since you are doing inner joins..

select T1.*
   from T2
      inner join T1
         on t2.b = t1.a
      inner join T3
         on t2.c = t3.d
   where
          t2.e = 'a certain value'
      and dbo.MyUDF( T1.id ) = 1

Since the t2 is the primary table of the WHERE clause, I would have that as my primary From source, and link in the OTHER tables since the inner join includes all 3 anyway. THEN, tack on your UDF() call. Ignore your where 1=1, its never applicable as it always returns true.

score 0 · Answer 6 · answered Jun 23 '10 at 08:26

First of all, thanks to everybody. I learn very mutch with all you answer.

So the solution seems to create a view and call it into the request.

So I first create a view :

select id,MyUDF(id) 
From T1

And then change the request into:

SELECT * from T1
Inner join T2 on T1.a = T2.b
Inner join T3 on T2.c = T3.d
inner join MyView V on T1.id = V.id
WHERE 1=1
AND T2.e = 'a certain value'  --I call i the Clause1
and v.value = 1      --I call i the Clause2

and it takes 15 sec.

Wourrayyy !!!

Where clause in sql request should be resolved last

6 Answers6