SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Query plan - largest cost


Query plan - largest cost

Author
Message
rgtft
rgtft
UDP Broadcaster
UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)

Group: General Forum Members
Points: 1447 Visits: 754
This query takes about 45 seconds to complete (down from 10+ minutes by adding clustered index to table). Looking at the query plan, the largest cost is a clustered index seek -- which, as I understand it, is what I'm supposed to be getting rather than table or index scans.

What other indexes may be missing to speed this up?

I know it's a big topic, where should I start reading for more information on how to read & understand a query plan and tune a query.


DBCC FREEPROCCACHE;
DBCC DROPCLEANBUFFERS;
GO

SET STATISTICS IO ON
SET STATISTICS TIME ON

SELECT DISTINCT vwea.FranchiseNumber,
vwea.CustomerID,
vwea.EmailBefore,
vwea.EmailAfter,
vwea.ChangeDate,
j.ScheduledEndDate AS MoveDate,
CASE
WHEN j.ScheduledEndDate < vwea.ChangeDate THEN 'After Move Change'
ELSE 'Before move change'
END AS Change
FROM dbo.tblJob j
INNER JOIN dbo.tblWorkOrder wo ON j.DatabaseInfoID = wo.DatabaseInfoID
AND j.WorkOrderID = wo.WorkOrderID
INNER JOIN dbo.EmailAudit vwea ON wo.CustomerID = vwea.CustomerID
INNER JOIN dbo.tblDatabaseInfo ON j.DatabaseInfoID = dbo.tblDatabaseInfo.DatabaseInfoID
INNER JOIN dbo.tblFranchise ON j.DatabaseInfoID = dbo.tblFranchise.DatabaseInfoID
AND vwea.FranchiseNumber = dbo.tblFranchise.FranchiseNumber
WHERE (vwea.FranchiseNumber = 253)
AND j.ScheduledEndDate < vwea.ChangeDate
ORDER BY vwea.ChangeDate DESC;

SET STATISTICS IO OFF;
GO
SET STATISTICS TIME OFF;
GO



Thanks,
Rob

(Query plan attached)



Attachments
AuditSQLplan.sqlplan (28 views, 296.00 KB)
Job & work order tables.txt (47 views, 15.00 KB)
Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)

Group: General Forum Members
Points: 99425 Visits: 33014
Looking at this, the first thing I'd suggest is you update your statistics. They're pretty badly out of wack. That might be why you're getting such an expensive plan. You're processing huge amounts of rows to only return 144. Also, I only see five tables the query, but, quick count, eight access points in the plan. Are you dealing with views or nested views here? That could also be problematic.

----------------------------------------------------
The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
Theodore Roosevelt

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
rgtft
rgtft
UDP Broadcaster
UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)

Group: General Forum Members
Points: 1447 Visits: 754
I'm updating statistics on those tables right now; dbo.EmailAudit is a view. I'll look into that.

Thanks,
Rob



Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)

Group: General Forum Members
Points: 99425 Visits: 33014
The simplification step in the optimization process can clean up issues with views, but not always and not always all the way. Depending on what's there, you might be better off accessing those tables more directly through this query.

----------------------------------------------------
The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
Theodore Roosevelt

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
rgtft
rgtft
UDP Broadcaster
UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)

Group: General Forum Members
Points: 1447 Visits: 754
I did an update of the statistics on all of the related tables. That didn't change the execution time.

The view (EmailAudit) duplicates many of the tables in the original query. Just running the view takes about 25 seconds.

The view definition is:


DBCC FREEPROCCACHE;
DBCC DROPCLEANBUFFERS;
GO

SET STATISTICS IO ON
SET STATISTICS TIME ON

