SQL Server Performance Forum – Threads Archive

What is causing slow in this query

Hi, I am using SQL 2k5 Enterprise version, i am trying to execute following query with given structure as follows tbl1 (227 mil records)
col0 char(16) -> indexed tbl2 (1179 records)
col0 char(16) -> indexed query 1
select col0 from tbl1 where tbl1.col0 in (‘1234’)
time taken < 0sec
query 2
select col0 from tbl1 where tbl1.col0 in (select top 1 col0 from tbl2)
time taken < infinite I am trying to use above query (query 2) to fetch information for all records available in tbl2. Any idea what is the reason for this query to being slow?
Waqar.
________________________________________________
~* Opinions are like a$$holes, everyone got one. *~

---

With query 2 query analyzer doesn’t have enough information in statistics to choose a good execution plan. Try to force an index on tbl1.col0 in the second query.

---

Alternatively you can change the query to = rather than in or change it to a batch as in declare @col0 char(16)
select top 1 @col0 = col0 from tbl2
select col0 from tbl1 where tbl1.col0 = @col0 Cheers
Twan

---

1. I assume that you are really trying to pull more columns in your select from tbl1 than just the col0 that show.
2. Have you tried using a join between the two tables?
3. When you say "fetch information for all records available in tbl2" do you really mean that you want to select for all records for DISTINCT values of col0 in tbl2? select tbl1.col0
from tbl1 inner join (select distinct col0 as col0 from tbl2) as t2 on tbl1.col0 = t2.col0 Hope it helps,
Dalton Blessings aren’t so much a matter of recieving them as they are a matter of recognizing what you have received.

---

]]>