ASP.NET

search query at Business Logic or build in Stored Procedure RSS

5 replies

Last post May 04, 2012 01:41 AM by Decker Dong - MSFT

‹ Previous Thread|Next Thread ›

- Reply
MindHours

Member

29 Points

13 Posts

search query at Business Logic or build in Stored Procedure

May 02, 2012 09:02 AM|LINK

What will be more preferable to Search Records.

i have around 10 search criterias.

currently i m passing all parameters in Stored Procedure and buid a search criteria in stored procedure , if value exists or not..

and another way is to create a Search Criteria at BLL(business logicl) level and pass Query as a parameter into stored procedure.

Please let me know which one is more convenient for good performance.
- Reply
arunavasaha

Member

2 Points

9 Posts

Re: search query at Business Logic or build in Stored Procedure

May 02, 2012 09:16 AM|LINK

Store Procedure is always faster in performance as it is a precompiled object of a database.

Arunava Saha
- Reply
kedarrkulkar...

All-Star

34295 Points

5514 Posts

Re: search query at Business Logic or build in Stored Procedure

May 02, 2012 09:16 AM|LINK

from performance perspective, there wouldnt be much difference....

but for maintainability, it would be better to form query in SP

Cheers!
KK

Please mark as Answer if post helps in resolving your issue
My Site
- Reply
ishq

Member

256 Points

76 Posts

Re: search query at Business Logic or build in Stored Procedure

May 02, 2012 09:18 AM|LINK

I think, exchanging less data between application and database will be more effective. Select the one which needs less data transfer

Keep smiling..
Lathika Morthodi
- Reply
MindHours

Member

29 Points

13 Posts

Re: search query at Business Logic or build in Stored Procedure

May 02, 2012 09:33 AM|LINK

Thanks for your response.

now in SP query should be as text , then i m executing , this wont get advatage of Precompile Stored procedures.

due to search criteria in Stored Procedure , i m facing Parameter sniffing issue.
- Reply
Decker Dong ...

All-Star

118619 Points

18779 Posts
Re: search query at Business Logic or build in Stored Procedure

May 04, 2012 01:41 AM|LINK
MindHours
due to search criteria in Stored Procedure , i m facing Parameter sniffing issue.

In fact I think you can combine splitted string splits together，just like this following：
```
create stored procdure AAA
(
   @typeName1 type1,
   ………………
   @typeName2 type2,
   ………………
)
As
declare @sql varchar(max)
set @sql = ……………… + convert(varchar(max),@typeName1) + convert(varchar(max),@typeName2)  --Do combination……
exec(@sql)
```
- Marked as answer by decker dong - msft on May 08, 2012 01:47 AM