Problem with query taking a long time.

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Reza Raquib (RezaRaquib_at_discussions.microsoft.com)
Date: 02/15/05 

Date: Tue, 15 Feb 2005 07:33:03 -0800

I have a large SQL table, called Event, that has 4.6 million records in it.
My problem is that one particular query on the table is taking over 30
seconds to run. All the other queries that I have take under 1 second to
run. I have tried numerous index changes, but it seems that nothing helps.
I have even removed all the indexes (excluding the primary key and clustered
index), and had the Index Tuning Wizard generate new indexes, but with no
luck. Below I have listed the SQL Queries that I am running, and the SQL
statements for creating all of the related tables and views. You will notice
that I am calling a view to retrieve the records. Any suggestions would be
greatly appreciated.

Good Query 1 (< 1 sec to run on 4.6 million records)
SELECT top 15 * FROM vw_HistEventDisplayGrid
WHERE SeqNo NOT IN (SELECT TOP 0 SeqNo FROM vw_HistEventDisplayGrid
WHERE (NAME like 'a%') AND (Sphere = 0 OR Sphere IN (SELECT Partition FROM
Sphere WHERE OperPrivId = 33))
ORDER BY NAME ASC, EDATE DESC)
AND (NAME like 'a%') AND (Sphere = 0 OR Sphere IN (SELECT Partition FROM
Sphere WHERE OperPrivId = 33))
ORDER BY NAME ASC, EDATE DESC

Good Query 2 (< 1 sec to run on 4.6 million records)
SELECT top 15 * FROM vw_HistEventDisplayGrid
WHERE SeqNo NOT IN (SELECT TOP 0 SeqNo FROM vw_HistEventDisplayGrid
WHERE (Sphere = 0 OR Sphere IN (SELECT Partition FROM Sphere WHERE
OperPrivId = 33))
ORDER BY NAME DESC, EDATE DESC)
AND (Sphere = 0 OR Sphere IN (SELECT Partition FROM Sphere WHERE OperPrivId
= 33))
ORDER BY NAME DESC, EDATE DESC

Bad Query (> 30 seconds on 4.6 million records)
SELECT top 15 * FROM vw_HistEventDisplayGrid
WHERE SeqNo NOT IN (SELECT TOP 0 SeqNo FROM vw_HistEventDisplayGrid
WHERE (Sphere = 0 OR Sphere IN (SELECT Partition FROM Sphere WHERE
OperPrivId = 33))
ORDER BY NAME ASC, EDATE DESC)
AND (Sphere = 0 OR Sphere IN (SELECT Partition FROM Sphere WHERE OperPrivId
= 33))
ORDER BY NAME ASC, EDATE DESC

SQL for creating tables and views:
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[vw_HistEventDisplayGrid]') and OBJECTPROPERTY(id,
N'IsView') = 1)
drop view [dbo].[vw_HistEventDisplayGrid]
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[Event]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Event]
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[Oper]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Oper]
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[Sphere]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Sphere]
GO

