Sql Server Performance: table variable inner join vs multiple conditions in where clause

Question

What is faster in MS Sql Server, a where clause with multiple conditions or a inner join after creating a table variable? For example:

select A.* from A where A.fk='one  ' or A.fk='two  ' or A.fk='three' ...ect.

vs

declare @temp (key as char(matchingWidth)) table;
insert into @temp values ('one  ');
insert into @temp values ('two  ');
insert into @temp values ('three');
select A.* from A inner join @temp t on A.fk=t.key;

I know normally the difference would be negligible; however, sadly the database I am querying use the char type for primary keys...

If it helps, in my particular case, table A has a few million records, and there would usually be about a hundred ids I'd be querying for. The column is indexed, but not a clustered index.

EDIT: I am also open to the same thing with a temp table... although I was under the impression that both a temp table and table variable where virtually identical in terms of performance.

Thanks!

I guess it would be good to check both cases and compare execution plans. — Evaldas Buinauskas
– Evaldas Buinauskas, Commented Mar 17, 2015 at 13:06
Good idea, but I don't have permissions needed to analyze execution plans. Additionally, I was hoping I could learn from someone else's hard fought experience as this is the first time I've had to dance around poor performance due to bad DB design. — TheCatWhisperer
– TheCatWhisperer, Commented Mar 17, 2015 at 13:08
Unfortunately, someone else's hard fought experience wasn't with your data in your schema on you kit... — Rhys Jones
– Rhys Jones, Commented Mar 17, 2015 at 13:09

Dmitrij Kultasev · Accepted Answer · 2015-03-17 13:09:01Z

1

In most cases the first approach will win as table variable does not use statistics. You'll notice big performance decrease with big amount of data. When you have just few values then there is not supposed to be any noticeable difference.

answered Mar 17, 2015 at 13:09

Dmitrij Kultasev

5,8658 gold badges53 silver badges108 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

TheCatWhisperer Over a year ago

What about a temp table?

Dmitrij Kultasev Over a year ago

@RhysJones yes, but in example where is table variable. A lot of people call it as temporary table.

Dmitrij Kultasev Over a year ago

@TheCatWhisperer temp table will win in most cases. However it uses resources to create the table and in your provided example the first case would probably win. Anyway the only true answer for your question is TEST, TEST, TEST ... check in profiler and you'll see the benefits.

Rhys Jones Over a year ago

@sidux No - a table variable and a temp table are very different things.

Dmitrij Kultasev Over a year ago

@TheCatWhisperer I mentioned CONDITIONAL index (add WHERE conditions to the index). And answer - yes.

|

Collectives™ on Stack Overflow

Sql Server Performance: table variable inner join vs multiple conditions in where clause

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related