Help on selecting and deleting many records in a DB.by neilwatson (Curate)
|on Apr 05, 2014 at 15:52 UTC||Need Help??|
neilwatson has asked for the
wisdom of the Perl Monks concerning the following question:
I'm working on a database application with Perl and Postgresql, using DBI and Mojolicous. The main DB table is very long and I'm trying to come up with a way to reduce the data. At first I tried a pure SQL approach, but the results were disappointingly slow. Now I'm considering a Perl approach. If you have the patience please read on.
Each line in the table is a log. There will be many rows that are the same except for the timestamp. As the data ages, I want to cull the rows. I want to group records that have the same combination of class, ip_address, and hostname, and keep the highest timestamp for each day from each group.
I tried aggregating the data with
I wrapped that in a query that didn't quite work, but was essentially DELETE * FROM agent_log WHERE NOT IN... It was painfully slow.Now I'm wondering if I can do this with Perl instead. Using cursor I could fetch rows, group them, and delete the lower date ones. Is it likely that using Perl in anyway could be faster than raw SQL? What would you do?