Issuing VACUUM on npvr's database?

[Spark]

I was just curious, have other people tried to vacuum their NPVR database npvr.db3?

The database was compacted using VACUUM statement.
Before compacting:
Page Count = 39045
Database Size = 39982080 bytes
After compacting:
Page Count = 532
Database Size = 544768 bytes

Nice cleanup.

[NYPlayer]

This is how I do it ... it checks to see if something is recording. If not shuts down service Vacuums and restarts service. It keeps checking every 5 minutes... once it is vacuumed it exits.

Code:

CD /d "%~dp0"
:loop
set chknpvr=1
"c:\Program Files (x86)\NPVR\NScriptHelper.exe" -isInUse | find "NOT RECORDING"
set chknpvr=%ERRORLEVEL%
if %chknpvr% EQU 0 net stop "NPVR Recording Service"
if %chknpvr% EQU 0 sqlite3.exe "C:\Users\Public\NPVR\npvr.db3" "VACUUM;"
if %chknpvr% EQU 0 net start "NPVR Recording Service"
if %chknpvr% NEQ 0 timeout 300
if %chknpvr% NEQ 0 goto loop
exit

[Spark]

This is how I do it ... it checks to see if something is recording. If not shuts down service Vacuums and restarts service. It keeps checking every 5 minutes.

Code:

:loop
set chknpvr=1
"c:\Program Files (x86)\NPVR\NScriptHelper.exe" -isInUse | find "NOT RECORDING"
set chknpvr=%ERRORLEVEL%
if %chknpvr% EQU 0 net stop "NPVR Recording Service"
if %chknpvr% EQU 0 sqlite3.exe "C:\Users\Public\NPVR\npvr.db3" "VACUUM;"
if %chknpvr% EQU 0 net start "NPVR Recording Service"
if %chknpvr% NEQ 0 timeout 300
if %chknpvr% NEQ 0 goto loop
exit

Hey, that pretty slick... didn't think about adding it to a script to do it, nor did I know that there was a way to query npvr to see if it was doing anything or not.
Thanks!

[Lao Pan]

I was just curious, have other people tried to vacuum their NPVR database npvr.db3?

- - -

Nice cleanup.

I was under the impression that Nrecord did this - either on start or stop of the service (I can't remember which). Although if you never shut down the computer or restart the recording service, it wouldn't have a chance.

I used to vacuum the db back in GBPVR days.

[johnsonx42]

I just tried it, no change in the npvr.db3 size, still 9.5MB.

[sub]

I was under the impression that Nrecord did this - either on start or stop of the service (I can't remember which).

We used to do something like this when using an MS Access with GBPVR. Access database files would grow at a phenomenal and uncontrollable rate. I've never found the need to do this with a SQLite databases. These databases are pretty good at reclaiming and reusing any free space.

[sub]

I was just curious, have other people tried to vacuum their NPVR database npvr.db3?

The database was compacted using VACUUM statement.
Before compacting:
Page Count = 39045
Database Size = 39982080 bytes
After compacting:
Page Count = 532
Database Size = 544768 bytes

Nice cleanup.

Honestly, I don't think those results are what you'd get in the real world. A lot of US users have nearly 500,000 listings, and will often have a database between 30-50MB, and that's what I'd consider normal. A database with 500,000 listings isn't going to shrink down to about 500,000 bytes. ie one byte per listing.

You'd possibly see results like this if you have a full listing database, then did an Empty EPG, then vacuumed it. As soon as you update the EPG, pulling in the listings again, then it's likely to be up to the same size again.

[mvallevand]

When I empty and vacuum, I see a similar reduction but then it grows again with the next epg update. I don't see this as worth the effort, unless you want to compress a file for uploading to the forum.

Martin

[Spark]

Honestly, I don't think those results are what you'd get in the real world. A lot of US users have nearly 500,000 listings, and will often have a database between 30-50MB, and that's what I'd consider normal. A database with 500,000 listings isn't going to shrink down to about 500,000 bytes. ie one byte per listing.

You'd possibly see results like this if you have a full listing database, then did an Empty EPG, then vacuumed it. As soon as you update the EPG, pulling in the listings again, then it's likely to be up to the same size again.

Actually, I did the vacuum a few days after I did a EPG update, I didn't empty anything.
I just happen to be browsing what the heck is writing to my SSD drive, and saw the NPVR database being listed... then I got curious, and ran the vacuum on it.

When I empty and vacuum, I see a similar reduction but then it grows again with the next epg update. I don't see this as worth the effort, unless you want to compress a file for uploading to the forum.

Yeah, doesn't seems like the database gets big enough to worry about it, though, you can still save some bytes, but, performance doesn't seem to be affected either way, at least not at the current size.