Charts slow report rendering to a crawl

I've searched the forums on this issue, haven't really found the answer.

I have several nifty little sales reports which crunch a ton of data quite efficiently and render in just a few seconds in Report Manager. I've pushed as much of the data processing back to the server as possible, use a stored procedure (with parameters) in a shared datasource, don't return unneccessary data, all that. It works great.

When I first developed the reports, I continued generating my charts (which use the same data as the reports, just grouped differently) in Excel and pasting them in as images. Now I want to stop that nonsense and use the SSRS charts. I fooled around with the charting function and got a reasonable facimile of my Excel charts, two per report, which use their own separate stored procedures and the same shared datasource.

Now, reports that used to render in 5-8 seconds may take 1-5 MINUTES. Help! It's definitely the charts--taking them back out fixes the problem.

I have complete control over the datasources--would it make more sense to use non-shared sources, or to create totally separate shared sources? I saw a post that recommended "making data calls non-synchronous," but I have no idea how to do that.

Thanks for any suggestions.

On further investigation, it appears that deleting EITHER ONE of the charts brings the rendering time down almost to the same time as no chart at all. It's apparent that having MULTIPLE charts on a page multiplies the rendering time exponentially (I'm gonna tell Edward Tufte!)

This happens whether I put the charts side-by-side (preferred) or one above the other on the page--they just take FOREVER to render.

Anybody...?

|||

I know this post is a bit old but im having exactly the same problem.

I have a report that has around 150 charts on it once expanded and it is taking around 3 seconds to load each chart.

Is there a resolution for this?

Regards

Will

Charts slow report rendering to a crawl

I've searched the forums on this issue, haven't really found the answer.

I have several nifty little sales reports which crunch a ton of data quite efficiently and render in just a few seconds in Report Manager. I've pushed as much of the data processing back to the server as possible, use a stored procedure (with parameters) in a shared datasource, don't return unneccessary data, all that. It works great.

When I first developed the reports, I continued generating my charts (which use the same data as the reports, just grouped differently) in Excel and pasting them in as images. Now I want to stop that nonsense and use the SSRS charts. I fooled around with the charting function and got a reasonable facimile of my Excel charts, two per report, which use their own separate stored procedures and the same shared datasource.

Now, reports that used to render in 5-8 seconds may take 1-5 MINUTES. Help! It's definitely the charts--taking them back out fixes the problem.

I have complete control over the datasources--would it make more sense to use non-shared sources, or to create totally separate shared sources? I saw a post that recommended "making data calls non-synchronous," but I have no idea how to do that.

Thanks for any suggestions.

On further investigation, it appears that deleting EITHER ONE of the charts brings the rendering time down almost to the same time as no chart at all. It's apparent that having MULTIPLE charts on a page multiplies the rendering time exponentially (I'm gonna tell Edward Tufte!)

This happens whether I put the charts side-by-side (preferred) or one above the other on the page--they just take FOREVER to render.

Anybody...?

Character Removal

Hello all,

I've been struggling with an interesting problem. I currently have a solution but it is very slow.

I will be cycling data through a table. Each cycle has 1 million records with 60 fields. One procedure I need to perform on this data is a character cleanse. I have a list of 12 characters that need to be removed.

Right now I have a stored procedure that pulls the characters from a table one at a time. It feeds it to a nested loop that replaces the character with nothing ('') on records that contain the character (something like "update tbl1 set FIELD = replace(FIELD, '&', '') where Field like '%&%'"). This works... but seems rather inefficient. It can take 10 minutes to do a 250,000 record table.

I have tried borrowing regular expressions from VBscript using com objects, it worked and seemed more efficient at first but then I threw a large file at it and it took a half hour to complete.

Im running SQL 2005 on a dual Xeon 3.4 box with 2 gb of ram.

Any advice would be greatly appreciated!!

~~~Thanks~~

The question is, is it taking so much time looking for the character or actually updating the field.

If your bottleneck is on the searching, the way to fix that is to create a full-text index.

Then, I would also change your statement to:

update tbl1
set field = replace(replace(replace(field,'&',''),'%',''),'$','') etc
where contains(field,' "&" OR "$" OR "%" ') -- etc

|||

Thanks for the suggestions, I'll do some rewrite and see if it improves.

Question, wouldn't creating this index slow down importing to the table and performing updates (which are the two main reasons that this database exists)? I guess what I'm worried about is simply spreading out the performance problem to other procedures.

Thanks again

|||

Can you give me a bit more information: Of the 60 fields, how many are you checking to update? Of the million records in each cycle, how many typically need updating? how many "bad" characters do you anticipate in 1 year...3 years?

|||

The table actually consists of 170 fields. We will load a file that will have up to 60 fields that require cleaning. A typical file that is cleansed has about 10% of the cells that need updating from the character cleanse procedure. However I would say that during the whole process we run on it, every row will be updated at least one time, sometimes multiple times.

As for how many bad characters in 1 year, I would say billions of bad character instances.

The 60 fields that require updating have been narrowed down by a view from the 170. Right now the stored procedure steps through whatever columns are in the view.

|||

Complex mathematic and string manipulation are always sql weak points. CLR was introduced in sql2k5 to remedy that. Since you're on sql2k5, you should definitely look into CLR Regex stored procedure.

This article should help.

http://msdn.microsoft.com/msdnmag/issues/07/02/SQLRegex/default.aspx