How to tune this SQL?

  • zxmgh

    SSC Eights!

    Points: 911

    Hi,

    I try to tune a slow query:

    SELECT 1 FROM tab1

     WHERE col1 = ?

       AND col2 = ?

       AND col3 = ?

       AND (SELECT COUNT(col1) FROM tab2 a

             WHERE a.col4 <> '10'

               AND a.col5 IN ('AB', 'BC', 'CD')

               AND a.col1 <> tab1.col1

               AND a.col5 = (SELECT b.col5 FROM tab2 b WHERE b.col1 = tab1.col1)

               AND EXISTS (SELECT 1 FROM tab1 c

                            WHERE c.col1 = a.col1

                              AND c.col2 = 1)

            ) = 0

    tab2 is parent of tab1 (1 to many)

    The problem is the first part of the query:

    SELECT 1 FROM tab1

     WHERE col1 = ?

       AND col2 = ?

       AND col3 = ?

    is fixed and can't be changed, in the rest of the query, I have to use tab2 two times and tab1 another time, how can I make this query runs faster?

     

     

     

  • Sameer Raval

    Hall of Fame

    Points: 3214

    First thing you need to check here is appropriate

    clustered and non clusetered index.

    You need to consider the size of your table and

    then if feasible create a non clustered covering index

    that include col1,col2 and col3 in first query.

    For Second and third Select also check and make sure

    you have index on column in where clause.

    This should be your first step and should definitely

    help boost performance.

    Hope this helps,

     

    Kindest Regards,

    Sameer Raval [Sql Server DBA]
    Geico Insurance
    RavalSameer@hotmail.com

  • Aaron Ingold

    SSCrazy Eights

    Points: 9070

    I find multiple nested subqueries tends to kill performance very quickly. Oftentimes you can rewrite them with clever joins. Though Sameer is correct, covering your queries with indexes is a good place to start. Also look at your query execution plan and see which part is causing the most trouble and think about what you can do to rewrite that.

  • Sameer Raval

    Hall of Fame

    Points: 3214

    Aaron I agree with your thoughts.

    use of table variable or temptable for subqueries is good option. I thought I will come to that level in next post.

    Thanks,

    Kindest Regards,

    Sameer Raval [Sql Server DBA]
    Geico Insurance
    RavalSameer@hotmail.com

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply