A complicated, dynamic grouping, intensive TSQL query, where performance is very important.

Posted on 2008-10-05
Last Modified: 2012-06-21
Hi Experts,

I need to write a dynamic query that'll take some input variables and return a recordset.

I'm finding this immensely difficult, the few successes i've had, have been ridiculously inefficient when run against 1,000,000 rows of data.

I've tried building customDateAdd and customDateDiff functions, but the nature of the query makes them just too slow.

I really need some fresh eyes, from the real experts!

I've embedded some comments in the example code that'll hopefully explain things further, if anyone needs any clarification on anything, please let me know!

Many thanks
/* i need a SP that takes the following parmeters */
declare @First_Interval DateTime
declare @Last_Interval DateTime
declare @Intervals_To_Fetch Int
declare @Mins_Per_Interval Int
declare @Offset_From_Now Int
declare @Day_Start_Time DateTime
declare @Day_End_Time DateTime
/* Set some test Vars */
select @First_Interval = '2008-10-01T14:15:00.000', @Last_Interval  = '2008-10-06T15:45:00.000', @Mins_Per_Interval = 15, @Day_Start_Time = '1900-01-01T14:00:00.000', @Day_End_Time = '1900-01-01T16:00:00.000'
/* Create the Test table; jtable */
IF  NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[jtable7]') AND type in (N'U'))
CREATE TABLE [dbo].[jtable7]([jtime] [datetime] NOT NULL,[b] [smallint] NULL) ON [PRIMARY]
INSERT INTO jtable7  (jtime, b) 
||||||||||||||       jtime             |||||  b  |||||||||||||||||
SELECT/*||||*/'2008-10-01T14:15:00.000',/**/  6  /*||||*/UNION ALL
SELECT/*||||*/'2008-10-01T14:45:00.000',/**/  2  /*||||*/UNION ALL
SELECT/*||||*/'2008-10-01T14:46:59.000',/**/  4  /*||||*/UNION ALL
SELECT/*||||*/'2008-10-01T14:52:55.000',/**/  1  /*||||*/UNION ALL
SELECT/*||||*/'2008-10-01T14:55:59.000',/**/  3  /*||||*/UNION ALL
SELECT/*||||*/'2008-10-03T14:00:05.000',/**/  5  /*||||*/UNION ALL
SELECT/*||||*/'2008-10-03T14:01:05.000',/**/  7  /*||||*/UNION ALL
SELECT/*||||*/'2008-10-03T15:37:08.000',/**/ -5  /*||||*/UNION ALL
SELECT/*||||*/'2008-10-03T15:43:08.000',/**/ -4  /*||||*/UNION ALL
SELECT/*||||*/'2008-10-06T14:09:05.000',/**/  0  /*||||*/UNION ALL
/*|||||||||||/ The next values are rogue-        /||||||||||||||||
|||||||||||||/ values for testing purposes...    /||||||||||||||||
|||||||||||||/ They should be IGNORED by the     /||||||||||||||||
|||||||||||||/ query because they are either     /||||||||||||||||
|||||||||||||/   1) < @Day_Start_Time            /||||||||||||||||
|||||||||||||/   2) > @Day_End_Time              /||||||||||||||||
|||||||||||||/   3) < @First_Interval            /||||||||||||||||
|||||||||||||/   4) > @Last_Interval             /||||||||||||||||
|||||||||||||/   5) Outside mon-fri              /||||||||||||||*/
SELECT/*||||*/'2008-10-01T14:01:03.000',/**/ 15  /*||||*/UNION ALL
SELECT/*||||*/'2008-10-03T13:59:59.000',/**/ 99  /*||||*/UNION ALL
SELECT/*||||*/'2008-10-04T14:01:05.000',/**/ 98  /*||||*/UNION ALL
SELECT/*||||*/'2008-10-06T16:09:05.000',/**/ 97  /*||||*/
/* jtable7 has been created and we have inserted some test data */
/* I need the following recordset returned when the input variables are:
@First_Interval   =  '2008-10-01T14:15:00.000'
@Last_Interval    =  '2008-10-06T15:45:00.000'
@Mins_Per_Interval=  15
@Day_Start_Time   =  '1900-01-01T14:00:00.000'
@Day_End_Time     =  '1900-01-01T16:00:00.000'
||Interval                 ||  O ||  H ||  L ||  C ||
---------------------------||----||----||----||----|| /__There's no 14:00 as 
||2008-10-01 14:15:00.000  ||  6 ||  6 ||  6 ||  6 || \  @First_Interval is 14:15,
||2008-10-01 14:30:00.000  ||NULL||NULL||NULL||NULL||    
||2008-10-01 14:45:00.000  ||  2 ||  4 ||  1 ||  3 ||  
||2008-10-01 15:00:00.000  ||NULL||NULL||NULL||NULL||  <-- Wendesday
||2008-10-01 15:15:00.000  ||NULL||NULL||NULL||NULL||  
||2008-10-01 15:30:00.000  ||NULL||NULL||NULL||NULL||  
||2008-10-01 15:45:00.000  ||NULL||NULL||NULL||NULL||  
||2008-10-02 14:00:00.000  ||NULL||NULL||NULL||NULL||  
||2008-10-02 14:15:00.000  ||NULL||NULL||NULL||NULL||  
||2008-10-02 14:30:00.000  ||NULL||NULL||NULL||NULL||  
||2008-10-02 14:45:00.000  ||NULL||NULL||NULL||NULL||  
||2008-10-02 15:00:00.000  ||NULL||NULL||NULL||NULL||  <-- Thurssday
||2008-10-02 15:15:00.000  ||NULL||NULL||NULL||NULL||  
||2008-10-02 15:30:00.000  ||NULL||NULL||NULL||NULL||  
||2008-10-02 15:45:00.000  ||NULL||NULL||NULL||NULL||  
||2008-10-03 14:00:00.000  ||  5 ||  7 ||  5 ||  7 ||  
||2008-10-03 14:15:00.000  ||NULL||NULL||NULL||NULL||  
||2008-10-03 14:30:00.000  ||NULL||NULL||NULL||NULL||  
||2008-10-03 14:45:00.000  ||NULL||NULL||NULL||NULL||  <-- Friday
||2008-10-03 15:00:00.000  ||NULL||NULL||NULL||NULL||  
||2008-10-03 15:15:00.000  ||NULL||NULL||NULL||NULL||  
||2008-10-03 15:30:00.000  || -5 || -5 || -4 || -4 ||  
||2008-10-03 15:45:00.000  ||NULL||NULL||NULL||NULL||
||2008-10-06 14:00:00.000  ||  0 ||  0 ||  0 ||  0 ||  
||2008-10-06 14:15:00.000  ||NULL||NULL||NULL||NULL||  
||2008-10-06 14:30:00.000  ||NULL||NULL||NULL||NULL||  
||2008-10-06 14:45:00.000  ||NULL||NULL||NULL||NULL||  
||2008-10-06 15:00:00.000  ||NULL||NULL||NULL||NULL||  <-- Monday
||2008-10-06 15:15:00.000  ||NULL||NULL||NULL||NULL||  
||2008-10-06 15:30:00.000  ||NULL||NULL||NULL||NULL||  
||2008-10-06 15:45:00.000  ||NULL||NULL||NULL||NULL*/  

