Hi Simon, Olaf , Garry, Great news to you all,now i can able to insert 65k docs in second, only i had done as per Simon and Garry suggestion 1. i freed the array as per suggestion 2. remove 'cmd.Parameters.Add(myparam) as per Garry suggestion.
*here are summary code :inserting 65k in 4-5 sec* * * //Reading text file 1 in yarray2= filetext.Split(Environment.NewLine) //Reading text file 1 in yarray1 = filetext1.Split(Environment.NewLine) insert: Dim tx = cons.BeginTransaction() For y = j To x Dim strval strval = Replace(yarray(y) + vbTab + yarray1(y), "'", "''") strval = Replace(strval, vbTab, "','") myparam.Value = strval cmd.CommandText = "INSERT into " & ticket & " VALUES('" & strval & "')" //////////' REMOVED cmd.Parameters.Add(myparam) cmd.ExecuteNonQuery() Next tx.Commit() Array.Clear(yarray, 0, x) ' // FREE ARRAY 1 Array.Clear(yarray1, 0, x) ' // FREE ARRAY 1 tx.Dispose() j = x + 1 x = x + 10000 If x < Frow1 Then GoTo insert Else tx = cons.BeginTransaction() Dim m As Integer m = x - 10000 + 1 For y = m To Frow1 Dim strval strval = Replace(yarray(y) + vbTab + yarray1(y), "'", "''") strval = Replace(strval, vbTab, "','") myparam.Value = strval cmd.CommandText = "INSERT into " & ticket & " VALUES('" & strval & "')" 'cmd.Parameters.Add(myparam) cmd.ExecuteNonQuery() Next tx.Commit() End If Thanks you again guys, many thanks... Regards, Alok On 6 January 2011 21:21, Olaf Schmidt <s...@online.de> wrote: > > "Alok Singh" <aaloksing...@gmail.com> schrieb > im Newsbeitrag > news:aanlktikhcyfsuybpjtv=+cd4asrddt-9+f7qx_qpq...@mail.gmail.com... > > > yeah that's correct Simon, its in 0.6 sec to insert > > for 10.5K rows with 20 columns (2 files both > > having 10.5k rows) > > That's the timing I would expect, if you'd have used > Garrys recommendation (to read in the whole file > into a String first, and then split the string into an > Array "InMemory", finally followed by an Insert- > Transaction, which makes use of this 2D-Array). > > That's memory-intensive - but "Ok" (and fast) for Testfiles > with that RowCount (filesize of your 10.5K-Rows > testfiles around 4-6MB I'd guess). > > Are you sure, that your replies address the person > you have in mind ... your previous reply was going to > Garry - and your last reply here was going to me, > and "both of us" are not Simon (who is a very helpful > person on this list, no doubt about that... :-). > > Olaf > > > > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Thanks Alok Kumar Singh _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users