--CREATE VIEW [dbo].[EmailAudit]
--AS
SELECT COALESCE(f.FranchiseNumber, di.FranchiseNumber) AS FranchiseNumber,
a.[CustomerID],
a.[EmailAddress] AS [EmailBefore],
c.EmailAddress AS [EmailAfter],
a.[ChangeDate]
FROM [dbo].[tblEmailAudit] a
INNER JOIN dbo.tblCustomer c ON C.DatabaseInfoID = a.DatabaseInfoID
AND c.CustomerID = a.CustomerID
AND ISNULL(c.FranUniqueID, c.DatabaseInfoID) = ISNULL(a.FranUniqueID, a.DatabaseInfoID)
LEFT JOIN dbo.tblFranchise f ON a.DatabaseInfoID = f.DatabaseInfoID
AND ISNULL(f.FranUniqueID, f.DatabaseInfoID) = ISNULL(a.FranUniqueID, a.DatabaseInfoID)
LEFT JOIN dbo.tblDatabaseInfo di ON a.DatabaseInfoID = di.DatabaseInfoID
WHERE ISNULL(a.EmailAddress,'') <> ISNULL(c.EmailAddress,'');

SET STATISTICS IO OFF;
GO
SET STATISTICS TIME OFF;
GO




So I rewrote the original query removing the view and I'm still getting about the same time (40-45 seconds).


-- original query rewritten to remove EmailAudit view --
DBCC FREEPROCCACHE;
DBCC DROPCLEANBUFFERS;
GO

SET STATISTICS IO ON
SET STATISTICS TIME ON

SELECT DISTINCT COALESCE(f.FranchiseNumber, di.FranchiseNumber) AS FranchiseNumber,
a.CustomerID,
a.EmailAddress AS EmailBefore,
c.EmailAddress AS EmailAfter,
a.ChangeDate,
j.ScheduledEndDate AS MoveDate,
CASE
WHEN j.ScheduledEndDate < a.ChangeDate THEN 'After Move Change'
ELSE 'Before move change'
END AS Change
FROM dbo.tblEmailAudit a
INNER JOIN dbo.tblCustomer c ON c.CustomerID = a.CustomerID
AND c.DatabaseInfoID = a.DatabaseInfoID
INNER JOIN dbo.tblWorkOrder wo ON c.CustomerId = wo.CustomerId
AND wo.DatabaseInfoId = a.DatabaseInfoId
INNER JOIN dbo.tblJob j ON j.WorkOrderID = wo.WorkOrderID
AND j.DatabaseInfoID = a.DatabaseInfoID
INNER JOIN dbo.tblDatabaseInfo di ON di.DatabaseInfoID = a.DatabaseInfoID
LEFT JOIN dbo.tblFranchise f ON a.DatabaseInfoID = f.DatabaseInfoID
AND ISNULL(f.FranUniqueID, f.DatabaseInfoID) = ISNULL(a.FranUniqueID, a.DatabaseInfoID)
WHERE (COALESCE(f.FranchiseNumber, di.FranchiseNumber) = 253)
AND j.ScheduledEndDate < a.ChangeDate
AND ISNULL(a.EmailAddress,'') <> ISNULL(c.EmailAddress,'')
ORDER BY a.ChangeDate DESC;

SET STATISTICS IO OFF;
GO
SET STATISTICS TIME OFF;
GO



The two biggest costs in the query plan are both index seeks (one clustered and the other non-clustered). The third largest cost is an index scan on dbo.tblJob.



Attachments
NewExecPlan.sqlplan (6 views, 212.00 KB)
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)

Group: General Forum Members
Points: 217989 Visits: 41995
rgtft (9/16/2012)

The two biggest costs in the query plan are both index seeks (one clustered and the other non-clustered). The third largest cost is an index scan on dbo.tblJob.


There's really only one way for the seeks to be taking longer than a scan... the seeks are being repeated. Look at the properties of the seeks and see that they are executed many times. A better index capable of doing an initial seek and a "range scan" following that would be a tremendous amount faster.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
rgtft
rgtft
UDP Broadcaster
UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)

Group: General Forum Members
Points: 1447 Visits: 754
Silly question, how do I read the execution plan to know what columns to index?

