php - Records disappearing in PDO mssql transaction loop -

-

i have following code (more or less) import anywhere 500.000 4.000.000 rows:

$ssql = "insert table (a,b,c) values(?,?,?)" $osqlstmnt = $pdo->prepare($ssql); $osqlstmnt->setattribute(pdo::sqlsrv_attr_encoding, pdo::sqlsrv_encoding_system); if (!$osqlstmnt) {     echo $pdo->errorinfo(); // handle errors } $pdo->begintransaction(); $ilinecounter = 1; while (($sline = fgets ($ocsv, 8000)) !== false) {       $aline = explode('|', $sline); //fgetscsv did not work         if ($ilinecounter % 100 == 0) {             lo("inserting row " . $ilinecounter);             $pdo->commit();             sleep(0.15);             $pdo->begintransaction();        }        try {             $osqlstmnt->execute($aline);             $isuccesulinserts++;        }        catch (exception $e) {             print_r($e);             $ifailedinserts++;        }         $ilinecounter++; } $pdo->commit();

as can see, perform commit every 100 lines, , added sleep. used run commit once every 25.000 lines, , did not use sleep. however, @ 1 point, discovered missing records. started playing these settings (sleep , number of rows). way reduced number of missing records 50.000 100. i'm still missing records! going? know sql ok, because receive errors when somethings wrong there.

i thought stack lot of inserts during transaction? calling begintransaction problem?

update:

the bounty ended , had award it. thank answers. or tips actually, none of answered question. not asking workaround, although suggestions appreciated. answer bounty awarded received because came closest answering question. unfortunately did not work.

for i'm using csv bulk import, works fine, if has other tips fixing issue, please let me know. prefer using original method.

have considered using sprocs instead of insert statements? writing number of records sequentially- 1 @ time- kindof waste of time / energy.. it's not fast should be.

are sure can't use bulk insert or xml instead insert multiple rows @ time?


Comments

Post a Comment

Popular posts from this blog

c# - SVN Error : "svnadmin: E205000: Too many arguments" -

-
i trying reposotries using c# code process svncommand = null; var psi = new processstartinfo("svnadmin"); psi.redirectstandardoutput = true; psi.redirectstandarderror = true; psi.useshellexecute = false; psi.arguments= @"dump c:\repositories\myrepo > c:\temp\myrepodumpfile.dump"; using (svncommand = process.start(psi)) { var myoutput = svncommand.standardoutput; var myerror = svncommand.standarderror; debug.write("output :" + environment.newline + environment.newline + myoutput.readtoend() + environment.newline + "error :" + environment.newline + environment.newline + myerror.readtoend()); svncommand.close(); } when use dump command commandline svnadmin dump c:\repositories\myrepo > c:\temp\myrepodumpfile.dump it works fine when try ...
Read more

c# - Copy ObservableCollection to another ObservableCollection -

-
how copy observablecollection item observablecollection without reference of first collection? here observablecollection item value changes affecting both collections. code private observablecollection<ratemodel> _allmetalrate = new observablecollection<ratemodel>(); private observablecollection<ratemodel> _metalrateondate = new observablecollection<ratemodel>(); public observablecollection<ratemodel> allmetalrate { { return this._allmetalrate; } set { this._allmetalrate = value; notifypropertychanged("metalrate"); } } public observablecollection<ratemodel> metalrateondate { { return this._metalrateondate; } set { this._metalrateondate = value; notifypropertychanged("metalrateondate"); } } foreach (var item in metalrateondate) allmetalrate.add(item); what causing , how can solve it? you need clone object referenced item before it's a...
Read more

All overlapping substrings matching a java regex -

-
is there api method returns (possibly overlapping) substrings match regular expression? for example, have text string: string t = 04/31 412-555-1235; , , have pattern: pattern p = new pattern("\\d\\d+"); matches strings of 2 or more characters. the matches are: 04, 31, 412, 555, 1235. how overlapping matches? i want code return: 04, 31, 41, 412, 12, 55, 555, 55, 12, 123, 1235, 23, 235, 35. theoretically should possible -- there obvious o(n^2) algorithm enumerates , checks substrings against pattern. edit rather enumerating substrings, safer use region(int start, int end) method in matcher . checking pattern against separate, extracted substring might change result of match (e.g. if there non-capturing group or word boundary check @ start/end of pattern). edit 2 actually, it's unclear whether region() expect zero-width matches. specification vague, , experiments yield disappointing results. for example: string line = "xx90xx"; strin...
Read more