Optimzing TSQL code
- by adopilot
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)
