Performance Issue With SQL Server Mobile

[JDee]

Hello,

I have a .Net CF application that uses SQL Server Mobile 3.0 on the
backend.

The problem is that when I run a query from the application that
results in joining 4 tables and returning denormalized data, it takes
too long to execute, around 4 to 5 seconds to return 10 rows. This is
a consistent behavior on all such database calls and not just the
first one.

The query and resultant dataset are not extravagant at all. When I
run the query from the query analyzer, it takes this long only for the
first time around. All subsequent calls are fairly quick and return
in milliseconds.

The strange thing is that if I keep SQL Server Mobile Query Analyzer
running in the background with the database active, the same query
executed from the application is returned in milli seconds.

Please, can anybody here help me understand this behavior and to
improve the performance of my application?

Thanks,
Jawad

 

[Ginny Caughey [MVP]]

Jawad,

That's an interesting observation that also having Query Analyzer running
speeds up queries, and I don't recall seeing that before. I assume the
performance improvement is due to the internal query analyzer already being
loaded with working memory allocated and/or perhaps the query plan is
already worked out, but I'm only guessing.

The usual guidance when people report slow queries with joins is to
denormalize the data before loading it onto devices (or design your
device-based schema to avoid most joins), since joins are a known poor
performer in SQL Mobile. SQL purists may not like this suggestion, but end
users generally do.

 

[Guest]

I'm guessing that you're tearing down the connection after each of your
queries in your app. SQL CE does a fair bit of clean up when the last
connection to a given DB is closed, and that must be undone when the first
connection is made.

There are a couple avenues you can take to avoid the perf hit you see.
Either don't close the connection after each query or open a second, unused
connection to the DB when your app starts and just leave that one open for
the lifetime of your app.


--

Chris Tacke, Embedded MVP
OpenNETCF Consulting
Managed Code in an Embedded World
www.OpenNETCF.com