Stored Proc takes longer in .NET Client than Mgmt. Studio

Question

Post reply

Stored Proc takes longer in .NET Client than Mgmt. Studio

Dave M-137455

SSC Enthusiast

Points: 158
More actions
January 12, 2008 at 9:04 pm

#180650

When running this particular stored proc in Mgmt Studio it returns data in approx 4 - 8 seconds, when running the same stored proc via ASP.NET using the SQL .Net Client it takes 264+ seconds.

Anyone encountered anything like this?

TIA

Dave

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Johan Bijnens SSC Guru Points: 134873 More actions · Answer 1

I'd first of all suspect the way the proc's parameters are defined in the vb.net app.

( qa.sqlservercentral.com/scripts/Miscellaneous/31639/ )

Avoid implicit conversions !

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

tim.rudy Grasshopper Points: 13 More actions · Answer 2

tim.rudy

Grasshopper

Points: 13

January 14, 2008 at 7:38 am

#767671

It may have to do with the query plan being cached on the first run and reused, even though it's non-optimal for the particular data. See: "How SQL Server Chooses the Type of Join" ( http://qa.sqlservercentral.com/articles/Performance+Tuning/2863/[/url] )