DevForce 2010: Query Optimization

Query Optimization

Printed From: IdeaBlade
Category: DevForce
Forum Name: DevForce 2010
Forum Discription: For .NET 4.0
URL: http://www.ideablade.com/forum/forum_posts.asp?TID=3708
Printed Date: 13-May-2026 at 12:56am

Topic: Query Optimization

Posted By: Vonzkie
Subject: Query Optimization
Date Posted: 11-Oct-2012 at 3:42am

Hi,

I can't provide a database but let's assume that my DB schema and indexes are all in good shape.

Then I have this kind of query (Disregard the logic here):

Another is there are no direct relationship between tables and models.

var customers = context.Customers.Join(context.Suppliers.Where(supplier => supplier.Active == true),

cus=> cus.CustomerID,

sup=> sup.SupplierID,

(cus, sup) => cus);

var stores = context.Store.Join(customers,

store => store.StoreID,

cus=> cus.CustomerID,

(store, cus) => store).Where(p=> p.Active == true);

var remainder = 0;

var count = stores.Count();

var pageSize = 50;

var pageCount = Math.DivRem(count , pageSize , out remainder);

if (remainder > 0)

pageCount = pageCount + 1;

for (int x =0; x <= pageCount ; x++)

{

var pagedStores = stores.OrderBy(o => p.StoreID).Skip(x * pageSize).Take(pageSize);

foreach(var store in pagedStores)

{

Console.WriteLine("Store ID: {0}", store.StoreID);

}

Example my count is 50k records, so I will have 1k page..

What happen in this query, every page it adds approximately 1 second delay.

So meaning the first page is 1 second, second is 2 second delay and so on and it will come to a point that it will time out...

What do you think is the problem with this approach or do you have any solution that can improve our query?

Note: We already used the EntityQueryPager<T> but still no luck..

We also try juggling some of the QueryStrategy but also failed.

Thanks,

Von

Replies:

Posted By: Vonzkie
Date Posted: 15-Oct-2012 at 3:46am

Hi Guys,

Just want to follow up this..

Thanks,

Von

Posted By: sbelini
Date Posted: 15-Oct-2012 at 9:59am

Von,

Have you tried executing the Query only once? i.e. customers.Execute() / stores.execute() - you might also execute them asynchronously.

In your for statement you are issuing a query against the datasource for each page. Since you are loading everything anyway, you my as well execute it against the data already loaded in the cache.

That might help.

sbelini.

Posted By: Vonzkie
Date Posted: 18-Oct-2012 at 8:23pm

Hi Silvio,

We're expecting a huge amount of records to query so querying it once will not work for us.

Async is not a choice either cause we're waiting for each page query to finish.

In your reply:

In your for statement you are issuing a query against the datasource for each page. Since you are loading everything anyway, you my as well execute it against the data already loaded in the cache.

Can you give me a detail on how to do this?

My concern is the delay w/c is incremented as the page index increments and it reach the point that it will time out.

What do you think is the problem here?

Thanks,

Von

Posted By: sbelini
Date Posted: 19-Oct-2012 at 12:11pm

Von,

I'll try to reproduce the behavior here so we can further investigate.

Silvio.

Posted By: Vonzkie
Date Posted: 23-Oct-2012 at 8:23pm

Hi Silvio,

Any update on this one?

This is a critical issue in our part and we need to fix it as early as possible.

Thanks,

Von

Posted By: sbelini
Date Posted: 24-Oct-2012 at 8:53am

Hi Von,

I'm still working on reproducing the issue.

Posted By: sbelini
Date Posted: 24-Oct-2012 at 10:53am

Von,

I was not able to reproduce the behavior you stated.

Here is my test solution: uploads/892/T12223_.zip - uploads/892/T12223_.zip

Please provide a reproducible test case so we can further investigate this issue.

Silvio.