SQL Server Forward_Only Cursor For Performance

For those of you that follow me on Twitter you might have heard my recent comments around a DotNetNuke SQL Script that I had to run that would have an impact of between 6 and 9 million SQL statements being executed on a database.  As part of this project I was focusing a lot on SQL Server performance, and getting the best overall bang for the buck with the query. Sadly, given what I was working with a Cursor was needed to ensure that I had the best, most stable solution.  In this post, I'll share a little about the process and the lesson learned with cursors.

The "Problem"

Given the nature of the problem, and the inherent risks associated with actually executing the SQL Scripts that I was using I will not be able to share the actual SQL statements, but I can give an overview of the structure of the queries.  

It was a DotNetNuke Portal to Portal User sync, moving all custom profile fields, roles, and other user attributes from on portal to a new portal.  With a need to ensure that the user had access to the portal, the proper roles, and profile data, keeping in mind that some users might already have access to the portal.

For those of you unfamiliar with DotNetNuke to accomplish these updates were needed to the UserPortals, UserRoles, and UserProfile tables, with multiple entries in the last two per user.  In the end, the process was simple

1. Get a list of users from Portal 0 that are not host or administrator users
2. For each user on this list do the following
   1. Make sure the user has access to portal 1
   2. Make sure the user has "Registered Users" role on Portal 1
   3. Move all Custom Profile Property values from Portal 0 to Portal 1
   4. Get a list of all Roles other than "Registered Users" that the user has on Portal 1
   5. For each role in the list from d
      1. See if the role exists on portal 1
      2. If it exists make sure that the user has access to it

Overall a pretty simple process right?  Well, the implementation, in the end, had a total of 1600 lines of SQL and two CURSORS.

The Performance Nightmare

So now that you know a little about the process lets talk about the performance nightmare that we had to consider.  The above process needed to be completed for a site with 60,000 users on it initially for a test process, each user has at least 3 roles with some users having upwards of 25-30 roles a piece.

By default for each of the items above I used a simple cursor to iterate through the list of users, similar to the following:

DECLARE @UserId INT
DECLARE UserCursor CURSOR FOR
    SELECT UserId 
    FROM dbo.SRC_Users 
    WHERE IsSuperUser = 0
OPEN UserCursor
FETCH NEXT FROM UserCursor INTO @UserId

This seemed simple enough, the query was done, maybe not elegant but done.  A similar process was used for each user to open a cursor to iterate through the roles.  

With not using Cursors very often I thought all was good and ran the query.  After 8 or so hours, the query still wasn't complete and I started to dig into the execution plan and started to notice a bunch of "missing index impact" notices on the query plan.

The "fix"

After looking into things for a while a few things came to mind.  First of all my list of users would NOT be changing from when I got the list to when I actually completed the actions.  Secondly, for the records that I'm iterating with the cursor I am not updating any values.

Enter the "Fast_Forward" argument for the creation of a cursor.  Reviewing the various arguments that can be added to your cursor definition you can find that a Fast_Forward cursor is a "Forward_Only Read_Only" cursor with additional performance modifications.  Following my situation I only need to navigate through the list, no need to go backwards, and I have no need to be updating records in the cursor.

So I updated my cursor statement to look similar to the following:

DECLARE @UserId INT
DECLARE UserCursor CURSOR FAST_FORWARD FOR
    SELECT UserId 
    FROM dbo.SRC_Users 
    WHERE IsSuperUser = 0
OPEN UserCursor
FETCH NEXT FROM UserCursor INTO @UserId

After making the update it was time to test the operation again. The database was reset and I executed the query.

Final Results

After making the change and running the query on a clean databae the results were amazing.  A total execution time of only 7 minutes and 45 seconds and a reduction of CPU impact by over 20%.  Now, being a bit skeptical I had to go through and make sure that records were actually updated and that the query still did what it was supposed to.

Everything checked out and we now have a simple, quick method to run this process on a production environment.

I hope you found this information helpful and that it might help you save some time on your next SQL Cursor project!