Beefy Boxes and Bandwidth Generously Provided by pair Networks
"be consistent"

Re: Data insert into DB

by jfroebe (Parson)
on Aug 10, 2011 at 14:42 UTC ( #919671=note: print w/replies, xml ) Need Help??

in reply to Data insert into DB

Well, this is really more of a SQL Server / T-SQL question than a Perl question. From the example data you provided and the amount of time it takes, it appears that you either use referential integrity (primary/foreign keys) and/or triggers for a set of tables...

  1. Your file looks to be in a delimited format. Why not just use native bcp?
  2. How long does it take for the inserts to run when using isql?
  3. You're inserting one row at a time. One row per transaction. Is there a reason why you can't insert say 1,000 rows at a time before your execute()?
  4. Instead of using single quotes for adding your data, use $dbh->quote($string) as there is no guarantee that your data file won't have embedded quotes that could mess up the insert. You may want to use $query = sprintf "INSERT INTO SOWND1 values(%s)", $dbh->quote($data[$j]);  $dbh->do($query);

Don't be too quick to blame Perl here.

Jason L. Froebe

Blog, Tech Blog

Replies are listed 'Best First'.
Re^2: Data insert into DB
by mje (Curate) on Aug 10, 2011 at 15:21 UTC

    Not that I am disagreeing with what you've said jfroebe but the code provided dropped the table and recreated it without triggers or any keys.

      oops! missed the drop. sorry about that

      Jason L. Froebe

      Blog, Tech Blog

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://919671]
NodeReaper updates his toe tag collection database

How do I use this? | Other CB clients
Other Users?
Others perusing the Monastery: (4)
As of 2018-06-25 04:46 GMT
Find Nodes?
    Voting Booth?
    Should cpanminus be part of the standard Perl release?

    Results (126 votes). Check out past polls.