Jon & Enrico,
Maybe I can help here as I do have some experience moving large amounts (for
a pda) of drug data. I'm moving in CSV form approx 130,000 records from 19
different tables. The drugs database comes in at 13.2M on the device and
takes 30 minutes to build on my 5450. Approx. 4300rows/minute. The tables
vary considerably re data types and individual table sizes. I haven't
bothered doing any exact time testing on the individual tables, but on say
one of the large tables that only contains 4 int columns, the record counter
on the window really speeds up on this table, as you would expect, as
compared to the products table that has some reasonably large nvarchar
fields and quite a number of foreign keys, where you can observe the record
counter slow down quite considerably. All the tables have PK's, most have a
various FK's and constraints and most of the large tables have multiple
indexes. So the figure of 4300rows/minute could obviously move up on less
complicated data and could even be slower on more complicated data - I think
I could then agree with Jon's claim of 6000rows/minute.
I'm coding it in a similar style to Jon's example. I build the sql statement
with the "?" placeholders as Jon did, add the SqlCeParameters to the command
object and then call Prepare. Jon may have accidently left this out of his
example code snippet. All of the above is outside of the main looping
mechanism of the streamreader, then I just loop as Jon did. I've usually got
to massage the data on the way in before I insert the data in each
Parameter.Value, so I most likely lose a bit of speed at that point too.
I've read somewhere that calling prepare may be unnecessary as
ExecuteNonQuery does it internally, I'm not sure about that and have never
tested without the command.Prepare(). Calling prepare at least does some
more error checking on your command object if you happen to screw up a
parameter. I reckon it could also be made to do more - see my comments on
the ce newsgroup.
I've saved the worst for the last. The big kicker here are the machines -
they really do vary in speed in a big way. I've also borrowed a 2210 and the
same test came in at 25minutes(5200/min). However my colleagues at head
office have a Dell Axim, I think its a 5, not sure. The same test took over
2 hours(1100/min)
I'd love to know why, because the specs of my 5450 and the Dell appear to be
the same.
So I guess I know what test I'm going to run on a machine before I buy a new
one<g>
Regards
Graham
Jon Skeet said:
Enrico Bizzarri said:
I've reached the speed of above 600 records per minute. It's the fastest
solution I've found. Really not "fast" but fastest...
I do:
line = sr.ReadLine()
Do Until line = "[endTable]"
mLine = line.Split(";"c)
sql = "INSERT INTO table "
sql = sql & "("
sql = sql & "field1, field2, field3"
sql = sql & ")VALUES("
sql = sql & "'" & mLine(0) & "', "
sql = sql & "'" & mLine(1) & "', "
sql = sql & "" & mLine(2) & ""
sql = sql & ");"
cmd.CommandText = sql
cmd.ExecuteNonQuery()
line = sr.ReadLine()
Loop
600 records per minute is significantly slower than we're seeing
(although I don't know what device you're using). One of the reasons is
probably that the above isn't able to use the fact that it's the same
basic SQL every time. Change it to (C# code for my own sanity):
cmd.CommandText = "INSERT INTO table (field1,field2,field3) "+
"VALUES (?, ?, ?)";
// Fix this bit up, obviously.
cmd.Parameters.Add ("field1", SqlDbType.VarChar, 20);
cmd.Parameters.Add ("field2", SqlDbType.VarChar, 10);
cmd.Parameters.Add ("field3", SqlDbType.VarChar, 30);
string line;
char[] splitCharacters = ";".ToCharArray();
while ( (line=sr.ReadLine()) != "[endTable]")
{
string[] values = line.Split (splitCharacters);
for (int i=0; i < 3; i++)
{
cmd.Parameters
.Value = values;
}
cmd.ExecuteNonQuery();
}
We use something similar, and on my XDA2 it can insert just under 3000
records in about 25-30 seconds.
) and i'll post the