Re: Slow but critical SQL query

From: Tom Moreau (tom_at_dont.spam.me.cips.ca)
Date: 02/17/04 

Date: Tue, 17 Feb 2004 18:18:08 -0500

Some comments:

  1.. If you don't have a WHERE clause, you'll get a lot of rows and it will
take a long time.
  2.. You may be able to get better performance if you include filter
criteria for both sides of the join. Here's an example. You have a filter
for w.TypeID = @WorkType and you also join onto dbo.WorkType ON w.TypeID =
dbo.WorkType.TypeID. Try adding a filter for dbo.WorkType.TypeID =
@WorkType. Same goes for the others.
  3.. If you are routinely running the filter for w.CreationDate, this is a
good candidate for a clustered index. Currently, there is no index on it at
all. Obviously, you'd want to test the two scenarios of clustering on it
vs. clustering on CustomerID.

Without having data to test this with, I'm afraid that's all I can
contribute. 

-- 
   Tom
----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON   Canada
www.pinnaclepublishing.com/sql
.
"Team One" <team1dev1@hotmail.com> wrote in message
news:69D65F4A-95B5-46FB-BF9E-FDA091E58FC3@microsoft.com...
This query is the bottleneck of our system. We can't change any aspect of
the schema except for stored procedures, indexes, and views.
SELECT TOP 50 w.TicketID, w.CustomerID, w.BuildingID, w.TypeID,
w.TechnicianID, w.ContactName, w.ContactEmail, w.ContactPhone,
w.ContactFax, w.FloorNumber, w.RoomNumber, w.WorkDescription,
w.TicketStatus, w.TicketPriority, w.TimeSpent, w.TotalCharges,
w.CreationDate, w.CompletionDate, w.SubmittedBy, dbo.Priority.Description AS
Priority,
dbo.Status.Description AS Status, t.FirstName + ' ' + t.LastName AS
TechnicianName,
t.Phone AS TechnicianPhone, dbo.WorkType.Description AS WorkType
FROM dbo.WorkTickets w INNER JOIN
dbo.Priority ON w.TicketPriority = dbo.Priority.PriorityID INNER JOIN
dbo.Status ON w.TicketStatus = dbo.Status.StatusID INNER JOIN
dbo.Technicians t ON w.TechnicianID = t.TechnicianID INNER JOIN
dbo.WorkType ON w.TypeID = dbo.WorkType.TypeID
We build this query dynamically since we allow attaching a WHERE clause
based on user input. The TOP value varies, but is usually no more than 50.
If it exists, the WHERE clause will usually contain exactly one of the
following:
w.CustomerID = @CustomerId
w.CreationDate >= @From AND w.CreationDate <= @To
w.TypeID = @WorkType
w.TechnicianID = @TechnicianId
w.TicketStatus = @Status
The Priority, Status, and WorkType tables are simple look-up tables
containing only the primary key and a description. They contain between
three and six rows. The WorkTickets table is very wide (the schema is given
below) and contains about 100,000 rows. The Technicians table is not as wide
(the schema is given below) but also contains about 100,000 rows.
There is no referential integrity between any of the tables. All tables have
an identity column as their primary key, which is clustered. We are not
allowed to change the primary key, except for changing whether or not it is
clustered (i.e., some other index can be clustered). Right now, the
CustomerID index is clustered in the WorkTickets table because we have
another similar expensive query that runs ten times faster with a clustered
CustomerID index. However, we are willing to change which index is clustered
if we can significantly improve this query.
There are 1,000 customers, each with 100 tickets. Although there are 100,000
technicians, only 1,000 of them appear in the WorkTickets table and each has
100 tickets.
CREATE TABLE [WorkTickets] (
[TicketID] [int] IDENTITY (1, 1) NOT NULL,
[CustomerID] [int] NOT NULL,
[BuildingID] [int] NOT NULL,
[TypeID] [int] NOT NULL,
[TechnicianID] [int] NOT NULL,
[ContactName] [varchar] (100) NOT NULL,
[ContactEmail] [varchar] (50) NOT NULL,
[ContactPhone] [char] (10) NOT NULL,
[ContactFax] [char] (10) NOT NULL,
[FloorNumber] [varchar] (50) NOT NULL,
[RoomNumber] [varchar] (50) NOT NULL,
[WorkDescription] [varchar] (500) NOT NULL,
[TicketStatus] [int] NOT NULL,
[TicketPriority] [int] NOT NULL,
[TimeSpent] [decimal](10, 0) NULL,
[TotalCharges] [decimal](18, 0) NULL,
[CreationDate] [datetime] NOT NULL,
[CompletionDate] [datetime] NULL,
[SubmittedBy] [varchar] (50) NULL,
CONSTRAINT [PK_WorkTickets] PRIMARY KEY  NONCLUSTERED
(
[TicketID]
)  ON [PRIMARY]
) ON [PRIMARY]
CREATE CLUSTERED INDEX IX_WorkTickets_Customer ON dbo.WorkTickets
(
CustomerID
) ON [PRIMARY]
CREATE NONCLUSTERED INDEX IX_WorkTickets_Status ON dbo.WorkTickets
(
TicketStatus
) ON [PRIMARY]
CREATE NONCLUSTERED INDEX IX_WorkTickets_Technician ON dbo.WorkTickets
(
TechnicianID
) ON [PRIMARY]
CREATE NONCLUSTERED INDEX IX_WorkTickets_Type ON dbo.WorkTickets
(
TypeID
) ON [PRIMARY]
CREATE TABLE [Technicians] (
[TechnicianID] [int] IDENTITY (1, 1) NOT NULL,
[CustomerID] [int] NOT NULL,
[BuildingID] [int] NOT NULL,
[UserName] [varchar] (50) NOT NULL,
[Password] [varchar] (50) NOT NULL,
[TechCompanyName] [varchar] (50) NOT NULL,
[FirstName] [varchar] (50) NOT NULL,
[LastName] [varchar] (50) NOT NULL,
[Phone] [char] (10) NOT NULL,
[Fax] [char] (10) NOT NULL,
[Email] [varchar] (50) NOT NULL,
[BillingRate] [decimal](18, 0) NOT NULL,
CONSTRAINT [PK_Technicians] PRIMARY KEY  CLUSTERED
(
[TechnicianID]
)  ON [PRIMARY]
) ON [PRIMARY]
CREATE NONCLUSTERED INDEX IX_Customer_Building ON dbo.Technicians
(
CustomerID,
BuildingID
) ON [PRIMARY]
CREATE NONCLUSTERED INDEX IX_Technicians_ID_Name_Phone ON dbo.Technicians
(
TechnicianID,
FirstName,
LastName,
Phone
) ON [PRIMARY]
The execution plan shows a cost of 86% for an index seek of
Technicians.IX_Technicians_ID_Name_Phone. The Index Tuning Wizard has no
useful suggestions.

Relevant Pages