Beefy Boxes and Bandwidth Generously Provided by pair Networks
Think about Loose Coupling
 
PerlMonks  

Re: Data insert into DB

by jfroebe (Vicar)
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


Comment on Re: Data insert into DB
Download Code
Re^2: Data insert into DB
by mje (Deacon) 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?
Username:
Password:

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

How do I use this? | Other CB clients
Other Users?
Others contemplating the Monastery: (6)
As of 2014-09-02 06:08 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    My favorite cookbook is:










    Results (20 votes), past polls