Subnested cursor Loop takes too long- any other suggestions?

[CanNeverThinkOfaName]

Hi

I'm trying to count the number of people watching TV at a specific minute in time (well I need to do it for every minute in an entire month!)

My data shows when people started and stopped watching TV- so I need to check for every person when they started watching it and whether they shud be included for watching it at e.g. 01/04/2008 00:01:00.

I do this by checking (per account) the last start watching time- check that next stop watching time - if the stop watching is after the required minute i'm checking for, then include this account.

By the time I've done all this (and the table I'm querying has 1.6 million rows and has indexes), it takes 25 minutes to get the final number for just one minute!
I need to do every minute in a month (every month!!)

Has anyone any suggestions to another approach or another way to make it run faster please?
Anything would really be appreciated!!

Sample data here:

CREATE TABLE x
(
  ACCOUNTID             VARCHAR2(20 BYTE),
  SESSIONID             VARCHAR2(200 BYTE),
  USAGETIMESTAMP        DATE,
  ELAPSEDTIME_secs          NUMBER,
  EVENTNAME             VARCHAR2(200 BYTE)
 );
 
 insert into x values
 ('69900107727','5A5A7DA61B3E4FC3D8E913999E94E672',to_date(to_char('31/03/2008 23:57:17'),'dd/mm/yyyy hh24:mi:ss'),'','start watching');
 insert into x values
 ('69900107727','5A5A7DA61B3E4FC3D8E913999E94E672',to_date(to_char('01/04/2008 00:03:17'),'dd/mm/yyyy hh24:mi:ss'),'360','stop watching');

script:

declare sec integer := 0;


cursor c0
is
select to_date(to_char('01/04/2008 00:00:00'),'dd/mm/yyyy hh24:mi:ss') d
from dual;;

addate date;
begin

for r0 in c0
loop

addate := r0.d;
--dbms_output.put_line('start: '||to_char(sysdate,'dd/mm/yyyy hh24:mi:ss'));
	while addate < to_date(to_char('01/05/2008 00:00:00'),'dd/mm/yyyy hh24:mi:ss')
	loop
	
	addate := r0.d + sec/1440; --every minute
	
		declare 
		cursor c1
		is
		select max(usagetimestamp) maxtimestamp,accountid
		from x
		where usagetimestamp < addate
		and eventname like 'start watching%'
		group by accountid;
		
		accno varchar2(15) := null;
		noviewers number := 0;
		
		begin
		select count(distinct accountid) 
		into noviewers 
		from x 
		where usagetimestamp = addate
		and eventname like 'start watching%';
				
			  for r1 in c1
			  loop
			  	  accno := r1.accountid;
				  
				  declare cursor c2 is
					select min(usagetimestamp) mintimestamp, accountid
					from x 
					where usagetimestamp >= r1.maxtimestamp
					and accountid = accno
					and eventname like 'stop watching%'
					group by accountid;
					
					begin
					
						 for r2 in c2
						 loop
						  if r2.mintimestamp >= addate
						 then
								noviewers := noviewers + 1;
						 end if;		
						 end loop;		
					
					end;
				  
			  end loop;
			  
			  insert into y (addate, noc4viewers, lastupdated) values (addate,  noviewers, sysdate);
			  commit;
		end;	
	sec := sec+1;
	--dbms_output.put_line('end: '||to_char(sysdate,'dd/mm/yyyy hh24:mi:ss'));	
	end loop;
end loop;

end;

/

I use toad to run and am using Oracle 10g

Thanks in advance

 

[LKBrwnDBA]

DO NOT use subnested cursors -- use SQL queries with joins.



----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[CanNeverThinkOfaName]

What do you mean by SQL joins? and I would still have to use one loop to increase the time period by a minute?

Thanks for an alternative tho

 

[taupirho]

One way might be to generate a secondary table which contains a record for each account_id and the minutes they're watching TV
In your example (not sure exactly what you're start/end times would be but you get the picture) this second table would contain something like

Account ID, date_time_watching
'69900107727' '31/03/2008 23:58:17'
'69900107727' '31/03/2008 23:59:17'
'69900107727' '31/03/2008 24:00:17'
'69900107727' '31/03/2008 24:01:17'
'69900107727' '31/03/2008 24:02:17'
'69900107727' '31/03/2008 24:03:17'

This second table would be fairly easy to generate (just using SQL) but would obviously be much bigger. The beauty though is that you can now just do a

select account_id,count(*)
from t2
group by to_char(date_time_watching,'dd/mm/yyyy hh:mi')

and you've got your counts per minute

What do you reckon?



In order to understand recursion, you must first understand recursion.

 

[sem]

Use Oracle jobs instead of calculating time within pl/sql loop. Then your outermost loop may be replaced by single statement

addate := to_date('01/04/2008 00:00:00','dd/mm/yyyy hh24:mi:ss');

And why do you think that incrementing variable sec changes current time by seconds? Didn't you try to declare a variable min to avoid subtracting by 1440, but come accross the reserved word ?

Regards, Dima

http://seminihin.narod.ru

 

[ChrisHunt]

Is the session_id unique for each pair of start/stop watching signals? That would make life easy:

SELECT COUNT(*)
FROM   x start_x, x stop_x
WHERE  start_x.session_id = stop_x.session_id
AND    start_x.eventname like 'start watching%'
AND    stop_x.eventname like 'stop watching%'
AND    TO_DATE ('01-JAN-2008 12:00','DD-MON-YYYY HH24:MI')
BETWEEN start_x.usagetimestamp AND stop_x.usagetimestamp

You'd just have to run that for each minute of the month.

If you can't use the session_id in this way, I'd suggest you first create a temporary table of watching sessions - one row per session, with a column for the start date/time and one for the stop date/time. Then you can use a query similar to the one above.

-- Chris Hunt
Webmaster & Tragedian
Extra Connections Ltd

 

[CanNeverThinkOfaName]

I've been working hard on all your suggestions and have come up with this so far- its reduced the runtime to 8 mins but this is still too long!

Any other ways to make it run faster?!

sem- I don't understand what you mean about the minute thing - and I'm not familiar with jobs but can they increment a variable by a minute and keep running the script over and overlike the first loop does?

ChrisHunt- the session_id can't be relied upon unfortunately

taupirho- I'm gona have a think about your suggestion and play around to see if its any quicker than what I have below

Thanks for all your help so far!

declare sec integer := 0;

cursor c0
is
select to_date(to_char('01/04/2008 00:02:00'),'dd/mm/yyyy hh24:mi:ss') d
from dual;
--dbms_output.put_line(to_char(sec));

addate date;
begin

for r0 in c0
loop

addate := r0.d;
--dbms_output.put_line('start: '||to_char(sysdate,'dd/mm/yyyy hh24:mi:ss'));
	while addate < to_date(to_char('01/05/2008 00:00:00'),'dd/mm/yyyy hh24:mi:ss')
	loop
	
		addate := r0.d + sec/1440; --every minute
		
		insert into channel4adtimes (addate, noc4viewers,lastupdated)
		select addate,count(accountid),sysdate
		from 
		(select au.accountid
		from x au, (select max(au.usagetimestamp) maxtimestamp,au.accountid
								from x au
								where au.usagetimestamp <= addate
								--and (au.accountid = '69900372728' or au.accountid = '69900125147')
								group by au.accountid) b
		where au.accountid = b.accountid
		and au.usagetimestamp >= b.maxtimestamp
		and b.maxtimestamp <= addate
		group by au.accountid
		having min(usagetimestamp) >=addate
		);
		commit;
	sec := sec+1;
	--dbms_output.put_line(to_char(sec));
	--dbms_output.put_line('end: '||to_char(sysdate,'dd/mm/yyyy hh24:mi:ss'));	
	end loop;
	
end loop;
--dbms_output.put_line('end: '||to_char(sysdate,'dd/mm/yyyy hh24:mi:ss'));
end;

 

[sem]

CanNeverThinkOfaName
Oracle jobs allow to run your task (pl/sql program) by schedule, e.g. each minute.

Sorry, I missed your task to analyze retrospective data, not get a kind of live results.

I need to clarify:
1. Do you need data for all minutes or just not empty ones?
2. Is it possible that single account has multiple sessions at the same moment?
3. Are all the sessions already stopped?


Regards, Dima

http://seminihin.narod.ru

 

[sem]

1. You need a query that returns more results than a number of minutes in your period. In smaller cases I'd suggest to query from all_objects, but for this one you may create a piplined function or a table with single field. Lets assume the table is called long_table

2. Assuming all your sessions are started within a table, the query may look like

select mnt, count(distinct xB.Accountid)
  from x xE,
       x xB,
       (select :start_date + (rownum - 1) / (24 * 60) mnt
          from long_table
         where rownum < (24 * 60 * (:end_date - :start_date)))
 where xE.eventname(+) = 'stop watching'
   and xB.eventname = 'start watching'
   and xE.Sessionid(+) = xB.Sessionid
   and xE.accountid(+) = xB.Accountid
   and (xE.usagetimestamp >= :start_date or xe.usagetimestamp is null)
   and xB.usagetimestamp < :end_date
   and (mnt >= xB.usagetimestamp and
        (xE.usagetimestamp > mnt or xE.Usagetimestamp is null))
 group by mnt

The idea is to make Oracle do its work. The efficiency may depend on the distribution of your data.

P.S. Don't write to_char('01/04/2008 00:00:00'), because '01/04/2008 00:00:00' is already a char. Correct syntax is to_date('01/04/2008 00:00:00', 'dd/mm/yyyy hh24:mi:ss')

Regards, Dima

http://seminihin.narod.ru

 

[CanNeverThinkOfaName]

Hi Sem

I need data for all minutes but could live without empty minutes(ie 0 viewers for a certain minute)

A single account could have multiple sessions open at one time- I can't use sessionid to link with in the join

You can pretty much ignore sessions for this query.

I've being playing with your solution but it doesn't cater for people have are watching tv around the start point (ie 1/4/08 00:00:00) - people may have tuned in on 31/3/08 23:55:00 and so should be included as they are still watching tv at 1/4/08 00:00:00

I can't see how to change your code to include these people though...

Thanks!

 

[CanNeverThinkOfaName]

Hi taupirho

I hate to ask you this but my brain is just not working anymore today and I think I've looking at this query too long and can't see the wood for the trees anymore!

Could you give me a helping hand and help me with the code I need to log to generate a secondary table which contains a record for each account_id and the minutes they're watching TV as you showed in your example?!!

I'd appreciated it greatly

Thanks!

 

[karluk]

Please allow me to ask a stupid question - why do you need to do a table join at all? You only give two rows of sample data, but for those two rows there is nothing in the "start watching" row that can't be derived from the "stop watching" row. In particular you have the session end time and the session duration (in seconds), so you can calculate the session begin time by subtracting the duration from the end time. That suggests you can get the number of viewers for a given minute by something similar to the following query.

select to_char(to_date('01-APR-2008','DD-MON-YYYY'),
              'dd/mm/yyyy hh24:mi:ss') as report_minute,
       count(distinct accountid) as NUMBER_OF_VIEWERS
  from x
    where eventname = 'stop watching'
      and to_date('01-APR-2008','DD-MON-YYYY')
         between usagetimestamp-elapsedtime_secs/86400
             and to_date('01-APR-2008','DD-MON-YYYY');

 

[ChrisHunt]

I've got some ideas, but I'm a bit hazy on some aspects of your data. I can see that you get a series of start/stop messages for each account/session, but there's still a couple of things I don't follow.

Will a customer always get records in the order start-stop-start-stop-... , or might they get two start records in a row if they've got two TVs? Is that where session IDs come in?

If a customer has two TVs switched on at once, how many times should that be counted? Once (a count of customers) or twice (a count of TVs)?

Perhaps you could supply a bit more test data, illustrating all the tricky things that can happen and that you need to cope with. Otherwise we'll just waste mre time coming up with solutions that won't work for you.



-- Chris Hunt
Webmaster & Tragedian
Extra Connections Ltd

 

[CanNeverThinkOfaName]

Hi Chris

It doesnt go in a specific order of start stop start stop - there could be 2 start watchings on 2 TVs (this is the idea of sessionid but it isn't a field to rely on unfortunately)

I am counting customer's not TVs

I will work on getting some test data up here to show different scenarios

I have since worked out a way of looking at all of the 'stop watching' data and using the elapsed time to work out the start watching time and its been easier to do work out my answer as i have the start and stop watching on one line.

select count(distinct accountid)
		from x
		where  eventname = 'stop watching'
		and to_date('01/04/2008 00:00:00', 'dd/mm/yyyy hh24:mi:ss') between usagetimestamp-elapsedtime/1000/60/60/24 and usagetimestamp;

I still would like to work out how to do it using both lines for start and stop watching as sometimes an event doesnt get logged for stop watching- and for the purpose of stats it should be included until the session ends as they could well be watching all that time and I'm missing them out by using the stop watching and elapsed time method (hope that makes sense!)

Thanks!