SQL UPDATE query performance

Question

I'm using Azure SQL Database. If I've got 150K records in Building table. Residental column is of type bit. Half of records have Residental = 1. Which of these two queries is better in terms of performance in my case?

UPDATE p.Building
SET Residental = 1
WHERE References > 100

UPDATE p.Building
SET Residental = 1
WHERE References > 100
AND Residental <> 1

UPDATE:

This is profiler results. First is better i guess? And what if all records have Residental = 1 or 0? This affects the result of profiling? I've tried to perform tests but profiler results almost the same.

I removed the incompatible database tags. Please tag the question with the database you are actually using. — Gordon Linoff
– Gordon Linoff, Commented Sep 26, 2016 at 14:23
I'd imagine the second would be fastest. There will be fewer updates,and if Residental is indexed, there would be fewer reads — John Cappelletti
– John Cappelletti, Commented Sep 26, 2016 at 14:24
add an index on References and Residential and then the second one will be twice as fast. — Hogan
– Hogan, Commented Sep 26, 2016 at 14:32
Next to performance, you also got the fact that your first query might update rows already having Residental = 1. That would cause 'useless' locks. I just try to avoid locking more than I actually need whenever I can. — Jens
– Jens, Commented Sep 26, 2016 at 14:40

Community · Accepted Answer · 2017-05-23 10:27:27Z

2

EDIT

Now that you've posted your explain output, a couple of things:

Looks like you don't have an index on anything that your DBMS is using. If I were you, I would add an index on the "References" field and "Residential" field and re-run your profiling.
For the second query, you could add a multi-column index on both fields which is probably your best bet as far as performance.
Your "Clustered Index Scan" should change to tell you it's using your new index.

Here's a pretty good explanation about indexes in detail, but you could just cheat and add the recommended ones and you should be good.

How does database indexing work?

Original Answer

Which of these two queries is better in terms of performance in my case?

Sort answer, there's no way to tell with the information you've provided. You need to look at the plan that your RDBMS is going to use to execute the query. You didn't say which database, so here are the MySQL docs:

http://dev.mysql.com/doc/refman/5.7/en/explain.html

It mainly depends on indexes and statistics and the DB vendor, but you can generate all that information by explaining your query. Post the results of the explain for both queries and you'll get answers.

As of now the answer is it depends

edited May 23, 2017 at 10:27

CommunityBot

11 silver badge

answered Sep 26, 2016 at 14:25

mikeb

11.5k9 gold badges73 silver badges135 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

AsValeO Over a year ago

Thank you for detailed answer!

Collectives™ on Stack Overflow

SQL UPDATE query performance

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related