469,112 Members | 2,010 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,112 developers. It's quick & easy.

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

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

Jul 23 '05 #1
6 1463
I'm not an expert but using subquery is usually slower. Depends on
slowness of query you use for subquery.

Josko
Jul 23 '05 #2
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
Jul 23 '05 #3
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

Jul 23 '05 #4
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?
Jul 23 '05 #5
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)
Jul 23 '05 #6
Q (Q@not.yet) 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
Jul 23 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by grayFalcon | last post: by
22 posts views Thread by KsAdmin | last post: by
1 post views Thread by cybernerdsx2 | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by kglaser89 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.