December 12, 2016 at 8:50 am
Hi all,
I was wondering if someone could help me figure out why this Insert statement is taking 9 minutes to run. It never took that long before and now, all of the sudden, it is taking longer and longer to complete.
Here is the statement
INSERT INTO Metrics
(Application,
App1_Performance,
App1_Availability,
App2_Performance,
App2_Availability,
week,
Current_Week)
SELECT
COALESCE (n.Application, k.Application) AS Application,
AVG(n.Performance) AS App1_Performance,
AVG(n.Availability) AS App1_Availability,
AVG(k.Performance) AS App2_Performance,
AVG(k.Availability) AS App2_Availability,
COALESCE (DATEPART(wk, n.Timestamp), DATEPART(wk, k.Timestamp)) AS INPUT_WEEK,
DATEPART(wk, GETDATE()) AS CURRENT_WEEK
FROM Table2 k
FULL OUTER JOIN Table1 n
ON k.Application = n.Application
LEFT JOIN Metrics f
ON f.Application = COALESCE (n.Application, k.Application)
WHERE NOT EXISTS (SELECT week FROM Metrics WHERE week != DATEPART(wk, n.Timestamp) OR week != DATEPART(wk, k.Timestamp))
GROUP BY COALESCE (n.Application, k.Application), COALESCE (DATEPART(wk, n.Timestamp), DATEPART(wk, k.Timestamp))
Table1 and Table2 have about 12,000 rows.
I delete everything from Metrics each time the stored procedure is run.
Is it just the computations with that many records that is taking too long? Is there anyway to make it faster?
December 12, 2016 at 8:57 am
Firstly, let's fix your code up a bit. Try to ensure you use IFCode brackets around your SQL. it makes it a lot easier to read for others:
INSERT INTO Metrics(Application,
App1_Performance,
App1_Availability,
App2_Performance,
App2_Availability,
week,
Current_Week)
SELECT COALESCE (n.Application, k.Application) AS Application,
AVG(n.Performance) AS App1_Performance,
AVG(n.Availability) AS App1_Availability,
AVG(k.Performance) AS App2_Performance,
AVG(k.Availability) AS App2_Availability,
COALESCE (DATEPART(wk, n.Timestamp), DATEPART(wk, k.Timestamp)) AS INPUT_WEEK,
DATEPART(wk, GETDATE()) AS CURRENT_WEEK
FROM Table2 k
FULL OUTER JOIN Table1 n ON k.Application = n.Application
LEFT JOIN Metrics f ON f.Application = COALESCE (n.Application, k.Application)
WHERE NOT EXISTS (SELECT week
FROM Metrics
WHERE week != DATEPART(wk, n.Timestamp)
OR week != DATEPART(wk, k.Timestamp))
GROUP BY COALESCE (n.Application, k.Application),
COALESCE (DATEPART(wk, n.Timestamp), DATEPART(wk, k.Timestamp))
The first thing I've noticed is your COALESCE IN your LEFT JOIN. This won't make things quick.
How does the query run if you change your LEFT join to the following:
LEFT JOIN Metrics f ON f.Application = n.Application
OR (n.Application IS NULL and f.Application = k.Application)
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
December 12, 2016 at 9:04 am
Does the Metrics table have a clustered index? How are you removing rows, DELETE or TRUNCATE?
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
December 12, 2016 at 9:09 am
Just wondering but why are you joining to the metrics table at all in the select if you're clearing the data from that table anyways before running it? It would seem that the join to metrics and the NOT EXISTS are never going to return anything?
December 12, 2016 at 9:10 am
Matt.Altman (12/12/2016)
...I delete everything from Metrics each time the stored procedure is run.
...
Then you shouldn't need this:
WHERE NOT EXISTS (SELECT 1 FROM Metrics WHERE week != DATEPART(wk, n.Timestamp) OR week != DATEPART(wk, k.Timestamp))
Edit: or the other join to the Metrics table:
INSERT INTO Metrics
(Application,
App1_Performance,
App1_Availability,
App2_Performance,
App2_Availability,
week,
Current_Week)
SELECT
COALESCE (n.Application, k.Application) AS Application,
AVG(n.Performance) AS App1_Performance,
AVG(n.Availability) AS App1_Availability,
AVG(k.Performance) AS App2_Performance,
AVG(k.Availability) AS App2_Availability,
COALESCE (DATEPART(wk, n.Timestamp), DATEPART(wk, k.Timestamp)) AS INPUT_WEEK,
DATEPART(wk, GETDATE()) AS CURRENT_WEEK
FROM Table2 k
FULL OUTER JOIN Table1 n
ON k.Application = n.Application
--LEFT JOIN Metrics f
--ON f.Application = COALESCE (n.Application, k.Application)
--WHERE NOT EXISTS (SELECT 1 FROM Metrics WHERE week != DATEPART(wk, n.Timestamp) OR week != DATEPART(wk, k.Timestamp))
GROUP BY COALESCE (n.Application, k.Application), COALESCE (DATEPART(wk, n.Timestamp), DATEPART(wk, k.Timestamp))
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
December 12, 2016 at 9:11 am
Matt.Altman (12/12/2016)
@thom-2. Thanks for cleaning up the code and making it readable. I changed the LEFT JOIN and the query is still running at 8 minutes. Should finish soon@chrism-2. I do not have a clustered index. I run a DELETE at the beginning.
Can you check the size of the table?
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
December 12, 2016 at 9:11 am
Good point. I used to not delete everything but when my weeks flipped, my data got messed up. I was lazy and decided to just wipe it instead of figuring out why the data was messed up. I left everything the same except I commented out my UPDATE statement.
December 12, 2016 at 9:14 am
The entire database is 9 MB
December 12, 2016 at 9:16 am
Thank you Chris. 19 seconds to run that time.
December 12, 2016 at 9:21 am
Matt.Altman (12/12/2016)
Thank you Chris. 19 seconds to run that time.
Okay, that's more reasonable. Now, although your db is miniscule, this may be occurring - which is why I asked about the table size. Although it isn't mentioned in the article, I understand that using TRUNCATE instead of DELETE avoids the issue.
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
December 12, 2016 at 9:31 am
Thank you. I will read through that article.
December 14, 2016 at 10:17 am
Hi,
How does this version perform, I wonder?
--TRUNCATE TABLE Metrics
--GO
DECLARE @CurrentWeek INT = DATEPART(wk, GETDATE());
WITH
Table1Metrics AS (
SELECT
t.Application,
AVG(t.Performance) AS App_Performance,
AVG(t.Availability) AS App_Availability,
DATEPART(wk, t.Timestamp) AS INPUT_WEEK
FROM
Table1 t
GROUP BY
t.Application,
DATEPART(wk, t.Timestamp)
),
Table2Metrics AS (
SELECT
t.Application,
AVG(t.Performance) AS App_Performance,
AVG(t.Availability) AS App_Availability,
DATEPART(wk, t.Timestamp) AS INPUT_WEEK
FROM
Table2 t
GROUP BY
t.Application,
DATEPART(wk, t.Timestamp)
)
INSERT INTO Metrics(Application,
App1_Performance,
App1_Availability,
App2_Performance,
App2_Availability,
week,
Current_Week)
SELECT
COALESCE (n.Application, k.Application) AS Application,
n.App_Performance AS App1_Performance,
n.App_Availability AS App1_Availability,
k.App_Performance AS App2_Performance,
k.App_Availability AS App2_Availability,
COALESCE (n.INPUT_WEEK, k.INPUT_WEEK) AS INPUT_WEEK,
@CurrentWeek AS CURRENT_WEEK
FROM
Table1Metrics n
FULL OUTER JOIN
Table2Metrics k
ON k.Application = n.Application
AND k.INPUT_WEEK = n.INPUT_WEEK
GO
I think this is more readable and clear, but that's a personal preference.
Of course I don't have the full picture to begin with, so might be completely off with this one. 😀
(No testing done)
Viewing 13 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply