Optimzing TSQL code

Posted by adopilot on Stack Overflow See other posts from Stack Overflow or by adopilot
Published on 2010-06-16T14:25:34Z Indexed on 2010/06/16 14:32 UTC
Read the original article Hit count: 296

My job is the maintain one application which heavy use SQL server (MSSQL2005).
Until now middle server stores TSQL codes in XML and send dynamic TSQL queries without using stored procs.
As I am able change those XML queries I want to migrate most of my queries to stored procs.
Question is folowing:

Most of my queries have same Where conditions against one table

Sample:

Select 
   .....
from ....
where ....
and (a.vrsta_id = @vrsta_id  or @vrsta_id = 0)
and (a.podvrsta_id = @podvrsta_id or @podvrsta_id = 0)
and (a.podgrupa_2 = @podgrupa2_id or @podgrupa2_id = 0)
and (
 (a.id in (select art_id from osobina_veze where podosobina_id in (select ado from dbo.fn_ado_param_int(@podosobina))
        group by art_id
        having count(art_id)= @podosobina_count ))
     or ('0' = @podosobina)
 )

They also have same where conditions on other table.

How I should organize my code ?
What is proper way ?
Should I make table valued function that I will use in all queries
or use #Temp tables and simple inner join my query to that each time when proc executing? or use #temp filed by table valued function ?
or leave all queries with this large where clause and hope that index is going to do their jobs.
or use WITH(statement)

© Stack Overflow or respective owner

Related posts about sql

Related posts about sql-server