Stopping reports from running hundreds of individual queries
Printed From: IdeaBlade
Category: DevForce
Forum Name: DevForce Classic
Forum Discription: For .NET 2.0
URL: http://www.ideablade.com/forum/forum_posts.asp?TID=1909
Printed Date: 23-Apr-2025 at 10:13am
Topic: Stopping reports from running hundreds of individual queries
Posted By: agrogers
Subject: Stopping reports from running hundreds of individual queries
Date Posted: 23-Jun-2010 at 6:04am
This issue seems like one that would be pretty common. We have created a report that is bound to a simple Entity list. The report also displays data in related tables.
The initial attempt at this report meant that for each row in the simple entity list, two corresponding queries were sent off to the DB to get the related information. An entity list of 1000 rows meant one DB query for the initial list of entities and 2000 queries for the related data.
That can be worked around by issuing a span query to retrieve all the entities we need. So now all objects are in the cache. Sadly though the report still issues individual queries because (I assume) the individual queries dont match any query in the query cache.
That can we worked around by changing the fetch strategy to CacheOnly while the report is generating and turn it back to CacheThenDatasource when it is finished. That works fine because we know we have all the objects in the cache we need. Our report slowness problem is solved. Kind of.
The report has now displayed efficiently. But i want to adjust the margin (we are using DevExpress xtraReport control). I adjust the margin which fires off a data requery and, lo, 2000 individual queries get sent off to the DB because the Fetch strategy has been returned to CacheThenDatasource and none of those 2000 queries are in the query cache. Who would have guessed a margin change would cause so many database queries :)
We must be missing something here. How can we tell a report (or form for that matter) that we never want it to go to the server to get data? We can do that easily for queries we issue our self. But these queries are not under our control.
Or how we can construct the report so that it has no need to send off individual queries to get related data?
Thanks
Andrew
PS: The issue here is that the individual queries being fired are not on the primary key which means that the Primary Key lookup part of the CacheTheDatasource fetch strategy doesn’t help us.
|
Replies:
Posted By: GregD
Date Posted: 23-Jun-2010 at 12:36pm
I'm not sure why the XtraReport control does a requery when you adjust the margin, or whether that behavior can be suppressed - those would be questions for DevExpress.
If you can't set the DefaultQueryStrategy for the EntityManager to CacheOnly and leave it that way while the report control is displayed (because other operations are using it and need a different QueryStrategy), then you might consider collecting your report data into a separate EntityManager instance where you can do that.
|
|