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=’


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.
]]>