Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

We've had an issue reported where a stored procedure was behaving badly when called from some .Net code. On the face of it, that sounds similar to this case, which turned out to be due to EDMX, but in this case, we're just executing a stored procedure.

In the case today, when connected directly to the server holding the database, via SSMS, the query took 2 seconds; when running the query from the client application it was about 5 minutes, and when running the query from a linked server, it also took around 5 minutes. The degradation in performance "suddenly happens" without any changes having been committed.

From a comment on a previous answer, the suggestion wasn't due to query plans, even though recompiling the stored procedure did "fix" the performance for now. i.e. Recompiling the stored procedure makes it equally performant from both SSMS in direct vs linked scenario, and also to the .Net client. I am passing through exactly the same parameters through for comparison.

The stored procedure does a small amount of dynamic SQL (to unwrap a comma-separated list of IDs into a temp table, of the form INSERT INTO #Temp SELECT id FROM dbo.MyTable WHERE Id IN ( 1, 2 ,3 )) before going on to to the main query which joins a whole bunch (circa 20) of tables for the final result.

Ideally I meant to better understand the cause, such that this can be avoided in the future, so what should I be doing to stop this from happening?

share|improve this question
    
For clarification: 1.) Are you saying that recompiling the procedure and then running again from the client DID fix the problem for now? Or are you saying that worked for the linked question but not you? I'm confused :-) 2.) Are you supplying different parameters to the proc locally vs what the app was using? 3.) Does the proc have any dynamic SQL in it? Can you post the stored procedure create code in your question if not overly complex? –  Mike Walsh Feb 10 '12 at 16:36
    
@MikeWalsh has updated to address your queries; in short: yes, yes and yes :) –  Rowland Shaw Feb 10 '12 at 16:56
    
Have you considered creating a name-value pair user defined table and passing that in to your SP as the list of Ids rather than a string of Ids? It would eliminate the need for your dynamic sql. –  Ben Feb 10 '12 at 23:42
    
Could this be the answer? –  Martin Smith Mar 21 '12 at 23:16
add comment

2 Answers

up vote 2 down vote accepted

The difference between SSMS and .Net client performance could well be down to how plans are cached. Part of the cache key is the session options - for me, at least, SSMS sets ARITHABORT, whereas .Net SqlClient does not, by default. You sound like you're ending up in the situation where a bad plan is cached vs. one cache key, and a good plan vs. another.

share|improve this answer
add comment

SQL Performance... its been a while.

Have you considered using a function for splitting delimited values rather than dynamic SQL?

Are you using the regular ADO.Net SqlClient objects to execute the SP?

And a random question from a previous problem that bit me on the arse.... are you running the query in SSMS on the same machine the client app is being tested? I have seen an issue with large data sets over flakey networks. As a dev, I had a different route to the server than the clients did.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.