SQL Query invoked by Crystal Reports v9.2 Performance Issue
-
All,
I seek someone with ancient knowledge of .Net/Crystal reports to hopefully lead me to a solution to this problem.
Platform:
- Crystal Reports v9.2
- .Net Framework v1.1
- SQL Server 2008 R2
We have a report that is generated by a call to an SP and it is running really slow on a Prod server. However this same SP when run from SSMS runs in less than one second.
The 2 profiler traces were run 1) from the application (ApplicationName=IIS) and 2) from SSMS (ApplicationName=MS SQL SSMS). They are both operating against the exact same server, database, rows, time window, etc. Only the invoking ApplicationName is different.
None of the other reports that get results from an SP use the odd '; 1' syntax circled at the bottom in red, and none of those other reports seem to exhibit this same problem (but we'll be investigating this some more). My research indicates that the semicolon 1 syntax on the SP causes it to use "version 1" of that SP. I've checked and there is no version 2. Research also suggests that this syntax will be deprecated in future versions of SQL Server, but that isn't really the issue here. I ran the query in SSMS with that odd syntax and without, and saw no noticeable difference in SP execution time.
My thinking is that somehow we've managed to invoke the SP using some antiquated method in C#.Net and this may be resulting in the horrendously different performance profile when running the SP from IIS.
Since the SP/query only returns about 130 rows, I'm thinking that this is not some kind of odd memory caching issue in IIS. The SP itself uses no temp tables or table variables, and pretty much all it does in fact is SELECT from a rather complex VIEW. Clearly executing in 187ms on SSMS, the SQL query itself is probably not the problem here.
Has anybody got any ideas or experience with a similar case to share?
Thanks.
My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St -
A couple of clarifications...
Numbered stored procedures: http://beyondrelational.com/modules/2/blogs/77/Posts/19618/0289-sql-server-deprecated-features-numbered-stored-procedures.aspx (This was new to me but I could have done without the information since clearly it will be deprecated).
The first profiler trace posted above is actually against the Prod server, while the second one is from the same database on our Dev server. However, I am currently running that application functionality on our Dev server against this database and since the report has not rendered yet after about 30 minutes, I assume the Profiler trace I'll get will be roughly the same as the one shown for the Prod server.
I like to say that "if we can replicate a bug, we can fix it." In this case, I hope that statement is true!
My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St -
The first place I'd look would be the SET options, are they the same when running on SSMS/Prod?
2 plans in the cache?
-
Have you tried recreating the proc without the versioning? (I.E. without the "; 1") and then executing it through your code that way? Then see if it still continues to cause this issue?
Yeah, I know it doesn't answer your initial question, but I am curious if it does really make that much of a difference.
-
Is there varchar parameters used in query predicates? I have seen that reports are calling always with nvarchar and this causes scans instead of seeks.
-
We believe we have isolated the problem to a different method used to call the SP from C#.Net.
We are working through the recoding on that and I expect to see new results today.
I'm keeping my fingers crossed.
My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St -
Brandie Tarvin (1/23/2015)
Have you tried recreating the proc without the versioning? (I.E. without the "; 1") and then executing it through your code that way? Then see if it still continues to cause this issue?Yeah, I know it doesn't answer your initial question, but I am curious if it does really make that much of a difference.
It wasn't created with ;1 in the first place. I wrote the SP and didn't even know about versioning until this issue came up. The versioning is applied by SQL server by default.
My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St -
dwain.c (1/25/2015)
Brandie Tarvin (1/23/2015)
Have you tried recreating the proc without the versioning? (I.E. without the "; 1") and then executing it through your code that way? Then see if it still continues to cause this issue?Yeah, I know it doesn't answer your initial question, but I am curious if it does really make that much of a difference.
It wasn't created with ;1 in the first place. I wrote the SP and didn't even know about versioning until this issue came up. The versioning is applied by SQL server by default.
Really? That's odd. The article you referenced made it sound like the versioning was a manual thing.
But then there's a lot going on under the hood of SQL that I don't fully understand myself yet.
EDIT: Is Crystal running on the same exact server as SQL Server? If not, what else is on the server that Crystal runs? And if so, what else besides Crystal and SQL is on the server?
I'm having a sudden vague memory about something related to resource contention, but I can't quite pin it down yet.
-
Brandie Tarvin (1/26/2015)
EDIT: Is Crystal running on the same exact server as SQL Server? If not, what else is on the server that Crystal runs? And if so, what else besides Crystal and SQL is on the server?I'm having a sudden vague memory about something related to resource contention, but I can't quite pin it down yet.
No it is not. Application server tier is on a different physical machine than the SQL server. It appears from the profile traces I originally posted that Crystal is accessing the SQL server through IIS. On our Dev machine there's all sorts of other stuff running on that machine (probably) but on the Prod server I don't think so, as it is dedicated to serving this one application.
I am trying to obtain answers to the question "what resources does the application server (IIS) tell SQL server that it needs?" but no one is able to answer this. I assume there must be some way to find out.
I certainly hope this jogs your memory because my suggestion earlier that we're on our way to finding a solution seems to be not true. I'm about to rewrite the query in the SP into two different forms:
- I believe I can make it run as a select from a schema-bound iTVF
- As a schema-bound VIEW - and that one is going to be much more tricky
My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St -
After more than a little frustration with the development team that seemed unable to resolve this, I started Googling and I came up with a suggestion that might offer a "slight improvement." Obviously from those stats I posted I needed a heck of a lot more than just a slight improvement, but I figured what the hey it can't hurt.
So I tried it and the report now renders instantly.
The solution:
1. DROP the PROCEDURE
2. CREATE the PROCEDURE WITH RECOMPILE
I feel like an idiot. :w00t:
My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St -
So does it recompile each time it runs or did it just recompile the once?
-
I believe that causes it to recompile on each execution. Just like putting the option a query does.
My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St -
I once ran into a similar problem with a stored procedure which worked really almost instantaneously in SSMS but took over a minute to return in crystal. I thought it might be a parameter sniffing issue but that still does not explain why it worked so quickly in ssms.
Regardless the crystal report performance improved substantially when I changed the fixed fields to variables.
For examples
where name = 'abc' ran very slow in crystal
but
set @var = 'abc'
where name = @var ran dramatically quicker in crystal.
-
This sounds like a parameter sniffing issue, as it's differently behaved in SSMS and fixed by recompiling. There are two ways to easily get around parameter sniffing (perhaps more, but these are what I know). I ran into this issue and posted about it many years ago: http://www.sqlservercentral.com/Forums/Topic1055385-391-1.aspx (Though apparently I didn't follow up with the solution that was deployed, which was OPTIMIZE FOR UNKNOWN in this case, because this company had two customers, one of which had 99% of the data in all the tables). Wow, I'm SO glad I don't work there anymore. The query in that thread is the tip of the iceberg in the disaster that was going on. Moving on...
Assign the parameters to variables before using them in a WHERE clause. (This isn't a good example because this particular query won't cause different query plans based on parameter values, but it shows the technique)
CREATE PROCEDURE test(@p1 sysname)AS
DECLARE @p1_copy sysname = @p1;
SELECT * FROM sys.columns c WHERE c.name = @p1_copy;
The other technique is to tell the optimizer to pretend that it doesn't know the value of the parameter when it first compiles a query plan:
CREATE PROCEDURE test2(@p1 sysname)AS
SELECT * FROM sys.columns c WHERE c.name = @p1 OPTION (OPTIMIZE FOR (@p1 UNKNOWN));
Of course, RECOMPILE should also work, but you have to determine if it imposes its own performance hit.
-
Stephanie Giovannini (1/27/2015)
This sounds like a parameter sniffing issue, as it's differently behaved in SSMS and fixed by recompiling. There are two ways to easily get around parameter sniffing (perhaps more, but these are what I know). I ran into this issue and posted about it many years ago: http://www.sqlservercentral.com/Forums/Topic1055385-391-1.aspx (Though apparently I didn't follow up with the solution that was deployed, which was OPTIMIZE FOR UNKNOWN in this case, because this company had two customers, one of which had 99% of the data in all the tables). Wow, I'm SO glad I don't work there anymore. The query in that thread is the tip of the iceberg in the disaster that was going on. Moving on...Assign the parameters to variables before using them in a WHERE clause. (This isn't a good example because this particular query won't cause different query plans based on parameter values, but it shows the technique)
CREATE PROCEDURE test(@p1 sysname)AS
DECLARE @p1_copy sysname = @p1;
SELECT * FROM sys.columns c WHERE c.name = @p1_copy;
The other technique is to tell the optimizer to pretend that it doesn't know the value of the parameter when it first compiles a query plan:
CREATE PROCEDURE test2(@p1 sysname)AS
SELECT * FROM sys.columns c WHERE c.name = @p1 OPTION (OPTIMIZE FOR (@p1 UNKNOWN));
Of course, RECOMPILE should also work, but you have to determine if it imposes its own performance hit.
Since the user advised that in Prod the result now renders "nearly instantaneously" I'd say I'm happy with the results for the time being, or until such time as it becomes an issue again.
My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply