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

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.

-- 
Ginny


"JDee"  wrote in message 
news:1185349002.414076.196400@19g2000hsx.googlegroups.com...

> 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
> 

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


"JDee"  wrote in message 
news:1185349002.414076.196400@19g2000hsx.googlegroups.com...

> 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
>