Re: Improving SQL speed

Your title and question suggested to me a need for appropriate table indexing to improve database performance. But your code shows only a single INSERT on an unindexed table. An index slows down table INSERTs (but is often justifiable because of the needs of future queries on the data set). It is also faster than DROPping and rebuilding those indexes every time you do an INSERT, except for bulk INSERTs when that strategy can make sense.

If you are having some performance issues with this code, I doubt it is in the database interactions.

I'd suggest using Devel::NYTProf to start to get a handle on where the resources are being used. I'd guess that parsing your mp3 library is not a trivial task.

-- Hugh

if( $lal && $lol ) { $life++; }
if( $insurance->rationing() ) { $people->die(); }

Comment on Re: Improving SQL speed

Replies are listed 'Best First'.
Re^2: Improving SQL speed by Mad_Mac (Beadle) on Nov 02, 2009 at 19:02 UTC
Thanks. I'll look into that module. I'm not having performance issues per se, but the code seems to parse ~15-20 songs per second, which is ~20 minutes for the 19k file in my mp3 library. Not terrible, but I have a feeling it can be faster. I don't notice an CPU or RAM hit while it's running, but I haven't looked closely. Mad_Mac	[reply]
Re^3: Improving SQL speed by CountZero (Bishop) on Nov 03, 2009 at 06:59 UTC
Just to compare speed: I have an MP3 library on network attached storage which I access through WiFi, so it is not terribly fast. It contains 5900 MP3-songs in 500 folders and is 35 GB large. Indexing it with MP3::Find and MP3::Find::DB (using an SQLite database) took 3022 seconds, so it is almost 2 ~~seconds per song~~ songs per second. I am sure if the songs are on a local harddisk, the speed would be higher. The processor never maxed-out, so the bottle-neck is most probably in the network and/or harddisk. Given these circumstances it seems therefore you have indeed some room to improve the speed of your script. Update: fixed typo. CountZero A program should be light and agile, its subroutines connected like a string of pearls. The spirit and intent of the program should be retained throughout. There should be neither too little or too much, neither needless loops nor useless variables, neither lack of structure nor overwhelming rigidity." - The Tao of Programming, 4.1 - Geoffrey James	[reply]