Solved

optimize blocking query

Posted on 2013-12-19
4
229 Views
Last Modified: 2013-12-27
Hello,

I try to optimize this query :
SELECT TOP 1 evt_message.evt_message_id,
             evt_message.evt_id,
             evt_message.evt_key,
             evt_message.whse,
             evt_message.validate_key,
             evt_message.nbr_of_retry,
             evt_message.stat_code,
             evt_message.error_seq_nbr,
             evt_message.cl_message_id,
             evt_message.create_date_time,
             evt_message.mod_date_time,
             evt_message.user_id,
             evt_message.schema_id,
             evt_message.els_actvty_code,
             evt_message.cd_master_id
FROM   evt_message WITH (updlock, rowlock)
WHERE  ( ( ( evt_message.stat_code >= @P1 )
           AND ( evt_message.stat_code <= @P2 ) )
         AND ( evt_message.nbr_of_retry < @P3 ) )
ORDER  BY evt_message.evt_message_id ASC
OPTION ( fast 1 )  

The structure table is

USE [SADRFWMSPRD]
GO

/****** Object:  Table [dbo].[evt_MESSAGE]    Script Date: 12/19/2013 11:25:00 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[evt_MESSAGE](
      [evt_MESSAGE_ID] [int] NOT NULL,
      [evt_ID] [int] NULL,
      [evt_KEY] [varchar](100) NULL,
      [WHSE] [varchar](3) NULL,
      [VALIDATE_KEY] [varchar](100) NULL,
      [NBR_OF_RETRY] [int] NOT NULL,
      [STAT_CODE] [int] NOT NULL,
      [ERROR_SEQ_NBR] [int] NOT NULL,
      [CL_MESSAGE_ID] [int] NULL,
      [CREATE_DATE_TIME] [datetime] NULL,
      [MOD_DATE_TIME] [datetime] NULL,
      [USER_ID] [varchar](15) NULL,
      [SCHEMA_ID] [varchar](3) NULL,
      [ELS_ACTVTY_CODE] [varchar](15) NULL,
      [CD_MASTER_ID] [int] NULL,
 CONSTRAINT [PK_evt_MESSAGE] PRIMARY KEY CLUSTERED
(
      [evt_MESSAGE_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = OFF, FILLFACTOR = 80) ON [PKMSDATA]
) ON [PKMSDATA]

GO

SET ANSI_PADDING ON
GO

ALTER TABLE [dbo].[evt_MESSAGE] ADD  CONSTRAINT [DF_evt_MESSAGE_NBR_OF_RETRY]  DEFAULT ((0)) FOR [NBR_OF_RETRY]
GO

ALTER TABLE [dbo].[evt_MESSAGE] ADD  CONSTRAINT [DF_evt_MESSAGE_STAT_CODE]  DEFAULT ((0)) FOR [STAT_CODE]
GO

ALTER TABLE [dbo].[evt_MESSAGE] ADD  CONSTRAINT [DF_evt_MESSAGE_ERROR_SEQ_NBR]  DEFAULT ((0)) FOR [ERROR_SEQ_NBR]
GO

ALTER TABLE [dbo].[evt_MESSAGE]  WITH NOCHECK ADD  CONSTRAINT [FK_evt_MESSAGE_TO_CD_MASTER] FOREIGN KEY([CD_MASTER_ID])
REFERENCES [dbo].[CD_MASTER] ([CD_MASTER_ID])
GO

ALTER TABLE [dbo].[evt_MESSAGE] NOCHECK CONSTRAINT [FK_evt_MESSAGE_TO_CD_MASTER]
GO


@P1 = 20, @P2 = 22, @P3 = 3

Thanks

Regards
0
Comment
Question by:bibi92
  • 2
4 Comments
 
LVL 13

Expert Comment

by:magarity
ID: 39730685
That looks pretty good actually with your clustered index on the order by field.

What is the rowcount and the distribution of values in evt_message.stat_code and evt_message.nbr_of_retry ?  A non-clustered index on one or both of those MIGHT help but not if there are only a few choices relative to how many rows in the table.

You could convert this:
 ( evt_message.stat_code >= @P1 )
           AND ( evt_message.stat_code <= @P2 )
to:
 evt_message.stat_code BETWEEN @P1 and @P2.
0
 
LVL 15

Assisted Solution

by:JimFive
JimFive earned 166 total points
ID: 39732299
Using a subquery will get rid of the sort required by TOP
Select <fields>
FROM   evt_message evt1 WITH (updlock, rowlock)
WHERE evt1.evt_message_id = (SELECT Min(evt2.evt_message_id)
                                                    FROM evt_message evt2
                                                   WHERE ( ( ( evt2.evt_message.stat_code >= @P1 )
                                                   AND ( evt2.evt_message.stat_code <= @P2 ) )
                                                   AND ( evt2.evt_message.nbr_of_retry < @P3 ) ))
OPTION ( fast 1 )   

Open in new window

0
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 167 total points
ID: 39733044
If that's typically how you query the table, you should change the clustered index to:
( stat_code, nbr_of_retry, evt_MESSAGE_ID ) --esp. if you make "nbr_of_retry" smallint instead of int
--or ( stat_code, evt_MESSAGE_ID ), although that may be less efficient if you have many different "nbr_of_retry" values per stat_code range

Once you get the table properly clustered, that query will ZIP.


Here are the T-SQL commands to do that:

ALTER TABLE dbo.evt_message  DROP CONSTRAINT PK_evt_MESSAGE

CREATE UNIQUE CLUSTERED INDEX CL_evt_message ON dbo.evt_message ( stat_code, nbr_of_retry, evt_MESSAGE_ID ) WITH ( FILLFACTOR = 95 ) ON [PKMSDATA]

--If you really need a separate index on MESSAGE_ID alone,
ALTER TABLE dbo.evt_message  ADD CONSTRAINT PK_evt_MESSAGE PRIMARY KEY NONCLUSTERED ( evt_MESSAGE_ID ) WITH ( FILLFACTOR = 95 ) ON [PKMSDATA]
0
 
LVL 13

Assisted Solution

by:magarity
magarity earned 167 total points
ID: 39738376
I also think you should remove "OPTION ( fast 1 )" because your query specifies only one row with the TOP 1 already.  Using the fast option causes the query plan to be considered differently and this may be conflicting with your actual requirement of only getting one row in the first place.  Use the fast option for example when there are a lot of rows to retrieve and a lot of processing by the calling application so it can get started on the first rows asap.  In this case, there is only one row anyway.
Before you re-index anything, please answer my questions above re: how many codes and dates typically per ID value.  Depending on this answer different indexing will either help a lot or actually hurt.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Stored procedure 4 32
MS SQL order by with "over" statement and row_number() 11 49
SQL Agent Timeout 5 58
SQL Syntax:  How to Find Commonality Among Similar Results 2 49
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

778 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question