SQL running very slow from .Net

Question

SQL running very slow from .Net

WangcChiKaBastar

SSCrazy

Points: 2297
More actions
December 30, 2011 at 8:43 am

#248918

SQL queries/calls are running terribly slow from .Net Application
but when we run the same query on SSMS its fine.
I ran profiler but didnt get much clue, Tlog is OK as well.
No open transactions. What else basic stuff I should be checking ?

Viewing 7 posts - 1 through 6 (of 6 total)

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

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 1

WangcChiKaBastar (12/30/2011)
SQL queries/calls are running terribly slow from .Net Application
but when we run the same query on SSMS its fine.
I ran profiler but didnt get much clue, Tlog is OK as well.
No open transactions. What else basic stuff I should be checking ?

Identify your worst performing query.

Trace it twice... once executed from .Net and once executed on SSMS; compare exec plans as well as trace files looking into wait events.

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

WangcChiKaBastar SSCrazy Points: 2297 More actions · Answer 2

ok

Its actually an iterative call to a Stored Proc from .Net

and the proc runs super fast on SSMS. every call it fetches 2 to 3 rows and

returns data to front end.

WangcChiKaBastar SSCrazy Points: 2297 More actions · Answer 3

WangcChiKaBastar

SSCrazy

Points: 2297

December 30, 2011 at 10:27 am

#1427065

What worked for me was windows update. Thanks

LutzM SSC Guru Points: 107049 More actions · Answer 4

WangcChiKaBastar (12/30/2011)
ok
Its actually an iterative call to a Stored Proc from .Net
and the proc runs super fast on SSMS. every call it fetches 2 to 3 rows and
returns data to front end.

I'm not sure if this is the best way to get the data.... Have you tried query all data at once?

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

David Moutray SSCertifiable Points: 5956 More actions · Answer 5

This is actually a fairly common problem. The default ANSI set options are different for .NET than they are for SSMS. the same query, submitted with different ANSI set options, will have two query execution plans saved for it.

The query execution plan which is stored for the .NET query is apparently less than optimal. That can happen if the stored procedure happened to get called with an unusual parameter the first time.

The quick fix is to use sp_recompile on this stored procedure. That will cause a new query plan to be compiled the next time the stored procedure is called.

Erland Sommarskog published an outstanding article about this last year. http://www.sommarskog.se/query-plan-mysteries.html

It explains this particular problem as well as other situations that may cause a query to execute slowly in the application when it runs quickly from SSMS.

Devendrakumar SSC-Forever Points: 42493 More actions · Answer 6

David has good point but I suspect something else.

When you say SQL run slow, you might be referring to time taken to render the page (on web) or window (on desktop) applications. It’s cumulative time of network latency & application’s own processing time. I believe there are few diagnostic tools (also freeware) that can give you this breakup of time in one task request.

I won’t suspect database unless & until Front End Team prove they are fine on their stand. I found their faults in most of the cases I handled Performance SLAs.