SSRS rendering limits?

Question

SSRS rendering limits?

jcrawf02

SSC-Insane

Points: 24198
More actions
July 2, 2010 at 7:36 am

#224365

I have a view for a mailing list, pulls roughly 236k rows of data, SELECT * from that view runs in about 30 seconds in SSMS, when I pull it into SSRS and don't massage it in any way, I get a System.outofMemoryException?
Am I just trying to return too much data to be rendered effectively?
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 1

jcrawf02 (7/2/2010)
I have a view for a mailing list, pulls roughly 236k rows of data, SELECT * from that view runs in about 30 seconds in SSMS, when I pull it into SSRS and don't massage it in any way, I get a System.outofMemoryException?
Am I just trying to return too much data to be rendered effectively?

Yeah... and it's one of the reasons I don't like SSRS or many other reporting programs...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

jcrawf02 SSC-Insane Points: 24198 More actions · Answer 2

humph. Well, that's just annoying. Thanks for the response Jeff.

---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]

"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 3

jcrawf02 (7/2/2010)
humph. Well, that's just annoying. Thanks for the response Jeff.

Agh... sorry about the short response, Jon. I just get ticked off at all these wonder "magical" GUI's that are supposed to solve the problems of the world. 😛

For the thing you're trying to do, you'll either need to setup individual more restrictive reports or take it to the next level... create a data ware house using SSAS. I've not actually done one myself but, from what I've seen, it's not too bad and it seems to have all the flexibility you may need for drill downs and the like. Heh... done correctly, it even seems to be effective for a GUI... 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

jcrawf02 SSC-Insane Points: 24198 More actions · Answer 4

Thanks Jeff, don't want you to think I was irritated with you.

I'm just trying to allow the users to run their own friggin' mailing list, rather than requesting it of our team every time. They'll probably be mailing it out to every member if they mail one, so it would need to be the full list (246k+) every time they run it.

Just seems like a simple stupid request, irritating that it can't be handled in SSRS when it's such a straigh-forward task of retrieval.

Oh well, good to know the limitations of your tools, I guess.

And I'm not going to ask how you know SSAS makes a good GUI... 😛

---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]

"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 5

Heh... not to worry. Didn't take it the wrong way. I was truly sorry for being short on the subject.

So far as SSAS being a "good GUI".... it's not from personal use. I saw a dog'n'pony on it once. It seemed pretty easy to do what was done but we all know how canned demos are. If I had my druthers, though, it would all also be easily available to do from T-SQL.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Steven.Howes SSCertifiable Points: 6230 More actions · Answer 6

If it's just a mailing list (I assume for a mail merge), could you set up a Custom URL to export it directly using &rs:Format=CSV or &rs:Format=Excel?

gayathridevi.msit Ten Centuries Points: 1294 More actions · Answer 7

Hi,

Just try this and let me know if it works..

1) In your dataset.. EDIT ->ATASOURCE -> EDIT -> CONNECTION STRING -> EDIT ->ADVANCED ->CONNECT TIME OUT -> 0..

2) Another way just deploy your report and view it in IE.. some times SSRS Preview wont work...

Regards,
Gayathri 🙂

jcrawf02 SSC-Insane Points: 24198 More actions · Answer 8

stevejunk-667456 (7/7/2010)
If it's just a mailing list (I assume for a mail merge), could you set up a Custom URL to export it directly using &rs:Format=CSV or &rs:Format=Excel?

I'll give that a shot, you're correct about the mail merge.

---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]

"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."

jcrawf02 SSC-Insane Points: 24198 More actions · Answer 9

gayathridevi.msit (7/12/2010)
Hi,
Just try this and let me know if it works..
1) In your dataset.. EDIT ->ATASOURCE -> EDIT -> CONNECTION STRING -> EDIT ->ADVANCED ->CONNECT TIME OUT -> 0..
2) Another way just deploy your report and view it in IE.. some times SSRS Preview wont work...

1 did not work (connection timeout), 2 let me view it in html, but I can't export it to anything without dying a horrible death.

---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]

"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."

gayathridevi.msit Ten Centuries Points: 1294 More actions · Answer 10

Hi,

U just deploy the report and view it in internet explorer (report server) ...

Regards,
Gayathri 🙂

jcrawf02 SSC-Insane Points: 24198 More actions · Answer 11

gayathridevi.msit (7/12/2010)
Hi,
U just deploy the report and view it in internet explorer (report server) ...

yeah, that's not helpful when they need to mail things out based on the report results.

Thanks though,

Jon

---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]

"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."

Bill Scott SSCrazy Points: 2338 More actions · Answer 12

It seems to me that you are using the wrong hammer. can you set up a SSIS job to dump the list nightly to a share somewhere? then their program can pick it up from there and send out the email.