Best Method for SQLCE reading (8000 records)

[news]

I have 8.000 records in my SQL CE DB.
Im using this construciton:

SqlCeConnection sqlConn = new
SqlCeConnection(AppProperties.ConnectionString);
SqlCeCommand sqlCmd = new SqlCeCommand();
try
{
sqlConn.Open();
sqlCmd.CommandText = "SELECT ProductID, ProductCategoryRepID,
ProductCategoryID, ProductGroupID, ProductTypeID, TesterCode, Description,
EAN, ItemsCount, Price, AdvisedPrice, Tax, Status, DeliveryDate,
StoreAmount, SortKey, IsNew FROM tblProducts WHERE ProductCategoryID <>
'H'";// ORDER BY ProductCategoryRepID";
sqlCmd.CommandType = CommandType.Text;
sqlCmd.Connection = sqlConn;
SqlCeDataReader dataReader =
sqlCmd.ExecuteReader(CommandBehavior.CloseConnection);
while(dataReader.Read())
{
object[] vals = new object[17];
dataReader.GetValues(vals);
Product tmpProduct = new Product(vals);
instance.Add(tmpProduct);
}
dataReader.Close();
}

But this construction takes a lot of time (3 mins)
Any idea for FASTER access to the SQLCE DB???

Thanks

Endys

 

[Rick Winscot]

Endys,

Your operation overhead is what is killing the performance. Try a DataSet
and .Fill it... then push it to the db. This works with reads as well as
inserts. The DataSet is a great tool... Desktop habits are hard to break -
seriously... these kinds of operation on the handheld do take some time...
I would suggest reading the chapter on ADO .NET in Microsoft's Compact
Framework reference. It has some very useful examples.

I think that it would be well worth the time to think of a method that would
allow you to load chunks of the data... as the user needs it. If the
operation must be done all at once - be aware that you can only optimize to
a point... after that, you can only provide a status bar and let the user
know where they are at in the process.

Rick Winscot
rickly@zyche dot com

 

[news]

I tried to use DataSet but it takes still a lot of times.

SqlCeDataAdapter da = new SqlCeDataAdapter("SELECT * FROM tblProducts",
AppDatabase.AppSqlConn);
da.Fill(AppDatabase.AppDataSet,"tblProducts");

Endys,

Your operation overhead is what is killing the performance. Try a DataSet
and .Fill it... then push it to the db. This works with reads as well as
inserts. The DataSet is a great tool... Desktop habits are hard to break -
seriously... these kinds of operation on the handheld do take some time...
I would suggest reading the chapter on ADO .NET in Microsoft's Compact
Framework reference. It has some very useful examples.

I think that it would be well worth the time to think of a method that would
allow you to load chunks of the data... as the user needs it. If the
operation must be done all at once - be aware that you can only optimize to
a point... after that, you can only provide a status bar and let the user
know where they are at in the process.

Rick Winscot
rickly@zyche dot com

I have 8.000 records in my SQL CE DB.
Im using this construciton:

SqlCeConnection sqlConn = new
SqlCeConnection(AppProperties.ConnectionString);
SqlCeCommand sqlCmd = new SqlCeCommand();
try
{
sqlConn.Open();
sqlCmd.CommandText = "SELECT ProductID, ProductCategoryRepID,
ProductCategoryID, ProductGroupID, ProductTypeID, TesterCode, Description,
EAN, ItemsCount, Price, AdvisedPrice, Tax, Status, DeliveryDate,
StoreAmount, SortKey, IsNew FROM tblProducts WHERE ProductCategoryID <>
'H'";// ORDER BY ProductCategoryRepID";
sqlCmd.CommandType = CommandType.Text;
sqlCmd.Connection = sqlConn;
SqlCeDataReader dataReader =
sqlCmd.ExecuteReader(CommandBehavior.CloseConnection);
while(dataReader.Read())
{
object[] vals = new object[17];
dataReader.GetValues(vals);
Product tmpProduct = new Product(vals);
instance.Add(tmpProduct);
}
dataReader.Close();
}

