Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««1234»»»

Grouping records by time interval Expand / Collapse
Author
Message
Posted Friday, March 1, 2013 6:42 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 6:52 AM
Points: 6,872, Visits: 14,185
geert.de.vylder (3/1/2013)
Hi ChrisM@Work,

Thank you for your query and help. But this query needed 13 minutes to run. That's quiet long. How can the performance be improved?

Greetz,

Geert


Post the actual execution plan as a .sqlplan file, and I'll tell you. But is it correct? Your last post puts column event_channel into the left-hand side along with date, from the ticket table.



“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
Post #1425474
Posted Monday, March 4, 2013 8:36 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, March 25, 2014 7:47 AM
Points: 24, Visits: 141
Hi ChrisM@Work,

Thank you for your reply. In attachment you will find the executionplan. The Tussen_Tickets table has at this moment 31779 records.
The query is almost correct. I only had to change Talk = SUM(tt.OnHold) to Talk = SUM(tt.Talk) in the OUTER APPLY.
I also saw that the Event_Channel is not always filled (NULL) and not always correctly sorted.
Is that maybe your remark that you saw that my previous post puts column event_channel into the left-hand side along with date, from the Tussen_Ticket table?

Greetz,

Geert


  Post Attachments 
ExecutionPlan.sqlplan (3 views, 57.88 KB)
Post #1426281
Posted Monday, March 4, 2013 8:44 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 6:52 AM
Points: 6,872, Visits: 14,185
geert.de.vylder (3/4/2013)
Hi ChrisM@Work,

Thank you for your reply. In attachment you will find the executionplan. The Tussen_Tickets table has at this moment 31779 records.
The query is almost correct. I only had to change Talk = SUM(tt.OnHold) to Talk = SUM(tt.Talk) in the OUTER APPLY.
I also saw that the Event_Channel is not always filled (NULL) and not always correctly sorted.
Is that maybe your remark that you saw that my previous post puts column event_channel into the left-hand side along with date, from the Tussen_Ticket table?

Greetz,

Geert


Does event_channel have to be populated, even if there isn't a match in the Tussen_Ticket table?

Meantime, try this index:
CREATE NONCLUSTERED INDEX ix_Ticket_Closed_DateTime
ON [dbo].[Tussen_Tickets] ([Ticket_Closed_DateTime])
INCLUDE ([Event_Channel],[Tijdsduur],[WrapUp],[OnHold],[DVBIntern],[DVBExtern],[Talk])


“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
Post #1426288
Posted Monday, March 4, 2013 8:49 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 6:52 AM
Points: 6,872, Visits: 14,185
What version of SQL Server are you connecting to?

“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
Post #1426290
Posted Monday, March 4, 2013 9:10 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, March 25, 2014 7:47 AM
Points: 24, Visits: 141
Hi ChrisM@Work,

Yes, event_channel has to be populated, even if there isn't a match in the Tussen_Ticket table. This is because I have to update the results into another table in another database.

Thank you very much for your help and assistance.

Greetz,

Geert
Post #1426304
Posted Monday, March 4, 2013 9:11 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, March 25, 2014 7:47 AM
Points: 24, Visits: 141
I'm connecting to SQL Server 2008 R2.
Post #1426305
Posted Monday, March 4, 2013 9:13 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 6:52 AM
Points: 6,872, Visits: 14,185
geert.de.vylder (3/4/2013)
I'm connecting to SQL Server 2008 R2.


Run this: SELECT @@VERSION


“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
Post #1426308
Posted Monday, March 4, 2013 9:15 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 6:52 AM
Points: 6,872, Visits: 14,185
geert.de.vylder (3/4/2013)
Hi ChrisM@Work,

Yes, event_channel has to be populated, even if there isn't a match in the Tussen_Ticket table. This is because I have to update the results into another table in another database.

Thank you very much for your help and assistance.

Greetz,

Geert


You're very welcome. Your cooperation is very very much appreciated. Here's a modified query which takes Event_Channel into account as discussed:

