Re: Performance problem in sql ce

I haven't seen any published limit because I suspect it's variable. I've had good luck with 4 joins, but 9 is a lot.

With the amount of data you need to work with, I like Jin's suggestion of using SqlCeResultSet objects. It would be more work since you'd need to do all the joins programmatically, but you could probably get very good performance going that route, even with the amount of data you have. I'd suggest using TableDirect and specify each index and range to get the best performance. Note that you can databind SqlCeResultSets (actually the default ResultSetView) to your UI elements, but you'd need to provide a current changed event handler(s) and programmatically refresh the "child" resultsets as the most senior parent one changes.

When I said denormalize your 9 tables, I was thinking of merging them into maybe 4 tables rathter than splitting them further into 15. I don't know your data so I don't know if that would be a practical option for you or not, and with the large amount of data, you'd still get better performance with SqlCeResultSet.

Ginny

"Gagan" <Gagan@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:C5869C54-5E05-4C06-B2AC-B9A3DD459279@xxxxxxxxxxxxxxxx
Hi Ginny,
Thanks for reply. While sync i am keeping required fields in tables.
That means all fields that i am keeping i need to show on UI. Suppose i
denormalize my 9 tables to 15 then i have to put joins on 15 tables (joins
will increase) definitely row size will decrease. But can it improve
performance to
acceptable limit ? like i am not getting results in even 15 minutes.
Or reducing the amount of data is the only solution. Is there any predefined
limit of data in sql ce ?

"Ginny Caughey MVP" wrote:

Even with correct indexes, having lots of joins is a known performance
bottleneck. How many is "lots"? I guess it depends on the data. The only
workaround I'm aware of is to denormalize your data.

Ginny

"Gagan" <Gagan@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:2262FDFC-E4AD-4337-AF87-30432E95922B@xxxxxxxxxxxxxxxx
> 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.

--

Ginny Caughey
Device Application Development MVP

www.wasteworks.com
Software for Waste Management




--

Ginny Caughey
Device Application Development MVP

www.wasteworks.com
Software for Waste Management



.

Relevant Pages

  • Re: how big can disconnected dataset be?
    ... there are advantages in certain design scanarios. ... > amount of time used for network traffic, you'll have a big burst at ... >> Library here) and then start pushing the data into Sql ... >> Server from the datasets. ...
    (microsoft.public.dotnet.languages.vb)
  • Re: How to find the difference between values of multiple records
    ... i plugged in the sql using my names and stuff. ... i am not sure, but i think you intended 2 separate queries, here is what ... This query should give you the dates to match up ... need to calculate the amount of time between each record. ...
    (microsoft.public.access.queries)
  • Report By Year
    ... I have a SQL view that shows data by Category and Year - so the data may ... Category - Year - Amount ... An Access-style report ... the SQL query good enough or do I need to do something with the query in ...
    (microsoft.public.dotnet.languages.vb)
  • RE: External data query using MAX/SUM
    ... 2)I edited your SQL to make it resemble what you should try to achieve. ... The XL list is named myTable and has 2 columns: myDate and Amount. ... I suggest playing with the XL model first, then try to adapt the technique ... > I'm trying to create an external data link using Microsoft Query. ...
    (microsoft.public.excel)
  • Re: Query
    ... SELECT "M" as PeriodType ... qryIllinoisPlanSponsorMonthtoDate.[SumOfCommission Amount], ... FROM tblIllinoisPlanSponsors INNER JOIN qryIllinoisPlanSponsorQTR ON ... Another option would be to use the three queries as the source for three sub-reports. ...
    (microsoft.public.access.gettingstarted)