Re: Performance problem in sql ce

On Apr 21, 10:13 am, Gagan <Ga...@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:
Hi Jin,
Thanks your suggestion works i tried with this
SqlCeResultSet rs = CeCommand.ExecuteResultSet(ResultSetOptions.Scrollable);
For query, with where clause i was getting result in 1 min now i am getting
result in 10 sec. I can use this on many places to improve performance.
Previously i was using
CeDataAdapter = new SqlCeDataAdapter(CeCommand);
to fill the result in dataset.
But the problem is still same for the query which is taking > 15 min.
By saying programmatically joins means first create separate result sets for
all tables then start filtering or some thing else please clear.
Another problem is that i am using dataset every where. After getting
results for filtering, sorting and showing on UI. Can we convert result set
object to dataset directly or i have to work with resultset only. i am not
much familiar with this object and i don't have much time for r&d.

"Jin" wrote:
On Apr 21, 4:26 am, Gagan <Ga...@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:
Hi,
I am very disappointed with the sql queries performance in sql ce. In my
some queries i have inner joins on 9 tables some tables have more then 150000
records. Db size is around 15 MB. If i run that query with particular where
clause (ex RoomNo. = 100) then it gives result in 1 minute otherwise i wait
for 15 min. & i did not get results. However i am using proper indexing on
SARG still response time is not acceptable. I also tried by putting db on sd
card doesn't work.
Can any body help me to find out where i am wrong or the limitations of sql
ce queries.

150,000 records sounds a bit too much for mobile application to
handle. Couple that with joins on 9 tables, I wouldn't expect good
performance at all. Ginny's suggestion of denormalization is a good
one if you can afford to make the changes. Another approach might be
to avoid the joins as much as possible and go with programmatic joins
via the SqlCeResultSet. This approach will at least allow you to have
a bit more control over the performance bottlenecks.

- Jin

Working with SqlCeResultSet is pretty much what Ginny described.
Although you can use it in similar manner to that of DataSet, it's
much easier (and probably faster) if you rely only on methods
available in SqlCeResultSet for data operations (like Insert, Delete,
Update, etc.). Start with the examples from the documentation and do
a search on the internet for other tips. If your existing program is
heavily reliant on DataAdapter, it may take quite a bit of changes to
move away from that. If not, then it should be ok. Generally
speaking, I prefer working with SqlCeResultSet over the DataSet
because of the performance and ease of use.

To further clarify what I mean by "programmatic join," I'm basically
referring to obtaining the values from other table SqlCeResultSet
object on a need-basis. For example, if you have the Master table
which is being traversed via rsMaster, retrieve the Detail table value
via rsDetail only when needed (by performing filtering or even the
Seek using FirstEqual option on existing ResultSet object). In one of
my application, I have around 10 ResultSet object open simultaneously
and retrieve the values I need using the Seek method. It works great
and is super fast.

Finally, DataAdapter is rather heavy on memory requirements, so moving
away from that will improve the memory usage on your mobile device,
which is another plus.

- Jin
.

Relevant Pages

  • Re: Performance problem in sql ce
    ... SqlCeResultSet rs = CeCommand.ExecuteResultSet; ... But the problem is still same for the query which is taking> 15 min. ... I am very disappointed with the sql queries performance in sql ce. ...
    (microsoft.public.sqlserver.ce)
  • Re: Data access: SqlCeResultSet or DataSet ?
    ... you need to do it yourself with SqlCeResultSet or denormalize your data. ... Which leads me to investigate typed DataSet vs typed SqlCeResultSet, TableDirect mode vs query engine. ... Does it make sense to use a SqlCeResultSet with a query or do I lose all of the performance vs a Data Set? ...
    (microsoft.public.sqlserver.ce)
  • Re: MSSQL 2005 and SQL Mobile Edition - how transfer data?
    ... SqlCeResultSet is generally the fastest way to read/write SQL Compact data. ... "insert or replace" query, in SQL mobile i have to catch exception on ...
    (microsoft.public.dotnet.framework.compactframework)
  • Re: MSSQL 2005 and SQL Mobile Edition - how transfer data?
    ... SqlCeResultSet is generally the fastest way to read/write SQL Compact data. ... copy a sdf file from the device to the desktop and run your updates from ... "insert or replace" query, in SQL mobile i have to catch exception on ...
    (microsoft.public.dotnet.framework.compactframework)
Loading