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 <[email protected]> wrote:
>
> "Alok Singh" <[email protected]> schrieb
> im Newsbeitrag
> news:[email protected]...
>
> > 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
> [email protected]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
--
Thanks
Alok Kumar Singh
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
