SQL Server Performance

What is causing slow in this query

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by waqar, Jun 15, 2006.

  1. waqar Member


    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?

    ~* Opinions are like a$$holes, everyone got one. *~
  2. mmarovic Active Member

    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.
  3. Twan New Member

    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

  4. druer New Member

    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,

    Blessings aren't so much a matter of recieving them as they are a matter of recognizing what you have received.

Share This Page