Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Is the query an update or a read? Write queries contain protection against 'mutating data' (mostly in form of haloween protection) but read queries contain weaker protection semantics (derived from isolation level).
–
Remus RusanuApr 6 '12 at 21:25

@RemusRusanu: IMO write queries may provide complete protection against 'mutating data', but we cannot assume it is always the case - there are many examples proving otherwise. What do you think?
–
A-KApr 7 '12 at 1:59

@AlexKuznetsov: most of those cases are examples of 'optimizing one step beyond correctness'...
–
Remus RusanuApr 7 '12 at 2:24

@RemusRusanu: The ultimate operation is an update. The query options I posted were for populating table variables to get the details for the update (adjacency model).
–
OMG PoniesApr 7 '12 at 3:06

1

The estimated subtree costs shown in SQL Server execution plans aren't always that useful for comparing queries. Particularly where table variables are concerned due to the one row assumption. Can you show the (possibly simplified) queries?
–
Martin SmithApr 8 '12 at 9:11

For the second query, SQL Server could run the subquery first, and store the result in a temporary table. If it did that, that would be the EXACT SAME way it executes the first query!

So the effect of splitting a query in two is that it limits the query optimizer's choice. The first query demands that the subquery is executed before the updates. The second query leaves the optimizer free to fetch one row, then update the table, and then fetch the next row.

Now when you upgrade the hardware, SQL Server version, or if the database changes, you would ideally re-verify that your limitation is in fact beneficial. That's kind of expensive.

So I would prefer not split the query, or enforce a query plan, unless the expense is offset by a considerable advantage. Going from 0.07 to 0.3 seconds doesn't look like a big difference to me, unless the query is run more than once every minute or so.

Yes, I understand that. But I'm using table variables for the indexing -- indexed views, with better read for the performance hit of populating the index. I looked at using a CTE for the operation, but a CTE is just syntactic sugar. The amount of time the operation takes is important -- it's timing out in the app.
–
OMG PoniesApr 7 '12 at 17:09

1

@Andomar : I think there is a big difference in 2 approaches you mentioned. The second one guarantees to be atomic, the first one as it is may update records that were changed between insert and update or skip new records that would satisfy condition col1 =7.
–
a1ex07Apr 8 '12 at 15:23

@a1ex07 - Yep, and to fix that first query to behave atomically you'd need to set your isolation level to SERIALIZABLE or REPEATABLE READ. (In this case REPEATABLE READ is OK because after the initial INSERT runs, new data can only enter the range WHERE col1 = 7 but not leave it.)
–
Nick ChammasApr 8 '12 at 17:04

1

@a1ex07, NickChammas: The second one is not "guaranteed to be atomic". Changes are possible between the select and the update for both queries.
–
AndomarApr 8 '12 at 17:17

@Andomar: Not sure I understand you. Single statement is supposed to be atomic, isn't it? Subquery cannot return row that didn't exist at the beginning of its execution. UPDATE should cause U and then X locks on the records. Unless I'm missing something obvious (if you think it's the case, I'd really appreciate if you point me to the right direction), the second query should be atomic (taking into account that we select and update the same table)
–
a1ex07Apr 8 '12 at 18:05

I believe one query should almost always behave better. The overall performance problems may be related to the fact that SQLServer blocks readers until write transaction completes. Fortunately, it can be changed with setting db option READ_COMMITTED_SNAPSHOT ON. I'm not 100% sure if it's the culprit in your case, but I personally saw a great performance improvement because of setting this option.