[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

optimize blocking query

Posted on 2013-12-19
4
Medium Priority
?
239 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 664 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 70

Accepted Solution

by:
Scott Pletcher earned 668 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 668 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
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.
Loops Section Overview
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…

873 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