System.OutOfMemoryException

Hello,

I have SQL report pull a data via SProc from 1 table,
That report has 6 columns and 3 row groups.
It works fine when filter for 3 day but when change the filter 7 days or more I got error message System.OutOfMemoryException.
If I run SProc with same date scope, works fine and retrieve more than 1 million rows.

Thanks
SanPrgAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Anthony PerkinsCommented:
Would you like to post the Stored Procedure?  This way we may be able to make suggestion that make the it more efficient.
0
SanPrgAuthor Commented:
Here a go,
CREATE PROCEDURE [dbo].[rptParm]
@Begin datetime
,@End datetime      
AS
BEGIN
      SET NOCOUNT ON;
SELECT
    cast([Time] as date) [Date]
    ,[Time]
      ,SUBSTRING([Path],CHARINDEX('/',Path,1)+1,255) Path
      ,[Note]
      ,[Status]
      ,[Name]
   ,SUBSTRING([Client],CHARINDEX('\',[Client],1)+1,255) [Client]
  FROM [DWClient]
  where cast([Time] as date) between @Begin and @End
  Order by [Time],SUBSTRING([Client],CHARINDEX('\',[Client],1)+1,255)
END

GO
0
Anthony PerkinsCommented:
If [Time] is not a date or does not have the format "YYYYMMDD" then I am afraid no amount of indexes are going to help you here.   The best you can do is create a persisted computed column with a value of CAST([Time] as date) and call it [Date2]  You can then index that computed column and modify your Stored Procedure as follows:
CREATE PROCEDURE [dbo].[rptParm]
    @Begin datetime,
    @End datetime
AS
SET NOCOUNT ON;

DECLARE @BeginDate date = CAST(@Begin AS date),
		@EndDate date = CAST(@End AS date)
   
SELECT  CAST([Time] AS date) [Date],
        [Time],
        SUBSTRING([Path], CHARINDEX('/', Path, 1) + 1, 255) Path,
        [Note],
        [Status],
        [Name],
        SUBSTRING([Client], CHARINDEX('\', [Client], 1) + 1, 255) [Client]
FROM    [DWClient]
WHERE   Date2 BETWEEN @BeginDate AND @EndDate
ORDER BY [Time],
        SUBSTRING([Client], CHARINDEX('\', [Client], 1) + 1, 255) 
GO

Open in new window

0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

Anthony PerkinsCommented:
You can also add another persisted computed column called Client2 as follows:
SUBSTRING([Client], CHARINDEX('\', [Client], 1) + 1, 255)  
And index it.

Your Stored Procedure should then look like this:
CREATE PROCEDURE [dbo].[rptParm]
    @Begin datetime,
    @End datetime
AS
SET NOCOUNT ON;

DECLARE @BeginDate date = CAST(@Begin AS date),
		@EndDate date = CAST(@End AS date)
   
SELECT  CAST([Time] AS date) [Date],
        [Time],
        SUBSTRING([Path], CHARINDEX('/', Path, 1) + 1, 255) Path,
        [Note],
        [Status],
        [Name],
        Client2 [Client]
FROM    [DWClient]
WHERE   Date2 BETWEEN @BeginDate AND @EndDate
ORDER BY [Time],
        Client2
GO

Open in new window

0
DcpKingCommented:
You have a system here that has data at one end and a report at the other. The report has a tiny amount of data (but lots of information) and the database has a huge amount of data.

Somewhere in the middle (the stored procedure and SSRS) the data is getting aggregated and reduced so that you get information out. If you do more of that in the stored procedure then the server will be doing the work. If you shift the load out to the SSRS system then it'll be the machine running SSRS that does the work (and the rendering, etc.). It looks like you should be doing more in the stored procedure part, rather than dumping a million rows onto SSRS!

hth

Mike
0
SanPrgAuthor Commented:
Guys sorry for delay, I delete the index "Order by" from SProc and I removed all aggregation from the report but still I got same error message.
I found article online http://msdn.microsoft.com/en-us/library/ms159206.aspx
and I changed some configuration in RSReportServer.config but still same problem.

what's the scenario for SSRS with a huge data like a billion rows?
Really I need answer for this question, I hope you can help me.
Thanks
0
SanPrgAuthor Commented:
Thanks SouthMod.
0
Anthony PerkinsCommented:
Really I need answer for this question, I hope you can help me.
I am sorry you did not like my solution.

Best of luck.
0
SanPrgAuthor Commented:
acperkins,
"I am sorry you did not like my solution."
 do like or do not like, Did you test your "solution" with a billion rows in SQL Report,
I am sorry I tested your solution and I got same error message.
Thanks for your effort
0
Anthony PerkinsCommented:
Very nice.  Thank you.
0
DcpKingCommented:
SanPrg,

I would say that a million rows was too much for SSRS. As before, I would strongly suggest that you move the work on the data back into the stored procedure rather than have SSRS try to handle it, which it obviously can't!

hth

Mike
0
SanPrgAuthor Commented:
Mike,
As I said before, I removed  all aggregations just for test and the report works for 7 or 10 days but mot more, I agree with I have a huge but I need to know

Does SSRS  work with a huge data?
what's the scenario for SSRS with a huge data like a billion rows?


Thanks Mike
0
DcpKingCommented:
Personally, I've experienced problems with using a tablix for pivoting data when giving it more than about 30,000 records. There's a pub from Microsoft here that explains it a bit. Someone else has also had your problem - see here and look at the last reply which, I think, gives the best suggestions.

BTW, where is SSRS running? If it's on your local machine then all rendering will be done there, which may be contributing to you hitting a limit.

Mike
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
SanPrgAuthor Commented:
Mike,
Your links have a good information,

Thanks for all of you.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.