CODEWITHSUNDEEP
sql-servert-sqlcursor
Maury Markowitz
Maury Markowitz

Reputation: 9279

SQL Server cursors, horrid performance?

A semi-technical customer reported that their SQLServer-based application (3rd party) periodically crashed. They were not able to determine what set of inputs caused this, but it seemed to be happening when a particular SP was called on a particular "project code".

So I wrote a T-SQL script to run every SP with every possible input and ran it in SQL Server Studio:

--doing this makes it easier to see error messages
SET NOCOUNT ON

DECLARE @sp_name NVARCHAR(50) -- proc name
DECLARE @id_proj NVARCHAR(50) -- project name
DECLARE @SQL NVARCHAR(250) -- SQL command-

DECLARE sp_cursor CURSOR FOR SELECT SPECIFIC_NAME FROM information_schema.routines WHERE routine_type = 'PROCEDURE'

OPEN sp_cursor
FETCH NEXT FROM sp_cursor INTO @sp_name
WHILE @@FETCH_STATUS = 0
BEGIN
 DECLARE pr_cursor CURSOR FOR SELECT ProjName FROM dtaprojects
 OPEN pr_cursor
 FETCH NEXT FROM pr_cursor INTO @id_proj
    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @SQL = 'EXEC ' + @sp_name + ' ''' + @id_proj + ''''
        BEGIN TRY
            EXECUTE sp_executesql @SQL
        END TRY
        BEGIN CATCH
            PRINT 'Error when running SP ''' + @sp_name + ''' with project ''' + @id_proj + ''''
        END CATCH
        FETCH NEXT FROM pr_cursor INTO @id_proj
    END
    CLOSE pr_cursor
    DEALLOCATE pr_cursor
    FETCH NEXT FROM sp_cursor INTO @sp_name
END;
CLOSE sp_cursor
DEALLOCATE sp_cursor

--and just to be safe...
SET NOCOUNT OFF

After running for about 7 minutes, Studio would crash. Several runs, no improvement.

So I re-wrote the code in VBA in Excel using ADO:

Public Sub TestStoredProcs()
    Dim WS As Worksheet
    Set WS = ThisWorkbook.Sheets(1)
    Dim R As Long
    R = 1

    Dim SQL As String

    Dim DS As New Recordset 
    Dim DP As New Recordset 
    Dim DR As New Recordset

    Dim Cnn As New Connection
    Cnn.ConnectionString = cConStr
    Cnn.Open

    DS.Open "SELECT SPECIFIC_NAME FROM information_schema.routines WHERE routine_type='PROCEDURE'", Cnn
    Do Until DS.EOF
        DP.Open "SELECT ProjName FROM dtaprojects", Cnn
        Do Until DP.EOF
            SQL = "N'EXEC " & Trim(DS!SPECIFIC_NAME) & " ''" & Trim(DP!projname) & "'''"
            WS.Cells(R, 1) = DS!SPECIFIC_NAME
            WS.Cells(R, 2) = DP!projname

            Debug.Print "EXECUTE sp_executesql " & SQL

            DR.Open "EXECUTE sp_executesql " & SQL, Cnn
            WS.Cells(R, 3) = DR.RecordCount
            DR.Close

            R = R + 1
            DP.MoveNext
        Loop
        DP.Close
        DS.MoveNext
    Loop
    DS.Close
End Sub

This runs in 26 seconds.

Ok, so what's going on here? Are T-SQL cursors really that slow? I mean, the VBA version has to run three separate ADO queries for each iteration, each of them going over the pipe and back, and ultimately running the exact same query. So how can that possibly be 100 times faster than running the code on the server itself in it's own native language? It makes my brain hurt.

Or am I seeing a problem in Studio itself? I thought maybe it's running out of memory, but watching it run I cannot see it running after faster at the start than the end. But the possibility still exists... can someone suggest a way to test this?

Upvotes: 0

Views: 415

Answers (2)

ASH
ASH

Reputation: 20302

This is a little off topic of ADO, but definitely on topic of performance. Make sure your tables are indexed if you are dealing with very large data sets. You probably know this already, but just to make sure...

https://www.simple-talk.com/sql/learn-sql-server/sql-server-index-basics/

https://www.simple-talk.com/sql/database-administration/brads-sure-guide-to-indexes/

Upvotes: 0

Y.B.
Y.B.

Reputation: 3586

When you run all the procedures in SQL Server Studio it loads and try to display all the result sets. ADO in Excel does not. That explains the difference in time: SQL Server Studio retrieves all the rows returned by the stored procedures.

I would run the same from SQL Server Studio logging into a separate table before and after each EXECUTE sp_executesql @SQL and after crash analyse this particular procedure output.

It also might well be that SQL Server Studio crash out of memory trying to load all those result sets.

Upvotes: 2

Related Questions