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, Sep 26 '16 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, Sep 26 '16 at 14:24
add an index on References and Residential and then the second one will be twice as fast. — Hogan, Sep 26 '16 at 14:32
What is PK_BildingsAll -- it has Residental but not References? — Hogan, Sep 26 '16 at 14:39
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, Sep 26 '16 at 14:40
@Jens, thanks. @Hogan, it is a primary key of `Buildings` table. No more indexes. — AsValeO, Sep 26 '16 at 14:44

score 2 · Accepted Answer · edited May 23 '17 at 10:27

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

Thank you for detailed answer! – AsValeO Sep 26 '16 at 15:44 — AsValeO, Sep 26 '16 at 15:44

SQL UPDATE query performance

1 Answers1