http://qs1969.pair.com?node_id=279745


in reply to Database input speed question

This issue has plagued me all day, but I've come up with a solution that I like:

I use a perl script to sort the data, open(SQL,"|psql $database"), 'print SQL "BEGIN\;\n"' and print each generated SQL statement directly to the pipe called SQL. When the $count % 1000 is 0, I 'print SQL "COMMIT\;\nBEGIN\;\n";'.

I'm getting about 2k entries/second vs the 129 entries/second using the DBI method.

I admit that it isn't pretty, but it gets the job done and quickly. I have a year's worth of data (~600k entries/day) to dump into my WFA web activity database soon. This should make it go quicker.

J. J. Horner 
CISSP,CCNA,CHSS,CHP,blah,blah,blah