Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion Groups
DB Engine
SQL ServerMSDESQL Server CE
Services
Analysis (Data Mining)Analysis (OLAP)DTSIntegration ServicesNotification ServicesReporting Services
Programming
CLRConnectivitySQLXML
Other Technologies
ClusteringEnglish QueryFull-Text SearchReplicationService Broker
General
Data WarehousingPerformanceSecuritySetupSQL Server ToolsOther SQL Server Topics
DirectoryUser Groups
Related Topics
MS AccessOther DB ProductsMS Server Products.NET DevelopmentVB DevelopmentJava DevelopmentMore Topics ...

SQL Server Forum / Services / Reporting Services / August 2006

Tip: Looking for answers? Try searching our database.

Charting a large number of values causes problems

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Martin Hinchy - 29 Aug 2006 02:58 GMT
I have created a SSRS 2000 report that charts application crashes on a daily
basis.

Most of the time this chart only displays a handful of values and renders
quite quickly. I had a situation recently though where the chart tried to
render 2100 values and the report took around 15 or 20 minutes to render.

A check of our database server has shown that the SQL query that returns the
data is executing in under a second. When I checked the Task Manager on the
web server I found that w3wp.exe was running at almost 100%.

I would have thought that 2100 data points was not an unreasonable number of
points to plot. Does anyone know if the rendering speed of charts has been
improved in SSRS 2005? Is it worth upgrading?

Signature

Regards
Martin

Wei Lu [MSFT] - 29 Aug 2006 08:30 GMT
Hello Martin,

Based on my experience, generally the SSRS 2005 render the charts as quick
as SSRS 2000. I would like to know whether this issue appeared on the
specify report or on all the report. From your description, this issue
seems to related with the report rendering. I would like to suggest you use
the Report Cache and the Report Snapshot as a workaround. The Report Cache
will cache the report in the tempdb and you may get higher performance.

Performance issues can be difficult to troubleshoot and resolve in a
newsgroup setting due to the number of variables and the amount of time
required to narrow down possible causes and observe the effects.  We will
assist as best as we can, but you may wish to consider contacting CSS for a
more timely resolution for these type issues.

To obtain the phone numbers for specific technology request please take a
look at the web site listed below.
http://support.microsoft.com/default.aspx?scid=fh;EN-US;PHONENUMBERS

If you are outside the US please see http://support.microsoft.com
for regional support phone numbers.

Thank you for your patience and understanding.

Sincerely,

Wei Lu

Microsoft Online Community Support

==================================================

Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.

Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.

==================================================
(This posting is provided "AS IS", with no warranties, and confers no
rights.)
Martin Hinchy - 30 Aug 2006 00:17 GMT
This issue occurs only on a specific report where the chart contains a large
number of values (2100).

I have cached the report and this has decreased the render time to 5
minutes. This is still pretty high but I suppose we will just have to live
with it.

Signature

Regards
Martin

> Hello Martin,
>
[quoted text clipped - 47 lines]
> (This posting is provided "AS IS", with no warranties, and confers no
> rights.)
Wei Lu [MSFT] - 30 Aug 2006 10:04 GMT
Hello Martin,

I am not sure why you have a large number of values in the chart.

Do you mean you have 2100 records in the database ?

Have you use any complex expression in the chart?

Sincerely,

Wei Lu

Microsoft Online Community Support
Martin Hinchy - 30 Aug 2006 22:29 GMT
When the report loads it requests a couple of parameters from the user (Year
and month). For Aug 2006 the SQL query just happens to return around 2100
records. The query executes in under a second. Two values are then selected
from each record and plotted as points on a line chart. There are no complex
expressions used in the chart.

Signature

Regards
Martin

> Hello Martin,
>
[quoted text clipped - 9 lines]
>
> Microsoft Online Community Support
Wei Lu [MSFT] - 31 Aug 2006 13:08 GMT
Hello Martin,

I have tested on my side. I use the AdventureWorks databases in a SQL 200
database and I add a Line Chart in the report.

The dataset will return about 12000 records and I use the Parameter in the
dataset filter. Then the chart will render in about 10 second either in the
VS design preview or in the Report Server.

Since you use the parameter in the report, I would like to suggest you use
the Parameter in the dataset filter to filter the data. So that, the
dataset will return all the data at the first time and if you change the
Parameter value, the report do not need to query the database again and
this will enhance the performance of the Chart Render.

Sincerely,

Wei Lu

Microsoft Online Community Support
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2009 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.