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