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

The ‘Microsoft.ACE.OLEDB.12.0’ provider is not registered on the local machine Excel
Get all referencing tables of a table in Sql Server Sql Server
Getting SQL Server sample databases Sql Server
Sql Server error: Cannot construct data type date, some of the arguments have values which are not valid. Sql Server
Sql Server add constraint WITH NOCHECK Sql Server
How to know who is blocking a query in Sql Server 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 (4)
  • 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