Take the 2-minute tour ×
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.

I ran into a situation with two options:

  1. Use a single query with a cost of ~0.07

    INSERT INTO @vals
    SELECT ...
      FROM ...
      JOIN ... 
      JOIN ...
     WHERE a.col = 1 OR b.col IS NOT NULL
    
  2. Use two queries to get the same result -- the first cost ~0.05, the second cost ~0.3

    INSERT INTO @temp
    SELECT ...
      FROM ...
      JOIN ...
    
    INSERT INTO @vals
    SELECT ...
      FROM @temp
      JOIN ...
     WHERE a.col = 1
    UNION ALL
    SELECT ...
      FROM @temp
      JOIN ...
      JOIN ...
     WHERE b.col IS NOT NULL
    

I chose the first option - single query, so less concern of mutating data between queries and less overall cost. Was this the prudent choice?

This was for an operation that originally took ~5+ minutes, timing out in our application. The re-write got that down to 1.5 minutes consistently, using table variables with unique clustered indexes.

share|improve this question
1  
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 Rusanu Apr 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? –  AlexKuznetsov Apr 7 '12 at 1:59
    
@AlexKuznetsov: most of those cases are examples of 'optimizing one step beyond correctness'... –  Remus Rusanu Apr 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 Ponies Apr 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 Smith Apr 8 '12 at 9:11

2 Answers 2

The difference between one and two queries may not be as large as you'd think. For example, compare this query:

declare @t table (id int)
insert into @t select id from Table1 where col1 = 7
update Table1 set col2 = 8 where id in (select id from @t)

to this query:

update Table1 set col2 = 8 where id in (select id from Table1 where col1 = 7)

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.

share|improve this answer
    
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 Ponies Apr 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. –  a1ex07 Apr 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 Chammas Apr 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. –  Andomar Apr 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) –  a1ex07 Apr 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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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