We have an application supported by vendor and there was a piece of code which was doing very heavy logical reads and time consuming inside a procedure, hence I proposed them to tweak the query a bit to have much lesser IO and time, it worked well in test in terms of data and performance however its failing in production and returning different data, we had to rollback the changes. Need your expert advise on this.
This was tested for data in Test environment for more than 3 months and we never had any data issue. Started failing sparingly in Production immediately after the deployment and was producing inconsistent data.
Existing Query:
SELECT @Ref= CAST(MAX(ISNULL(CAST(ref_clnt AS INT),0))+1 AS VARCHAR(10))
FROM table_name WITH(NOLOCK)
WHERE s_mode='value'
Proposed Query:
SELECT @Ref = ref_clnt+1 FROM table_name WITH(NOLOCK)
WHERE RefNo = (SELECT MAX(RefNo) FROM table_name WHERE s_mode = 'value')
The DDL of table is as below:
CREATE TABLE [dbo].[table_name](
[RefNo] [dbo].[udt_RefNo] NOT NULL,
[S_Mode] [varchar](10) NOT NULL,
[ref_clnt] [varchar](50) NULL)
CONSTRAINT [PK_table_name] PRIMARY KEY CLUSTERED
(
[RefNo] ASC
)
Providing only those columns from definition which is used in the query.
Udt_RefNo
is a user defined datatype as:
CREATE TYPE [dbo].[udt_RefNo] FROM [char](16) NOT NULL
GO
Version of SQL Server: Microsoft SQL Server 2014 (SP3) Copyright (c) Microsoft Corporation Enterprise Edition (64-bit).
Non-clustered index covering columns as shown below:
CREATE NONCLUSTERED INDEX [ncidx_table_name_1] ON [dbo].[table_name]
(
[S_Mode] ASC,
[S_Status] ASC
)
INCLUDE ( [ref_clnt])
Please find the query plans as requested:
Comparison of Number of Reads after enabling statistics IO and time:
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
(1 row affected)
Table 'table_name'. Scan count 1, logical reads 2732, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(1 row affected)
SQL Server Execution Times:
CPU time = 157 ms, elapsed time = 161 ms.
(1 row affected)
Table 'table_name'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(1 row affected)
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
Vendor has come back with suggestion as
SELECT MAX(ISNULL(ref_clnt,0))+1 FROM table_name WITH(NOLOCK) WHERE S_Mode='value'
Issue is - how to test them as all of them seems to work for most of the scenario however fails only in few cases. I am not very much aware of the application and its vendor supported one, so can't get much details like how does business logic work for underlying procedure.