July 10, 2017 at 11:03 am
I have a stored procedure (sp) that returns six to ten rows of data with ten columns, displaying five of them in the report. All processing is done at the server - case statements and sums. The sp takes less than 1 second to run. When I create a SSRS report using the sp it takes over ten minutes to generate the report. There is one column of text and the other four are all decimal. I don't have any groups on the report, although I would like to add one for stock versus non-stock purchases with sums for each.
My initial sp returned all the rows and I let SSRS sum them and hid the detail. When that took forever to generate, I changed the sp to perform the summations. That did not help, it still takes forever to generate the report.
What makes this really weird is that sometimes it will generate quickly. Then if I make a change to the report, like a format to the numbers, or simply close Visual Studio and open it again it starts going really slow.
I'm at a loss here. Over the last four years I have build over 250 reports and never seen this behavior.
John
SQL Rebel without a Where Clause
SQL/Epicor/Prophet21/VISUAL/e.i.e.i.o.
July 10, 2017 at 11:26 am
Have you run a trace/XE session on the SQL Server to ensure that the query performance when called from SSRS is still as fast as SSMS? If you've never read Erland Sommarskog's Slow in the Application, Fast in SSMS article you should as it may explain some of what you are seeing.
Have you looked at network between SSRS and the SQL Server? You can see some of that by looking for ASYNC_NETWORK_IO waits on the SQL Server.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
July 10, 2017 at 11:35 am
You might also want to look at the ExecutionLog3 view to see where the time is being used - that can often give you an idea on where to start looking. Also check the additional info column in the view as that provides some other information on process times as well.
Sue
July 10, 2017 at 11:52 am
You also say you are doing this through visual studio. Have you tried closing visual studio, removing (or renaming) the .data file related to that report. If memory serves, the .data file is a cache of the table/SP data so repeated executions from within visual studio result in faster reports being presented.
Might want to look at your memory and CPU usage while loading the report.
There are a lot of things that could cause slowdowns, and the methods mentioned above are excellent places to look as well as check for blocking, deadlocks and check your wait stats.
With the SSRS reports that I work on, we don't have horrible rendering time with most reports. We do some data processing on the SQL side (stored procedure, view, etc) and some data processing on the report side (filtering results and query parameters). How wide are your rows? You say that there are 16 of them, but if you have 100 columns, it will be slow to render in SSRS I've found. The fewer columns you can have, the nicer that SSRS behaves.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
July 10, 2017 at 12:46 pm
Jack Corbett - Monday, July 10, 2017 11:26 AMHave you run a trace/XE session on the SQL Server to ensure that the query performance when called from SSRS is still as fast as SSMS? If you've never read Erland Sommarskog's Slow in the Application, Fast in SSMS article you should as it may explain some of what you are seeing.
I'd start with those as well, especially check the ARITHABORT settings of the sessions. You can see that in the trace/XE, or in sys.dm_exec_sessions.
Microsoft says you should always set ARITHABORT ON, even though it's not the default:
https://technet.microsoft.com/en-us/library/ms190306(v=sql.110).aspx#Anchor_1
You can make ARITHABORT default to ON by right clicking the instance in Object Explorer of Management Studio, selecting Properties, then go to the Connections page and check the "arithmetic abort" option. There's also a setting at the database level if you right click the database in Object Explorer and select Properties, then go to the Options page and set "Arithmetic Abort Enabled" to True.
July 11, 2017 at 6:19 am
Ya'll ready for this? Two of the fields I return are a description and a sort order for an enumerated field. Rather than put in case statements that may require maintenance later if the vendor adds a new enumeration I used one of several user defined tables set up by the vendor and stored both my description and sort numbers in this user defined table. When I removed the table and went back to case statements the SSRS report ran fast. I don't know if that is because the user defined tables are in a different schema or if it is because the table has 98 columns but only 14 rows or the table has five keys and I only used three. I'll have to spend a few minutes investigating that when I have some time. I suspect I should have used AND UD39.Key4 = '' AND UD39.Key5 = '' in my ON statement. In this system there are almost no nulls, only dates. Numbers all default to zero and character fields to blank character ''.
John
SQL Rebel without a Where Clause
SQL/Epicor/Prophet21/VISUAL/e.i.e.i.o.
July 11, 2017 at 8:39 am
TexasJohn - Tuesday, July 11, 2017 6:19 AMYa'll ready for this? Two of the fields I return are a description and a sort order for an enumerated field. Rather than put in case statements that may require maintenance later if the vendor adds a new enumeration I used one of several user defined tables set up by the vendor and stored both my description and sort numbers in this user defined table. When I removed the table and went back to case statements the SSRS report ran fast. I don't know if that is because the user defined tables are in a different schema or if it is because the table has 98 columns but only 14 rows or the table has five keys and I only used three. I'll have to spend a few minutes investigating that when I have some time. I suspect I should have used AND UD39.Key4 = '' AND UD39.Key5 = '' in my ON statement. In this system there are almost no nulls, only dates. Numbers all default to zero and character fields to blank character ''.
I'm glad you figured out how to make it faster, but that doesn't answer the original question of why it runs quick in SSMS but slow in SSRS.
I would be looking in profiler or extended events and look at the execution plan to see why the SP is slow when run from SSRS. I would imagine that the slowness is due to either bad indexes or join logic making everything turn into a table scan instead of an index seek. I've seen where taking the query with multiple joins and breaking it up into multiple temporary tables improved performance drastically. Or it could be memory pressure as SSRS operates outside of SQL Server memory space.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
July 11, 2017 at 9:04 am
bmg002 - Tuesday, July 11, 2017 8:39 AMI'm glad you figured out how to make it faster, but that doesn't answer the original question of why it runs quick in SSMS but slow in SSRS.
I would be looking in profiler or extended events and look at the execution plan to see why the SP is slow when run from SSRS. I would imagine that the slowness is due to either bad indexes or join logic making everything turn into a table scan instead of an index seek. I've seen where taking the query with multiple joins and breaking it up into multiple temporary tables improved performance drastically. Or it could be memory pressure as SSRS operates outside of SQL Server memory space.
Yup...if it's the stored procedure that's slow. I would guess that is the problem but I've seen other things cause this as well when rendering reports. I thought the difference was in the stored procedure execution time and the report generation time, not necessarily something that runs fast in SSMS and slow in SSRS. Report generation has several pieces to it, one of them being data retrieval. The Addition info column in the execution view has scalability times that can indicate a possible memory pressure issue. And memory usage in that column can indicate issues with memory as well.
I think it probably is the stored procedure but that's not guaranteed to be the issue. I've been burned a few times by assuming it's the stored procedure.
Sue
July 11, 2017 at 9:07 am
I will admit, often SSRS is at least a little slower at returning results than when running a query in SSMS. One of the biggest reasons is due to rendering. When running a query in SSMS a dataset is returned, and this is fairly easy for SSMS to show in a "simple" grid view or text. In SSRS, often your report is a little more "pretty" than a simple data view, and so this creates an overhead.
Adding expressions to your report, I've found, can also provide a much bigger overhead. If you have a large of them, this will certainly slow your report down. Like you seem to have experienced, a CASE expression in the SQL is a lot quicker than adding an expression for a column.
It's also worth noting that you were running the report in VS, which will be installed on your local machine. This means that the SQL server will do the work for getting the dataset, but that's all. Your PC will then do the rendering of the report, which includes any expressions for derived values. Your PC is going to have much lower specifications than your SQL server, so you should always expect lower performance when running locally.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply