asked
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
Depending on how much memory you have in your machine, I can imagine that could be problematic - the final formatted report could be several times larger than the data set behind it. Is it possible to set something up that only displays a subset of the final report at any given moment?
Do you get the error when you run the report or while you are designing it? When you export it, how bug is the CSV that comes out?
Thanks for the response! This occurs when you attempt to run or preview the report (with no export currently) It displays the design on screen, but when the "go" button is pushed it will run for a period of time (haven't timed it yet) and then it produces the screen with the "System.OutofMemoryException". Currently the .csv file that is produced for this query is about 214 MB.
I appreciate all the input on this. Below is the solution I came up with and used drilldown USE LOSS_RATIOS declare @i int declare @Str varchar(10) SET @i=1 WHILE @i < 14 BEGIN SELECT @Str = 'R0' SELECT @Str = @Str + case when @i<10 then '0'+cast(@i as varchar(2)) else cast(@i as varchar(2)) END SELECT @str=@str + '%' SELECT convert(varchar(10),MonthEndDate,101) AS MED, 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 Loss_Ratios.dbo.Auto_LR_Earned_Unearned Auto_LR_Earned_Unearned WHERE (Policy Like @str)AND Monthenddate = (Select max(MonthEndDate) from Auto_LR_Earned_Unearned ) ORDER BY Policy SET @i=@i+1 END
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 appreciate your response. We are trying to get away from the spreadsheets if at all possible, but I will keep that as an alternative if I continue to struggle with this or cannot get our customer to rein in their requirements just a bit. I agree with you on the size. As I stated above in the previous post, I was concerned with size from the "get go" and I am sure they are not perusing the entire spreadsheet currently. As I have stated previously, I am a bit rusty with Reporting Services and have not had a lot of experience with using report parameters within the context of the report, but I am coachable if not anything else. Thanks again
Report Parameters can be used to filter the report's data. Basically you add a parameter to the report and then use it in the main dataset's WHERE clause. There are several articles available on that topic if you search for something like 'ssrs parameters'. If you have some specific questions though, I'll be glad to guide you!
You can try to limit the number of columns returned by the query to reduce the size.
That was my first question to the functional users. According to their team, the columns in the query are "must-have". I explained to them that what they were requesting would be extremly wide and large, But I am trying to work within the context and confines that my customer has requested. I know there is always more than one way to skin the perverbial cat and that may include having to ask my customer to curtail their request a bit.
Keep in mind that Excel has a limit of 65536 rows...
As of Excel 2007, that's no longer an issue. Limit is now over 1 million.
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.
16 People are following this question.
