Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2008-10-05
10
Medium Priority
?
225 Views
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
Jon
/* 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'))
BEGIN
CREATE TABLE [dbo].[jtable7]([jtime] [datetime] NOT NULL,[b] [smallint] NULL) ON [PRIMARY]
END
DELETE FROM jtable7
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

0
Comment
Question by:jondanger
  • 4
  • 2
  • 2
10 Comments
 
LVL 8

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:

http://www.developer.com/db/article.php/3607186


Thank you.
0
 

Author Comment

by:jondanger
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)


0
 
LVL 8

Accepted Solution

by:
Leo Eikelman earned 1000 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:

http://msdn.microsoft.com/en-us/library/ms189794.aspx

Thanks.
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
LVL 51

Assisted Solution

by:Mark Wills
Mark Wills earned 1000 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 ?

0
 
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
begin
      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
      begin
         insert @dates values (@first_interval,@end_time)
         set @first_interval = @end_time
      end
      else
      begin
         set @first_interval = convert(datetime, (convert(varchar,dateadd(dd,1,@first_interval),106) + ' ' + @day_start_time),106)
      end
 
end
 
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

0
 
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...

0
 

Author Comment

by:jondanger
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.

0
 
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.
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

Lotus Notes has been used since a very long time as an e-mail client and is very popular because of it's unmatched security. In this article we are going to learn about  RRV Bucket corruption and understand various methods to Fix "RRV Bucket Corrupt…
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

971 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