SqlCeDataReader extremly slow?

[Thomas Bandt]

Hi,

I am currently building a little database application for
Pocket PC with CF 2.0 and SQL Mobile 2005.

Actually I try to get a little fulltext search running. For
that I am using a SqlCeCommand object to get the data from
the database, and a User Control bound in a loop to the
database data to display each result.

Because of that I can't using automatic databinding - I have
to iterate trough a result set. And that is my problem.

To get the data from the database it takes about 2 seconds.
To iterate through the result set, takes about 16-18 seconds!
The resultset contains only 10 rows (of 26.000 in the database
table at all)!

I tried to use a SqlCeDataReader, a SqlCeResultSet and also
a DataTable (I know this uses DataReader inside) - with the
same results:

--

SqlCeDataAdapter da = new SqlCeDataAdapter(cmd);

DataTable results = new DataTable();
da.Fill(results);

duration1 = DateTime.Now - start;

for (int i = 0; i < results.Rows.Count; i++)
{

}

--> Filling the data into the DataTable ~ 16 seconds.
Looping thourgh the rows is fast, but to get the data
into the DataTable it uses a DataReader, which is slow

--

using (SqlCeResultSet rs =
cmd.ExecuteResultSet(ResultSetOptions.Insensitive))
{
duration1 = DateTime.Now - start;
while (rs.Read())
{
}
}

Get the data ~ 7 seconds. Looping ~ 13 seconds.

--

using (SqlCeDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
}
}

Get the data ~ 2 seconds, looping 18 seconds.

--

In the loop itself I do nothing, it is empty. So I
don't understand why it takes so long? And that for 10 rows?

Any idea?

 

[Ginny Caughey [MVP]]

Thomas,

Without seeing your data I'm only guessing, but are you selecting your 10
rows out of 26,000 based on an indexed column? If so you can use TableDirect
and specify the index name to get just that data very quickly by specifying
a range. I'd expect 7 ms rather than 7 seconds to loop through a small
result set without doing anything in the loop. Have you tried your test
multiple times to eliminate JIT time? And I'd suggest using
Environment.TickCount for begin and end times.

 

[Thomas Bandt]

Thomas,

Without seeing your data I'm only guessing, but are you selecting your 10
rows out of 26,000 based on an indexed column? If so you can use TableDirect
and specify the index name to get just that data very quickly by specifying
a range. I'd expect 7 ms rather than 7 seconds to loop through a small
result set without doing anything in the loop. Have you tried your test
multiple times to eliminate JIT time? And I'd suggest using
Environment.TickCount for begin and end times.

Thanks for answer. How can I set an index on a column? I had
set indices für the original tables on Sql Server 2005, but
I can't find a option for Sql Mobile?

And of curse I tried my tests multiple times, JIT time is not
the problem.

But what could be the reason for this extremly long processing
time?

 

[Thomas Bandt]

My fault ... the measurement was false. The bottleneck
isn't the loop, it's the SQL statement. I am using a
Like '%bla'% - what causes the delay. But I also do not
know why this is such a problem :-(

 

[Ginny Caughey [MVP]]

Thomas,

The best way to test different queries is by using SQL Server Management
Studio's query analyzer. You can tell it to show the actual query plan so
you can see precisely which parts of the query are slowing you down. You can
also create and drop indexes and see how that affects performance. Here's a
link that might be useful too:
http://msdn2.microsoft.com/en-us/library/ms172984(SQL.90).aspx. I suspect
however that you're going to end up with a table scan for the type of query
you've got. Would it be possible to store the search token in its own column
so you could build an index over that?

 

[Thomas Bandt]

Hi,

I think I found the bottleneck ... I get the records with
the following code:

string ticks = string.Empty;
using (SqlCeConnection connection = new
SqlCeConnection(Helper.ConnectionString))
{
using (SqlCeCommand cmd = new SqlCeCommand())
{
cmd.Connection = connection;
cmd.CommandType = CommandType.Text;
cmd.CommandText = "Select TypeId From Typen Where FkSpracheId =
1 And Type Like '%mammut%' ";

connection.Open();

using (SqlCeDataReader rs = cmd.ExecuteReader())
{
TypeItem ti;

duration1 = Environment.TickCount - start;

while (rs.Read())
{
ticks += rs[0].ToString() + " - " +
(Environment.TickCount - start).ToString() + "\n";
ti = new TypeItem();
// ...
til.Add(ti);
}

duration2 = Environment.TickCount - start;

}

}
}

After that I let me show ticks, with the following result:

8583 - 6317
8586 - 6347
20956 - 13416
....
25141 - 16070

The first number in each line shows the output of "TypeId"
(Primary Key, Integer) - the second the time ... every time
the Id jumps up the delay becomes unnormal ...

Can anybody explain this behavior?

 

[Ginny Caughey [MVP]]

Thomas,

The line where you concatenate a new value onto the string ticks inside your
loop is probably triggering garbage collector activity. Try declaring ticks
as a big StringBuilder instead so a new string doesn't get allocated on each
iteration through the loop. Instantiating new TypeItem objects inside the
loop will trigger the GC too, but probably you don't have any way around
that.

 

[Thomas Bandt]

Thomas,

The line where you concatenate a new value onto the string ticks inside your
loop is probably triggering garbage collector activity. Try declaring ticks
as a big StringBuilder instead so a new string doesn't get allocated on each
iteration through the loop. Instantiating new TypeItem objects inside the
loop will trigger the GC too, but probably you don't have any way around
that.

Hi,

you're right - but the effect also occours when I use StringBuilder
intead of string ...