Where clause in sql request should be resolved last

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 ce开发者_如何学Pythonrtain 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 ?

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

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)

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.

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

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.

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 !!!