SQL Server 2005 Mobile - first Query so slowly - why?

[Thomas Bandt]

Hello,

I wrote a little Test-Application which fetchs about
10 records from a SQL Server Mobile table with at all
~ 1500 records in it.

I also added a SQLite database with exactly the same data
and the same procedure.

Here is the result (duration in seconds):

SQL Server Mobile 1 42 46 38 39
SQLite 7 8 8 8
SQL Server Mobile 2 6 7 7 6
SQL Server Mobile 1 7 9 6 7
SQL Server Mobile 2 8 6 7 6
SQLite 2 2 2 2

SQL Server Mobile 1 is a encrypted .sdf with password, SQL
Server Mobile 2 is a .sdf without password and encryption.

I first thought before this test, SQL Server Mobile is so
slowly at all, but the test shows that only the first
call is so slow.

Then I thought about the decryption of the data by the
runtime at the first call. So i took the call of
SQL Server Mobile 2, which isn't encrypted, at first
position. Result: 45 seconds.

This means the decryption could not be the bottleneck.

But what else?

Greetings, Thomas

 

[Thomas Bandt]

Additional informations: the called methods look like this one:

private void GetMachineTypes()
{
using (SqlCeConnection connection = new
SqlCeConnection(PocketCatalog.Utilities.Database.ConnectionString))
{
using (SqlCeCommand cmd = new SqlCeCommand())
{

cmd.Connection = connection;
cmd.CommandType = CommandType.Text;
cmd.CommandText = "Select Distinct FieldName From TableName
Where Not FieldName Is Null Order By FieldName Asc";

DataSet ds = new DataSet();
SqlCeDataAdapter da = new SqlCeDataAdapter();
da.SelectCommand = cmd;

da.Fill(ds);

ComboBox1.DataSource = ds.Tables[0].DefaultView;
ComboBox1.DisplayMember = "FieldName";

}
}

}

Regards, Thomas

 

[Ilya Tumanov [MS]]

Few pointers for better performance measurement:



- First run would include code JITing, so you should ignore first run.

- Eliminate UI from the time measurement. Populating ComboBox is very slow,
probably takes more time than query itself.

- Increase number of records retrieved, say to 1000. That would increase
precision greatly.

- Use DataReader and discard of data to measure raw data read speed.

- Execute two similar queries retrieving different number of records to
calculate query processing time: Ttotal = Tquery+Trow*Nrows.



Ttotal - time to execute query in QP and load rows.

Tquery - time to execute query in QP

Trow - time to load one row

Nrows - number of rows retrieved.




Best regards,



Ilya

This posting is provided "AS IS" with no warranties, and confers no rights.

*** Want to find answers instantly? Here's how... ***

1. Go to
http://groups-beta.google.com/group/microsoft.public.dotnet.framework.compactframework?hl=en
2. Type your question in the text box near "Search this group" button.
3. Hit "Search this group" button.
4. Read answer(s).

 

[Thomas Bandt]

Hi Ilya,

first thanks for answering.

- First run would include code JITing, so you should ignore first run.

Yes, that would be okay, but it takes also so long if the
application is restarted ...

 

[Thomas Bandt]

Hi Ilya,

We have tests done with and without counting JITing/loading. The difference
is 2-3 seconds for DataSet/SqlServerCe code on PXA 255@400 MHz PPC 2003
device -that's how long it takes to JIT/load. ~40 seconds you see is
probably some measurement anomaly. I've a simple test which retrieves
records from standard NorthWind (included with SQL Mobile), puts them into
DataSet and binds it to the grid (and other controls). It takes about 3
seconds for the first run, 150-170 ms for consequential runs. In fact, here
it is (attached), try it out.

thank you very much for the test-application. I ran it at my pocket pc,
and it is much faster than my own application. So I'll be looking for
the bottleneck.

Thank you

Thomas

 

[Thomas Bandt]

Hi again,

I tried today your application and took a comparison of
your evaluating code and my own. I found no fundamental
differences.

But in fact, my code runs much slower than yours. So i
decided a few minutes ago to use SQLite ...

But of course I am interested to know what the reason
is. I can't publish my database because of sensible data
in it, but I think the database itself is not the problem.

How can this query took more than 3 times longer in SQL
Server than in SQLite?

Select Distinct Hersteller From Landmaschinen Where
Maschine = ? order by Hersteller Asc

"Hersteller" is a nvarchar field with an index on it (non-
unique, non-clustered).

The time used to bind the data to the combobox control is
not mentionable ...

Greetings, Thomas

 

[Ilya Tumanov [MS]]

How long it takes for you?



I've tried similar query on NorthWind: "Select Distinct [Order Date] From
Orders Where [Customer ID] = 'ALFKI' order by [Order Date] Asc";



This table has about 1000 rows returning 7 rows which is close to what you
have, right?



The first Fill() run takes about 3 seconds which is the same as before as
same code needs to be JITed.

Next Fill() takes 60-70 ms after that. I'm running it on 400MHz PXA 263 with
PPC 2003 SE.

Best regards,

Ilya

This posting is provided "AS IS" with no warranties, and confers no rights.

*** Want to find answers instantly? Here's how... ***

1. Go to
http://groups-beta.google.com/group/microsoft.public.dotnet.framework.compactframework?hl=en
2. Type your question in the text box near "Search this group" button.
3. Hit "Search this group" button.
4. Read answer(s).

 

[Thomas Bandt]

How long it takes for you?

A query like the one below takes about ~ 1,2 - 2 seconds, the
same query with SQLite takes only 10% of that.

I've tried similar query on NorthWind: "Select Distinct [Order Date] From
Orders Where [Customer ID] = 'ALFKI' order by [Order Date] Asc";

This table has about 1000 rows returning 7 rows which is close to what you
have, right?
Yes.

The first Fill() run takes about 3 seconds which is the same as before as
same code needs to be JITed.

Next Fill() takes 60-70 ms after that. I'm running it on 400MHz PXA 263 with
PPC 2003 SE.

I was not able to reproduce it ... But we just finally decided
to work with SQLite because of the licensing conditions of SQL
Server Mobile.

Regards, Thomas

 

[Ilya Tumanov [MS]]

I see 60-70 ms, not 1.2-2 seconds. Is this the time for same query I've
tried or it's for different DB?

Which device are you using? Is your database on storage card?



Also, it's not quite clear to me what do you mean by "licensing conditions".
SQL Mobile is free.

If that's about SQL Server 2000/2005 CALs, that is SQL 2000/2005 licensing,
not SQL Mobile's.

If your application connects to SQL 2000/2005, you need CALs in any case,
does not matter if you use SQL Mobile or not.



In any case, if SQLite works for you, go with it.



Best regards,



Ilya

This posting is provided "AS IS" with no warranties, and confers no rights.

*** Want to find answers instantly? Here's how... ***

1. Go to
http://groups-beta.google.com/group/microsoft.public.dotnet.framework.compactframework?hl=en
2. Type your question in the text box near "Search this group" button.
3. Hit "Search this group" button.
4. Read answer(s).

How long it takes for you?

A query like the one below takes about ~ 1,2 - 2 seconds, the
same query with SQLite takes only 10% of that.

I've tried similar query on NorthWind: "Select Distinct [Order Date] From
Orders Where [Customer ID] = 'ALFKI' order by [Order Date] Asc";

This table has about 1000 rows returning 7 rows which is close to what
you have, right?
Yes.

The first Fill() run takes about 3 seconds which is the same as before as
same code needs to be JITed.

Next Fill() takes 60-70 ms after that. I'm running it on 400MHz PXA 263
with PPC 2003 SE.

I was not able to reproduce it ... But we just finally decided
to work with SQLite because of the licensing conditions of SQL
Server Mobile.

Regards, Thomas

 

[Thomas Bandt]

I see 60-70 ms, not 1.2-2 seconds. Is this the time for same query I've
tried or it's for different DB?

It's for my specific query.

Which device are you using? Is your database on storage card?

I use a "Medion MDA 96700" PDA, which is only avaiable in Germany
I think. The processor is from type "PXA270 312 MHZ". The database
file lies not on storage card.

Also, it's not quite clear to me what do you mean by "licensing conditions".
SQL Mobile is free.

If that's about SQL Server 2000/2005 CALs, that is SQL 2000/2005 licensing,
not SQL Mobile's.

If your application connects to SQL 2000/2005, you need CALs in any case,
does not matter if you use SQL Mobile or not.

My application only connects to a local SQL Mobile database on the
same machine (Pocket PC). There is also no synchronization feature
or so - only at the initializing data import.

Does this mean I don't need to buy any license and can distribute it
royalty free to end customers? Can you show me this in written form
(Website ...)?

If yes, I'll try it again because I think SQL Mobile self is a
great tool.

If you want I can send you my own database file, so you can try
and maybe reproduce it ... just send me an email to
spam AT thomasbandt DOT de

Regards, Thomas

 

[Ilya Tumanov [MS]]

Please go ahead and send me your database, just remove "online" from my
e-mail.

Please provide problematic query as well. If you'd like to compress it which
is probably a good idea, please use standard ZIP.



As to licensing, yes, you don't need to buy any licenses and you can
redistribute SQL Mobile to your customers royalty free:


http://www.microsoft.com/sql/editions/sqlmobile/howtobuy.mspx

Best regards,

Ilya

This posting is provided "AS IS" with no warranties, and confers no rights.

*** Want to find answers instantly? Here's how... ***

1. Go to
http://groups-beta.google.com/group/microsoft.public.dotnet.framework.compactframework?hl=en
2. Type your question in the text box near "Search this group" button.
3. Hit "Search this group" button.
4. Read answer(s).