But this construction takes a lot of time (3 mins)
Any idea for FASTER access to the SQLCE DB???

Thanks

Endys

 

[Brad Syputa - MS]

Adding Indexes on your key columns may help if you have not already. If
there are 8000 rows returned, it will still take a while, although that does
seem like a long time.

--
Brad
(e-mail address removed)
This posting is provided "AS IS" with no warranties, and confers no rights.

Endys,

Your operation overhead is what is killing the performance. Try a DataSet
and .Fill it... then push it to the db. This works with reads as well as
inserts. The DataSet is a great tool... Desktop habits are hard to break -
seriously... these kinds of operation on the handheld do take some time...
I would suggest reading the chapter on ADO .NET in Microsoft's Compact
Framework reference. It has some very useful examples.

I think that it would be well worth the time to think of a method that would
allow you to load chunks of the data... as the user needs it. If the
operation must be done all at once - be aware that you can only optimize to
a point... after that, you can only provide a status bar and let the user
know where they are at in the process.

Rick Winscot
rickly@zyche dot com

I have 8.000 records in my SQL CE DB.
Im using this construciton:

SqlCeConnection sqlConn = new
SqlCeConnection(AppProperties.ConnectionString);
SqlCeCommand sqlCmd = new SqlCeCommand();
try
{
sqlConn.Open();
sqlCmd.CommandText = "SELECT ProductID, ProductCategoryRepID,
ProductCategoryID, ProductGroupID, ProductTypeID, TesterCode, Description,
EAN, ItemsCount, Price, AdvisedPrice, Tax, Status, DeliveryDate,
StoreAmount, SortKey, IsNew FROM tblProducts WHERE ProductCategoryID <>
'H'";// ORDER BY ProductCategoryRepID";
sqlCmd.CommandType = CommandType.Text;
sqlCmd.Connection = sqlConn;
SqlCeDataReader dataReader =
sqlCmd.ExecuteReader(CommandBehavior.CloseConnection);
while(dataReader.Read())
{
object[] vals = new object[17];
dataReader.GetValues(vals);
Product tmpProduct = new Product(vals);
instance.Add(tmpProduct);
}
dataReader.Close();
}

But this construction takes a lot of time (3 mins)
Any idea for FASTER access to the SQLCE DB???

Thanks

Endys

 

[news]

But all people talking about 20.000 records in 30 secs..... it is 'a
while'... not 8.000 recs in 3 mins....
I have indexes, Im using dataset now, but the speed is still terrible....

Adding Indexes on your key columns may help if you have not already. If
there are 8000 rows returned, it will still take a while, although that does
seem like a long time.

--
Brad
(e-mail address removed)
This posting is provided "AS IS" with no warranties, and confers no rights.

Endys,

Your operation overhead is what is killing the performance. Try a DataSet
and .Fill it... then push it to the db. This works with reads as well as
inserts. The DataSet is a great tool... Desktop habits are hard to break -
seriously... these kinds of operation on the handheld do take some time...
I would suggest reading the chapter on ADO .NET in Microsoft's Compact
Framework reference. It has some very useful examples.

I think that it would be well worth the time to think of a method that would
allow you to load chunks of the data... as the user needs it. If the
operation must be done all at once - be aware that you can only optimize to
a point... after that, you can only provide a status bar and let the user
know where they are at in the process.

Rick Winscot
rickly@zyche dot com

I have 8.000 records in my SQL CE DB.
Im using this construciton:

SqlCeConnection sqlConn = new
SqlCeConnection(AppProperties.ConnectionString);
SqlCeCommand sqlCmd = new SqlCeCommand();
try
{
sqlConn.Open();
sqlCmd.CommandText = "SELECT ProductID, ProductCategoryRepID,
ProductCategoryID, ProductGroupID, ProductTypeID, TesterCode, Description,
EAN, ItemsCount, Price, AdvisedPrice, Tax, Status, DeliveryDate,
StoreAmount, SortKey, IsNew FROM tblProducts WHERE ProductCategoryID

'H'";// ORDER BY ProductCategoryRepID";
sqlCmd.CommandType = CommandType.Text;
sqlCmd.Connection = sqlConn;
SqlCeDataReader dataReader =
sqlCmd.ExecuteReader(CommandBehavior.CloseConnection);
while(dataReader.Read())
{
object[] vals = new object[17];
dataReader.GetValues(vals);
Product tmpProduct = new Product(vals);
instance.Add(tmpProduct);
}
dataReader.Close();
}

