Re: Performance problem in sql ce
- From: "Ginny Caughey MVP" <ginny.caughey.online@xxxxxxxxxxxxxx>
- Date: Mon, 21 Apr 2008 13:03:39 -0400
Gagan,
You specify the range on the command object that you call ExecuteResultSet on. Here's some sample code I use to filter Orders in Northwind based on the customer ID in the customersResultSet to give you an idea:
if (sqlTableDirectCommand == null)
{
sqlTableDirectCommand = conn.CreateCommand();
sqlTableDirectCommand.CommandText = "Orders";
sqlTableDirectCommand.CommandType = System.Data.CommandType.TableDirect;
sqlTableDirectCommand.IndexName = "Customer ID";
}
// Generate the ResultSet
//
object[] startValues = new object[] { customersResultSet.Customer_ID };
object[] endValues = new object[] { customersResultSet.Customer_ID };
sqlTableDirectCommand.SetRange(System.Data.SqlServerCe.DbRangeOptions.InclusiveStart |
System.Data.SqlServerCe.DbRangeOptions.InclusiveEnd, startValues, endValues);
sqlTableDirectCommand.ExecuteResultSet(this.resultSetOptions, this);
This is indeed more work than just using the query processor to fetch the data you want, but if you want the best performance, then this is how you can get it.
Ginny
"Gagan" <Gagan@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:8D206625-BEED-48AF-8920-2567560B2414@xxxxxxxxxxxxxxxx
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
--
Ginny Caughey
Device Application Development MVP
www.wasteworks.com
Software for Waste Management
.
- References:
- Performance problem in sql ce
- From: Gagan
- Re: Performance problem in sql ce
- From: Ginny Caughey MVP
- Re: Performance problem in sql ce
- From: Gagan
- Re: Performance problem in sql ce
- From: Ginny Caughey MVP
- Re: Performance problem in sql ce
- From: Gagan
- Performance problem in sql ce
- Prev by Date: Re: Performance problem in sql ce
- Next by Date: Re: Performance problem in sql ce
- Previous by thread: Re: Performance problem in sql ce
- Next by thread: Re: Performance problem in sql ce
- Index(es):
Relevant Pages
|
Loading
