2

I'm trying to rewrite the following query to make it more efficient:

update c set c.cartype = (select Id from carTypes where carType = 'Coupe')
from cars c
inner join doorcount dc
    on c.Id = dc.carId and dc.Value=2
       and c.carType= (select Id from carTypes where carType = 'Sedan')

What I've come up with so far is:

update c set c.cartype = ct.id
from cars c
inner join cartypes ct on ct.carType='Coupe'
inner join doorcount dc on c.Id = dc.carId and dc.Value=2 
inner join carTypes ct2 on c.carType = ct2.Id and ct2.carType = 'Sedan'

This looks better to me, but is performing almost exactly the same.

Is there a more efficient way to perform this update?

  • 1
    There is no magic T-SQL syntax that can make an update faster than that, though you could try with some EXISTS instead of direct joins (the only tables where you really need materialized values are cars and cartypes). You need to make sure that the indexes you're using efficiently support these operations. Could you share an actual, post-execution query plan somewhere? – Aaron Bertrand Sep 23 '15 at 14:13
  • I think I prefer the 1st version where it's obvious that the 2 subqueries are uncorrelated to the base query (and I mean "prefer for readability", the efficiency depends on available indexes and materialized views, as Aaron has already commented). – ypercubeᵀᴹ Sep 23 '15 at 14:16
  • @ypercube, the more at it, the more I agree with you. That first inner join is really bugging me. – Kevin Sep 23 '15 at 14:17
1

You could also remove any doubt about the impact of the subqueries using:

DECLARE @CoupeID INT, @SedanID INT;

SELECT @CoupeID = MAX(CASE carType WHEN 'Coupe' THEN Id END), 
       @SedanID = MAX(CASE carType WHEN 'Sedan' THEN Id END)
FROM dbo.carTypes WHERE carType IN ('Coupe','Sedan');

UPDATE c SET c.carType = @CoupeID
FROM dbo.cars AS c
WHERE c.carType = @SedanID
AND EXISTS (SELECT 1 FROM dbo.doorcount AS dc
  WHERE dc.Value = 2 AND dc.carId = c.Id);

Of course appropriate indexes on specific columns mentioned here would be useful for performance (again, if you post the plans somewhere, we can probably help identify opportunities). But in general, an update is an update, and unless these tables are massive, even the slowest way to find the rows to update will pale in comparison to the cost of the update itself.

Please also always use schema prefix and be very careful about CaSe SenSiTivIty.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.