Hi Everyone
I would like to know if there is a simple way to enhance the performance on an SSRS report that uses a data provider, and heaps of data, (say anything between 300 000 and a million records that get written to the temp table). Some of the data is retrieved from display methods.
I have this scenario:
Table a;
while select a
{
tmp.field1 = a.field1;
tmp.field2 = a.getValue();
tmp.insert();
}
while select b
{
tmp.field1 = b.field1;
tmp.field2 = b.getValue();
tmp.insert();
}
Here is what I've discovered and tried:
- if the resulting tmp table gets over 300000 records inserted into it, it is the insert() part that slows everything down
- I tried to use a RecordInsertList but it doesn't appear to work on tmp tables (correct me if i'm wrong)
- I thought of trying to use insert_recordset, but I can't get around the display methods (I HAVE to use display methods)
- I'm trying to build one query now that consists of table A, and table B. The problem with this though is that on the report, I can't just reference 'field2' which I would do if I were using a temp table. Now I have to somehow reference a.getValue and b.getValue on the report itself, consolidated into 1 field
- My next thought was to use a union query (with field2) consisting of a view made from table A, and a view from table B (table A and table B do NOT have the same structure), but using a view like this means that I'd have to use a computed column to retrieve the values in getValue in each table, which pushes the sql calls up again, which slows the report down again.
Is there a simple way to do this that I'm missing? (Hope my explanation isn't too confusing)
Thank you!
