[Solved] Performance Improvement went wrong in Production, worked fine in Test
Looking to automatically optimize YOUR SQL query? Start for free.

EverSQL Database Performance Knowledge Base

Performance Improvement went wrong in Production, worked fine in Test

Database type:

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:

Execution Plan 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.

How to optimize this SQL query?

The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:

  1. Description of the steps you can take to speed up the query.
  2. The optimal indexes for this query, which you can copy and create in your database.
  3. An automatically re-written query you can copy and execute in your database.
The optimization process and recommendations:
  1. Create Optimal Indexes (modified query below): The recommended indexes are an integral part of this optimization effort and should be created before testing the execution duration of the optimized query.
Optimal indexes for this query:
CREATE INDEX table_name_idx_s_mode ON table_name (s_mode);
The optimized query:
SELECT
        @Ref = CAST(MAX(ISNULL(CAST(table_name.ref_clnt AS INT),
        0)) + 1 AS VARCHAR (10)) 
    FROM
        table_name WITH (NOLOCK) 
    WHERE
        table_name.s_mode = 'value'

Related Articles



* original question posted on StackOverflow here.