T-SQL, Multiple Updates in a scheduled job

Question

I've been developing a query to be used as a scheduled job. To make it short, I'm to make some specific calculations on a table, and update a few columns (like STATE) according to the calculation results.

A sample (as currently how it is) could be demonstrated as follows :

---

UPDATE TEST_TABLE SET STATE = 1
WHERE {some condition}

UPDATE TEST_TABLE SET STATE = 2
WHERE {some condition}

UPDATE TEST_TABLE SET STATE = 3, SOME_OTHER_COLUMN={value}
WHERE {some condition}

WHILE(some condition)

BEGIN
UPDATE TEST_TABLE SET STATE = 4, SOME_OTHER_COLUMN={value}

WHERE {some condition}

END

---

What I've tried to demonstrate above is, the flow of the query. As It can be seen above, I'm updating a single table multiple times, mostly setting a single state column with different values depending on different conditions.

I also had to use a while loop, instead of a cursor (because of poor performance), because it was needed to update the data in this table with small groups depending on those conditions.

Assuming all the query is wrapped in a transaction and a try-catch block.

And finally, here's my question : Since this is gonna be a scheduled job to be executed at night, the performance is not the top priority for me. However, i couldnt figure out how to make the same operation with a somewhat more cleaner and more efficient (performance wise) query. I'm in need of some advices. Please note that, {some condition} areas hold subqueries with EXISTS functionality. So the original code looks far more messy than this. Thanks in advance. --Ozan

Answer 1

If the table is small enough, then multiple queries will not hurt.

Even if it is considerably large to where the WHERE clause of the query works efficiently because of indexes, you may be OK.

You could consolidate the queries - if the WHERE clauses are the same, then you can put those together.

IMO, the easier the code is to read, the easier it is to maintain. If you're not getting major performance gains then there's really no point in consolidating.

Efficiency of a query is measured in the amount of resources it consumes, not how nicely consolidated it is or how complex it looks. You may be better off looking at execution plans and figuring out any structural changes or indexes you need on the table.

The biggest thing that catches my eye is that you tried a CURSOR and now have changed it to a WHILE loop.

You will find that Set-based operations are much more conducive to database work than cursors.

EDIT based on OPs comment

Since the table is expected to fill up quite fast, you should do a load test. Load the table up with a ton of test data (let's say one years worth) and see how your queries perform.