But this construction takes a lot of time (3 mins)
Any idea for FASTER access to the SQLCE DB???

Thanks

Endys

 

[Boris Nienke]

this application creates 2000 records in the database but the creating
time is very long (more than 2 minutes... damned!) ;

i've seen the same affect: insert-speed is _very_ low with SQLCE!

questions:
- is this execution time for the "create table" statement normal (2
minutes...)?

Create Table should be fast.
What consumes the time are the 2000 insert statements. I've tried prepared
statements too - but it is very slow too (i needed to insert (import) about
50000 or 150000 records ... forget it! )

- is this execution time for the "select" from sql server ce 2.0
interface normal (20 seconds in craddle, 30 outside craddle) ?

i think so

- what are the solution, if any, for these problems ?

i'm looking for one to! I don't need SQLCE (to much overhead for installing
too) - i just need a fast database solution for WindowsCE which i can use
with C#

- isn't ms access cdb data format faster ?

i think so - i can't test it with the device because the only wrapper (from
InTheHand.com) is running on the emulator only (in the trial version)

And the cdb is just a solution for tables with - say - 15000 records as a
maximum. After that it is going really slow too (even if you have indexes)

- why haven't microsoft implemented access data format ???

because they like to sell the SQL-Server too? ;-)
I think it could be a good "bundle" if you plan to sync the data with the
Desktop/Server (replication) ... but for simple apps it is really a lot of
overhead - and very slow

cu

Boris

 

[Boris Nienke]

SQL CE works fine, but you have to be prepared to wait when you run insert

100000 times... ;-)
It works, but it will take 8-12hours...

yes - that is not acceptable ;-)

There a some facts that won't let me use SQL CE:
1.) i cannot create a database (DB) for the PPC on my desktop machine
(where things would be much faster). This wouldn't just nice for me (as
developer) but also for the clients - so they could export some data to a
small DB and copy it to the PocketPC... not possible with SQL CE! (the
clients won't buy SQL-Server and replication would to a complex step for my
solutions)

2.) i need the clients to install a big package to the PocketPC. I know SQL
CE is a very complete system with lots of features... but i only need a
very simple way to hold some lines of data - take a simple Word-List (like
a dictionary): you have 150000 lines, every line has 2 Strings and 2
integers (maybe) and two indexes. that's it. Or take a simple order-system.
2 or 3 Tables, few fields, ready. SQL CE is definetly to much overhead for
me


So what would be really great:
- a simple and fast database solution which can be used with .net (C#
preferred)
- fast inserts and reads
- be able to create the database on the desktop-system and simply copy it
to the device to use it

- something like DBase or Paradox would be enough. May something like
BTrieve (OK, no SQL but fast!) would be enough.

Think about a client-user who receives a mail with it's pocketPC. Attached
is a text- or XML-File with new informations. No he likes to import it...
but he won't wait minutes or hours... 2-4 seconds for 1000 lines would be
OK (since an update won't be that large)

Boris

 

[Joakim Rylander]

Hi,

What are you trying to do?
What search capabilities do you need?
Is it only 8000 rows at most? or only 1000?

Have you tried to use a simple textfile?
Is MDB/CDB sync also too slow for 8000 rows?
How often are the user syncing 8000 rows?

8000 rows should be quite big anyway, 500kB - 1MB.
That will take a minute or so to copy the file with ActiveSync, forget copy
500kB in a few seconds.

I assume the PDA user only reads the 8000 rows, not adding, deleting
changing them.
If they do you only have to add some extra columns for the changes and send
back those with your C# code. The 8000 lines doesn't have to move back to
the server.

The standard RDA Push/Pull is probably the best for you after you have
downloaded the CDB or SDF once, and only need to sync the changes.
But you are right, the SQL CE installation is a overhead if you only have a
few thousand lines, then CDB is better, CSV or XML could also work if your
requirements are simple.

My example of 100000 rows was 20MB large, that is heavy for the PDA to
process, but again I used eVB to run it, C# or eVC might do it better.
My example was for a database which only change once per year, so 8 hour
onetime job for me is nothing. The user only read the finished database. I
would never let a user load a database with 100000 rows like that, that is
not acceptable ;-)
I send them a 25MB ready SQL CE SDF file on a memory card. Btw, the indexed
SDF is not bloated, the ASCII CSV itself is 20MB.
Now I have 400000 rows and a 65MB SDF, and it's still as fast as before.
Very good!

