Data insert into DB

[sowraaj]

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;
}

[mje]

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.

[jfroebe]

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.

[mje]

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.

[jfroebe]

oops! missed the drop. sorry about that

[runrig]

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

YOU HAVE BULK OF DATA USE BULK LOADER. HTH.

[thundergnat]

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}
        );
    };
}

[sundialsvc4]

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.