Sql-server – SQL Server index causing incorrect behavior on cursors

cursorsindexsql-server-2008-r2

I have a SQL Cursor that's behaving incorrectly as soon as we added an index to a table. Here's a trimmed-down example of the query causing issue:

DECLARE @CUR_CRDATE     NVARCHAR(8) 

DECLARE CUR_SRCHGT_UPDATE   CURSOR FOR
    SELECT  CRDATE  
    FROM    
        SRCHGT      
    WHERE   
        CUS_CD      = '0001031486'
    AND IO_GB       = '021'
    AND TOTDATE = '20150406'
    AND TOT_NO = '000001'
    AND TOT_NO_SEQ = '000003'
    AND REQ_YN = 'O'
FOR UPDATE OF JHSTAT

OPEN CUR_SRCHGT_UPDATE

FETCH NEXT FROM CUR_SRCHGT_UPDATE
INTO    @CUR_CRDATE

WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT 'TEST'

    UPDATE  SRCHGT
    SET JHSTAT      = '005'
    WHERE CURRENT OF CUR_SRCHGT_UPDATE

    FETCH NEXT FROM CUR_SRCHGT_UPDATE
    INTO    @CUR_CRDATE
END

CLOSE   CUR_SRCHGT_UPDATE
DEALLOCATE  CUR_SRCHGT_UPDATE

The problem is that the above while statement always runs double the count of number of rows returned. For example, if the above select statement defined in the cursor returns one row, the while loop would run twice.

Strangely, this only happens when I add a particular index to the table. Here's what the index looks like:

