SQL CE: Speed improvement?

[Boris Nienke]

Hello,

i've tried to use the SQL CE Database. But it seems to be very slow...

The Table (just for testing purposes):
ID1 int
Word1 NText
ID2 int
Word2 NText

No Index etc.

i have a Textfile with about 92,000 Lines.
Now i read the lines, split them and insert them into the table.
"Insert Into MyTable(ID1, Word1, ID2, Word2) Values (?,?,?,?)"

dbCom.Parameters.Add(new SqlCeParameter("ID1", SqlDbType.Int));
dbCom.Parameters.Add(new SqlCeParameter("Word1", SqlDbType.NText, 200));
dbCom.Parameters.Add(new SqlCeParameter("ID2", SqlDbType.Int));
dbCom.Parameters.Add(new SqlCeParameter("Word2", SqlDbType.NText, 200));
dbCom.Prepare();


then inside the loop i do:

dbCom.Parameters["ID1"].Value = iID1;
dbCom.Parameters["Word1"].Value = slZeile[0];
dbCom.Parameters["ID2"].Value = iID2;
dbCom.Parameters["Word2"].Value = slZeile[1];

i'm able to insert ~1700 Lines in a minute... this means i need nearly a
hour to complete this job.... TOO slow for me.

Any ideas?

Boris

 

[Guest]

BN [Wed, 23 Jul 2003 21:55:57 +0200]:

i've tried to use the SQL CE Database. But it seems to be very slow...
i'm able to insert ~1700 Lines in a minute... this means i need nearly a
hour to complete this job.... TOO slow for me.

You're assuming it inserts in linear time. Not likely (for mssqlce).

Any ideas?

Abandon mssqlce and use something with speed. For example, here's
software that blows the doors off mssqlce (insert rate is about
one million per minute -- that's not a load rate, but insert rate).

- - - -

Updated results to include the PXA255 here

http://40th.com/gt40/bench_gt40_arm.html

and also there -- a little 65 KB app to run (and see for) yourself. The

Performance summary
------------------------------------------------------------------------------
PXA255/400, Insert: 16,000 items per second (insert rate: 960,000 items/minute
Read: 333,000 items per second (read rate: 20+ million items/minu
PXA250/400, Insert: 7,000 items per second (insert rate: 425,000 items/minute
Read: 200,000 items per second (read rate: 12+ million items/minu
SA1110/206, Insert: 3,900 items per second (insert rate: 234,000 items/minute
Read: 185,000 items per second (read rate: 11+ million items/minu

A tad slower on the PXA250 front than last time, but it's a threaded app with
a GUI to take care of.

And very, very simple to master.

 

[John T]

Abandon mssqlce and use something with speed. For example, here's
software that blows the doors off mssqlce (insert rate is about
one million per minute -- that's not a load rate, but insert rate).

And very, very simple to master.

Sounds great. Does it have a .NET CF library?

 

[Mario Enriquez]

You may try codebase for windows CE also. This is a very
fast dbf engine.

They currently have a support for CF through C#.

Regards,
Mario

 

[Boris Nienke]

As far as i know, XML is always read completely to memory... Do you really
think it's a good way to load 92,000 records to memory when opening the
application?

And i need to search for records... similiar to a dictionary - for example:
"Show me all records, where Word1 is starting with 'abo'" ... or "All
records containing 'thi' somewhere in Word1"

For the first case i need an index... this HAS to be fast!

I wonder how other companies working with big record-sets in nearly
realtime? I've seen dictionaries (TrueTerm, SlovoEd, ...) - when you type a
letter - whoosh - it's there!

Boris

 

[C. Vollmer]

Steve,

thanks for the good overview. I had the same problems with IBM. I'm not
surprised that many people use SQLCE. You get it with VS and there is a
lot of code by now. But I'm also not surprised that many people don't
like SQLCE when they start developing bigger apps with more complex
conditions. You can't fullfill all the things the customers want. IBM,
Oracle and others could get a lot more success if they would start
supporting us with more information, source and evaluation versions. But
maybe they will start when they finished their betas.
Thanks again,
Christoph