Putting it all in a transcation doesn't help because it's the dispatch and roundtrip time for all the SQL statements that takes up the majority of the time.
To add to the other ways already mentioned: you can make a temporary table or a table variable containing the updates you want to do, then run the UPDATE statement linking the table to the table you intend to update.
The dominant factor in the time taken to complete the overall operation tends to be the “admin” work in conveying the application’s intention to the database server rather than the actual updates to the database.
A more effective solution to this problem is to attempt to reduce the number of UPDATE statements.
If you want to review the complete syntax of the UPDATE statement then please refer to Books Online.
To properly show you how to use the UPDATE statement I will need to create a few tables to hold some sample data. Below is the code to create my Toy table: In order to show you how to UPDATE a table from data in another table I need to build a second sample data table that I will call New Toy Price.
Is there a cleaner way to roll this up into one statement rather than two.
I've tried several iterations but this seems to be the only way I can successfully execute these two statements.
Switching to a combined variant like suggested gave us about 700% speedup so we now do the same work in less than ten seconds.It looks a bit silly, but it's to illustrate yet another way of doing this.Note that for two updates, you get two statements: the INSERT into the update table and the UPDATE statement itself.In this article I will show you the most common methods of using the UPDATE statement.Below is the basic syntax for the UPDATE statement: This is not the complete syntax of the update statement.