Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 242
  • Last Modified:

optimize blocking query

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
bibi92
Asked:
bibi92
  • 2
3 Solutions
 
magarityCommented:
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
 
JimFiveCommented:
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
 
Scott PletcherSenior DBACommented:
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
 
magarityCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now