Beefy Boxes and Bandwidth Generously Provided by pair Networks
Just another Perl shrine

Re: Database input speed question

by jjhorner (Hermit)
on Jul 31, 2003 at 19:34 UTC ( #279745=note: print w/replies, xml ) Need Help??

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 

Log In?

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: note [id://279745]
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others rifling through the Monastery: (4)
As of 2023-06-06 10:14 GMT
Find Nodes?
    Voting Booth?
    How often do you go to conferences?

    Results (26 votes). Check out past polls.