CODEWITHSUNDEEP
sqlsql-server
Odatas
Odatas

Reputation: 178

Cursorfetch error because of wrong variable type

i try to do a benchmark for SQL Statments for SQLServer.

I found a good benchmark loop online: https://github.com/jOOQ/jOOQ/blob/master/jOOQ-examples/Benchmarks/SQLServer/Benchmarking%20SQL%20Server%20(absolute).sql

DECLARE @ts DATETIME;
DECLARE @repeat INT = 10000;
DECLARE @r INT;
DECLARE @i INT;
DECLARE @dummy VARCHAR;

DECLARE @s1 CURSOR;
DECLARE @s2 CURSOR;

SET @r = 0;
WHILE @r < 5
BEGIN
  SET @r = @r + 1

  SET @s1 = CURSOR FOR
  -- Paste statement 1 here
  SELECT 1 x;

  SET @s2 = CURSOR FOR
  -- Paste statement 2 here
  WITH t(v) AS (
    SELECT 1
    UNION ALL
    SELECT v + 1 FROM t WHERE v < 10
  )
  SELECT * FROM t

  SET @ts = current_timestamp;
  SET @i = 0;
  WHILE @i < @repeat
  BEGIN
    SET @i = @i + 1

    OPEN @s1;
    FETCH NEXT FROM @s1 INTO @dummy;
    WHILE @@FETCH_STATUS = 0
    BEGIN
      FETCH NEXT FROM @s1 INTO @dummy;
    END;

    CLOSE @s1;
  END;

  DEALLOCATE @s1;
  PRINT 'Run ' + CAST(@r AS VARCHAR) + ', Statement 1: ' + CAST(DATEDIFF(ms, @ts, current_timestamp) AS VARCHAR) + 'ms';

  SET @ts = current_timestamp;
  SET @i = 0;
  WHILE @i < @repeat
  BEGIN
    SET @i = @i + 1

    OPEN @s2;
    FETCH NEXT FROM @s2 INTO @dummy;
    WHILE @@FETCH_STATUS = 0
    BEGIN
      FETCH NEXT FROM @s2 INTO @dummy;
    END;

    CLOSE @s2;
  END;

  DEALLOCATE @s2;
  PRINT 'Run ' + CAST(@r AS VARCHAR) + ', Statement 2: ' + CAST(DATEDIFF(ms, @ts, current_timestamp) AS VARCHAR) + 'ms';
END;

PRINT '';
PRINT 'Copyright Data Geekery GmbH';
PRINT 'https://www.jooq.org/benchmark';

This works great for when the statments i test only have one column they return. For example:

Select ID from Items Where ID=2;

But as soon as i try to select multiple rows like

Select * from Items Where ID=2;

i get the error:

Msg 16924, Level 16, State 1, Line 135 Cursorfetch: The number of variables declared in the INTO list must match that of selected columns.

So the column this concerns is

FETCH NEXT FROM @s1 INTO @dummy;

So as far as i understand the issue is that i try to fit to much columsn into the dummy variable. But how do i fix it? Im not so long working with SQL so any help would be appreciated.

Upvotes: 0

Views: 86

Answers (1)

David Browne - Microsoft
David Browne - Microsoft

Reputation: 89006

That is not a useful or simple way to test queries.

It’s a lot of code so it’s not particularly easy, and uses a cursor to process the results so it includes the cost of processing the results on the server with a cursor, which is not present normally.

Normally you just run the query in SSMS and look at the Actual Execution Plan and perhaps the time and IO statistics, and perhaps the client statistics. The query results should be returned to the client, because that's what happens in production, and you should consider the time needed to transmit results over the network when benchmarking.

If you need to run a query without returning data to the client, you can use a pattern like

go
set statistics io on
set statistics time on
go
drop table if exists #foo;

with q as
(
  select ...
  from ...
)
select *
into #foo
from q
go 5
set statistics io off
set statistics time off
go

Upvotes: 1

Related Questions