CREATE TABLE [dbo].[Event] (
        [Seqno] uniqueidentifier ROWGUIDCOL NOT NULL ,
        [Priority] [smallint] NULL ,
        [Cat] [smallint] NULL ,
        [PnlNo] [smallint] NULL ,
        [EDate] [datetime] NULL ,
        [Number] [smallint] NULL ,
        [Status] [smallint] NULL ,
        [Facno] [smallint] NULL ,
        [Badge] [bigint] NULL ,
        [Class] [varchar] (48) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
        [Description] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
        [Name] [varchar] (48) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
        [Arch] [smallint] NULL ,
        [AckOpr] [smallint] NULL ,
        [AckTStamp] [datetime] NULL ,
        [Actions] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
        [RespReq] [bit] NULL ,
        [Sphere] [int] NULL ,
        [Tag] [int] NULL ,
        [Pending] [bit] NULL ,
        [HasPhoto] [bit] NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[Oper] (
        [OpNo] [smallint] NOT NULL ,
        [ScrName] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
        [Name] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
        [Passwrd] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
        [Priv] [int] NULL ,
        [Changed] [datetime] NULL ,
        [GroupNo] [smallint] NULL ,
        [AckTO] [smallint] NULL ,
        [Opt] [smallint] NULL ,
        [Flag] [bit] NULL ,
        [AutoAck] [smallint] NULL ,
        [ShowPrior] [smallint] NULL ,
        [UP_CBCaptions] [bit] NULL ,
        [UP_CBIcons] [bit] NULL ,
        [UP_UseBg] [bit] NULL ,
        [UP_BgIndex] [smallint] NULL ,
        [UP_X] [smallint] NULL ,
        [UP_Y] [smallint] NULL ,
        [UP_Width] [smallint] NULL ,
        [UP_Height] [smallint] NULL ,
        [UP_Monitor] [bit] NULL ,
        [UP_Control] [bit] NULL ,
        [UP_ShowPhoto] [bit] NULL ,
        [LogOffTime] [int] NULL ,
        [EventViewCount] [int] NULL ,
        [EventViewDay] [bit] NULL ,
        [EventViewSpan] [int] NULL ,
        [WebRights] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
        [OperFontName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
        [OperFontSize] [int] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Sphere] (
        [OperPrivId] [smallint] NOT NULL ,
        [GroupId] [smallint] NOT NULL ,
        [GroupNo] [smallint] NOT NULL ,
        [Changed] [datetime] NULL ,
        [Partition] [int] NOT NULL ,
        [Restriction] [smallint] NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Oper] WITH NOCHECK ADD
        CONSTRAINT [PK_Oper] PRIMARY KEY CLUSTERED
        (
                [OpNo]
        ) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Sphere] WITH NOCHECK ADD
        CONSTRAINT [PK_Sphere] PRIMARY KEY CLUSTERED
        (
                [OperPrivId],
                [GroupId],
                [GroupNo]
        ) ON [PRIMARY]
GO

 CREATE CLUSTERED INDEX [EdateX_Event] ON [dbo].[Event]([EDate]) ON
[PRIMARY]
GO

ALTER TABLE [dbo].[Event] ADD
        CONSTRAINT [DF_Event_Seqno] DEFAULT (newid()) FOR [Seqno],
        CONSTRAINT [DF__Event__HasPhoto__3DE82FB7] DEFAULT (0) FOR [HasPhoto],
        CONSTRAINT [PK_Event] PRIMARY KEY NONCLUSTERED
        (
                [Seqno]
        ) ON [PRIMARY]
GO

 CREATE INDEX [Event50] ON [dbo].[Event]([AckOpr], [EDate] DESC , [Seqno],
[Class], [Description]) ON [PRIMARY]
GO

 CREATE INDEX [Event51] ON [dbo].[Event]([Description], [EDate] DESC ,
[Seqno], [Class]) ON [PRIMARY]
GO

 CREATE INDEX [Event52] ON [dbo].[Event]([Class], [EDate] DESC , [Seqno],
[Description]) ON [PRIMARY]
GO

 CREATE INDEX [Event53] ON [dbo].[Event]([Class] DESC , [EDate] DESC ,
[Seqno], [Description]) ON [PRIMARY]
GO

 CREATE INDEX [Event54] ON [dbo].[Event]([Description]) ON [PRIMARY]
GO

 CREATE INDEX [Event55] ON [dbo].[Event]([AckOpr]) ON [PRIMARY]
GO

 CREATE INDEX [Event56] ON [dbo].[Event]([Sphere]) ON [PRIMARY]
GO

 CREATE INDEX [Event59] ON [dbo].[Event]([Sphere], [Name]) ON [PRIMARY]
GO

 CREATE INDEX [Event57] ON [dbo].[Event]([Name], [EDate] DESC ) ON [PRIMARY]
GO

 CREATE INDEX [Event58] ON [dbo].[Event]([Name] DESC , [EDate] DESC ) ON
[PRIMARY]
GO

ALTER TABLE [dbo].[Oper] ADD
        CONSTRAINT [DF_Oper_OpNo] DEFAULT (1) FOR [OpNo],
        CONSTRAINT [DF_Oper_Priv] DEFAULT (0) FOR [Priv],
        CONSTRAINT [DF_Oper_GroupNo] DEFAULT (0) FOR [GroupNo],
        CONSTRAINT [DF_Oper_AckTO] DEFAULT (60) FOR [AckTO],
        CONSTRAINT [DF_Oper_AutoAck] DEFAULT (0) FOR [AutoAck],
        CONSTRAINT [DF_Oper_UP_ShowPhoto] DEFAULT (0) FOR [UP_ShowPhoto],
        CONSTRAINT [DF_Oper_LogOffTime] DEFAULT (5) FOR [LogOffTime],
        CONSTRAINT [DF_Oper_EventViewCount] DEFAULT (50) FOR [EventViewCount]
GO

 CREATE INDEX [PKO_Group] ON [dbo].[Oper]([GroupNo]) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Sphere] ADD
        CONSTRAINT [DF_Sphere_OperPrivId] DEFAULT (1) FOR [OperPrivId],
        CONSTRAINT [DF_Sphere_GroupId] DEFAULT (0) FOR [GroupId],
        CONSTRAINT [DF_Sphere_GroupNo] DEFAULT (1) FOR [GroupNo],
        CONSTRAINT [DF_Sphere_Partition] DEFAULT (0) FOR [Partition]