You don't need SQL Server, only MSDE. You can run insert or update on the
PDA in C#, and 1000 lines should be quite ok to update from a CSV, try it,
what is the performance?

If you find a quick solution for database/text/xml data, even without SQL
querying I would like to know. I have tested Sybase and Oracle, but they
have same issues as SQLCE, they are for larger projects.

After learning the tricks and limitations with SQL CE and CDB I'm happy with
it, it's fast and simple but you have to select the right tool for your
task.
I didn't do that in the beginning and I was very frustrated... blaming M$
for all sorts of things... ;-)

Best regards,
Jocke

 

[Boris Nienke]

What are you trying to do?

different things.
1.) i like to build a dictionary with LOTS of words for the user to search
for. Here i'm speaking about 100000 records and more - but only two or
three tables with max. 5 rows. Simple Database - but to heavy for the
Access-File-Format (limited to 65k records and to ~16k records with
indexes)

2.) i like to build an application for the user to enter some information
while they are working, visiting etc... Before the user is going to work he
will export the data for the day out of a big system. And when he's back to
office he will copy his data back into the big system.
Here i'm talking about 8000 rows on the PocketPC (most of the time it will
be less - maybe 50 to 1000 rows - but i could be more).
Because the user needs to export the data it would be cool, if i could
create the "right" format on the desktop/server machine so that the user
could simply copy the resulting-file (database) to the PocketPC (for
example with the File-Sync of ActiveSync). I don't like the idea, that the
user has to copy a textfile which then has to be parsed into a
pocketPC-Database (...time...space... you know)

What search capabilities do you need?

1.) full text to find the right words. SQL would be great (Select...
Like...)

2.) very few. Just reading records with the right ID. A simple Index,
GetGreaterOrEqual (like old BTrieve times) would be enough. SQL would be
cool though....

Is it only 8000 rows at most? or only 1000?

1.) much more

2.) mostly less than 8000

Have you tried to use a simple textfile?

yes. but much too slow. It may OK for 100 or 200 lines - but then... i
don't like the user to wait seconds to input the data - it has to be
quicker than using a simple sheet of paper!

Is MDB/CDB sync also too slow for 8000 rows?

what is MDB/CDB?

How often are the user syncing 8000 rows?

every morning/evening of a day

8000 rows should be quite big anyway, 500kB - 1MB.

it depends on the data
Most of it are IDs (for the big system to know where to put the data in
when the user is coming back to office) or simple texts (short
informations)

The Dictionary may be 2 to 5 MB ... maybe more (stored as simple text) - i
have startet to load it in a SQL-CE File with indexes... after 20 MB i've
stopped it. Too heavy!

I assume the PDA user only reads the 8000 rows, not adding, deleting
changing them.

adding maybe!
deleting - no!
changing - yes!

If they do you only have to add some extra columns for the changes and send
back those with your C# code. The 8000 lines doesn't have to move back to
the server.

if i could simply copy the whole file - it doesn't matter.
When i need to export, i would only export changed records of course.

few thousand lines, then CDB is better, CSV or XML could also work if your
requirements are simple.

CSV is too slow for bigger files and doesn't work for the dictionary at all
XML is much slower when the files are getting bigger and you need LOTS of
memory

