Slow query, joins datetime, uses derived tables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Slow query, joins datetime, uses derived tables

Author

Topic

Hybird
Starting Member

3 Posts

Posted - 2010-06-07 : 18:01:33

Using SQL Server 2005 and trying to classify records into specific periods via a datetime type column. I have a table called TimeCards which has a primary key, a date, and a timegridID. The TimeGridID is used to relate to the TimeGrid table where the stored 'Text' value determines if the date fits into these categories:

Daily
Weekly
Monthly
Quarterly
Yearly

Now I have a select statement that goes through and picks the dates and groups them into their corresponding TimeGridID's. I.e. if the record is on a yearly grid, it is grouped into a new date corresponding to the first day of the year at 12:00:00 AM. This is so dates can be joined later in another query. So for Yearly, I would have something like this:


SELECT
     TimeCards.TimeGridText,
     TimeCards.ExpDate,
    [PeriodDate] = (DateAdd(year,Year(TimeCards.ExpDate)-1900,'1900-01-01'))
FROM
     TimeCards INNER JOIN TimeGrid
     ON TimeCards.GridID = TimeGrid.GridID
WHERE
     TimeGrid.TimeGridText = 'Yearly'
GROUP BY
     TimeCards.TimeGridText,
     TimeCards.ExpDate,
     (DateAdd(year,Year(TimeCards.ExpDate)-1900,'1900-01-01'))

