Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
phenreid
Starting Member
29 Posts |
 Posted - 2010-11-19 : 14:21:21
|
I have a semi-complex SELECT statement that returns seven (7) records in four (4) seconds.Select a.field1, b.field2 from tableA a, tableB b where <complex conditions and implied joins> Now I want to update the comments field of each related record so I simply replace the select field list with update statement:Update TableB set b.comments='Certain comment' from tableA a, tableB b where <complex conditions and implied joins> Since it only has to update seven records, I expect it to be instant, but as far as I can tell it re-runs the select for each individual record and takes 1.5 min.I tried to return the select as just a list of seven unique PK fields and then UPDATE ... where TranID in (Select TranID....). That also took 1.5 min.I want SQL to find my seven records, then just update those seven. I can do it with temp tables, of course, but what is the secret to making SQL process the update only on the final results of the SELECT?So, I ran an execution plan of the SELECT then an execution plan of the UPDATE. The execution plan of the update suggested an index to add (one field and two included columns). When I did this, that solved the problem.But my question is why is the execution plan different for an "UPDATE ... FROM" compared to "SELECT... FROM" in the case that the SQL following the word FROM is identical?Seems like there must be an easier way to construct my SQL so I don't end up having to check execution plans and create indicies -- especially for one-time use. |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2010-11-19 : 14:40:52
|
| It's hard for us to assist when the most critical part of the query is "<complex conditions and implied joins>". Posting that actual code will go a long way to help us help you. |
 |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2010-11-19 : 14:52:07
|
| For one you are accessing the comments column - could tyrn it into a table scan.Updates in general need more care to optimise.trySelect b.<pk>, comments = 'Certain comment' into #a from tableA a, tableB b where <complex conditions and implied joins>Update TableBset comments=a.commentsfrom TableB bjoin #a aon a.<pk> = b.<pk>That should at least tell you which bit is slow and be easier to optimise.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-11-19 : 15:40:46
|
| Should there be an "into #a" in the SELECT Nigel? |
|
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2010-11-19 : 15:52:32
|
quote:
Originally posted by Kristen
Should there be an "into #a" in the SELECT Nigel?
There is - I've changed it to a black font now :).ty.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
phenreid
Starting Member
29 Posts |
Posted - 2010-11-22 : 12:30:52
|
| I didn't post the rest of the SQL because this question was conceptual -- when you replace a select list that returns only seven records and executes immediately, why does replacing select w/ update take so long?Answer given explains it -- update requires more to optimize than SELECT. Adding a PK is a great trick and just what I was seeking with this post.I have since learned that part of my problem hasto do with Table-valued UDF's. They work great but the optimizer can't use them properly when they're included in a JOIN, especially if parms are passed to UDF via other parms in a sproc. I ended up re-writing my view so it returned a one-to-one relationship that could be joined on PK's -- that definitely solves it. With the UDF's in sprocs, just have to use temp tables in that case.Thanks very much! This post is answered/complete. |
|
|
|
|
|
|
|
|
What is fastest SQL syntax to UPDATE?