Slow Stored Procedure (sometimes) 2

[wooody1245]

I've got a stored procedure that feeds a Crystal Report that is sometimes very slow and sometimes very fast. When I run the stored procedure using Query Analyzer, it only takes about 10 seconds to return 7 to 10 thousand records. When I run the Crystal Report, it takes about 5 minutes to get the same data.

I know it sounds like a problem with Crystal Reports but I don't think it is. When I use SQL Profiler I can see where Crystal calls the stored procedure but it takes 5 minutes to complete.

I've tried making an exact copy of the stored procedure with a different name and it runs perfect, sometimes. Other times it will take 5 minutes to complete. Sometimes the original stored procedure will run very quickly too. Anytime either stored procedure is slow, I can use SQL Profiler to verify when the SQL Server started and finished the query. It's always proportional to what I see with Crystal.

I'm getting really bummed out by this. Any ideas?

 

[SQLDenis]

probably ansi null settings etc

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[mikeyblobs]

Have you tried running an update stats prior to running the crystal report. I have experienced a similar situation but with MS reporting services reports. I'm not sure why, but a daily update stats cured this problem.

Example:

exec sp_updatestats 'resample'

HTH
Mike.

 

[wooody1245]

I tried updating the statistics and the crystal report ran perfectly for about an hour. Now it's back to taking 5 minutes to run. I updated the statistics again but it did not help at all this time.

Any other ideas?

 

[gmmastros]

>> it only takes about 10 seconds to return 7 to 10 thousand records

That, to me, is way to long for such a small recordset. As such, I would recommend that you take a look at your indexes to see if you can speed this up. You may also be running in to a parameter sniffing issue. Another possible cause is that you are trying to return records but are running in to locking/blocking issues.

My suggestion... try making the query faster. This may not completely resolve your problem, but it couldn't hurt either. If you'd like, you can post the procedure here and we'll take a look at it for you.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[wooody1245]

I'm not concerned at all about the stored procedure taking 10 seconds to complete. I'm much more concerned about the 5 minutes it takes to run when called from Crystal Reports. I would be grateful if it only took 30 seconds to complete.

I would post the stored procedure but it is fairly long and I'm sure it would take a ton of time to sort through it and the indexes.

It makes no sense to me why it takes so much longer to run when it's called from Crystal than from Query Analyzer. Something about the way they both call the stored procedure has to be different.

 

[SQLSister]

Did you search out what George was talking about when he said it could be a parameter sniffing problem? Google the term and you may find the answer to your problem.

"NOTHING is more important in a database than integrity." ESquared

 

[wooody1245]

SQLSister, thanks for pointing me to George's comments about parameter sniffing. George, you are a genius. I googled parameter sniffing and found this article

http://omnibuzz-sql.blogspot.com/2006/11/parameter-sniffing-stored-procedures.html

I created local variables and assigned the input parameters to the local variables. Then I modified the where clause to use the local variables. The stored procedure runs perfect, even from Crystal Reports.

 

[ESquared]

Do not so quickly discard Georges advice to look at the SP for taking 10 seconds. Think about it. The same genius who solved your problem—that you couldn't—gave moer than one piece of advice. Remember, from the start, you were here asking for help because you didn't know what to do. To discard part of his advice simply because you are "not concerned at all" with it was to falsely assume you understood something you didn't. You were essentially judging that you knew better than him, without warrant! That his advice sounded strange could have been a signal that there was something you didn't understand, that you might want to learn about.

Examining the SP and changing how it functions for the shorter execution time could help the parameter sniffing issue because parameter sniffing problems are all about execution plans & statistics.

My guess about the reason your Crystal Report ran well for a while is that after rebuilding statistics, the server decided to recompile the SP and you got a good execution plan for the report's specific parameters. Eventually the execution plan was discarded or otherwise switched to be tuned for the parameters you were using from QA, so it blew up the Crystal Report.

Since it's all about execution plan, it's likely that speeding up your 10-second query would have also alleviated the execution plan problems of the other query. If you were able to get your SP using Nested Loops and all Clustered Index Seeks/Index Seeks (or otherwise optimize it), there's a good chance that the parameter sniffing problem would have gone away.

Does that help? Do you understand now that the 5-minute execution time is intimately related to the 10-second execution time? And that changing the query enough to return 10,000 rows much faster than 10 seconds could have completely solved the other problem as well?

Also don't treat idly his suggestion that we could look at your SP.

The other day I went to look at the query behind a combobox in an Access database (developed by someone else) that was taking many long seconds to display the first time. I hadn't even started to think about why it could be slow or how to speed it up when I noticed that the query was using a LEFT JOIN instead of an INNER JOIN, so I fixed that. And blam, the query was running much more quickly. I never had to solve the speed problem on its own because my experience and "instinct" in queries had already led me right to the problem!

Similarly, without even considering parameter sniffing at all, some of the experts here might suggest optimizations that would have cured the problem without the need for local variable shadowing of the input variables.