GO

 CREATE INDEX [PKS_GroupID] ON [dbo].[Sphere]([GroupId]) ON [PRIMARY]
GO

 CREATE INDEX [PKS_Group] ON [dbo].[Sphere]([GroupNo]) ON [PRIMARY]
GO

 CREATE INDEX [PKS_OperPrivID] ON [dbo].[Sphere]([OperPrivId]) ON [PRIMARY]
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE VIEW dbo.vw_HistEventDisplayGrid
AS
SELECT dbo.Event.Seqno, dbo.Event.Priority, dbo.Event.Cat,
dbo.Event.PnlNo, dbo.Event.EDate, dbo.Event.Number, dbo.Event.Status,
dbo.Event.Facno,
                      dbo.Event.Badge, dbo.Event.Class,
dbo.Event.Description, dbo.Event.Name, dbo.Event.Arch, dbo.Event.AckOpr,
dbo.Event.AckTStamp,
                      dbo.Event.Actions, dbo.Event.RespReq,
dbo.Event.Sphere, dbo.Event.Tag, dbo.Event.Pending, dbo.Event.HasPhoto,
dbo.Oper.ScrName
FROM dbo.Event LEFT OUTER JOIN
                      dbo.Oper ON dbo.Event.AckOpr = dbo.Oper.OpNo

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

Relevant Pages

  • Re: arrrggghhh!! Select statements..
    ... Note also the BETWEEN(date, sdate, edate) is an Xbase function. ... Both work in VFP but as soon as you start communicating with other SQL ... invonum linenum rptrnum product amount ...
    (microsoft.public.fox.programmer.exchange)
  • Re: how to generate a randomized "seqno" column
    ... Michel Cadot schrieb: ... | rows a unique, random seqno. ... | inside the loop, to call repeatedly dbms_crypto.randomInteger until he's ... SQL> select id from t order by id; ...
    (comp.databases.oracle.server)
  • Re: how to generate a randomized "seqno" column
    ... | rows a unique, random seqno. ... | inside the loop, to call repeatedly dbms_crypto.randomInteger until he's ... | able to find a new unique seqno. ... SQL> select id from t order by id; ...
    (comp.databases.oracle.server)
  • Converting Crosstab Query to a Pass-Through Query
    ... I have created a crosstab query in Access that looks like ... TRANSFORM CountAS CountOfseqno ... query and directly in Query Analyzer in SQL 2000. ...
    (microsoft.public.access.queries)