Beefy Boxes and Bandwidth Generously Provided by pair Networks
Perl: the Markov chain saw
 
PerlMonks  

Data insert into DB

by sowraaj (Novice)
on Aug 10, 2011 at 14:08 UTC ( #919662=perlquestion: print w/ replies, xml ) Need Help??
sowraaj has asked for the wisdom of the Perl Monks concerning the following question:

I NEED TO INSERT DATA INTO SQLSERVER PLS HELP ME TO MAKE EFFICIENT MY PROGRAM

NOw I'M CURRENTLY USE WINDOWS SQL2008R2, AND WINDOWS2003

I HAVE BULK OF DATA LIKE THIS,I NEED TO INSERT INTO SQLSERVER

CURRENTLY I TOOK 50min FOR 4250 ROWS

I WOULD LIKE TO REDUCE THE TIME NEARLY A MINUTE

PLS HELP ME TO REDUCE THE EXECUTION TIME

======== SCHOOL (B): MALE ========= SCHOOL_NAME :ABCDEFGH NAME_OF_STUDENT :RAJ ID_NUMBER :9632587410 ADDRESS :23,7TH STREET PINCODE :03214 MOBILE_NUMBER :4563210789 LAND_LINE :0123859674 FATHER'S_NAME :SHIVA MOTHER'S_NAME :JAYA ANNUAL_INCOME :520000 CHARACTER :GOOD ======== SCHOOL (G): GIRLS ========= SCHOOL_NAME :QWERTY NAME_OF_STUDENT :FATEMAH ID_NUMBER :10234567890 ADDRESS :635,9TH STREET PINCODE :63520 MOBILE_NUMBER :9632105478 FATHER'S_NAME :NHOORE MOTHER'S_NAME :JAVATHI ANNUAL_INCOME :420000 CHARACTER :GOOD #PLS NOTE THAT THE LAND_LINE NUMBER OF GIRLS SCHOOL IS MISSING #!/usr/bin/perl use strict; use warnings; use DBI; my $k=0; my @event; my @data; my $a="BOYS"; my $b="GIRLS"; my $dbh = DBI-> connect('dbi:ODBC:SQLServer','new', undef, {PrintError + => 0, RaiseError =>0}); my $sth1 =$dbh->prepare("DROP TABLE SOWND1"); $sth1->execute(); my $sth =$dbh->prepare("CREATE TABLE SOWND1(TYPE varchar(300),SCHOOL_N +AME varchar(300), NAME_OF_STUDENT varchar(300),ID_NUMBER varchar(300),ADDRESS varchar(30 +0),PINCODE varchar(300), MOBILE_NUMBER varchar(300),LAND_LINE varchar(300),FATHER'S_NAME varcha +r(300),MOTHER'S_NAME varchar(300), ANNUAL_INCOME varchar(300),CHARACTER varchar(300))"); $sth->execute(); my $lines; my @files = <C:/strawberry/perl/bin/SCHOOL/STUDENTS/*>; foreach $filee (@files) { open(FILE,$filee); foreach $lines (<FILE>) { chomp ($lines); $lines =~ tr/\n//; $lines=~ s/^\======== SCHOOL \(B\): MALE =========/Type : BOYS/ig; $lines=~ s/^\======== SCHOOL \(G\): FEMALE =========/Type : GIRLS/ig; ($event[$k], $data[$k]) = split /\s*:\s*/,$lines; ++$k; } my $j=0; while($j<=$#event) { if($data[$j] eq $a) { my $sth2 = $dbh->prepare("INSERT INTO SOWND1 values('$data[$j]','$data +[$j+1]','$data[$j+2]', '$data[$j+3]','$data[$j+4]','$data[$j+5]','$data[$j+6]','$data[$j+7]', '$data[$j+8]','$data[$j+9]','NE','$data[$j+10]')"); $sth2->execute(); $j=$j+10; } if ($data[$j] eq $b) { my $sth2 = $dbh->prepare("INSERT INTO SOWND1 values('$data[$j]','$data +[$j+1]','$data[$j+2]', '$data[$j+3]','$data[$j+4]','$data[$j+5]','$data[$j+6]','$data[$j+7]', +'$data[$j+8]', '$data[$j+9]','$data[$j+10]','$data[$j+11]')"); $sth2->execute(); $j=$j+11; } ++$j; } $#event=-1; $#data=-1; }

Comment on Data insert into DB
Download Code
Re: Data insert into DB
by mje (Deacon) on Aug 10, 2011 at 14:33 UTC

    There are a number of ways of speeding this script up and I'm sure most if not all of them will be in Perl Monks replies in past postings. As you stand you can prepare those statements once and just use execute with parameters in your loop. You can disable AutoCommit and commit at the end. You might find reading multiple lines and using execute_array a little faster but I doubt much with DBD::ODBC as it does not have its own execute_array. You can use MS SQL Server bulk insert command instead. There are probably a load of other things you can do.

Re: Data insert into DB
by jfroebe (Vicar) on Aug 10, 2011 at 14:42 UTC

    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

      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

Re: Data insert into DB
by runrig (Abbot) on Aug 10, 2011 at 16:03 UTC
    I HAVE BULK OF DATA LIKE THIS,I NEED TO INSERT INTO SQLSERVER

    YOU HAVE BULK OF DATA USE BULK LOADER. HTH.

Re: Data insert into DB
by sundialsvc4 (Monsignor) on Aug 10, 2011 at 16:37 UTC

    When you say fifty minutes for a mere 4,200 rows, then my first assumption would be that the script (which I have not inspected closely...) simply has a bug in it.

    When a database has “incredibly bad performance,” especially on inserts, updates and deletes, then you can pretty much hang your hat on the assumption that the system thinks that it has to commit to disk and verify every single disk-I/O operation that it is doing.   (SQLite, for example, will do this unless told otherwise.)   The trick, then, is to make sensible use of transactions.   When a transaction is in progress, the DB knows that it can “lazy write.”

    Nevertheless, I agree with the earlier post:   this looks merely like a job for “a bulk loader,” and there’s a plenty of those.   I would be profoundly surprised if you actually had to “write a program” to do this at all.

Re: Data insert into DB
by thundergnat (Deacon) on Aug 10, 2011 at 20:39 UTC

    I call bullshit. That script won't even compile. If you are using a script to insert the data, it isn't that one. Or possibly you stuck a use strict; and use warnings; at the top and hoped we wouldn't notice what a godawful pile of crap it is.

    You have undeclared variables. You are redefining the global $a and $b variables. (Probably not hurting you here but a bad idea in general.) You aren't doing any error checking. Not only are you not enabling error checking, you are actively disabling it. You are looking for a line that contains:

    ======== SCHOOL (G): FEMALE =========

    when your example data uses

    ======== SCHOOL (G): GIRLS =========

    I highly doubt that SQL Server allows column names with apostrophes in them, and even if it does, using them seems like a stupendously bad idea. You should do some kind of $dbh quoting to to avoid weird data errors, or better yet use placeholders in your prepare statement (which will do quoting automatically). Don't prepare a statement for every record, prepare it once then execute it for each record. Don't set yourself up for failure, format your code so it is readable. Use some indenting so you can tell where blocks start and end. Use descriptive variable names. In the words of MJD, "You can't just make shit up and expect the computer to know what you mean."

    Oh what the heck... Do something like this.

    #!/usr/bin/perl use strict; use warnings; use DBI; my $table = 'SOWND1'; my $dbh = DBI->connect( 'dbi:ODBC:SQLServer', 'new', undef, { PrintError => 0, + RaiseError => 1 } ); my $sth = $dbh->prepare("DROP TABLE IF EXISTS '$table'"); $sth->execute; $sth = $dbh->prepare( "CREATE TABLE $table( TYPE varchar(300), SCHOOL_NAME varchar(300), NAME_OF_STUDENT varchar(300), ID_NUMBER varchar(300), ADDRESS varchar(300), PINCODE varchar(300), MOBILE_NUMBER varchar(300), LAND_LINE varchar(300), FATHERS_NAME varchar(300), MOTHERS_NAME varchar(300), ANNUAL_INCOME varchar(300), CHARACTER varchar(300) )" ); $sth->execute; $sth = $dbh->prepare("INSERT INTO $table VALUES(?,?,?,?,?,?,?,?,?,?,?, +?)"); my @files = <C:/strawberry/perl/bin/SCHOOL/STUDENTS/*>; die "No files found\n" unless scalar @files; for my $infile (@files) { local $/ = ''; open my $fh, '<', $infile or die "$!\n"; while (my $record = <$fh>) { chomp $record; my %info; $record =~ s/^=+.+ MALE =+/TYPE :BOYS/; $record =~ s/^=+.+ GIRLS =+/TYPE :GIRLS/; map { my ( $key, $value ) = split / :/, $_; $key =~ s/\'//g; $info{$key} = $value; } split /\n/, $record; $info{LAND_LINE} ||= 'NE'; $sth->execute( $info{TYPE}, $info{SCHOOL_NAME}, $info{NAME_OF_STUDENT}, $info{ID_NUMBER}, $info{ADDRESS}, $info{PINCODE}, $info{MOBILE_NUMBER}, $info{LAND_LINE}, $info{FATHERS_NAME}, $info{MOTHERS_NAME}, $info{ANNUAL_INCOME}, $info{CHARACTER} ); }; }

Log In?
Username:
Password:

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

How do I use this? | Other CB clients
Other Users?
Others surveying the Monastery: (13)
As of 2014-08-27 15:35 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    The best computer themed movie is:











    Results (242 votes), past polls