Beefy Boxes and Bandwidth Generously Provided by pair Networks
No such thing as a small change
 
PerlMonks  

How many record insert into SqlServer 2K per second?

by pysome (Scribe)
on Aug 30, 2007 at 03:31 UTC ( #635978=perlquestion: print w/replies, xml ) Need Help??

pysome has asked for the wisdom of the Perl Monks concerning the following question:

Hey ,Monks
I am using DBI::Sybase and FreeTDS to connect SqlServer 2K.
Now i need to insert into some data into db's table.
To speed,i delete the index in that table.Then i start inserting and calculating the rate of insertion as following.
it spent 35 minutes inserting about 310,000 items.That is,9000 /m or 150/s or so.
How about its speed on your experience?

Thanks
-Pysome

  • Comment on How many record insert into SqlServer 2K per second?

Replies are listed 'Best First'.
Re: How many record insert into SqlServer 2K per second?
by andreas1234567 (Vicar) on Aug 30, 2007 at 06:11 UTC
    pysome,

    You will have to provide more information if you want a relevant response here at perlmonks. The speed of your database inserts generally depends on:

    • Your hardware configuration and environment.
    • Your disk system(s).
    • Your database engine's configuration.
    • The structure of your database instance (definition of tables, indices, and objects).
    • The nature of the data you are inserting.
    • The client script you use to access your database.

    Without knowing any of the above there is no Monk in the world that can answer you.

    --
    Andreas
      Thank you. I add more information:

          * Your hardware configuration and environment.
          	My client host :192.168.11.12 ,and the SqlServer DB server:192.168.11.16 .They located at some place. 
          
          * Your disk system(s).
            My client host HD:300G. 
          
          * Your database engine's configuration.
          	DB is charged by our DBA.I really don't know what's config you means
          	
          * The structure of your database instance (definition of tables, indices, and objects).
          	Table Definition:
          	test_c (ID int not null,cust_id int not null,status char(1) null,money decimal(12,2) null)
            no indices 	
          
          * The nature of the data you are inserting.
            My data is from my client host's sqlite DB  
        
          
          * The client script you use to access your database. 
          
      My Code :
      #!/usr/bin/perl use strict; use warnings; use DBI; my $mydbh = DBI->connect( "dbi:SQLite:/root/loc.db","","",{AutoCo +mmit => 0} ) or die "Error $DBI::errstr \n"; my $dbh = DBI->connect( "dbi:Sybase:server=192.168.11.16;databa +se=customer", "xxx", "xxx", {AutoCommit =>1}) or die $DBI::errstr; insert_it(); $mydbh->disconnect; $dbh->disconnect; exit; ########################## sub insert_it() { my $r = get_data(); for (keys %$f) { my $mo = $r->{$_}->{money}; my $status= $r->{$_}->{status}; $dbh->do(qq{insert into test_c(custid,money,status) values + ($_,'$mo','$status')}) or die $DBI::errstr; } } sub get_data { my %data; my $sth = $mydbh->prepare(qq{select custid,money,status from tem +p_test order by custid}); my ($cid,$mon,$sta); $sth->execute() or die $DBI::errstr; $sth->bind_columns(undef, \$cid, \$mon, \$sta); while ( $sth->fetch() ) { $data{$cid} = {money => $bal, status => $sta}; } return \%data; }
      I have tried the placeholder method,but the sqlserver 2K doesn't support it.

Log In?
Username:
Password:

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

How do I use this? | Other CB clients
Other Users?
Others musing on the Monastery: (4)
As of 2023-06-07 12:11 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?
    How often do you go to conferences?






    Results (29 votes). Check out past polls.

    Notices?