I have a web application that has a search engine that returns records based off what the user selects in the search engine. I am currently using coalesce in the where statement in my stored procedure to return the records. For eample,
where field1= coalesce(@.parm1,field1). I don't know if this example is better than building the sql statement dynamically in a parameter then executing the parameter with sp_executesql. Can someone explain to me which is better or if there is a better solution?
Thanks,
JamesYou're on the right track. You'll find a great article at http://www.sommarskog.se/dyn-search.html.|||it is better then dynamic sql
you can use isnull function also|||I think it depends.
My understanding is that with dynamic WHERE clauses, SQL Server does not necessarily always store the most efficient execution plan; in the long run, this can hurt the performance of your query, especially if you have a ton of such dymanic elements in the where clause.
That said, I hate maintaining dynamic sql code.
ou might run some tests and see what you get performance-wise.|||But, on the OTHER hand, rebuilding an execution plan each time may be better for the particular combination of parameters than using the one excution plan that was built the first time for the query. E.g., if there are 10 parameters and the first time through, you enter values for all 10. An execution plan is built. Then, the normal user searches with one parameter most of the time. The original execution plan won't be ideal for that query.
It all depends. I went to a conference where some very good examples of both methods were displayed, profiled, etc. Very enlightening. Test and test some more for your particular case.|||I made some performance tests one day concerning the issue:
http://blogs.x2line.com/al/archive/2004/03/01/189.aspx
No comments:
Post a Comment