SQL Server Performance Forum – Threads Archive
Query command vs stored procedure
<br />Hi<br /><br />I have advanced search with copuple of fields like dates<br />or document numbers, names, etc.<br />To search text fields I use LIKE.<br /><br />Result list shows first 100 records but all columns<br />are sortable.<br /><br />Now, I must choose between my own "query builder object" and<br />lots of wariants stored procedures/queries? :/<br /><br />I’ve tested CASE in ORDER clause on table over 100000 records<br />and performance goes down. Without CASE query runs very fast <img src=’/community/emoticons/emotion-1.gif’ alt=’
‘ /><br /><br />CASE in WHERE clause generates different execution plans.<br />With Case is "worse", of course. :/<br /><br />Any ideas? <img src=’/community/emoticons/emotion-1.gif’ alt=’‘ /><br /><br />How to design good, usefull and fast working "advanced search"? <img src=’/community/emoticons/emotion-1.gif’ alt=’‘ /><br /><br />Best regards<br />Darek<br /><br />PS<br />Sorry for my poor english.<br />
For the ORDER BY clause, you may get better performance by adding a column expression with CASE to get the value from whichever column is selected for ordering, and then ORDER BY on the column of the CASE expression. This sort of situation cannot be optimized unless you narrow down the options available to the users. If they need the flexibility, they’ll have to live with reduced performance.
]]>