| Groups | Blog | Home |
sql server reporting services : Large data sets - memory error, break up the dataset?
Hello,
I have read through several posts here, and I think I have some partial answers. Here's my problem. I have a report that needs to be run at the end of each month (for an insurance company). The amount of data returned by December is about 1 gig. Most machines can't handle this, ASP.NET recycles when you reach 60 percent of total memory, so ASP recycles in the middle of the report. Now, I saw someone said you can render a report directly to Excel or some other format and not use the report manager for the UI. Can someone tell me how this is done, I think that might help. The second thing that could help is to break up the data set and link the data sets in the report. I have about 1 million rows of data, but it is a fact table, and has 4 numbers I need, and about 8 id's I need, so about 50 bytes of data per row (so 50 mg). However, I need the English description for the ids, and if I join that on the SQL box, each row becomes 2 k instead which brings me over a gig. Is there a way I can return a separate dataset with the English names (I know I can do that, and I have) AND get to that information based on the main dataset field information. Any help would be great. Thanks! Chris
I was a bit hesitant to ask this question, but someone else would have, I
think. How many lines are there on the report? A million may be a bit overwhelming in the end, and you may not want to print it either. Do you process the incoming data and generate a report, some type of a summary, that has a few hundred or a couple of thousand lines maybe? Hopefully this is the case, then the work must be done on the SQL Server and it returns only the processed data for the report. In this case the select statement can have some "Group By" clauses, or use a stored procedure to massage the data in a temp table on the server and return it from there. If this is the case then I may be able to give you some pointers. [quoted text, click to view] "Chris Hastings" <chrshstngs@comcast.net> wrote in message news:To2dnQIdecSmy5vfRVn-jQ@comcast.com... > Hello, > I have read through several posts here, and I think I have some partial > answers. > Here's my problem. I have a report that needs to be run at the end of > each month (for an insurance company). The amount of data returned by > December is about 1 gig. Most machines can't handle this, ASP.NET > recycles when you reach 60 percent of total memory, so ASP recycles in the > middle of the report. > Now, I saw someone said you can render a report directly to Excel or some > other format and not use the report manager for the UI. Can someone tell > me how this is done, I think that might help. > The second thing that could help is to break up the data set and link the > data sets in the report. I have about 1 million rows of data, but it is a > fact table, and has 4 numbers I need, and about 8 id's I need, so about 50 > bytes of data per row (so 50 mg). However, I need the English description > for the ids, and if I join that on the SQL box, each row becomes 2 k > instead which brings me over a gig. Is there a way I can return a > separate dataset with the English names (I know I can do that, and I have) > AND get to that information based on the main dataset field information. > Any help would be great. > Thanks! > Chris >
I agree. Reporting Services should only handle the final formatting,
summary, grouping etc. Most of the work should be done in SQL Server. Reporting Services does a lot (most/all) of rendering in memory so if you have very large amount of data it can totally bog down. Reports are for humans and humans do not read 1 million rows of data. If the intent is for it to go into some other program then use DTS or some other method. -- Bruce Loehle-Conger MVP SQL Server Reporting Services [quoted text, click to view] "Chris Botha" <chris_s_botha@.AT.hotmail.com> wrote in message news:OqPeQkHDFHA.3416@TK2MSFTNGP09.phx.gbl... > I was a bit hesitant to ask this question, but someone else would have, I > think. How many lines are there on the report? A million may be a bit > overwhelming in the end, and you may not want to print it either. > Do you process the incoming data and generate a report, some type of a > summary, that has a few hundred or a couple of thousand lines maybe? > Hopefully this is the case, then the work must be done on the SQL Server and > it returns only the processed data for the report. In this case the select > statement can have some "Group By" clauses, or use a stored procedure to > massage the data in a temp table on the server and return it from there. If > this is the case then I may be able to give you some pointers. > > "Chris Hastings" <chrshstngs@comcast.net> wrote in message > news:To2dnQIdecSmy5vfRVn-jQ@comcast.com... > > Hello, > > I have read through several posts here, and I think I have some partial > > answers. > > Here's my problem. I have a report that needs to be run at the end of > > each month (for an insurance company). The amount of data returned by > > December is about 1 gig. Most machines can't handle this, ASP.NET > > recycles when you reach 60 percent of total memory, so ASP recycles in the > > middle of the report. > > Now, I saw someone said you can render a report directly to Excel or some > > other format and not use the report manager for the UI. Can someone tell > > me how this is done, I think that might help. > > The second thing that could help is to break up the data set and link the > > data sets in the report. I have about 1 million rows of data, but it is a > > fact table, and has 4 numbers I need, and about 8 id's I need, so about 50 > > bytes of data per row (so 50 mg). However, I need the English description > > for the ids, and if I join that on the SQL box, each row becomes 2 k > > instead which brings me over a gig. Is there a way I can return a > > separate dataset with the English names (I know I can do that, and I have) > > AND get to that information based on the main dataset field information. > > Any help would be great. > > Thanks! > > Chris > > > >
Thank you for the responses.
You are right, I need to adjust the model for our reports. In reality, I only need the summary data, so I will place the burden on SQL with stored Procs. [quoted text, click to view] "BruceLC" <brucelc@newsgroup.nospam> wrote in message news:OjPhcvRDFHA.3452@TK2MSFTNGP09.phx.gbl... >I agree. Reporting Services should only handle the final formatting, > summary, grouping etc. Most of the work should be done in SQL Server. > Reporting Services does a lot (most/all) of rendering in memory so if you > have very large amount of data it can totally bog down. Reports are for > humans and humans do not read 1 million rows of data. If the intent is for > it to go into some other program then use DTS or some other method. > > -- > Bruce Loehle-Conger > MVP SQL Server Reporting Services > > > "Chris Botha" <chris_s_botha@.AT.hotmail.com> wrote in message > news:OqPeQkHDFHA.3416@TK2MSFTNGP09.phx.gbl... >> I was a bit hesitant to ask this question, but someone else would have, I >> think. How many lines are there on the report? A million may be a bit >> overwhelming in the end, and you may not want to print it either. >> Do you process the incoming data and generate a report, some type of a >> summary, that has a few hundred or a couple of thousand lines maybe? >> Hopefully this is the case, then the work must be done on the SQL Server > and >> it returns only the processed data for the report. In this case the >> select >> statement can have some "Group By" clauses, or use a stored procedure to >> massage the data in a temp table on the server and return it from there. > If >> this is the case then I may be able to give you some pointers. >> >> "Chris Hastings" <chrshstngs@comcast.net> wrote in message >> news:To2dnQIdecSmy5vfRVn-jQ@comcast.com... >> > Hello, >> > I have read through several posts here, and I think I have some partial >> > answers. >> > Here's my problem. I have a report that needs to be run at the end of >> > each month (for an insurance company). The amount of data returned by >> > December is about 1 gig. Most machines can't handle this, ASP.NET >> > recycles when you reach 60 percent of total memory, so ASP recycles in > the >> > middle of the report. >> > Now, I saw someone said you can render a report directly to Excel or > some >> > other format and not use the report manager for the UI. Can someone > tell >> > me how this is done, I think that might help. >> > The second thing that could help is to break up the data set and link > the >> > data sets in the report. I have about 1 million rows of data, but it >> > is > a >> > fact table, and has 4 numbers I need, and about 8 id's I need, so about > 50 >> > bytes of data per row (so 50 mg). However, I need the English > description >> > for the ids, and if I join that on the SQL box, each row becomes 2 k >> > instead which brings me over a gig. Is there a way I can return a >> > separate dataset with the English names (I know I can do that, and I > have) >> > AND get to that information based on the main dataset field >> > information. >> > Any help would be great. >> > Thanks! >> > Chris >> > >> >> > >
Don't see what you're looking for? Try a search.
|
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
| home · blog · groups | Questions? Comments? Contact the dn |