Sql IN clause slows down performance

Question

I need a help about sql query performance...

I have a view and when I run view as

select * 
from udv_salesAnalyze 
where _month=12 and _year=2012

I got result in 2 seconds

but when I add another filter as

select * from udv_salesAnalyze 
where _month=12 and _year=2012 
and userRef in (1,2,5,6,9,11,12,13,14
,19,22,25,26,27,31,34,35,37,38,39,41,47,48,49,53,54,57,59,61,62
,65,66,67,68,69,70,74,77,78,79,80,83,86,87,88,90,91,92,94)

I got result in 1 min 38 seconds..

I modified query as

select * from udv_salesAnalyze 
where _month=12 and _year=2012 
and userRef in (select * from udf_dependedUsers(2))

(here udf_dependedUsers is table returned Function) I got result in 38 seconds

I joined table retuned function to view but again I got result in 38-40 seconds...

is there any other way to get result more fastly...

I ll be very appreciated you can give me a solution...

thanks a lot ...

execution plan :

here code fo udf_dependedUsers :

ALTER FUNCTION [dbo].[udfn_dependedUsers] (@userId int)
RETURNS @dependedUsers table (userRef int)
AS
BEGIN
DECLARE @ID INT
SET @ID = @userId
;WITH ret AS(SELECT userId FROM users
             WHERE  userId = @ID
             UNION ALL
             SELECT t.userId
             FROM   users t INNER JOIN ret r ON t.Manager = r.userId
             ) 
insert into @dependedUsers (userRef)
select * from ret
order by userId
RETURN 
END

I have organization schema and when I use that function I got the result set of depended user list of User... for instance udf_dependedUsers(2) gives the result set of user who is depended to User with Id 2.. that function runs fast ... return a result in 0 second — Gökhan Yılmaz, Dec 24 '13 at 18:50
This is confusing: your UDF appears to have nothing to do with your original constant list. Worse, it's a recursive CTE which is pretty much a formula for bad performance. Please clarify what you are really trying to do here. Also, the `udv_salesAnalyze` object clearly isn't a simple table select, so we will need to see it's definition also. — RBarryYoung, Dec 24 '13 at 19:10
hi again , the purpose is to get data of users of specied manager...so for Example if the Manager User Id is 2 that function returns a table of users who is depended to Manager... for some other Manager returns some other list... That constant list was for a test to understand query execution time if it is a constant list... udf_dependedUser(2) return same result with that list..I dont think that there is a problem with function because it result very fast ... — Gökhan Yılmaz, Dec 24 '13 at 19:18
Please upload the XML version of the actual execution plan somewhere (e.g. pastebin.com) — Martin Smith, Dec 24 '13 at 20:07
Thank you for your help because that execution plan lead me to find the solution... Thanks a lot... — Gökhan Yılmaz, Dec 25 '13 at 11:44

score 1 · Answer 1 · answered Dec 24 '13 at 18:38

1

Try using a left join

select * from udv_salasAnalyze  MainTable
LEFT JOIN
(select * from udf_dependedUsers(2)) SelectiveInTable --Try direct query like that you wrote in user function
ON SelectiveInTable.userRef = MainTable.userRef
where _month=12 and _year=2012 
and SelectiveInTable.userRef != null

answered Dec 24 '13 at 18:38

Sandip Bantawa

2,822
4
31
47

@GökhanYılmaz did you try direct query in LEFT JOIN instead of UDF – Sandip Bantawa Dec 24 '13 at 18:49

score -1 · Answer 2 · answered Dec 24 '13 at 21:17

-1

Use a JOIN instead of an IN, like this:

select 
   data.* 
from udv_salesAnalyze data
join ( values
    (1), (2), (5), (6), (9),(11),(12),(13),(14),(19)
   ,(22),(25),(26),(27),(31),(34),(35),(37),(38),(39)
   ,(41),(47),(48),(49),(53),(54),(57),(59)
   ,(61),(62),(65),(66),(67),(68),(69),(70),(74),(77),(78),(79)
   ,(80),(83),(86),(87),(88),(90),(91),(92),(94)
) V(userRef) on V.userRef = data.userRef
where _month = 12
  and _year  = 2012

answered Dec 24 '13 at 21:17

Pieter Geerkens

11,775
2
32
52

@GökhanYılmaz: interesting.it worked for this poster with te same performance issue: http://stackoverflow.com/questions/16878465/alternative-to-using-where-in-for-slow-sql-queries/16884643#16884643 – Pieter Geerkens Dec 27 '13 at 13:36

score -1 · Accepted Answer · answered Dec 25 '13 at 11:42

Problem was indicies of table which holds user data

Here is Solution ;

1- write your query to Query Editor and Click "Display Estimated Execution Plan" button ..

enter image description here

2- SQL Server gives you hints and query about index in "Execution Plan Window" that should be created on table

enter image description here

3- Right Click on Execution Plan window and choose "Missing Index Details"

enter image description here

4- In Opend Query Page Rename index name ([] to something else which you want) and run query

enter image description here

5- and Run you own query which was slow as I mentiond in my question... after these steps my query run in 4 second instead of 38

But you said that the query `select * from udv_salesAnalyze where _month=12 and _year=2012` ran in two! Whilst the index may well be useful it isn't the whole story. Presumably different cardinality estimates affect the plan choice when the `IN` is present. — Martin Smith, Dec 25 '13 at 12:54

Sql IN clause slows down performance

3 Answers3