SQLCE in C#

  • Thread starter Thread starter rbalfe
  • Start date Start date
R

rbalfe

I am using c# to access a SQLCE database using SqlCeDataReader
I have placed the read function in a while loop:

while(myReader.Read())
{
do someting...
}

I am using a database with 20,000 records and if I do a read of approx
40. When my loop comes around for the 41st time it hangs for about 15
seconds on the while statement and then jumps out of the loop.

Has anyone come across this before?
Has anyone any idea on how to fix this problem?

Thanks in advance
 
Sound like something is wrong with the data and raising an exception.
Have you got a try-catch in place, when used correctly this should help
you diagnose the error. Alternative, try removing the 41st record to
see if the problem 'moves' or disappears altogether.

Chris
 
Hi Chris thanks for your reply.

There is actually no 41st item, I was just using it as an example and
looking back didn't explain myself too well. It just loops for this
much i.e. it hits the end of the recorset. I have a try catch which
doesn't give an exception. This happens with any amount of records.

When it hits the end of the recorset the "while(myReader.Read())" just
sits on this line for about 50 seconds, breaks out of the loop and then
continues on as normal with the rest of the program. It only seems to
be slow when it hits the end of the recordset and not during...

Thanks.

- Ronan
 
do you really need to loop through 20,000 rows? first, be sure
this table has a unique index on it and that the SQL that you are
using to create the SqlCeDataReader can use the index. next,
if all you want is to get to a specific row and grab it's column
values, consider using TableDirect and Seek instead of the
datareader - it is much faster than the reader.

--
Darren Shaffer
..NET Compact Framework MVP
Principal Architect
Connected Innovation
www.connectedinnovation.com
 
I'm not looping through 20,000 rows. The database has 20,000 rows but I
might only read about 40 or 100 of them at a time.

It loops through these quick enough for me but it's just when it
reaches the end of the recordset and there are no values left it hangs
on the while statement trying to figure out that there is nothing left
to read...

Thanks
 
Hi,

Are you executing the reader with an associated CommandBehaviour?

If not, maybe changing the commandbehaviour property can solve your
problem. See CommandBehaviour possible values in MSDN help and choose
yours. Maybe CommandBehavior.SingleResult would do it for you.

I would alternatively suggest you using SQLCEResultSet object. It comes
with many improvements and has been specially programmed for CF 2.0 and
SQL Mobile 2005. The reading of the records is the same as DataReader,
so you can use "while resultset.Read()". Check and see if
SQLCeResultSet solves your strange behaviour.

Hope it helps.
 
Hi Lonifasiko,

This seems to have been the problem with the recordset in that it has
multiple result sets, I wasn't using a CommandBehaviour. I have tried
with the CommandBehaviour and the recordset read is now instant when it
reaches the end of the recordset, but previously the query itself in
ExecuteReader was almost instant without the CommandBehaviour where as
now it's as slow as the recorset read used to be.

I have tried the SQLCeResultSet with the same result as using the
CommandBehaviour. I have the database indexed.

I think I will just have to face the fact that SQLCE is just slow :(

Thanks for all the answers

- Ronan
 
Ronan,

You should be able to improve the performance if you have an appropriate
index by specifying that index and a range of values that would use that
index:

SqlCeCommand cmd = conn.CreateCommand();
cmd.CommandType = Commandtype.TableDirect;
cmd.CommandText = "YourTableName";
cmd.IndexName = "YourIndexName";
cmd.SetRange(DbRangeOptions.ExclusiveStart | DbRangeOptions.ExclusiveEnd,
new object[] {yourStartValue}, new object[] {yourEndValue});

This approach should work with either a SqlCeDataReader or SqlCeResultSet.
You should get very slightly better performance with the data reader, but
since the result set can be scrollable and updatable, it might be the better
choice overall.
 
Back
Top