But there are cases when a dynamic plan is worse than a static one. If the STATIC keyword is missing, the cursor is dynamic and uses a dynamic plan if available. READ_ONLY FORWARD_ONLY cursor can be scrolled only from the first row to the last one. If we run the syntax using this argument LOCAL READ_ONLY FORWARD_ONLY we get the same results. Below we have the results:ġ2 seconds are a lot better that 4 minutes and 47 seconds, but keep in mind the restrictions explained above. 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.īe aware of this before using these arguments and check if it matches your needs. A STATIC cursor is read-only and is also referred to as a snapshot cursor because it only works with the data from the time it was opened, meaning that it won’t display any changes made in database on the set of data used by the cursor. The STATIC keyword makes a temporary copy of the data used by the cursor in tempdb in a temporary table. Also, the cursor can be referenced by a stored procedure, trigger or by a local cursor variable in a batch. After the batch finishes executing, the cursor is automatically deallocated. 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. There are many arguments we can use in a cursor definition, more on that on this link CURSOR Arguments, but for now let’s focus on what this two words mean. Let’s give our cursor another chance and uncomment the line –LOCAL STATIC. 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. Our demo tables are relative small containing roughly 1,000 and 500 rows. These operations are repeated until there are no more rows to work with.įinally, CLOSE syntax releases the current result set and removes the locks from the rows used by the cursor, and DEALLOCATE removes cursor reference. Then the second SELECT uses the variable value to get data from. In our example, the cursor sets its position to the first row returned by the first SELECT and fetches the ProductID value that matches WHERE condition in variable. Step two, the retrieval, when it gets the data from that specific row in an operation called the FETCH. Step one, the positioning, when the cursor sets its position to a row from the result set. In the cursor execution, we have two steps. It’s important to mention the chosen syntaxes above are only for demo purposes, and I made no index tuning to speed things up. Let’s start by using a CURSOR, and write the following syntax:Īfter a short coffee break, the query finished executing, returning 833 rows in the time shown below. table, for every product that requires less than a day to manufacture, that is from table. Below, we will show some examples where using a CURSOR creates performance issues and we will see that the same job can be done in many other ways.įor the purpose of this demonstration we will use AdventureWorks2012 database, and let’s say we want to get some data from. There are some cases, when using CURSOR doesn’t make that much of a mess, but generally they should be avoided. But be advised, take this path and trouble may follow. In T-SQL, a CURSOR is a similar approach, and might be preferred because it follows the same logic. If you possess programming skills, you would probably use a loop like FOR or WHILE to iterate through one item at a time, do something with the data and the job is done. In T-SQL, one way of doing this is using a CURSOR. Sometimes the application logic needs to work with a row at a time rather than the entire result set at once. For example, a SELECT statement returns a set of rows which is called a result set. In relational databases, operations are made on a set of rows.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |