Optimize SQL query performance

Do not use *(all) use only the names of the columns that you require like

  select `col1`,`col2` from `table`

Also, I recommend you not sorting the query if speed is the priority. You can use the following code :-

  select `cols` from `dbo`.`Products` where `Products`.`ProId`='P01'

and then in your programmming language, use if-else-if or switch-case to get the query and sort the data. However, I recommend using sql to sort and arrange the data for a few thousand rows.

I do not understand why you do the case statement in the select. This will be validated for every row in the select statement. Because it is a variable and is not depended on anything in the select. I would do this:

DECLARE @order VARCHAR(10)
declare @addPrefix as Varchar(10)

set @addPrefix = 'ADD02' 
SET @order= 
 ( 
    case @addPrefix
    when 'ADD01' then 'Address01'
    when 'ADD02' then 'Address01'
    when 'ADD03' then 'Address03' 
    when 'ADD04' then 'Address04' 
    when 'ADD05' then 'Address05'
    END
 )

And then do the query on the variable. Like this:

select * from dbo.Products 
where Products.ProdId='P01'  
order by 
@order, Products.ProdName  desc

This is a pretty much plain select statement, so I don't think there is much you can do.Apart from creating a index on ProdId column of Products table, I cant think of anything else.

Note: I am a Oracle guy

Use an explain plan. This is always the first place to go for optimization information. Note however in your case use one for each of the Address01/03/04/05 sorts.

If I had to guess I would say it is your ORDER BY that is slowing this simple query.

try this:

declare @addPrefix as Varchar(10)
set @addPrefix = 'ADD02'

select case @addPrefix
   when 'ADD01' then 'Address01'
   when 'ADD02' then 'Address01'
   when 'ADD03' then 'Address03' 
   when 'ADD04' then 'Address04' 
   when 'ADD05' then 'Address05'  
end as orderbycolumn, *
from dbo.Products where Products.ProdId='P01'  
order by orderbycolumn, Products.ProdName  desc