SELECT mx.Ticket_Closed_Date, mx.Interval, op.*	 
FROM ( -- create a matrix containing all intervals from Tijdsintervallen and all dates & Event_Channels from Tussen_Tickets
SELECT *
FROM Tijdsintervallen
CROSS JOIN (
SELECT DISTINCT Ticket_Closed_Date = CAST(Ticket_Closed_DateTime AS DATE)
FROM Tussen_Tickets
) d
CROSS JOIN (
SELECT DISTINCT Event_Channel
FROM Tussen_Tickets
) e
) mx
OUTER APPLY (
SELECT tt.Event_Channel,
DVBIntern = SUM(tt.DVBIntern),
DVBExtern = SUM(tt.DVBExtern),
Tijdsduur = SUM(tt.Tijdsduur),
WrapUp = SUM(tt.WrapUp),
OnHold = SUM(tt.OnHold),
Talk = SUM(tt.Talk),
RowsAggregated = COUNT(*) -- not in spec but handy to have
FROM Tussen_Tickets tt
CROSS APPLY (
SELECT
Ticket_Closed_Time = CAST(tt.Ticket_Closed_DateTime AS TIME(3)),
Ticket_Closed_Date = CAST(tt.Ticket_Closed_DateTime AS DATE)
) ta
WHERE ta.Ticket_Closed_Time BETWEEN mx.beginTijdsinterval AND mx.eindTijdsinterval
AND ta.Ticket_Closed_Date = mx.Ticket_Closed_Date
AND tt.Event_Channel = mx.Event_Channel
GROUP BY ta.Ticket_Closed_Date, tt.Event_Channel
) op
ORDER BY mx.Ticket_Closed_Date, mx.Interval, op.Event_Channel;



“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
Post #1426310
Posted Monday, March 4, 2013 9:20 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 6:52 AM
Points: 6,872, Visits: 14,185
With the added cost of Event_Channel in the matrix table correlating with the inner query, it may be worthwhile splitting the query up, like this:

SELECT *
INTO #Matrix
FROM Tijdsintervallen
CROSS JOIN (
SELECT DISTINCT Ticket_Closed_Date = CAST(Ticket_Closed_DateTime AS DATE)
FROM Tussen_Tickets
) d
CROSS JOIN (
SELECT DISTINCT Event_Channel
FROM Tussen_Tickets
) e

CREATE UNIQUE CLUSTERED INDEX ucx_Everything ON #Matrix (Event_Channel, Ticket_Closed_Date, beginTijdsinterval)
-----------------------------------------------------

SELECT mx.Ticket_Closed_Date, mx.Interval, op.*
FROM #Matrix mx
OUTER APPLY (
SELECT tt.Event_Channel,
DVBIntern = SUM(tt.DVBIntern),
DVBExtern = SUM(tt.DVBExtern),
Tijdsduur = SUM(tt.Tijdsduur),
WrapUp = SUM(tt.WrapUp),
OnHold = SUM(tt.OnHold),
Talk = SUM(tt.Talk),
RowsAggregated = COUNT(*) -- not in spec but handy to have
FROM Tussen_Tickets tt
CROSS APPLY (
SELECT
Ticket_Closed_Time = CAST(tt.Ticket_Closed_DateTime AS TIME(3)),
Ticket_Closed_Date = CAST(tt.Ticket_Closed_DateTime AS DATE)
) ta
WHERE ta.Ticket_Closed_Time BETWEEN mx.beginTijdsinterval AND mx.eindTijdsinterval
AND ta.Ticket_Closed_Date = mx.Ticket_Closed_Date
AND tt.Event_Channel = mx.Event_Channel
GROUP BY ta.Ticket_Closed_Date, tt.Event_Channel
) op
ORDER BY mx.Ticket_Closed_Date, mx.Interval, op.Event_Channel;



“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
Post #1426314
Posted Tuesday, March 5, 2013 1:28 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, March 25, 2014 7:47 AM
Points: 24, Visits: 141
Hi,

I connect to version: Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64) Apr 2 2010 15:48:46 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7600: ) (Hypervisor)

Greetz,

Geert
Post #1426635
« Prev Topic | Next Topic »

Add to briefcase ««1234»»»

Permissions Expand / Collapse