Comparison of Perl insertion into sqlserver Database

Comparison of Perl database insertion Methods: Python/ruby1. This article compares the three database insertion methods. A) execute $ something-> execute () cyclically, and control $ DBH-> commit (); B) $ something-> bind_param_array () and $ th-> execute_array () c) use bulkinsert provided by sqlserver to compare with direct insert. 2. the Code is as follows: Click (here) to collapse or open use strict; Use warnings; Use dBi; Use Time: hires QW (gettimeofday); main (); sub main {my $ data_source = "DBI: Ado: Driver = {SQL Server}; server = localhost; database = nepmdb ;"; my $ DBH = DBI-> connect ($ data_source, 'nepuser', '1q2w3e4r % t'); $ DBH-> {autocommit} = 0; Insertdata1 ("record", "recorddata.txt", $ DBH); $ DBH-> disconnect ();} sub insertdata1 {local $ | = 1; my ($ table, $ file, $ DBH) = @ _; open in, $ file or die "cannot open file"; my @ A = <in>; close in; my $ something = $ DBH-> prepare ("insert into $ table values (?, ?, ?, ?, ?, ?, ?, ?) ") Or die $ DBH-> errstr; my ($ start_sec, $ start_microsec) = gettimeofday (); my $ I; foreach (@ A) {My ($ Sid, $ WID, $ ETA, $ ISC, $ VOC, $ ff, $ RS, $ rsh) = Split/,/; $ something-> execute ($ Sid, $ WID, $ ETA, $ ISC, $ VOC, $ ff, $ RS, $ rsh); $ I ++; $ DBH-> commit () if $ I % 500 = 0 ;}$ DBH-> commit (); $ something-> finish (); my ($ end_sec, $ end_microsec) = gettimeofday (); my $ timespan = ($ end_microsec-$ start_microsec)/1000 + ($ en D_sec-$ start_sec) * 1000; print "\ t $ timespan \ t"; $ timespan;} sub insertdata2 {local $ | = 1; my ($ table, $ file, $ DBH) = @ _; open in, $ file or die "cannot open file"; my @ A = <in>; close in; my $ something = $ DBH-> prepare ("insert into $ table values (?, ?, ?, ?, ?, ?, ?, ?) ") Or die $ DBH-> errstr; my ($ start_sec, $ start_microsec) = gettimeofday (); my $ I = 0; my (@ Sid, @ WID, @ ETA, @ ISC, @ VOC, @ ff, @ Rs, @ rsh); foreach (@ A) {My ($ _ SID, $ _ WID, $ _ ETA, $ _ ISC, $ _ VOC, $ _ ff, $ _ rs, $ _ rsh) = Split/,/; push @ Sid, $ _ SID; push @ WID, $ _ WID; push @ ETA, $ _ ETA; push @ ISC, $ _ ISC; push @ VOC, $ _ VOC; push @ ff, $ _ ff; push @ Rs, $ _ rs; push @ RSH, $ _ RSH;} $ something-> bind_param_array (1, \ @ Sid); $ something-> bind_param_array (2, \ @ WID ); $……> bind_param_array (3, \ @ ETA); $……> bind_param_array (4, \ @ ISC); $……> bind_param_array (5, \ @ VOC ); $ ......> bind_param_array (6, \ @ ff); $……> bind_param_array (7, \ @ Rs); $……> bind_param_array (8, \ @ rsh ); $ things-> execute_array ({arraytuplestatus => \ My @ tuple_status}); $ DBH-> commit (); $ things-> finish (); my ($ end_sec, $ end_microsec) = gettimeofday (); my $ timespan = ($ end_microsec-$ start_microsec)/1000 + ($ end_sec-$ start_sec) * 1000; print "\ t $ timespan \ t"; $ timespan;} sub bulkinsert {local $ | = 1; my ($ table, $ file, $ DBH) = @_; open in, $ file or die "cannot open file"; my $ SQL = 'truncate Table '. $ table; my $ trunca = $ DBH-> prepare ($ SQL); $ trunca-> execute (); $ trunca-> finish (); my @ A = <in>; close in; my ($ start_sec, $ start_microsec) = gettimeofday (); my $ something = $ DBH-> prepare ("bulk insert $ table from 'C: \ recorddata.csv 'with (fieldterminator =', ', rowterminator =' \ n ') ") or die $ DBH-> errstr; $ something-> execute (); $ DBH-> commit (); $ something-> finish (); my ($ end_sec, $ end_microsec) = gettimeofday (); my $ timespan = ($ end_microsec-$ start_microsec)/1000 + ($ end_sec-$ start_sec) * 1000; print "\ t $ timespan \ t"; $ timespan;} 2. result: When 100,000 pieces of data are compared, insertdata1: 156.930sinsertdata2: 157.624 sbulkinsert: 44.018 S.