It’s only in the cases where SQL Server can return a single row or small set of rows, and the table has an appropriate index, that you’ll see a seek using an index. For simpler queries, you may see a clustered or non-clustered index scan. For a join, you’ll most commonly see a nested loop, but sometimes also a merge or hash join. You can see this when you examine the SQL Server query plan for a query that returns many rows from a large data set. Even the SQL Server engine iterates through data when it scans or joins data using the various types of joins available to it. Many accesses to database data must be iterative in some fashion in order to prepare the data for further manipulation. Our iteration through the parent ids used a WHILE loop, and finished in less than three minutes.
#Tsql cursor update
We then concatenated the strings using an UPDATE statement, executed per parent id. We followed the developer’s original strategy, building the blank table using SELECT statements, and we reduced the time for that step to less than two minutes. When we examined the vendor’s script, we saw that the developer had coded the pivoting process in two steps: a cursor to iterate through all of the parent table ids to build a blank pre-formatted table, and then another script to concatenate the strings, again using a cursor.īy using a set-based approach, we were able to reduce the processing time from 16-plus hours down to less than five minutes.
#Tsql cursor upgrade
However, the vendor’s Transact-SQL script to pivot the data during the upgrade took 16 hours to run on a test machine, and the customer could not afford more than a few hours of downtime for the upgrade. The client application could query the resulting comma-delimited strings faster than getting each of them as individual rows, and in the context, the change made sense and did improve the application’s performance. The vendor wanted to improve performance by making the table smaller, so they decided to store the detail data horizontally, as comma-delimited strings for each parent id. They designed the script to pivot a very large table and store the relevant data in new table horizontally, as concatenated strings.
I bring this up because a few months ago, I had to deal with a vendor’s Transact-SQL script that upgraded their database component to a new version of the vendor’s application. But iterating through data using Transact-SQL cursors often does not scale well, and I hope to convince you that it’s also not a good design or architectural practice.
#Tsql cursor code
After all, since you have to iterate through data structures in your own code on the client side, you may be tempted to do the same when you’re dealing with SQL Server data. When you have a complex job to perform and you need to manipulate the rows in a table, the quickest way may seem to iterate through the rows one by one using a Transact-SQL cursor. Cursors can look like shortcuts to a developer.
0 kommentar(er)
