SQL Server stored procedures fast in SQL but slow when called by ASP.NET

Question

Post reply

SQL Server stored procedures fast in SQL but slow when called by ASP.NET

Viewing 3 posts - 16 through 17 (of 17 total)

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

jkramprakash SSCommitted Points: 1870 More actions · Answer 1

Lowell - Monday, September 24, 2018 7:53 AM

don't forget the basics: make sure you've added SET NOCOUNT ON at the top of your procedure, as well as commenting out any PRINT statements.
those add some added overhead as they send messages back to the application, which are most likely ignored anyway.

from there i would start checking to make sure that all queries are SARG-able(meaning the SearchArgument can use an index...
no LIKE statements,
no functions on column names,
no OR statements,
no implicit conversions on data types.

Thank you.

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 2

Lowell - Monday, September 24, 2018 7:53 AM

don't forget the basics: make sure you've added SET NOCOUNT ON at the top of your procedure, as well as commenting out any PRINT statements.
those add some added overhead as they send messages back to the application, which are most likely ignored anyway.

from there i would start checking to make sure that all queries are SARG-able(meaning the SearchArgument can use an index...
no LIKE statements,
no functions on column names,
no OR statements,
no implicit conversions on data types.

Careful now. Non-leading-wildcard LIKEs are incredibly useful in certain cases and don't hurt SARGability. They may cause some extra range scanning but that's normally trivial compared to what happens with non-SARGable queries. The other things you mention are absolutely spot on.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

jkramprakash SSCommitted Points: 1870 More actions · Answer 3

Jeff Moden - Thursday, October 4, 2018 7:26 AM

Lowell - Monday, September 24, 2018 7:53 AM

don't forget the basics: make sure you've added SET NOCOUNT ON at the top of your procedure, as well as commenting out any PRINT statements.
those add some added overhead as they send messages back to the application, which are most likely ignored anyway.

from there i would start checking to make sure that all queries are SARG-able(meaning the SearchArgument can use an index...
no LIKE statements,
no functions on column names,
no OR statements,
no implicit conversions on data types.

Careful now. Non-leading-wildcard LIKEs are incredibly useful in certain cases and don't hurt SARGability. They may cause some extra range scanning but that's normally trivial compared to what happens with non-SARGable queries. The other things you mention are absolutely spot on.

ok.sure.thank you.