Open in new window

Question by:jondanger
  • 4
  • 2
  • 2

Expert Comment

by:Leo Eikelman
ID: 22644607
I see you are using Sql Server 2005.  You should use the SQL Server 2005 tuning advisor utility against your SQL statement(s).  It usually gives you good advice on what changes need to be done to speed up the query.

You can find a tutorial here:

Thank you.

Author Comment

ID: 22644683
Thanks leikelman, i only have 2k5 Express, but i'll be sure to check it out.

In any case, jtable7 is very simple and the index is obvious. I believe the problems lives in the code i've been trying to write. (which still doesn't perform how i'd hoped)


Accepted Solution

Leo Eikelman earned 250 total points
ID: 22644784
I'm not exactly clear on what you are trying to accomplish.

The built in DATEDIFF function will allow you to compare different parts of the date (day, hour, minute, etc..)

So basically what you would want to do in your stored procedure is execute the datediff function with the day "datepart" so you can get the total number of days and run a loop to check each interval in the day starting at the starting time of the first for the first loop and starting at 12:00 AM for each other day.

Once this is done, you will execute the datediff function using the hour "datepart" and loop through the remaining hours.

You can get some info on the DATEDIFF function here:

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

LVL 51

Assisted Solution

by:Mark Wills
Mark Wills earned 250 total points
ID: 22645124
What are the columns O H L C ? Guessing it is the value in column B  if Open, Highest, Lowest, Closing

