Re: Performance problem in sql ce

Gagan,

Just work with the result set directly - don't bother converting back and forth to a dataset. If you create an updatable result set, you can change the data in the underlying table directly without needing a dataset or data adapter at all. You just set the new values for the columns and call Update. There is also Delete and Insert if you need those methods. There are some pretty good samples in the documentation if you search under SqlCeResultSet.

For best perforumance create separate result sets for the other tables using TableDirect, specify the index name for that table and the range, which provides the filtering. This is very fast, even if there are a lot of records, because you are bypassing the query processor entirely and working directly with the indexes.

Ginny

"Gagan" <Gagan@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:FC6F0AB3-F2FF-429B-B05A-6AA1A7082215@xxxxxxxxxxxxxxxx
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


--

Ginny Caughey
Device Application Development MVP

www.wasteworks.com
Software for Waste Management



.

Relevant Pages

  • Re: Official Status of SQLServer 2005 ADP
    ... I have said that the support for SQL passthrough ... queries under MDB was bad and worst than the one offered by ADP while you ... > attempt to "pass through" every Access query against a linked ODBC ...
    (microsoft.public.access.adp.sqlserver)
  • Re: "Query Too Complex" Errors
    ... few dozens of queries, in the middle of which there's a long chain of ... we've been having a lot of those "Query Too ... some of the complexity in the SQL ... SQL statement you are working on. ...
    (microsoft.public.access.forms)
  • Re: Dynamic query problem
    ... On Oct 17, 9:41 am, Andy Hull ... If we were to provide a fully featured dynamic query generator we would have ... Provided with already built queries which they can edit ... SQL and see where it is the same as for the other queries and where it ...
    (microsoft.public.access.queries)
  • Re: CONTAINS performance
    ... mark, FTS needs to be very carefully tuned to achieve second response times, ... see SQL Server 2000 BOL title "Full-text Search Recommendations" for more ... When you include the "TOP 100" in your query, you are in fact limiting the ... valid for SQL queries, they often do not apply to FTS queries because the FT ...
    (microsoft.public.sqlserver.fulltext)
  • Re: Populating a list -- learning Access
    ... It is kinda funny -- I started using databases in the early 80's with dBase and, for years, never knew that I knew SQL! ... If you are on a form or report, the most important property is the NAME, because that is how you refer to it in code. ... I don't mean to skip your responce below, but thanks to your help with SQL, I was able to get the query to pull the info the way I needed. ... Queries (just shows the QBE grid for convenience -- ...
    (microsoft.public.access.forms)
Loading