Results 1 to 2 of 2

Thread: SQL Server : update command with subqueries

  1. #1
    Join Date
    Jun 2003
    Posts
    5

    SQL Server : update command with subqueries

    Hello everybody.

    I wrote an SQL update query for SQL Server 2000 but found it not fast enough. The query is :

    UPDATE mesures SET valeur=valeur*0.12516 WHERE champ_id in (select C.id from graphique G LEFT join champs C ON C.graphique_id=G.id WHERE G.extracteur='R30') AND instant_id in (select id from instant_mesure where jour>=20030301 and jour<20040509)

    The 2 SELECT sub-queries are very fast but the UPDATE 'total' query is too slow and I sometimes have a timeout while executing with ODBC. Note that 'mesures.champ_id' and 'mesures.instant_id' are the primary key of the 'mesures' table.

    Do you think it can be wroten using another faster syntax ?

    Thanks for your help.

  2. #2
    Join Date
    Feb 2003
    Posts
    1,048
    Here are some optimizer tips:

    1. You should never use "In" with subqueries. It's very bad for performance. Use "Exists" clauses instead when you can.

    2. When doing an Exists clause, use "Select 1" instead of "Select fieldname".

    3. For select queries (as in your subqueries) use the "with(nolock)" optimizer hint. This will prevent it from waiting for a table or row to be unlocked before reading.

    Code:
    UPDATE m
    SET valeur=valeur*0.12516
    From mesures As m
    WHERE Exists (Select 1
        From graphique As G with(nolock)
        Left Join champs As C with(nolock) On C.graphique_id = G.id
        WHERE G.extracteur = 'R30'
        And C.id = m.champ_id)
    And Exists (Select 1
        From instant_mesure with(nolock)
        Where jour >= 20030301
        And jour < 20040509
        And id = m.instant_id)
    The subqueries may be very fast when running them by themselves, but when they are used in an "In" clause, they run once for every record in mesures. This is when you can really see the performance difference.

    If you are still seeing speed issues, view the Estimated Execution Plan in Query Analyzer.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •