Sign In| Join
Get Help:Ask a Question in our Forums|Report a Bug|More Help Resources
Last post May 04, 2012 01:41 AM by Decker Dong - MSFT
Member
29 Points
13 Posts
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.
2 Points
9 Posts
May 02, 2012 09:16 AM|LINK
Store Procedure is always faster in performance as it is a precompiled object of a database.
All-Star
34295 Points
5514 Posts
from performance perspective, there wouldnt be much difference....
but for maintainability, it would be better to form query in SP
256 Points
76 Posts
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
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.
118619 Points
18779 Posts
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)
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.
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.
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
KK
Please mark as Answer if post helps in resolving your issue
My Site
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
Lathika Morthodi
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.
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
In fact I think you can combine splitted string splits together,just like this following: