New Posts New Posts RSS Feed: Query Optimization
  FAQ FAQ  Forum Search   Calendar   Register Register  Login Login

Query Optimization
 Post Reply Post Reply
Author
  Share Topic Share Topic  Topic Search Topic Search  Topic Options Topic Options
sbelini View Drop Down
IdeaBlade
IdeaBlade
Avatar

Joined: 13-Aug-2010
Location: Oakland
Posts: 786 		Post Options Post Options   Quote sbelini Quote  Post ReplyReply Direct Link To This Post Topic: Query Optimization
    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
 
Please provide a reproducible test case so we can further investigate this issue.
 
Silvio.
Back to Top
sbelini View Drop Down
IdeaBlade
IdeaBlade
Avatar

Joined: 13-Aug-2010
Location: Oakland
Posts: 786 		Post Options Post Options   Quote sbelini Quote  Post ReplyReply Direct Link To This Post Posted: 24-Oct-2012 at 8:53am
Hi Von,
 
I'm still working on reproducing the issue.
Back to Top
Vonzkie View Drop Down
Senior Member
Senior Member
Avatar

Joined: 01-Aug-2011
Location: PH
Posts: 133 		Post Options Post Options   Quote Vonzkie Quote  Post ReplyReply Direct Link To This Post 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
Back to Top
sbelini View Drop Down
IdeaBlade
IdeaBlade
Avatar

Joined: 13-Aug-2010
Location: Oakland
Posts: 786 		Post Options Post Options   Quote sbelini Quote  Post ReplyReply Direct Link To This Post Posted: 19-Oct-2012 at 12:11pm
Von,
 
I'll try to reproduce the behavior here so we can further investigate.
 
Silvio.
Back to Top
Vonzkie View Drop Down
Senior Member
Senior Member
Avatar

Joined: 01-Aug-2011
Location: PH
Posts: 133 		Post Options Post Options   Quote Vonzkie Quote  Post ReplyReply Direct Link To This Post 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
Back to Top
sbelini View Drop Down
IdeaBlade
IdeaBlade
Avatar

Joined: 13-Aug-2010
Location: Oakland
Posts: 786 		Post Options Post Options   Quote sbelini Quote  Post ReplyReply Direct Link To This Post 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.
 
Back to Top
Vonzkie View Drop Down
Senior Member
Senior Member
Avatar

Joined: 01-Aug-2011
Location: PH
Posts: 133 		Post Options Post Options   Quote Vonzkie Quote  Post ReplyReply Direct Link To This Post Posted: 15-Oct-2012 at 3:46am
Hi Guys,

Just want to follow up this..

Thanks,
Von
Back to Top
Vonzkie View Drop Down
Senior Member
Senior Member
Avatar

Joined: 01-Aug-2011
Location: PH
Posts: 133 		Post Options Post Options   Quote Vonzkie Quote  Post ReplyReply Direct Link To This Post 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
Back to Top
 Post Reply Post Reply

Forum Jump Forum Permissions View Drop Down