Our demo tables are relative small containing roughly 1, and rows. If we had to loop through tables with millions of rows it would last a considerable amount of time and the results would not please us. When we specify LOCAL keyword, the scope of the cursor is local to the batch in which it was created and it is valid only in this scope. After the batch finishes executing, the cursor is automatically deallocated.
Also, the cursor can be referenced by a stored procedure, trigger or by a local cursor variable in a batch. Here we have some gotchas. Basically, no updates, deletes or inserts made after the cursor was open will be visible in the cursors result set unless we close and reopen the cursor. Be aware of this before using these arguments and check if it matches your needs. Below we have the results:. But there are cases when a dynamic plan is worse than a static one.
Although we got the result in an average of 12 seconds using both of this methods, these arguments should be properly tested before choosing one of them. There are many ways of obtaining the same result, much quicker and with less impact on performance. After hitting the EXECUTE button, we produced the same results in ms, compared to time from the cursor method, and we have a smile on our face.
We do not need to iterate through every row to get what we need, we have no more loops, no while clause, no iterations, we are working with sets of data instead, getting what we want faster and writing less code. The top queries that are opening maximum cursors and are not closing subsequent cursors gracefully are displayed. If some code is running above SQL queries, then check that Java Statement, Resultset, or connection are closing properly or not if they have access to the code.
If the code is not closing the connections, then close all the open connections properly so that you can save memory leaks in the code and save database memory. So in that regard they are just plain old-fashioned. However, other than for backwards compatibility they can still serve us well in the right situations. One such time would be when you want to write a script to restore a bunch of databases from backup files on a disk. Another time this may be useful is when you need to update an entire column of a large table that is constantly being queried in a production environment.
Doing this on a row-by-row basis would avoid locks and waits for other users and your UPDATE query while concurrent operations are happening on the same pages of data. This would also avoid too many locks and would do the job quicker.
To illustrate how a while loop works I have massaged the above query example to return the same data again but this time, instead of row-by-row or a full set, it does something in between and returns rows at a time. It takes less than a second but is closer to ms than 87ms as is the case for the pure set-based query.
0コメント