The Access-Database could be the solution for my 2. application - but i
need to buy the wrapper from InTheHand ... But it's not that expensive.

But for the 1. application it's no help at all.

I wonder how other companys are able to write knowledge-Base-Application
for Palm and PocketPC with tons of records which are very quick and
lightweight...

You don't need SQL Server, only MSDE. You can run insert or update on the
PDA in C#, and 1000 lines should be quite ok to update from a CSV, try it,
what is the performance?

What is MSDE?

Reading CSV is too slow. It's OK for <500 records but not more - and i
don't like the idea to read thru all records in a loop everytime the user
is searching for something. Not very scalable...

If you find a quick solution for database/text/xml data, even without SQL
querying I would like to know.

:-D ... i havn't yet ... this is because i'm asking in this group ;-)

After learning the tricks and limitations with SQL CE and CDB I'm happy with
it, it's fast and simple but you have to select the right tool for your
task.

i like to believe this - but first i need a good simple database-solution
;-)

I didn't do that in the beginning and I was very frustrated... blaming M$
for all sorts of things... ;-)

Oh, i don't blame MS! I'm sure the SQL-CE is a great tool! But it's much
too much for me and my clients.

What would be really great:
- Create a small Paradox/DBase Database on the Desktop (in one directory)
- Copy the Directory to the PPC
- Start work on the PPC
- Copy the Directory back to the PC
- ready

But i havn't find a solution for these files yet (which will work on the
CompactFramework with C# and which are payable and usable for
shareware-tools too)

Boris

 

[ppcdev]

Hi. I think that a database called "sql server ce" should _at least_
be faster than pocket access... why would we wait for 15 seconds -the
best time!- just to retrieve a client data record? our company spent
too much time for investigating into sql server ce (only because the
name of this product was "attractive"), and now we'd prefer use pocket
access... (damn! pocket access is also bugged...). we really think
that microsoft "embedded" servers make companies lose money... that is
a fact! our company lose money... when you are a little company, you
have to reduce the risk of errors...
on palm os devices, databases are harder to manage for a coder, but
they work faster (1 second maximum to retrieve a client data record...
fifteen times faster, ***AT LEAST***)...
bye

 

[Boris Nienke]

Hi. I think that a database called "sql server ce" should _at least_
be faster than pocket access... why would we wait for 15 seconds -the
best time!- just to retrieve a client data record? our company spent
too much time for investigating into sql server ce (only because the
name of this product was "attractive"), and now we'd prefer use pocket
access... (damn! pocket access is also bugged...). we really think

yes... i'm with you ...

i've just read the announcement of this one:
http://www.db4o.com/

sounds really good. Now it's a matter of pricing ... will see....

Boris

 

[ppcdev]

I REALLY DONT UNDERSTAND
IVE POSTED A MESSAGE HERE ABOUT COMPARINT SQL SERVER CE AND PALMOS
DATABASES
AND YOU DONT POST MY MESSAGE???????
IVE BEEN PROGRAMMING POCKETPC SINCE 3 YEARS. WE ARE ONE OF COMPANIES
IN EUROPE THAT IS MOST ACTIVE IN POCKETPC AND PALM AND SYMBIAN
PROGRAMMING
MICROSOFT GROUPS ARE REALLY SHIT
YOU CENSOR US?????? WE'LL HACK YOU....

 

[Boris Nienke]

as far as i remember i've read your posting.
But i understand why they might censor your last posting. Shouting out loud
(all upper-case letters) and saying things like:
"[...]YOU CENSOR US?????? WE'LL HACK YOU....[...]"
are not very gentle, are they?

Are you sure that your posting is censored? Maybe just a server-problem?

Boris

 

[Paul G. Tobey [eMVP]]

Don't shout. The problem is with external attacks currently being
propagated against the news servers at MS. Many people are having messages,
both originals and replies lost. It's not some sort of anti-European thing.
Threatening to hack someone is a real nice invitation for your local police
to show up at your house and maybe, say, take your computers with them to
see if you might be responsible for this problem.

Paul T.