I created two indexes (just eyeballed query and guessed). The first one seems to have improved the query, but the second one (though being used) still doesn't seem to improve anything.


-- Two indexes created --
CREATE NONCLUSTERED INDEX [IX_tblCustomer_CustomerId_EmailAddress] ON [dbo].[tblCustomer]
(
[CustomerID] ASC,
[EmailAddress] ASC
);


CREATE NONCLUSTERED INDEX [IX_tboWorkOrder_CustomerID_DatabaseInfoId] ON [dbo].[tblWorkOrder]
(
[CustomerID] ASC,
[DatabaseInfoId] ASC
);



Thanks,
Rob



Attachments
NewExecPlan_01.sqlplan (9 views, 252.00 KB)
rgtft
rgtft
UDP Broadcaster
UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)

Group: General Forum Members
Points: 1447 Visits: 754
I see a couple of websites that explain some of this:

I also see that Grant has a book on execution plans:


Anyways, I've now got query down to 15-20 seconds; it looks like I've got some reading to do.

Rob



Attachments
NewExecPlan_02.sqlplan (6 views, 252.00 KB)
Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)

Group: General Forum Members
Points: 99425 Visits: 33014
ISNULL and COALESCE on columns in the where clause are going to hurt performance. I still think your stats are off. I'm seeing estimated 4.5 million, actual 15,000. That's a pretty big disparity and is usually indicative of out of date or badly sampled statistics.

----------------------------------------------------
The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
Theodore Roosevelt

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
ChrisM@Work
ChrisM@Work
SSC-Forever
SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)

Group: General Forum Members
Points: 42069 Visits: 20008
Grant Fritchey (9/17/2012)
ISNULL and COALESCE on columns in the where clause are going to hurt performance....


This should be close to a SARGable version of the query:

SELECT DISTINCT COALESCE(f.FranchiseNumber, di.FranchiseNumber) AS FranchiseNumber,
a.CustomerID,
a.EmailAddress AS EmailBefore,
c.EmailAddress AS EmailAfter,
a.ChangeDate,
j.ScheduledEndDate AS MoveDate,
CASE
WHEN j.ScheduledEndDate < a.ChangeDate THEN 'After Move Change'
ELSE 'Before move change'
END AS Change
FROM dbo.tblEmailAudit a

INNER JOIN dbo.tblCustomer c
ON c.CustomerID = a.CustomerID
AND c.DatabaseInfoID = a.DatabaseInfoID
and (
c.EmailAddress <> a.EmailAddress
OR (c.EmailAddress IS NULL AND a.EmailAddress IS NOT NULL)
OR (c.EmailAddress IS NOT NULL AND a.EmailAddress IS NULL)
)
INNER JOIN dbo.tblWorkOrder wo
ON c.CustomerId = wo.CustomerId
AND wo.DatabaseInfoId = a.DatabaseInfoId

INNER JOIN dbo.tblJob j
ON j.WorkOrderID = wo.WorkOrderID
AND j.DatabaseInfoID = a.DatabaseInfoID

LEFT JOIN dbo.tblDatabaseInfo di
ON di.DatabaseInfoID = a.DatabaseInfoID
AND di.FranchiseNumber = 253

LEFT JOIN dbo.tblFranchise f
ON (f.FranUniqueID = a.FranUniqueID OR f.DatabaseInfoID = a.DatabaseInfoID)
--a.DatabaseInfoID = f.DatabaseInfoID
AND f.FranchiseNumber = 253
--AND ISNULL(f.FranUniqueID, f.DatabaseInfoID) = ISNULL(a.FranUniqueID, a.DatabaseInfoID)
WHERE 1 = 1
--(COALESCE(f.FranchiseNumber, di.FranchiseNumber) = 253)
AND j.ScheduledEndDate < a.ChangeDate
--AND ISNULL(a.EmailAddress,'') <> ISNULL(c.EmailAddress,'')
ORDER BY a.ChangeDate DESC;



“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search