[Solved] SQL Server GMT date & time conversion hourly report using group by

EverSQL Database Performance Knowledge Base

SQL Server GMT date & time conversion hourly report using group by

Database type:

I have been beating my head on this problem for days now and would love some feedback or suggestions on how to properly handle this problem.

I am trying to create a SQL query that is run hourly that selects all records for the previous hour and inserts them into a new reports table. My current SQL query works as intended; but I now need to find out how to adjust the CreationHour and CreationDate based on the customers GMT setting.

The problem is that the Payment records are inserted based on the servers date / time (which is GMT -5 and datatype datetime) so I need to somehow adjust the time based on the customers GMT, and then add or subtract the -5 GMT of the server.

So far the only thing I can think of is using a CASE statement but the problem with this is that I will need to to use the same CASE statement 5 times, which will have 73 possible WHEN statements.

Can anyone suggest a better option on how to approach this or resolve this problem? So far over the last week I have run into 3 deadends and cannot find a more optimal way of approaching this.

Here is the current SQL query I am executing:

SELECT 
   P.ComputerID, P.CustomerID, MAX(C.GMT) AS TimeAdguest,
   MAX(DATEPART(HOUR, dateadd(hour, (C.GMT -1), P.CreationDateTime))) AS [CreationHour], 
   Convert(varchar(10),MAX(dateadd(hour, (C.GMT -1),P.CreationDateTime)),101) AS [CreationDate],
   SUM(P.SecondsPurchased) AS [Seconds],
   SUM(P.PagesPurchased) AS [Pages], 
   SUM(P.Amount) AS [Amount]
FROM [Payments] P
left Join Customer C ON C.CustomerID = P.CustomerID
WHERE 
    DATEPART(HOUR, P.CreationDateTime) = DATEPART(HOUR, dateadd(hour,-1, getdate()))
    AND Convert(varchar(10), dateadd(hour, -1, P.CreationDateTime), 101) = Convert(varchar(10), dateadd(hour, -1, getdate()), 101)
GROUP BY 
    Convert(varchar(10),P.CreationDateTime,101), P.ComputerID, P.CustomerID

And returns the following results:

ComputerID  CustomerID  TimeAdguest CreationHour    CreationDate    Seconds Pages   Amount
-------------------------------------------------------------------------------------------
 7679   1188    -2  8   03/27/2013  1670    0   56.9600009918213
12891   42  17  3   03/28/2013  740 0   26.7200005054474
12893   42  17  3   03/28/2013  480 0   20.1400001049042
12894   42  17  3   03/28/2013  280 0   10.8799998760223
12943   42  17  3   03/28/2013  120 0   1
12943   1188    -2  8   03/27/2013  240 0   2
13407   1188    -2  8   03/27/2013  960 0   40.2800002098084
13476   1188    -2  8   03/27/2013  840 0   25.6999996900558
14281   42  17  3   03/28/2013  562 0   6
16026   1188    -2  8   03/27/2013  1134    0   13

How to optimize this SQL query?

The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:

  1. Description of the steps you can take to speed up the query.
  2. The optimal indexes for this query, which you can copy and create in your database.
  3. An automatically re-written query you can copy and execute in your database.
The optimization process and recommendations:
  1. Avoid Calling Functions With Indexed Columns (query line: 24): When a function is used directly on an indexed column, the database's optimizer won’t be able to use the index. For example, if the column `CreationDateTime` is indexed, the index won’t be used as it’s wrapped with the function `Convert`. If you can’t find an alternative condition that won’t use a function call, a possible solution is to store the required value in a new indexed column.
  2. Avoid Calling Functions With Indexed Columns (query line: 26): When a function is used directly on an indexed column, the database's optimizer won’t be able to use the index. For example, if the column `CreationDateTime` is indexed, the index won’t be used as it’s wrapped with the function `Convert`. If you can’t find an alternative condition that won’t use a function call, a possible solution is to store the required value in a new indexed column.
  3. Create Optimal Indexes (modified query below): The recommended indexes are an integral part of this optimization effort and should be created before testing the execution duration of the optimized query.
  4. Index Function Calls Using Generated Columns (modified query below): When a function is used directly on an indexed column, the database's optimizer won’t be able to use the index to optimize the search. Creating and indexing a generated column (supported in MySQL 5.7) will allow MySQL to optimize the search.
Optimal indexes for this query:
CREATE INDEX customer_idx_customerid ON Customer (CustomerID);
CREATE INDEX payments_idx_datepart_computerid_customerid ON Payments (datepart_hour_creationdatetime,ComputerID,CustomerID);
The optimized query:
SELECT
        P.ComputerID,
        P.CustomerID,
        MAX(C.GMT) AS TimeAdguest,
        MAX(DATEPART(HOUR,
        dateadd(hour,
        (C.GMT - 1),
        P.CreationDateTime))) AS [CreationHour],
        Convert(varchar(10),
        MAX(dateadd(hour,
        (C.GMT - 1),
        P.CreationDateTime)),
        101) AS [CreationDate],
        SUM(P.SecondsPurchased) AS [Seconds],
        SUM(P.PagesPurchased) AS [Pages],
        SUM(P.Amount) AS [Amount] 
    FROM
        [Payments] P 
    LEFT JOIN
        Customer C 
            ON C.CustomerID = P.CustomerID 
    WHERE
        P.datepart_hour_creationdatetime = DATEPART(HOUR, dateadd(hour, -1, getdate())) 
        AND Convert(varchar(10), dateadd(hour, -1, P.CreationDateTime), 101) = Convert(varchar(10), dateadd(hour, -1, getdate()), 101) 
    GROUP BY
        Convert(varchar(10),
        P.CreationDateTime,
        101),
        P.ComputerID,
        P.CustomerID

Related Articles



* original question posted on StackOverflow here.