SQL server in operator performance

Question

I have a query that joins some tables and when I use = operator instead of in operator in a where clause like this I get a significant performance improvement.

= operator takes less than a second.
in operator takes about a minute.

where P.GID in ( SELECT GID from [dbo].[fn_SomeFunction] (15268) )

the sub query returns 1 result in most of the cases and just this change will improve most of the cases but will cause errors for some other cases.

any ideas why this behavior?

is this a table-valued function? If so try cross applying it instead of putting it in a subquery — Mihail Shishkov, Oct 14 '13 at 20:26
If you're using **IN** the way you posted, that's not a SARGable operator that improves performance (and generally **IN** isn't), whereas the **=** operator is. — Question3CPO, Oct 14 '13 at 20:37

score 1 · Accepted Answer · answered Oct 14 '13 at 20:37

Try something like that it is not tested and may contain some syntactic errors. The main idea is to get the desired ids in a temp table variable and use that in a join. Hope that helps.

DECLARE @gids TABLE( 
GID UNIQUEIDENTIFIER NOT NULL
)

INSERT INTO @gids (GID)
    SELECT
        GID
    FROM [dbo].[fn_SomeFunction](15268)

SELECT * FROM SomeTable st INNER JOIN st.GID = @gids.GID

score 0 · Answer 2 · answered Oct 14 '13 at 20:25

0

If that truly represents a functions that is what is killing your execution time. You can verify this by looking at the execution plan. What happens is that in your subquery that function has to be calculated over and over again. If you can remove the function and replace with a query you should notice an improvement.

Additionally, if the functions return type doesn't match the column's type there will need to be an implicit conversion done on each return item. Changing the functions return type(or the columns type) to match would also help performance.

DateTime <> smalldatetime for example and requires an implicit conversion.

answered Oct 14 '13 at 20:25

orgtigger

3,914
1
20
22

I replaced the function with the actual query it executes still in operator takes a minute while = operator takes less than a sec – mmohab Oct 14 '13 at 20:49
Is there a way to know if it's executed again and again? – mmohab Oct 14 '13 at 20:50
You can see that info in the query execution plan (Ctrl + M and execute and you will see the tab on the bottom). One icon will have a huge cost associated with it. If that item is a nested loop or a conversion it is running over and over again – orgtigger Oct 14 '13 at 21:22

SQL server in operator performance

2 Answers2