SSRS 2008 R2 - Report Builder 3.0 - "System.OutofMemoryException"
I am attempting to build a report for our Treasury Department using Report-Builder 3.0 and SSRS 2008 r2, but keep hitting the Exception Error "System.OutOfMemoryException" -- The Data Source is a SQL Server 2000 database -- The query is very straight forward: >SELECT convert(varchar(10),MonthEndDate,101), Policy, Coverage, LimitDeductible, WrittenPremium, UnearnedPremium, EarnedPremium, InforcePremium, VehicleYear, VehicleMake, County, AgencyNo, VehiclePoints, VehicleSymbol,VehicleBodyCode, VehicleDiscount1, VehicleDiscount2, VehicleDiscount3, VehicleUseCode, AddressZipCode, VehicleGarageZip, RatingTier, OriginalInceptionDate, EffectiveDate1, ExpirationDate1,NewBusinessDiscount,GoodPayDiscount,MultiPolicyDiscount,FullPayDiscount, DriverGender,DriverMaritalStatus,DriverAge,DriverDiscount1, DriverDiscount2,DriverDiscount3,DriverDiscount4, DriverDiscount5,DriverPoints,VehicleVINNumber >FROM AutoLREarned_Unearned >WHERE Policy Like 'R0%' and Monthenddate = (Select max(MonthEndDate) from AutoLREarned_Unearned ) >ORDER BY Policy The table this is running against has 23 million rows in it and returns a result set of 956,360 rows in a little over 2 minutes when run from a query window. This index is currently applied: >CREATE NONCLUSTERED INDEX [IXAutoLREarnedUnearned] ON [dbo].[AutoLREarnedUnearned] ( [MonthEndDate] ASC, [Policy] ASC )WITH (PADINDEX = OFF, STATISTICSNORECOMPUTE = OFF, SORTINTEMPDB = OFF, IGNOREDUPKEY = OFF, DROPEXISTING = OFF, ONLINE = OFF, ALLOWROWLOCKS = ON, ALLOWPAGELOCKS = ON) ON [PRIMARY] I would like to publish this report to our company intranet so that the Treasury Department can go out and look at it as they need to. Currently we spend an inordinate amount of time running a DTS package to pull and transform the data to *.csv files and then run a VB macro in the spreadsheet to import the data into the appropriate sheets and then we move the spreadsheet to a file share. It has been a while (about 3 years)since I have worked with reporting services and would like to get some input as to what I need to change, fix, stop doing to make this a viable tool for the company
A report with just under 1 million rows doesn't really sound useful to me. I mean, no one is going to examine all those rows separately, right? And that's also the reason for the out of memory exception. To be able to continue development, you should try to make the dataset smaller. One option is using the TOP clause, such as TOP 1000. Assuming people need to be able to analyze the data in that report, perhaps it's an idea to introduce some filters (report parameters) on the report? If you give them a default value which limits the result set to something workable, it should solve your issue. Another path, based on your current situation, which you may possibly examine is using Excel instead of a report. If you set up a view based on your query, you can connect to it from Excel and then users can use the Excel filter functionality to analyze the data.
I think I would introduce them to PowerPivot and let them construct the spreadsheet to their own requirements. it will easily eat 23 million rows and let them drill into the data at will. That, or get to building an OLAP cube. If they like Excel leave them in Excel, it's just a case of letting them have a method of accessing it quickly and simply.