Re: Performance problem in sql ce

Hi Ginny,
Resultset seems to be work. I tried with this
SqlCeResultSet rs = CeCommand.ExecuteResultSet(ResultSetOptions.Scrollable);
and it takes only 10 sec for query which was taking previously 1 min in
CeDataAdapter = new SqlCeDataAdapter(CeCommand);
But i want return results in dataset object because i am using dataset
every where for businss logic calculations.Can we convert resultset to
dataset ?
By programatic joins mean creating separate result sets then loop through
and filter matching PK & FK fields. please clear and how can we set range in
this scenario?
In db i have data like rooms table then minibars,model body,grids,sections,
gridsensors,sensors,products,productcategory etc. So its normalized.i don't
think i can denormalize it beacause merging data will create much redundancy
and it
would be difficult to extract required data to show on UI.

"Ginny Caughey MVP" wrote:

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: Performance problem in sql ce
    ... Resultset seems to be work. ... SqlCeResultSet rs = CeCommand.ExecuteResultSet; ... even with the amount of data you have. ... I am very disappointed with the sql queries performance in sql ce. ...
    (microsoft.public.sqlserver.ce)
  • Re: Performance problem in sql ce
    ... Resultset seems to be work. ... "Ginny Caughey MVP" wrote: ... even with the amount of data you have. ... >>> I am very disappointed with the sql queries performance in sql ce. ...
    (microsoft.public.sqlserver.ce)
  • Re: sqlCeResultSet readPrevious() method doesnt work always after a readAbsolut(currentPosition)
    ... > In my program i use a sqlCeResultSet for accessing my data. ... > When i execute a readPreviousafterwards, the resultset stays at the ... > sql server compact edition 3.1, ... > for me that seems to be a bug in the sql server compact edition. ...
    (microsoft.public.sqlserver.ce)
  • Re: Bindingsource Find method
    ... I also prefer SqlCeResultSet not ... .NET Compact Framework MVP ... Actually there is no "new" resultset. ...
    (microsoft.public.dotnet.framework.compactframework)
  • Re: DBF files and DataGrid...
    ... (Cindy as well) ... used SqlCeResultSet. ... searching through that many records in a SQL ... you bind a SqlCeResultset to a DataGrid, even if you have 10,000 records, ...
    (microsoft.public.dotnet.framework.compactframework)