Doeast GB-PVR poll the database?

[cginzel]

sub,

I've been running the MSDE as my backend for a while now and things seem to be working pretty good. But I've noticed on occation that performance slows down and my CPU cycles from nearly zero to 100% utilization. I can see in the task list that the MSDE database is the one jumping up and down in CPU utilization so I put the profiler on it and I see GB-PVR is constantly issue a simple query against the database, over and over again...

SELECT * from PROGRAMME where oid = 193478

where the oid value changes each time.

Every now and then I see another statement sprinked in there:

SELECT * FROM RECORDING_SCHEDULE order by manual_start_time

What is GB-PVR up to?! Is this a normal process of GB-PVR or some hiccup between MSDE and GB-PVR?

-Charles

[cginzel]

Just a note here... I added a primary key on oid to the PROGRAMME table and that drastically reduced the CPU! PROGRAMME currently has 61,583 rows.

The repeating querys continue, but they finished much quicker now... I'd still like to know what GB-PVR is doing...

-Charles

[KingArgyle]

The GBPVR recording service approxiametly every 8 or 10 minutes will poll to retrieve the most current Recording Schedule. The methods that it uses poll against both the Programme table and the Recording Schedule table. All OIDs should have a primary key created or at least be indexed, otherwise it will take longer for the database to find the information (as you are noticing).

The public interface for the polling mechanism in the GBPVR plugin API is LoadRecordings from the ScheduleHelper object. The problem here is that for most uses, all you want is a list of currently available recordings, and don't need the added overhead that LoadRecordings introduces.

Another thing that you might want to do is setup a scheduled task to periodaclly delete the old expired programme records that currently don't have the OID being used in the Recording Schedule table. The current GBPVR process that is supposed to do this still doesn't seem to work correctly with SQL Server/MSDE. This alone will help speed up the queries as there won't be as much information to go through.

[cginzel]

Quote[/b] ]Another thing that you might want to do is setup a scheduled task to periodaclly delete the old expired programme records that currently don't have the OID being used in the Recording Schedule table. The current GBPVR process that is supposed to do this still doesn't seem to work correctly with SQL Server/MSDE.

I will do that, Thanks!

-Charles

[capone]

I may be suffering from that one. Where would you add that?

[KingArgyle]

Capone, one way to do this is to use Enterprise Manager, and under the Management folder within SQL Server Agent, create a new scheduled job that will run a T-SQL stored procedure or script that deletes all Programs from the program table that are less than the current date, and aren't being used in the Recording Schedule table. Set this up for a daily or weekly job, and it'll purge the data for you.

[capone]

Ah...ok. I've got SQL installed on my network, but just not on the pvr. I'll connect to from the server and play around.