IN (subquery) slower than calculation @VAL and then IN (@VAL)?

[Caveman]

-- I have a situation where doing

-- first example
-- 1. Get series of values througha query into a string (@val)like
'1,2,3,4':

declare @val varchar(4000)
select @Val = @val + cast(myval as varchar) + ',' -- myval is an
integer variable
from xyz
where xyz.field = 33

SET @val = left(@val, len(@val) - 1)

-- 2. EXEC a query using IN (' + @val + ')'

EXEC('
select *
from qpr
where qpr.fieldx IN (' + @val + ')
')

-- is much faster than doing

-- second example
select *
from qpr
where qpr.fieldx IN (select myval
from xyz
where xyz.field = 33)

-- Since second example does not have a correlateed query, why is it
slower?

-- Thanks in advance,
-- Caveman

[Joško Šugar]

I'm not an expert but using subquery is usually slower. Depends on
slowness of query you use for subquery.

Josko

[Erland Sommarskog]

Caveman (IB*******@xemaps.com) writes:

-- first example
-- 1. Get series of values througha query into a string (@val)like
'1,2,3,4':

declare @val varchar(4000)
select @Val = @val + cast(myval as varchar) + ',' -- myval is an
integer variable
from xyz
where xyz.field = 33
This sort of query may work - or may not. The behaviour of it is undefined.
See http://support.microsoft.com/default.aspx?scid=287515.
SET @val = left(@val, len(@val) - 1)

-- 2. EXEC a query using IN (' + @val + ')'

EXEC('
select *
from qpr
where qpr.fieldx IN (' + @val + ')
')

-- is much faster than doing

-- second example
select *
from qpr
where qpr.fieldx IN (select myval
from xyz
where xyz.field = 33)

-- Since second example does not have a correlateed query, why is it
slower?

Do I guess right when I say that there is a non-clustered index on
qpr.fieldx?

In such case, in the first query, the optimzer has exact information
about the values in the query and can from it statistics make an accurate
estimate of how many rows that will be hit. Presumably, the conclusions
is that the index can be used.

In the second example, the optimizer has less information - it only
has the statistics on xyz. Therefore the estimate is less accurate, and
in fear of too many rows being hit, the optimizer selects to scan the
table. Using a non-clusterd index when many rows qualify can be disastrous,
since for each hit in the index, there must be an access to the data
page. (Important exception: the index covers the query. In this case
there is no need to access the data pages at all.)

There is another issue hiding here. If you increase the number in the
list, the EXEC version will take a considerable toll the first time
you run it, because it takes a very long time for the optimizer to
detmermine the plan. As long as the query is exactly the same, future
invocations will be rapid. But change a value in the list, and it will
take a long time again.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Ross Presser]

Caveman (IB*******@xemaps.com) writes:

-- second example
select *
from qpr
where qpr.fieldx IN (select myval
from xyz
where xyz.field = 33)

Wouldn't this be faster anyway?

select *
from qpr
inner join xyz on qpr.fieldx = xyz.field
where xyz.field = 33

[Q]

"Ross Presser" <rp******@imtek.com> wrote in message

Wouldn't this be faster anyway?

select *
from qpr
inner join xyz on qpr.fieldx = xyz.field
where xyz.field = 33

and wouldn't this be even faster?

select *
from qpr
inner join xyz on qpr.fieldx = xyz.field and xyz.field = 33

one could ask: physical independence, where are you?

[Hugo Kornelis]

On Wed, 9 Feb 2005 09:08:27 -0500, Ross Presser wrote:

Caveman (IB*******@xemaps.com) writes:

-- second example
select *
from qpr
where qpr.fieldx IN (select myval
from xyz
where xyz.field = 33)

Wouldn't this be faster anyway?

select *
from qpr
inner join xyz on qpr.fieldx = xyz.field
where xyz.field = 33

Hi Ross,

Maybe - but it wouldn't return the same result. More columns, to begin
with. And more rows as well, assuming that column xyz.myval is not unique.

(Oh, and all this of course assuming that you correct the first xyz.field
to xyz.myval - otherwise, you'd get completely different results).

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

[Erland Sommarskog]

Q ([e-mail]) writes:

"Ross Presser" <rp******@imtek.com> wrote in message

Wouldn't this be faster anyway?

select *
from qpr
inner join xyz on qpr.fieldx = xyz.field
where xyz.field = 33

and wouldn't this be even faster?

select *
from qpr
inner join xyz on qpr.fieldx = xyz.field and xyz.field = 33

No. Yours and Ross's queries are exactly the same. The optimizer will
rewrite both to the same internal represenation.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp