Beefy Boxes and Bandwidth Generously Provided by pair Networks
Perl-Sensitive Sunglasses

Re: Database input speed question

by blue_cowdawg (Monsignor)
on Jul 31, 2003 at 15:39 UTC ( #279621=note: print w/replies, xml ) Need Help??

in reply to Database input speed question

Short answer #1: it depends

Short answer #2: TIMTOWTDI!

When considering database performance there is a wide range of issues to consider. Some of which you may or may not have any control over if you are in a shop that stovepipes responsibility and you have to deal with a DBA for database layout, a Data Architect for table and index layout and a System Administrator who does the system stuff.

Some of the issues are:

  • How the database is layed out in terms of where the data and indexes are layed out in terms of "spindles."
  • How the tables are indexed and how many indexes are involved
  • IO badnwidth of the database server
  • I'm sure I am forgetting something...

As far as speeding up inserts themselves go I have discovered over the years that using bulk import commands that are supplied by the database manufacturer are usually faster than inserts done via code. So if you have lots of data to insert into a table all at once you could write your data from Perl to a file and use the bulk import feature of choice and import it that way.

An example of where I used this was for a major financial firm that I worked for I used to glean the ARP caches from our network devices from a Perl script that "walked" through the devices discovering neighboring devices along the way. This generated 20,000 ethernet address/IP address pairs (or more) that needed to be stored in a table for later analysis. Using normal inserts against an Oracle database took way too long but doing a bulk import took seconds.

Peter @ Berghold . Net

Sieze the cow! Bite the day!

Test the code? We don't need to test no stinkin' code! All code posted here is as is where is unless otherwise stated.

Brewer of Belgian style Ales

Log In?

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

How do I use this? | Other CB clients
Other Users?
Others wandering the Monastery: (5)
As of 2018-08-19 09:26 GMT
Find Nodes?
    Voting Booth?
    Asked to put a square peg in a round hole, I would:

    Results (186 votes). Check out past polls.