Don`t mind the expression for [PeriodDate] it just extracts the year from the date and and makes a new date with day/month/time set to 01-01 12:00:00AM

This Select query works on a table of over 2million records and takes a long time to run. Aside from that, I join a derived table to this table in a view further down the road and the whole process takes a very long time to run when I query the final view for a specific month. I stopped the query after it didn't return results for 35min.

I was wondering how to make this go faster? I can't index the view because of its complexity, and it seems that indexing the datetime column of the timecard table doesn't help because I modify the dates and then group them...

Any suggestions would be greatly appreciated.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-06-07 : 18:57:16

Without knowing more I can only guess. But, if you created an index on TimeGrid and included the other columns you are selecting. I'd think that would help. But it's possible that the selectivity is low. What is AreaTime? is that your derived table?

If you can show us the whole query we might be able to help better. Hard to tell what the issue is from that snippet.

Also, as an FYI, it looks like you are trying to get the first of day of the month for AreaTime.AreaDate. If that is true it might be clearer to just do straight date math. For example:
SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)

Hybird
Starting Member

3 Posts

Posted - 2010-06-07 : 19:57:09

quote:
Originally posted by Lamprey

Without knowing more I can only guess. But, if you created an index on TimeGrid and included the other columns you are selecting. I'd think that would help. But it's possible that the selectivity is low. What is AreaTime? is that your derived table?

If you can show us the whole query we might be able to help better. Hard to tell what the issue is from that snippet.

Also, as an FYI, it looks like you are trying to get the first of day of the month for AreaTime.AreaDate. If that is true it might be clearer to just do straight date math. For example:
SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)

Sorry, I can see how that would have made little sense. I should have read over this much more before posting, but I was kinda burnt out. Anyway I made the changes to the original post, it should make sense now. I tried to clean it up and missed some naming and picked the wrong section of code. Should be good now.

As for the derived table it isn't shown in here. I don't think posting the whole set of queries would be worth anyones effort, theres a lot to look through. I'll post the first one for the time being, if it helps. But don't look at my first post because it is just a simplified version of this SQL statement with different names.


CREATE VIEW [TimeCards]
AS
--------------------------------------------------------------------------
--STEP 1: 
--------------------------------------------------------------------------
SELECT 
	TimeGrid.GridID,
	TimeGrid.Value,
	AreaTime.DosAreaID,
	Contaminants.ContaminantID,
	[RequiredDate] = (CAST(FLOOR(CAST( AreaTime.AreaDate AS FLOAT))AS DATETIME))
FROM
	AreaTime 
	INNER JOIN 
	DosAreas ON AreaTime.DosAreaID = DosAreas.DosAreaID 
	INNER JOIN
    Contaminants ON DosAreas.SchemeID = Contaminants.SchemeID 
	INNER JOIN 
	TimeGrid ON DosAreas.GridID = TimeGrid.GridID
WHERE
	TimeGrid.Value = 'Daily'
GROUP BY
	AreaTime.DosAreaID,
	Contaminants.ContaminantID,
	TimeGrid.GridID,
	TimeGrid.Value,
	(CAST(FLOOR(CAST( AreaTime.AreaDate AS FLOAT))AS DATETIME))
	
UNION ALL
--------------------------------------------------------------------------
--STEP 2: 
--------------------------------------------------------------------------
SELECT 
	TimeGrid.GridID,
	TimeGrid.Value,
	AreaTime.DosAreaID,
	Contaminants.ContaminantID,
	[RequiredDate] = (dateadd(dd,-(datepart(dw,AreaTime.AreaDate)-1),(CAST(FLOOR(CAST(AreaTime.AreaDate AS FLOAT))AS DATETIME))))
FROM
	AreaTime 
	INNER JOIN 
	DosAreas ON AreaTime.DosAreaID = DosAreas.DosAreaID 
	INNER JOIN
    Contaminants ON DosAreas.SchemeID = Contaminants.SchemeID 
	INNER JOIN 
	TimeGrid ON DosAreas.GridID = TimeGrid.GridID
WHERE
	TimeGrid.Value = 'Weekly'
GROUP BY
	AreaTime.DosAreaID,
	Contaminants.ContaminantID,
	TimeGrid.GridID,
	TimeGrid.Value,
	(dateadd(dd,-(datepart(dw,AreaTime.AreaDate)-1),(CAST(FLOOR(CAST(AreaTime.AreaDate AS FLOAT))AS DATETIME))))
	
UNION ALL
--------------------------------------------------------------------------
--STEP 3: 
--------------------------------------------------------------------------
SELECT
	TimeGrid.GridID,
	TimeGrid.Value,
	AreaTime.DosAreaID,
	Contaminants.ContaminantID,
	[RequiredDate] = DateAdd(month,MONTH(AreaTime.AreaDate)-1,DateAdd(year,Year(AreaTime.AreaDate)-1900,'1900-01-01'))
FROM
	AreaTime 
	INNER JOIN 
	DosAreas ON AreaTime.DosAreaID = DosAreas.DosAreaID 
	INNER JOIN
    Contaminants ON DosAreas.SchemeID = Contaminants.SchemeID 
	INNER JOIN 
	TimeGrid ON DosAreas.GridID = TimeGrid.GridID
WHERE
	TimeGrid.Value = 'Monthly'
GROUP BY
	TimeGrid.GridID,
	TimeGrid.Value,
	AreaTime.DosAreaID,
	Contaminants.ContaminantID,
	DateAdd(month,MONTH(AreaTime.AreaDate)-1,DateAdd(year,Year(AreaTime.AreaDate)-1900,'1900-01-01'))

UNION ALL
--------------------------------------------------------------------------
--STEP 4: 
--------------------------------------------------------------------------
SELECT
	TimeGrid.GridID,
	TimeGrid.Value,	
	AreaTime.DosAreaID,
	Contaminants.ContaminantID,
	[RequiredDate] = CAST(
	(CAST(YEAR(AreaTime.AreaDate) AS VARCHAR(4)) +  
		CASE 
		WHEN DatePart(quarter,AreaTime.AreaDate) = 1 THEN '-01-01'
		WHEN DatePart(quarter,AreaTime.AreaDate) = 2 THEN '-04-01' 
		WHEN DatePart(quarter,AreaTime.AreaDate) = 3 THEN '-07-01'       
		WHEN DatePart(quarter,AreaTime.AreaDate) = 4 THEN '-10-01'       
	END) AS DATETIME)
FROM
	AreaTime 
	INNER JOIN 
	DosAreas ON AreaTime.DosAreaID = DosAreas.DosAreaID 
	INNER JOIN
    Contaminants ON DosAreas.SchemeID = Contaminants.SchemeID 
	INNER JOIN 
	TimeGrid ON DosAreas.GridID = TimeGrid.GridID
WHERE
	TimeGrid.Value = 'Quarterly'
GROUP BY
	TimeGrid.GridID,
	TimeGrid.Value,
	AreaTime.DosAreaID,
	Contaminants.ContaminantID,
	CAST((CAST(YEAR(AreaTime.AreaDate) AS VARCHAR(4)) +  
	CASE 
		WHEN DatePart(quarter,AreaTime.AreaDate) = 1 THEN '-01-01'
		WHEN DatePart(quarter,AreaTime.AreaDate) = 2 THEN '-04-01' 
		WHEN DatePart(quarter,AreaTime.AreaDate) = 3 THEN '-07-01'       
		WHEN DatePart(quarter,AreaTime.AreaDate) = 4 THEN '-10-01'       
	END) AS DATETIME)

UNION ALL
--------------------------------------------------------------------------
--STEP 5: 
--------------------------------------------------------------------------
SELECT
	TimeGrid.GridID,
	TimeGrid.Value,
	AreaTime.DosAreaID,
	Contaminants.ContaminantID,
	[RequiredDate] = DateAdd(year,Year(AreaTime.AreaDate)-1900,'1900-01-01')	
FROM
	AreaTime 
	INNER JOIN 
	DosAreas ON AreaTime.DosAreaID = DosAreas.DosAreaID 
	INNER JOIN
    Contaminants ON DosAreas.SchemeID = Contaminants.SchemeID 
	INNER JOIN 
	TimeGrid ON DosAreas.GridID = TimeGrid.GridID
WHERE
	TimeGrid.Value = 'Yearly'
GROUP BY
	TimeGrid.GridID,
	TimeGrid.Value,
	AreaTime.DosAreaID,
	Contaminants.ContaminantID,
	DateAdd(year,Year(AreaTime.AreaDate)-1900,'1900-01-01')	

GO

Hybird
Starting Member

3 Posts

Posted - 2010-06-07 : 20:09:18

Next, I would calculate the corresponding AreaReadings which the timecards must be multiplied against. That is done by the code below. I then do a left outer join on the two views (TimeCards->AreaReadings). Any TimeCard dates that come up with NULL readings assigned, meaning there are no readings for a certain time period, must get AreaReadings for a previous Period that does have readings and is within a certain range away. So I cross join the table with a dates tables and subtract dates to get a set of dates further back to rejoin with the AreaREadings table. I then use a Window function (Rank() Over(PARTITION BY blah,blah SORT dates DESC)as rnk) to select the closest date that has a Reading associated with it. So in that SQL statment I have already joined once to connect the two which creates a derived table, cross joined to this derived table to get dates to look back for, and joined once again to see if those dates are in the AreaReadings table.

So thats where my derived tables come from. I hope I didn't lose you along the way, but I can't see how I could really explain it any better without gettting into too much of the details.

Regardless, thanks for the look.


CREATE VIEW [View_AreaReadings]
AS

--------------------------------------------------------------------------
--STEP 1: Calculates readings based on a daily time grid.
--STEP 2: Calculates readings based on a weekly time grid.
--STEP 3: Calculates readings based on a monthly time grid.
--STEP 4: Calculates readings based on a quarterly time grid.
--STEP 5: Calculates readings based on a yearly time grid.
--------------------------------------------------------------------------

--------------------------------------------------------------------------
--STEP 1: Calculates readings based on a daily time grid. Note here that
--the PeriodDate is daily and hence only readings summed up for that date
--are used for each period. The period itself is just the date without 
--a time component, used to group similar calculations for a single day.
--------------------------------------------------------------------------
SELECT 
    DosAreas.GridID,
	TimeGrid.Value,	
    DosAreaMembers.DosAreaID,
	Contaminants.ContaminantID,
	[DosAreaValue] = Sum(Readings.Value * DosAreaMembers.RelativeWeight)/Sum(DosAreaMembers.RelativeWeight), 
    [Posts] = Sum(CASE WHEN Readings.Posted = 'TRUE' THEN 1 ELSE 0 END), 
    [RecordsUsed] = Count(Readings.Value),
	[PeriodDate] = (CAST(FLOOR(CAST(Readings.SamplingDate AS FLOAT))AS DATETIME))
FROM 
    (((DosAreas 
    INNER JOIN (DosAreaMembers 
    INNER JOIN Readings 
    ON DosAreaMembers.LocationID = Readings.LocationID) 
    ON DosAreas.DosAreaID = DosAreaMembers.DosAreaID) 
    INNER JOIN Methods 
    ON Readings.MethodID = Methods.MethodID) 
    INNER JOIN TimeGrid 
    ON DosAreas.GridID = TimeGrid.GridID) 
    INNER JOIN Contaminants 
    ON Methods.ContaminantID = Contaminants.ContaminantID
WHERE
	(TimeGrid.Value = 'Daily')AND(Contaminants.SchemeID = DosAreas.SchemeID)
GROUP BY 
	Contaminants.ContaminantID, 
    DosAreas.GridID,
	TimeGrid.Value,	
    DosAreaMembers.DosAreaID, 
    (CAST(FLOOR(CAST(Readings.SamplingDate AS FLOAT))AS DATETIME))
HAVING       
    (Sum(DosAreaMembers.RelativeWeight)<>0)

UNION ALL
--------------------------------------------------------------------------
--STEP 2: Calculates readings based on a weekly time grid. Note here that
--the PeriodDate is weekly and hence only readings summed up for that week
--are used for each period. The period itself is each week starting at 
--Sunday and going to Saturday. Days which fall on the first week of the 
--next year will fall into the grouping for decembers last week, unless
--the first day of the year happens to land on a Sunday.
--------------------------------------------------------------------------
SELECT  
    DosAreas.GridID,
	TimeGrid.Value,	
    DosAreaMembers.DosAreaID,
	Contaminants.ContaminantID,
	[DosAreaValue] = Sum(Readings.Value * DosAreaMembers.RelativeWeight)/Sum(DosAreaMembers.RelativeWeight), 
	[Posts] = Sum(CASE WHEN Readings.Posted = 'TRUE' THEN 1 ELSE 0 END), 
    [RecordsUsed] = Count(Readings.Value),
	[PeriodDate] = (dateadd(dd,-(datepart(dw,Readings.SamplingDate)-1),(CAST(FLOOR(CAST(Readings.SamplingDate AS FLOAT))AS DATETIME))))
FROM 
    (((DosAreas 
    INNER JOIN (DosAreaMembers 
    INNER JOIN Readings 
    ON DosAreaMembers.LocationID = Readings.LocationID) 
    ON DosAreas.DosAreaID = DosAreaMembers.DosAreaID) 
    INNER JOIN Methods 
    ON Readings.MethodID = Methods.MethodID) 
    INNER JOIN TimeGrid 
    ON DosAreas.GridID = TimeGrid.GridID) 
    INNER JOIN Contaminants 
    ON Methods.ContaminantID = Contaminants.ContaminantID
WHERE
	(TimeGrid.Value = 'Weekly')AND(Contaminants.SchemeID = DosAreas.SchemeID)
GROUP BY 
	Contaminants.ContaminantID, 
    DosAreas.GridID,
	TimeGrid.Value,		
    DosAreaMembers.DosAreaID,  
    (dateadd(dd,-(datepart(dw,Readings.SamplingDate)-1),(CAST(FLOOR(CAST(Readings.SamplingDate AS FLOAT))AS DATETIME))))
HAVING       
    (Sum(DosAreaMembers.RelativeWeight)<>0)	

UNION ALL
--------------------------------------------------------------------------
--STEP 3: Calculates readings based on a monthly time grid.
--------------------------------------------------------------------------
SELECT 
    DosAreas.GridID,
	TimeGrid.Value,	
    DosAreaMembers.DosAreaID,
	Contaminants.ContaminantID,
	[DosAreaValue] = Sum(Readings.Value * DosAreaMembers.RelativeWeight)/Sum(DosAreaMembers.RelativeWeight), 
    [Posts] = Sum(CASE WHEN Readings.Posted = 'TRUE' THEN 1 ELSE 0 END), 
    [RecordsUsed] = Count(Readings.Value),
	[PeriodDate] = DateAdd(month,MONTH(Readings.SamplingDate)-1,DateAdd(year,Year(Readings.SamplingDate)-1900,'1900-01-01'))
FROM 
    (((DosAreas 
    INNER JOIN (DosAreaMembers 
    INNER JOIN Readings 
    ON DosAreaMembers.LocationID = Readings.LocationID) 
    ON DosAreas.DosAreaID = DosAreaMembers.DosAreaID) 
    INNER JOIN Methods 
    ON Readings.MethodID = Methods.MethodID) 
    INNER JOIN TimeGrid 
    ON DosAreas.GridID = TimeGrid.GridID) 
    INNER JOIN Contaminants 
    ON Methods.ContaminantID = Contaminants.ContaminantID
WHERE
	(TimeGrid.Value = 'Monthly')AND(Contaminants.SchemeID = DosAreas.SchemeID)
GROUP BY 
	Contaminants.ContaminantID, 
    DosAreas.GridID,
	TimeGrid.Value,	
    DosAreaMembers.DosAreaID,  
    DateAdd(month,MONTH(Readings.SamplingDate)-1,DateAdd(year,Year(Readings.SamplingDate)-1900,'1900-01-01'))
HAVING       
    (Sum(DosAreaMembers.RelativeWeight)<>0) 	

UNION ALL
--------------------------------------------------------------------------
--STEP 4: Calculates readings based on a quarterly time grid.
--------------------------------------------------------------------------
SELECT 
    DosAreas.GridID,
	TimeGrid.Value,	
    DosAreaMembers.DosAreaID,
	Contaminants.ContaminantID,
	[DosAreaValue] = Sum(Readings.Value * DosAreaMembers.RelativeWeight)/Sum(DosAreaMembers.RelativeWeight), 
    [Posts] = Sum(CASE WHEN Readings.Posted = 'TRUE' THEN 1 ELSE 0 END), 
    [RecordsUsed] = Count(Readings.Value),
	[PeriodDate] =	CAST(
	(CAST(YEAR(Readings.SamplingDate) AS VARCHAR(4)) +  
	CASE 
		WHEN DatePart(quarter,Readings.SamplingDate) = 1 THEN '-01-01'
		WHEN DatePart(quarter,Readings.SamplingDate) = 2 THEN '-04-01' 
		WHEN DatePart(quarter,Readings.SamplingDate) = 3 THEN '-07-01'       
		WHEN DatePart(quarter,Readings.SamplingDate) = 4 THEN '-10-01'       
	END) AS DATETIME)		
FROM 
    (((DosAreas 
    INNER JOIN (DosAreaMembers 
    INNER JOIN Readings 
    ON DosAreaMembers.LocationID = Readings.LocationID) 
    ON DosAreas.DosAreaID = DosAreaMembers.DosAreaID) 
    INNER JOIN Methods 
    ON Readings.MethodID = Methods.MethodID) 
    INNER JOIN TimeGrid 
    ON DosAreas.GridID = TimeGrid.GridID) 
    INNER JOIN Contaminants 
    ON Methods.ContaminantID = Contaminants.ContaminantID
WHERE
	(TimeGrid.Value = 'Quarterly')AND(Contaminants.SchemeID = DosAreas.SchemeID)
GROUP BY 
	Contaminants.ContaminantID, 
    DosAreas.GridID,
	TimeGrid.Value,	
    DosAreaMembers.DosAreaID, 
	CAST(
	(CAST(YEAR(Readings.SamplingDate) AS VARCHAR(4)) +  
	CASE 
		WHEN DatePart(quarter,Readings.SamplingDate) = 1 THEN '-01-01'
		WHEN DatePart(quarter,Readings.SamplingDate) = 2 THEN '-04-01' 
		WHEN DatePart(quarter,Readings.SamplingDate) = 3 THEN '-07-01'       
		WHEN DatePart(quarter,Readings.SamplingDate) = 4 THEN '-10-01'       
	END)AS DATETIME)	
HAVING       
    ( Sum(DosAreaMembers.RelativeWeight) <> 0)
	
UNION ALL
--------------------------------------------------------------------------
--STEP 5: Calculates readings based on a yearly time grid.
--------------------------------------------------------------------------
SELECT 
    DosAreas.GridID, 
	TimeGrid.Value,	
    DosAreaMembers.DosAreaID,
	Contaminants.ContaminantID,
	[DosAreaValue] = Sum(Readings.Value * DosAreaMembers.RelativeWeight)/Sum(DosAreaMembers.RelativeWeight), 
    [Posts] = Sum(CASE WHEN Readings.Posted = 'TRUE' THEN 1 ELSE 0 END), 
    [RecordsUsed] = Count(Readings.Value),
	[PeriodDate] = DateAdd(year,Year(Readings.SamplingDate)-1900,'1900-01-01')
FROM 
    (((DosAreas 
    INNER JOIN (DosAreaMembers 
    INNER JOIN Readings 
    ON DosAreaMembers.LocationID = Readings.LocationID) 
    ON DosAreas.DosAreaID = DosAreaMembers.DosAreaID) 
    INNER JOIN Methods 
    ON Readings.MethodID = Methods.MethodID) 
    INNER JOIN TimeGrid 
    ON DosAreas.GridID = TimeGrid.GridID) 
    INNER JOIN Contaminants 
    ON Methods.ContaminantID = Contaminants.ContaminantID
WHERE
	(TimeGrid.Value = 'Yearly') AND
	(Contaminants.SchemeID = DosAreas.SchemeID)
GROUP BY 
	Contaminants.ContaminantID, 
    DosAreas.GridID,
	TimeGrid.Value,	
    DosAreaMembers.DosAreaID, 
	DateAdd(year,Year(Readings.SamplingDate)-1900,'1900-01-01')
HAVING       
    ( Sum(DosAreaMembers.RelativeWeight) <> 0)	
	
GO

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources