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.

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.