Skip to content

The Dummy Programmer

Stories of daily programming

  • Home
  • The Dummy Programmer Robot
    • Overview
    • Version history & downloads
    • Tutorials
      • TDP Robot – The basics
      • A simple SQL Server backup solution
      • A simple SQL Server backup solution – A next step
  • My other dummy projects
  • Games
    • Space Y: An Asteroids clone
  • Services
    • What’s my user agent
    • What’s my IP address
  • About
  • Toggle search form

Improving SQL Server cursor performance

Posted on June 25, 2019June 25, 2019 By

Hi everyone! Today I want to talk about a performance problem I had a few days ago using a SQL Server cursor.

Imagine using a cursor in the following classic way:

DECLARE @ID INT
DECLARE @ITEM_CODE VARCHAR(50)

DECLARE MY_CURSOR CURSOR FOR 
SELECT ID, ITEM_CODE
FROM MY_TABLE T
LEFT JOIN A_TABLE T1 ON T.ID = T1.ID
LEFT JOIN A_TABLE2 T2 ON T.ID = T2.ID
WHERE MY_CONDITION = 0 

OPEN MY_CURSOR

FETCH NEXT FROM MY_CURSOR INTO @ID, @ITEM_CODE

WHILE @@FETCH_STATUS = 0
BEGIN
  PRINT @ID
  PRINT @ITEM_CODE

  FETCH NEXT FROM MY_CURSOR INTO @ID, @ITEM_CODE
END

CLOSE MY_CURSOR
DEALLOCATE MY_CURSOR

Seems incredible, but the first FETCH required about 20 seconds to complete without a valid reason…

Finally, I found a solution adding the attributes READ_ONLY, LOCAL, and FORWARD_ONLY to the cursor declaration:

DECLARE MY_CURSOR CURSOR READ_ONLY LOCAL FORWARD_ONLY FOR 
  SELECT ID, ITEM_CODE 
  FROM MY_TABLE T 
  LEFT JOIN A_TABLE T1 ON T.ID = T1.ID 
  LEFT JOIN A_TABLE2 T2 ON T.ID = T2.ID 
  WHERE MY_CONDITION = 0

After this change the time has dropped to around zero seconds.

Clearly, the READ_ONLY and FORWARD_ONLY attributes are applicable only if no changes are to be made to the underlying data and if it is sufficient to read the cursor only forwards.

I suggest you to consult Microsoft books online for more information about those attirbutes. 😉

Sql Server, Sql Server 2016

Post navigation

Previous Post: Failed to decode downloaded font: (your web application address)
Next Post: Google Chrome OnBlur endless loop

Related Posts

Multi-tenant database in SQL Server Sql Server 2016
Sql Server error: A severe error occurred on the current command.  The results, if any, should be discarded. Sql Server
SQL Server and SARGables predicates Sql Server
Load a huge amount of data in a Sql Server table BCP
Sql Server: apply an aggregate function to fields values in a row Sql Server
Sql Server Instant File Initialization Sql Server

Search

Related posts

  • Get SQL Server version in T-SQL
  • Reset Sql Server cache
  • How to know who is blocking a query in Sql Server
  • SQL Server and SARGables predicates

Categories

  • .NET 6 (1)
  • AJAX (1)
  • Android (2)
  • Apache (4)
  • ASP.NET (9)
  • ASP.NET MVC (3)
  • Avalonia UI (1)
  • BCP (1)
  • Bitlocker (2)
  • C# (14)
  • CentOS (4)
  • ClosedXML (1)
  • CLR (1)
  • DNS (1)
  • Encryption (3)
  • Excel (2)
  • FuelPHP (3)
  • Games (2)
  • Google Chrome (1)
  • GSuite (1)
  • HTML (1)
  • Imagick (2)
  • Javascript (1)
  • Kindle (1)
  • LAMP (3)
  • Linux (6)
  • MariaDB (2)
  • Mathematics (2)
  • MySql (4)
  • NPOI (1)
  • Office 365 (1)
  • Perl (1)
  • PHP (6)
  • Programming (1)
  • Remote desktop (1)
  • SFTP (2)
  • Sockets (3)
  • Sql Server (18)
  • Sql Server 2016 (14)
  • Sql Server 2019 (1)
  • SSL (2)
  • Task scheduler (1)
  • Telerik ASP.NET AJAX (2)
  • The Dummy Programmer Chat (2)
  • The Dummy Programmer Robot (6)
  • Threading (5)
  • Tools (1)
  • TPL (3)
  • TypeScript (3)
  • Ubuntu (4)
  • Virtualization software (3)
  • Visual Studio (1)
  • Visual Studio Code (2)
  • Web fonts (1)
  • Web programming (6)
  • Windows (12)
  • Windows 10 (15)
  • Windows Forms (1)
  • Windows Server (6)

Copyright © 2023 The Dummy Programmer | Privacy Policy | Terms of use |

Powered by PressBook Masonry Dark