?
Solved

optimize blocking query

Posted on 2013-12-19
4
Medium Priority
?
236 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 69

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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…

752 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