For this type of thing, it is often advantageous to build a "calendar" table and then link against that - it really is painful doing things with datetime arithmetic and groupings because it invariably involves converting datetimes and as soon as you do that, some of the indexing dies... The other is trying to map vertical data into horizontal data... and depending on column definitions it is first and last as well as highest and lowest - with first and last being the more complex.

What kind of date ranges (ie start and end) are likely to be involved ?

LVL 51

Expert Comment

by:Mark Wills
ID: 22645479
Have you tried something like :

declare @First_Interval DateTime
declare @Last_Interval DateTime
declare @Intervals_To_Fetch Int
declare @Mins_Per_Interval Int
declare @Offset_From_Now Int
declare @Day_Start_Time varchar(8)
declare @Day_End_Time varchar(8)
declare @End_Time DateTime
set @First_Interval   =  '2008-10-01T14:15:00.000'
set @Last_Interval    =  '2008-10-06T15:45:00.000'
set @Mins_Per_Interval=  15
set @Day_Start_Time   =  '14:15:00'
set @Day_End_Time     =  '15:45:00'
declare @dates table (start_date datetime, end_date datetime)
while @First_Interval <= @last_interval
      set @end_time = dateadd(mi,@mins_per_interval,@first_interval)
      if convert(varchar(8),@first_interval,108) between @day_start_time and @day_end_time
         insert @dates values (@first_interval,@end_time)
         set @first_interval = @end_time
         set @first_interval = convert(datetime, (convert(varchar,dateadd(dd,1,@first_interval),106) + ' ' + @day_start_time),106)
select start_date, (select top 1 b from jtable7 where jtime between start_date and end_date order by jtime asc) as O,max(b) as H, min(b) as L, (select top 1 b from jtable7 where jtime between start_date and end_date order by jtime desc) as C
from @dates 
inner join jtable7 on jtime between start_date and end_date 
group by start_date, end_date

Open in new window

LVL 51

Expert Comment

by:Mark Wills
ID: 22645489
Need to add some more qualifiers to that such as an outer join so we have all the date times, and a check for week day etc etc. but though it might server as a good discussion point...


Author Comment

ID: 22645547
Hi mark_wills, yes you're correct, o = open, h = highest, l = lowest, c = closing.

When you say build a "calendar" table, do you mean build it as a temporary table upon each query execution for joining?  Sounds like a good idea, i'll investigate further.

>>What kind of date ranges (ie start and end) are likely to be involved ?
Typically, 40 intervals will be queried for, however it could be more than this, and never more than 100.
The number of intervals returned, can be calculated from First_Interval and Last_Interval / @Mins_Per_Interval, this could span any number of minutes worth of b data.
Again, typical use is, 15 min intervals looking at 2 days worth of data.
another example is 720 min intervals looking at 80 days worth of data.

LVL 51

Expert Comment

by:Mark Wills
ID: 22645591
Have a look at the sample code above. it builds a memory table, then uses it.

Featured Post

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

821 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question