Slow query performance advice
-
I've got this slow running query that performs very poorly, or at least that's what my SQL Server monitor is saying:
It definitely taking most of the "Top slowest queries" list (same query on this list):
Query plan:
Execution Count: 1
Avg execution time (ms): 42411
Avg CPU time: 42363
Avg I/O per sec: 0
Avg physical reads: 0
Avg logical reads: 666724
Avg logical writes: 0
Total execution time (ms): 42411.423
SELECT
Duplicates.ContributorId,
Duplicates.DateId,
Contributors.PensionNo,
Contributors.LastName + ', ' + Contributors.FirstName AS FullName,
Calendar.Date,
Calendar.Week,
WeeklyPayments.PostingId
FROM
(
SELECT
Calendar.DateId,
Conts.ContributorId
FROM Calendar INNER JOIN
(SELECT
Contributors.ContributorId
FROM Contributors
WHERE Contributors.PensionNo IN (31725,31761,20204,30829,33382)
) Conts ON 1 = 1
WHERE [Year] = 2016 AND [Week] BETWEEN 5 AND 9
INTERSECT
SELECT
WeeklyPayments.DateId,
WeeklyPayments.ContributorId
FROM
WeeklyPayments
) Duplicates
INNER JOIN Contributors ON Duplicates.ContributorId = Contributors.ContributorId
INNER JOIN Calendar ON Duplicates.DateId = Calendar.DateId
INNER JOIN WeeklyPayments ON Duplicates.DateId = WeeklyPayments.DateId AND Duplicates.ContributorId = WeeklyPayments.ContributorIdGenerally I get alerts over non indexed tables so I can improve the performance of the query but not in this case.
I'm not well versed on performance tuning so I though of asking for advice on this arena. What can I try to improve the performance of this query? Where to start looking and such?Cheers
UPDATE:
Attached .sqlplan
CONTRIBUTORS
CALENDAR
WEEKLYPAYMENTS
-
Your SQL Server monitor should give plenty of interesting information, such as wait stats, from which you may be able to work out what was causing your query to run slowly. We need the actual execution plan, please (not the estimated plan and not just a picture of it). What are the max degree of parallelism and cost threshold for parallelism on your server?
John
-
Can you capture the actual execution plan in ssms and post it as a .sqlplan file attachment please?
The query looks overengineered.“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor 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 -
Also, check the output of this against your original. It's likely to be different.
SELECT
wp.ContributorId,
wp.DateId,
conts.PensionNo,
conts.LastName + ', ' + conts.FirstName AS FullName,
cal.Date,
cal.Week,
wp.PostingId
FROM Calendar cal
INNER JOIN WeeklyPayments wp
ON wp.DateId = cal.DateId
INNER JOIN Contributors conts
ON Conts.ContributorId = wp.ContributorId
WHERE conts.PensionNo IN (31725,31761,20204,30829,33382)
AND cal.[Year] = 2016
AND cal.[Week] BETWEEN 5 AND 9
“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor 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 -
Can you post table definitions and index definitions of the involved tables, as well as the actual execution plan please?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Added table definitions, sqlplan and suggested query sqlplan. Same output btw, in no time, will need to check this a bit further later on...
Suggested NONCLUSTERED index speeds up the query from almost a minute to miliseconds.
CREATE NONCLUSTERED INDEX Duplicated_Payments_Query ON dbo.WeeklyPayments (ContributorId) INCLUDE (PostingId, DateId)Although I totally agree the query is poorly engineered and will not stick around...
-
Thanks.
Try this - which gets rid of some implicit conversions:SELECT
wp.ContributorId,
wp.DateId,
conts.PensionNo,
conts.LastName + ', ' + conts.FirstName AS FullName,
cal.Date,
cal.Week,
wp.PostingId
FROM Calendar cal
INNER JOIN WeeklyPayments wp
ON wp.DateId = cal.DateId
INNER JOIN Contributors conts
ON Conts.ContributorId = wp.ContributorId
WHERE conts.PensionNo IN (
'31725','31761','20204','30829','33382','32436','10926','33321','30202','11523','35801','20159','32357','35752',
'30160','11687','32243','32654','34602','20341','35886','34274','35456','34949','35018','10844','32434','35325','35458','35318','35723','10800')
AND cal.[Year] = '2016'
AND cal.[Week] BETWEEN 5 AND 9
“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor 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 -
Index definitions for the involved tables?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply