Avoiding Correlated Subqueries

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)

Avoiding Correlated Subqueries

Author

Topic

darinh
Yak Posting Veteran

58 Posts

Posted - 2009-03-01 : 21:49:01

I am anaylzing some Call Detail Records to determine Link Capacities for a Voice Switch. Call Detail Records hold, amongst other things, Originating Number, Destination Number, Time and Duration of the Call.

The key thing I want to determine is the peak number of concurrent calls per geographic region and carrier. I can easily determine the region and carrier from the destination number, so I am trying to determine the peak calls

I am currently doing this


CREATE TABLE #PeakCallAnalysis 
( 
  [CDR_ID] [bigint], 
  [StartDate] [DATETIME], 
  [EndDate]   [DATETIME]
) 

-- Some Rows of Test Data
INSERT INTO #PeakCallAnalysis 

SELECT 13080, '2009-01-01 00:01:00.000', '2009-01-01 00:03:00.000'
UNION
SELECT 24802, '2009-01-01 00:01:00.000', '2009-01-01 00:04:00.000'
UNION
SELECT 24975, '2009-01-01 00:02:00.000', '2009-01-01 00:07:00.000'
UNION
SELECT 14684, '2009-01-01 00:02:00.000', '2009-01-01 00:04:00.000'
UNION
SELECT 17195, '2009-01-01 00:03:00.000', '2009-01-01 00:05:00.000'
UNION
SELECT 38851, '2009-01-01 00:03:00.000', '2009-01-01 00:19:00.000'
UNION
SELECT 33572, '2009-01-01 00:04:00.000', '2009-01-01 00:11:00.000'
UNION
SELECT 31336, '2009-01-01 00:04:00.000', '2009-01-01 00:10:00.000'
UNION
SELECT 21806, '2009-01-01 00:05:00.000', '2009-01-01 00:07:00.000'
UNION
SELECT 21688, '2009-01-01 00:05:00.000', '2009-01-01 00:06:00.000'
UNION
SELECT 23641, '2009-01-01 00:07:00.000', '2009-01-01 00:08:00.000'
UNION
SELECT 27440, '2009-01-01 00:07:00.000', '2009-01-01 00:10:00.000'
UNION
SELECT 33053, '2009-01-01 00:08:00.000', '2009-01-01 00:11:00.000'
UNION
SELECT 37278, '2009-01-01 00:08:00.000', '2009-01-01 00:13:00.000'
UNION
SELECT 36546, '2009-01-01 00:08:00.000', '2009-01-01 00:12:00.000'
UNION
SELECT 30996, '2009-01-01 00:08:00.000', '2009-01-01 00:10:00.000'
UNION
SELECT 42371, '2009-01-01 00:09:00.000', '2009-01-01 00:21:00.000'
UNION
SELECT 34398, '2009-01-01 00:09:00.000', '2009-01-01 00:11:00.000'
UNION
SELECT 41011, '2009-01-01 00:10:00.000', '2009-01-01 00:15:00.000'
UNION
SELECT 31086, '2009-01-01 00:11:00.000', '2009-01-01 00:13:00.000'


SELECT		
    CDR_ID,
    StartDate,
    EndDate, 
	/* Find all the calls that started at the same time or
	before this one and haven't finished yet*/
    (SELECT 
        COUNT(*) 
    FROM   
        #PeakCallAnalysis A2 
     WHERE  
	A1.startdate >= A2.StartDate  
    AND 
        A1.startdate < A2.Enddate) ConcurrentCalls
FROM   
    #PeakCallAnalysis A1
ORDER BY
    StartDate

DROP TABLE #PeakCallAnalysis

This works well enough and gives me the right answer. However to do a proper analysis I need to analyse tens of million of records and my correlated subquery that doesn't even have a proper join seems like a bad idea.

Is there a better way to do it?

nr
SQLTeam MVY

12543 Posts

Posted - 2009-03-01 : 22:14:52

What happens if a call starts and ends then another starts and ends while the call you are counting for is running? They would be counted as concurrent when they aren't. Would give a very missleading figure for very long calls.

You can maintain a table with all the start/end times of calls and the counts at that point.
then the query becomes

select max(numcalls)
from counttbl
join calltbl
on counttime between startdate and enddate

But I suspect you are probably trying o generate a graph which can be done just from counttbl.

If you have a lastupdated column on your underlying call table you can use that to maintain the counttbl - probably wouldn't want a trigger on it.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

darinh
Yak Posting Veteran

58 Posts

Posted - 2009-03-01 : 23:38:25

I am doing this on historical data, so I don't have to worry about calls starting while I am counting. I am counting up how many calls were in progress at the time the call I am looking at starts. When the next call starts, I will do the same evaluation. I think the logic of my query works well enough, it is the performance I am concerned about. What I want to eventually end up with is a SSRS report that we can use to look at the cost benefits of increasing capacity on certain links, put a new link into new region, or interconnect with another carrier. I am worried about the resources that will be consumed with the correlated subquery.

nr
SQLTeam MVY

12543 Posts

Posted - 2009-03-02 : 04:54:51

I misread your query - you are actually doing what I suggested above except that you are trying to do it in a query rather than maintaining a table.
Have a look at the last sentence in my previous post?

The idea is to maintain the counttable incrementally so there is litle work to do when you run the report.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources