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 notic
e
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"Reza Raquib" <RezaRaquib@.discussions.microsoft.com> wrote in message
news:3B46DDF5-8361-4C96-9925-70ACB477B03F@.microsoft.com...
> 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
>
I'm not sure if I'm missing something, but I see no differences between
these two queries -- did you paste the wrong one?
Also, did you really mean SELECT TOP 0 ?
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--|||The top 0 is dynamic, as a form of paging. Replace the top 0 with a top 90,
that would be more representative. The only difference between these 2
queries are the ordering of the NAME column. The good query has the NAME
ordered descending. The bad query has the NAME ascending.
Thanks.
"Adam Machanic" wrote:
> "Reza Raquib" <RezaRaquib@.discussions.microsoft.com> wrote in message
> news:3B46DDF5-8361-4C96-9925-70ACB477B03F@.microsoft.com...
> OperPrivId
> OperPrivId
>
> I'm not sure if I'm missing something, but I see no differences between
> these two queries -- did you paste the wrong one?
> Also, did you really mean SELECT TOP 0 ?
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
>|||"Reza Raquib" <RezaRaquib@.discussions.microsoft.com> wrote in message
news:4E9E19FD-624F-4D2F-AE2B-171783E7266D@.microsoft.com...
> The top 0 is dynamic, as a form of paging. Replace the top 0 with a top
90,
> that would be more representative. The only difference between these 2
> queries are the ordering of the NAME column. The good query has the NAME
> ordered descending. The bad query has the NAME ascending.
I think you'll find that re-doing the paging a bit differently is going to
be the best solution -- however, paging is never an easy problem in SQL
Server...
Give this a try and tell me if it helps at all -- or if it compeltely bombs
:) -- (replace both <N>s with the last row number you want, and <M> with the
first row number you want):
SELECT *
FROM (
SELECT TOP <n>
E1.Seqno,
E1.Priority,
E1.Cat,
E1.PnlNo,
E1.EDate,
E1.Number,
E1.Status,
E1.Facno,
E1.Badge,
E1.Class,
E1.Description,
E1.Name,
E1.Arch,
E1.AckOpr,
E1.AckTStamp,
E1.Actions,
E1.RespReq,
E1.Sphere,
E1.Tag,
E1.Pending,
E1.HasPhoto,
E1.ScrName,
COUNT(*) AS Rank
FROM vw_HistEventDisplayGrid E1
JOIN vw_HistEventDisplayGrid E2 ON E2.Name <= E1.Name
AND (E2.Name < E1.Name OR (E2.EDate > E1.EDate))
WHERE EXISTS
(SELECT 1
WHERE E1.Sphere = 0
UNION ALL
SELECT 1
FROM Sphere
WHERE OperPrivId = 33
AND Sphere.Partition = E1.Sphere)
AND EXISTS
(SELECT 1
WHERE E2.Sphere = 0
UNION ALL
SELECT 1
FROM Sphere
WHERE OperPrivId = 33
AND Sphere.Partition = E2.Sphere)
GROUP BY
E1.Seqno,
E1.Priority,
E1.Cat,
E1.PnlNo,
E1.EDate,
E1.Number,
E1.Status,
E1.Facno,
E1.Badge,
E1.Class,
E1.Description,
E1.Name,
E1.Arch,
E1.AckOpr,
E1.AckTStamp,
E1.Actions,
E1.RespReq,
E1.Sphere,
E1.Tag,
E1.Pending,
E1.HasPhoto,
E1.ScrName
ORDER BY E1.NAME ASC, E1.EDATE DESC) y
WHERE Y.theCount BETWEEN <m> AND <n>
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
