How to solve this tabular matrix report issue

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 2000 Forums

Transact-SQL (2000)

How to solve this tabular matrix report issue

Author

Topic

ryn03
Starting Member

3 Posts

Posted - 2009-05-27 : 11:52:05

Hi All,

We have a database containing speed records of vehicles, and was asked to create a tabular matrix of total occurrences arranged by speed and time windows for a selected date range.

We have a stored procedure called from a web page and it works fine however when there is a load of data we get time-out issues, even if you run the script on query analyser it takes ages to execute.

I would appreciate any input to make this run faster.

If you run the below script on your query analyser you will see some results with dummy data. Based on this dummy data you should see on between 00:00 and 02:00 o'clock there were total of 3 cars doing between 0mph and 10mph. And between 20:00 and 22:00 there was 1 car doing between 21mph and 30mph etc...

Please let me know if you need any more information.

Cheers
Muzzy

DECLARE @inTimeIntervalInMinutes INT,
@inSpeedWindowStartValue1 INT,
@inSpeedWindowEndValue1 INT,
@inSpeedWindowStartValue2 INT,
@inSpeedWindowEndValue2 INT,
@inSpeedWindowStartValue3 INT,
@inSpeedWindowEndValue3 INT,
@inSpeedWindowStartValue4 INT,
@inSpeedWindowEndValue4 INT,
@inSpeedWindowStartValue5 INT,
@inSpeedWindowEndValue5 INT,
@inSpeedWindowStartValue6 INT,
@inSpeedWindowEndValue6 INT,
@inSpeedWindowStartValue7 INT,
@inSpeedWindowEndValue7 INT,
@inSpeedWindowStartValue8 INT,
@inSpeedWindowEndValue8 INT,
@inSpeedWindowStartValue9 INT,
@inSpeedWindowEndValue9 INT,
@dtStartDate DATETIME,
@dtEndDate DATETIME

SET @inTimeIntervalInMinutes = 120
SET @inSpeedWindowStartValue1 = 0
SET @inSpeedWindowEndValue1 = 10
SET @inSpeedWindowStartValue2 = 11
SET @inSpeedWindowEndValue2 = 20
SET @inSpeedWindowStartValue3 = 21
SET @inSpeedWindowEndValue3 = 30
SET @inSpeedWindowStartValue4 = 31
SET @inSpeedWindowEndValue4 = 40
SET @inSpeedWindowStartValue5 = 41
SET @inSpeedWindowEndValue5 = 50
SET @inSpeedWindowStartValue6 = 51
SET @inSpeedWindowEndValue6 = 60
SET @inSpeedWindowStartValue7 = 61
SET @inSpeedWindowEndValue7 = 70
SET @inSpeedWindowStartValue8 = 71
SET @inSpeedWindowEndValue8 = 80
SET @inSpeedWindowStartValue9 = 81
SET @inSpeedWindowEndValue9 = 90
SET @dtStartDate = '01-May-2009'
SET @dtEndDate = '01-Jun-2009'

-------------------------------------------------------------------------
--create the temp table to put some dummy data
DECLARE @tblTempSpeedReadings TABLE
(
inRecCount int IDENTITY,
vcValue varchar(10),
dtDateTime DateTime,
PRIMARY KEY (inRecCount)
)

DECLARE @inCounter Int
SET @inCounter = 0
WHILE @inCounter <= 90
BEGIN

INSERT INTO @tblTempSpeedReadings(vcValue, dtDateTime)
VALUES (@inCounter, DATEADD(hour,@inCounter,GetDate()))

SET @inCounter = @inCounter + 1
END

SET @inCounter = 1
WHILE @inCounter <= 90
BEGIN

INSERT INTO @tblTempSpeedReadings(vcValue, dtDateTime)
VALUES (@inCounter, DATEADD(hour,@inCounter,GetDate()))

SET @inCounter = @inCounter + 2
END

SET @inCounter = 1
WHILE @inCounter <= 90
BEGIN

INSERT INTO @tblTempSpeedReadings(vcValue, dtDateTime)
VALUES (@inCounter, DATEADD(hour,@inCounter,GetDate()))

SET @inCounter = @inCounter + 3
END
--------------------------------------------------------------------------------

--Main Script to create report

SELECT DATEPART(hh, MinuteSections) AS inHour, DATEPART(mi, MinuteSections) AS inMinute,
SUM(inSpeedWindow1) AS inSpeedWindow1,
SUM(inSpeedWindow2) AS inSpeedWindow2,
SUM(inSpeedWindow3) AS inSpeedWindow3,
SUM(inSpeedWindow4) AS inSpeedWindow4,
SUM(inSpeedWindow5) AS inSpeedWindow5,
SUM(inSpeedWindow6) AS inSpeedWindow6,
SUM(inSpeedWindow7) AS inSpeedWindow7,
SUM(inSpeedWindow8) AS inSpeedWindow8,
SUM(inSpeedWindow9) AS inSpeedWindow9,
AVG(CAST(vcValue AS INT)) AS AVGSPEED
FROM
(
SELECT
DATEADD(Minute, (DATEDIFF(Minute, 0, dtDateTime)/@inTimeIntervalInMinutes)*@inTimeIntervalInMinutes, 0) AS MinuteSections, vcValue,
SUM(CASE WHEN vcValue > @inSpeedWindowStartValue1 AND vcValue <= @inSpeedWindowEndValue1 THEN 1 ELSE 0 END) AS inSpeedWindow1,
SUM(CASE WHEN vcValue > @inSpeedWindowStartValue2 AND vcValue <= @inSpeedWindowEndValue2 THEN 1 ELSE 0 END) AS inSpeedWindow2,
SUM(CASE WHEN vcValue > @inSpeedWindowStartValue3 AND vcValue <= @inSpeedWindowEndValue3 THEN 1 ELSE 0 END) AS inSpeedWindow3,
SUM(CASE WHEN vcValue > @inSpeedWindowStartValue4 AND vcValue <= @inSpeedWindowEndValue4 THEN 1 ELSE 0 END) AS inSpeedWindow4,
SUM(CASE WHEN vcValue > @inSpeedWindowStartValue5 AND vcValue <= @inSpeedWindowEndValue5 THEN 1 ELSE 0 END) AS inSpeedWindow5,
SUM(CASE WHEN vcValue > @inSpeedWindowStartValue6 AND vcValue <= @inSpeedWindowEndValue6 THEN 1 ELSE 0 END) AS inSpeedWindow6,
SUM(CASE WHEN vcValue > @inSpeedWindowStartValue7 AND vcValue <= @inSpeedWindowEndValue7 THEN 1 ELSE 0 END) AS inSpeedWindow7,
SUM(CASE WHEN vcValue > @inSpeedWindowStartValue8 AND vcValue <= @inSpeedWindowEndValue8 THEN 1 ELSE 0 END) AS inSpeedWindow8,
SUM(CASE WHEN vcValue > @inSpeedWindowStartValue9 AND vcValue <= @inSpeedWindowEndValue9 THEN 1 ELSE 0 END) AS inSpeedWindow9

FROM @tblTempSpeedReadings
WHERE dtDatetime BETWEEN @dtStartDate AND @dtEndDate
GROUP BY DATEADD(Minute, (DATEDIFF(Minute, 0, dtDateTime)/@inTimeIntervalInMinutes)*@inTimeIntervalInMinutes, 0), vcValue
) A
GROUP BY DATEPART(hh, MinuteSections), DATEPART(mi, MinuteSections)
ORDER BY DATEPART(hh, MinuteSections), DATEPART(mi, MinuteSections)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-27 : 13:06:09

can you explain with some sample data what you're trying to achieve?

ryn03
Starting Member

3 Posts

Posted - 2009-05-27 : 18:05:59

We are tyring to create a report to show tabular matrix of total occurrences arranged by speed and time windows. If you run the above code on your query analyser you will see the results that we are trying to achive. This code works but too slow when there is too much data.

This report tells us how many cars were doing speed X for time range Y.

e.g: between 00:00 and 02:00 o'clock there were total of 3 cars doing between 0mph and 10mph. And between 20:00 and 22:00 there was 1 car doing between 21mph and 30mph etc...

jonasalbert20
Constraint Violating Yak Guru

300 Posts

Posted - 2009-05-28 : 03:50:10

Follow this .....

For fast result follow this...
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx

Want Philippines to become 1st World COuntry? Go for World War 3...

ryn03
Starting Member

3 Posts

Posted - 2009-05-28 : 06:58:27

Hi,

Thanks for the replies however I thought I already included the dummy data and explained what I am trying to achieve.

If you run the above script in query analyser against any database it will populate some dummy data in a temporary table and will produce the below result.


inHour      inMinute    inSpeedWindow1 inSpeedWindow2 inSpeedWindow3 inSpeedWindow4 inSpeedWindow5 inSpeedWindow6 inSpeedWindow7 inSpeedWindow8 inSpeedWindow9 AVGSPEED    
----------- ----------- -------------- -------------- -------------- -------------- -------------- -------------- -------------- -------------- -------------- ----------- 
0           0           0              4              0              4              0              0              1              0              0              37
2           0           0              4              0              4              0              0              4              0              0              39
4           0           0              3              0              0              1              0              3              0              0              41
6           0           0              4              0              0              4              0              4              0              0              43
8           0           0              0              2              0              4              0              4              0              0              45
10          0           0              0              3              0              3              0              0              1              0              40
12          0           4              0              4              0              4              0              0              4              0              37
14          0           4              0              4              0              0              2              0              4              0              39
16          0           3              0              3              0              0              3              0              3              0              41
18          0           4              0              0              1              0              4              0              4              0              43
20          0           4              0              0              4              0              4              0              0              2              45
22          0           0              1              0              3              0              3              0              0              3              47

This is what we are producing, my problem is that we have a table containing over 800.000 rows just for last week, and for a given road you are trying to run this code for over 30.000 rows (per week), therefore it takes ages to run, my question is that is there any way to change this script and make it run quicker?

Cheers

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-28 : 13:26:59

what the table storing? is it storing some second wise statuses? what are you trying to display in report from this?

souLTower
Starting Member

39 Posts

Posted - 2009-05-29 : 10:26:25

Is the big table indexed? If not that's a good place to start. Next try breaking your query into pieces and see if you can find the bottleneck using profiler. For example try running your entire inline query (table A). Next try taking some of the case statements out to see if that makes a difference. You can also try, instead of the case statements, using sub queries to get the column data. That sounds counter intuitive but I've had instances where when the table is indexed by the target column the subqueries are faster. They tend to take more reads but sometimes just run faster.

Hope this helps
ST

God Bless

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources