0

Referring to Fast query runs slow in SSRS my scenario is 1 row, 97 columns (I know a lot of columns - I can change the parameters to get more rows) also 9 parameters, I had a select statement calling a multi statement function so I tried moving to a proc but still calling the function made no difference, tried all the optimize options to avoid parameter sniffing, still makes no difference. The function or proc runs in 2 seconds in production a little slower in 2 other test environments. The SSRS report runs in about 6 to 40 seconds in the other environments, within Visual Studio like 40 seconds the first time you run then if you click the refresh button 6 seconds however PROD 20 to 30 mins yes heard me right minutes.

I know some of you are going to say you idiot reduce your number of columns, move your code out of the function into the proc (I would like to but the function is shared across other reports) or upgrade your SSRS and Visual Studio (it is coming trust me in was recorded in Jan 2012 in the things to do log book). Barring those suggestions anything else I can try?

Charlieface
  • 52,284
  • 6
  • 19
  • 43
Glen
  • 802
  • 1
  • 11
  • 27
  • 1
    Have you checked the execution log to see which part of the report generation is actually taking the time? – Alan Schofield Apr 04 '22 at 08:24
  • I am running my tests within Visual Studio so I am by passing the Report Manager, so if I just open the query designer to run SQL proc it takes many minutes to run, same code in SSMS 2 seconds. The code is a proc that calls an inline function that calls a multi-statement function that in turn calls another multi-statement function that also calls other scalar functions and joins and outer apply's etc but it runs in seconds in SSMS!!! And it worked since the last change and the new change was tested so y now does it run slow - some threshold reached perhaps so it exponentially grows. – Glen Apr 04 '22 at 09:01
  • As I said Developer environments no issue so I thought it maybe some transactional thingy happening in production that is different but it runs in SSMS in seconds! I keep harping on about that. – Glen Apr 04 '22 at 09:06
  • 1
    For performance questions, we need at a *minimum* the actual query you are doing, its execution plan, and relevant tables and indexes. Please share the query plan via https://brentozar.com/pastetheplan. This question is not answerable otherwise – Charlieface Apr 04 '22 at 09:12
  • Query plan for a performance issue is a good idea but there is no performance issue it runs in 2 seconds. I am thinking it is a threshold thing still, limitation of SSRS. I will try to reduce the number of columns by 10 to see if it works but will keep you idea in mind, thanks – Glen Apr 04 '22 at 09:28
  • 1
    97 columns is not a lot. If, in SSMS, you execute `SET ARITHABORT OFF; YourSelectStatementHere` does the performance go pear-shaped? ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS and ARITHABORT are all plan-affecting options, meaning that the query optimizer can come up with radically different plans depending on their settings. SSMS usually defaults to ARITHABORT ON, everything else to ARITHABORT OFF. – AlwaysLearning Apr 04 '22 at 09:31
  • Also, have you considered rewriting your multi-statement table-valued function to be an inline tabled-valued function? It would give query optimizer a better chance to work with it, excluding code and columns that aren't required for the immediate query. – AlwaysLearning Apr 04 '22 at 09:32
  • @AlwaysLearning the SET ARITHABORT OFF does make it go pear shaped so I as a humble developer if I am on to something here - then where and whom do I pay the check to – Glen Apr 04 '22 at 09:37
  • Yes I was 10% into turning the 1500 line function into inline then got distracted, I may have to keep going – Glen Apr 04 '22 at 09:38
  • I am not going to fight over as Charlieface said execution plan and may have been thinking the same so @AlwaysLearning I put SET ARITHABORT ON; in my proc and it returns in seconds please provide that as your answer. You are a genius, thank you so much. – Glen Apr 04 '22 at 09:50
  • @AlwaysLearning While `ARITHABORT` can cause a recompile, contrary to popular opinion it does not *by itself* cause poor plans. It just causes parameter sniffing to be more prominent because of the recompile. So this is a **red herring**, it will *not* directly fix the issue as it will just pop out again on the next recompile. See https://www.sommarskog.se/query-plan-mysteries.html and https://www.brentozar.com/archive/2016/11/query-sometimes-fast-sometimes-slow/ **We need to see the query plans to find out what's going on.** – Charlieface Apr 04 '22 at 23:44
  • @Charlieface `ARITHABORT` does not cause a recompile. The cache key for cached query plans is based on both the combination of the hash of the complete SQL statement (including whitespace) as well as the current `@@OPTIONS` value. By itself the different `@@OPTIONS` value causes different cache keys for the same statement when `ARITHABORT ON` is in effect, versus when `ARITHABORT OFF` is in effect. The problem is amplified, though, because `ARITHABORT` is actually a plan-affecting option, so an entirely different plan can be created and cached. – AlwaysLearning Apr 04 '22 at 23:56
  • You are correct, though, in that tweaking `ARITHABORT` isn't really a solution. The correct approach would be to examine the defective plan and mitigate the issues that cause it, e.g.: stale statistics, incorrect indexing, incorrect or missing index hints, etc.. – AlwaysLearning Apr 04 '22 at 23:57
  • @AlwaysLearning Sorry, that's what I meant to say. What I meant is that it causes a recompile if the only existing plan was with a different setting. So having it `ON` and `OFF` will cause two plans. Point is: setting it `ON` does not fix the issue. To quote Erland from the above article: *"And, no, putting SET ARITHABORT ON in the procedure is not the solution."* – Charlieface Apr 04 '22 at 23:58
  • Will do a proper job but it is a EOFY process no time can do post April. Hey it went from 28 minutes to 6 seconds so who is complaining! – Glen Apr 06 '22 at 00:58
  • I here what people are saying but 2 things are against me the system is 3rd party so I am limited to add indexes (red tape) at the table level do not want to give other people more headaches and 2 it is nested layers of function calls, so maybe I can try the inline approach for the second layer maybe the top 2 levels could be replaced with stored procedures and make use of temp tables along the way. The beauty of SQL there is no wrong or right (ignoring genuine mistakes), just right and better than right. I only tested 1 row of data, the whole system still takes hours even with this option on! – Glen Apr 07 '22 at 00:07

0 Answers0