Stopping reports from running hundreds of individual queries

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.



Edited by agrogers - 23-Jun-2010 at 6:05am