CREATE NONCLUSTERED INDEX [IX_SRCHGT] ON [Samsonite].[SRCHGT]
(
    [IO_GB] ASC,
    [MAT_CD] ASC,
    [JCOLOR] ASC,
    [CANCEL_YN] ASC,
    [CUS_CD] ASC,
    [JHSTAT] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

I've never heard of indices causing issues on cursors like this. Is this a SQL Server bug or am I looking at the wrong places?

Addendum1:

SQL Server version is the following:

Microsoft SQL Server 2008 R2 (SP3) – 10.50.6000.34 (X64)
Aug 19 2014 12:21:34
Copyright (c) Microsoft Corporation
Standard Edition (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1)

Addendum 2:

Cursors are a necessity in our logic. We have no practical means to remove them.

Edit1:

Upon request, Here's the DDL for the table

USE [samsonite_20150406]
GO

CREATE TABLE [Samsonite].[SRCHGT](
    [CRDATE] [nvarchar](8) NOT NULL,
    [CUS_CD] [nvarchar](10) NOT NULL,
    [IO_GB] [nvarchar](6) NOT NULL,
    [CRESEQ] [nvarchar](10) NOT NULL,
    [CRESEQ_SEQ] [nvarchar](10) NOT NULL,
    [MAT_CD] [nvarchar](5) NOT NULL,
    [JCOLOR] [nvarchar](4) NOT NULL,
    [CANCEL_YN] [nvarchar](1) NOT NULL,
    [STYLE] [nvarchar](5) NULL,
    [SJCODE] [nvarchar](5) NULL,
    [JPSIZE] [nvarchar](30) NULL,
    [DANGUB] [nvarchar](1) NULL,
    [T_SDANGA] [numeric](7, 0) NULL CONSTRAINT [DF_SRCHGT_SDANGA1]  DEFAULT ((0)),
    [T_SUPPLY_AMT] [numeric](12, 0) NULL CONSTRAINT [DF_SRCHGT_SUPPLY_AMT1]  DEFAULT ((0)),
    [T_VAT_AMT] [numeric](12, 0) NULL CONSTRAINT [DF_SRCHGT_VAT_AMT1]  DEFAULT ((0)),
    [T_CREQTY] [numeric](5, 0) NULL CONSTRAINT [DF_SRCHGT_CREQTY1]  DEFAULT ((0)),
    [JDANGA] [numeric](12, 0) NULL CONSTRAINT [DF__SRCHGT__JDANGA__7C1770C8]  DEFAULT ((0)),
    [SDANGA] [numeric](12, 0) NULL CONSTRAINT [DF__SRCHGT__SDANGA__7D0B9501]  DEFAULT ((0)),
    [SUPPLY_AMT] [numeric](12, 0) NULL CONSTRAINT [DF__SRCHGT__SUPPLY_A__7DFFB93A]  DEFAULT ((0)),
    [VAT_AMT] [numeric](12, 0) NULL CONSTRAINT [DF__SRCHGT__VAT_AMT__7EF3DD73]  DEFAULT ((0)),
    [DESCNT] [numeric](5, 2) NULL,
    [MAJINN] [numeric](5, 2) NULL,
    [CREQTY] [numeric](5, 0) NULL CONSTRAINT [DF__SRCHGT__CREQTY__7FE801AC]  DEFAULT ((0)),
    [CHGQTY] [numeric](5, 0) NULL CONSTRAINT [DF__SRCHGT__CHGQTY__02C46E57]  DEFAULT ((0)),
    [YETQTY] [numeric](5, 0) NULL CONSTRAINT [DF__SRCHGT__YETQTY__03B89290]  DEFAULT ((0)),
    [JHSTAT] [nvarchar](3) NULL CONSTRAINT [DF__SRCHGT__JHSTAT__04ACB6C9]  DEFAULT ('001'),
    [CHDATE] [nvarchar](8) NULL,
    [REMARK] [nvarchar](200) NULL,
    [POS_REMARK] [nvarchar](50) NULL,
    [SAP_REMARK] [nvarchar](50) NULL,
    [REQ_YN] [nvarchar](1) NULL CONSTRAINT [DF_SRCHGT_REQ_YN]  DEFAULT ('O'),
    [ORDER_GB] [nvarchar](1) NULL,
    [TOTDATE] [nvarchar](8) NULL,
    [TOT_NO] [nvarchar](6) NULL,
    [TOT_NO_SEQ] [nvarchar](6) NULL,
    [TAX_FREE] [nvarchar](3) NULL CONSTRAINT [DF_SRCHGT_WEB_HS]  DEFAULT ('N'),
    [INDATE] [datetime] NULL,
    [SUDATE] [datetime] NULL,
    [EMPNUM] [nvarchar](10) NULL,
    [JEMEIP_YN] [nvarchar](1) NULL,
    [BEF_JHSTAT] [nvarchar](3) NULL,
 CONSTRAINT [PK_SRCHGT] PRIMARY KEY CLUSTERED 
(
    [CRDATE] ASC,
    [CUS_CD] ASC,
    [IO_GB] ASC,
    [CRESEQ] ASC,
    [CRESEQ_SEQ] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100) ON [PRIMARY]
) ON [PRIMARY]

GO

Best Answer

Try with this modification:

Once with :

WITH (INDEX (IX_SRCHGT))   -- the present case
Once with 
WITH (INDEX (PK_SRCHGT)) 


DECLARE CUR_SRCHGT_UPDATE   CURSOR FOR
SELECT  CRDATE  
FROM    
    SRCHGT     WITH (INDEX (IX_SRCHGT)) 
    --SRCHGT     WITH (INDEX (PK_SRCHGT)) 
WHERE

or use keyset as type of the Cursor

DECLARE CUR_SRCHGT_UPDATE   CURSOR KEYSET FOR
SELECT  CRDATE  
FROM    
   SRCHGT    
...

Related Solutions

Sql-server – Parent-Child Tree Hierarchical ORDER

OK, enough brain cells are dead.

SQL Fiddle

WITH cte AS
(
  SELECT 
    [ICFilterID], 
    [ParentID],
    [FilterDesc],
    [Active],
    CAST(0 AS varbinary(max)) AS Level
  FROM [dbo].[ICFilters]
  WHERE [ParentID] = 0
  UNION ALL
  SELECT 
    i.[ICFilterID], 
    i.[ParentID],
    i.[FilterDesc],
    i.[Active],  
    Level + CAST(i.[ICFilterID] AS varbinary(max)) AS Level
  FROM [dbo].[ICFilters] i
  INNER JOIN cte c
    ON c.[ICFilterID] = i.[ParentID]
)

SELECT 
  [ICFilterID], 
  [ParentID],
  [FilterDesc],
  [Active]
FROM cte
ORDER BY [Level];

MySQL looking up more rows than needed (indexing issue)

Your indexes are fine for the two types of queries you mentioned.

This query will be satisfied by traversing the clustered index on the primary key...

[...] WHERE participant_id = x AND question_id = y AND given_answer_id = z;

...and this one is satisfied by the index on 'question_id':

[...] WHERE question_id = x;

The output of EXPLAIN SELECT is not telling you what you think it is telling you, because the value shown in rows is an estimate of the number of rows the server will need to consider, not the actual rows it will examine. For InnoDB these are based on index statistics.

rows

The rows column indicates the number of rows MySQL believes it must examine to execute the query.

For InnoDB tables, this number is an estimate, and may not always be exact.

^{— http://dev.mysql.com/doc/refman/5.5/en/explain-output.html#explain_rows}

The optimizer gathers information about different possible query plans, and chooses the one with the lowest cost. The information shown in EXPLAIN is the information the optimizer gathered about the plan it selected.

When type is ref and key is not NULL, this means that the name listed in the key column is the name of the index that the optimizer has chosen to use to find the desired rows, so your query plan looks exactly as it should.

Note, sometimes you will see Using index in the Extra column and a lot of people assume that this means an index is being used, or that no index is being used when that doesn't appear, but that's not correct, either. Using index describes a special case called a "covering index" -- it does not indicate whether an index is being used to locate the rows of interest.

It's possible that running ANALYZE [LOCAL] TABLE would cause the numbers in rows shown by EXPLAIN to differ, but this is a simple query and selecting this index is an obvious choice for the optimizer to make, so ANALYZE TABLE is unlikely to make any actual difference in performance.

It is possible, however, that your overall performance might see some marginal improvement with an occasional OPTIMIZE [LOCAL] TABLE, because you are not inserting rows in primary key order (as would be the case with an auto_increment primary key)... but on large tables this can be time-consuming because it rebuilds a new copy of the table... but, again, I wouldn't expect any significant change.

Best Answer

Related Solutions

Sql-server – Parent-Child Tree Hierarchical ORDER

MySQL looking up more rows than needed (indexing issue)

Related Question