Optimzing TSQL code

Question

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)

Answer 1

i've come to realize that having such complex searches in a single query is not really a good idea.

i prefer to construct the sql depeding on the input condition values. this makes it easier fo rsql server to construct a better execution plan for each search. this way i'd bet that you have a sub-optimal execution plan for your queries.

i realize that this would include dynamic sql so the usual warnings for it apply.

Answer 2

You have two different functional concerns here: the selection of values from your one table and the choice of columns to be returned or other tables to be joined to that data. If the number of items from filtering on your one table could be large, I would inclined to store the PK of selected values into a middle or work table. If it is a permanent table, you can separate different searches by something like SessionId or you could just separate each set of search results by a random value which you pass from the filtering routine to the selecting routine.

There is no reason you could not keep the filtering routine in dynamic SQL. However, I would not try to do dynamic SQL in T-SQL. T-SQL is awful for string manipulation. Building the queries dynamically from your middle tier affords you the ability to exclude elements from the Where clause that are effectively not passed. E.g., instead of having and (a.vrsta_id = @vrsta_id or @vrsta_id = 0), you could simply exclude this line altogether when @vrsta_id is in fact zero or have a.vrsta_id = @vrsta_id when @vrsta_id is not zero. Generally, this type of query will perform better than a series of ORs.

Once you have your work table, your selecting queries would look something like:

Select..
From WorkTable As W
    Join ...

Where SetId = 12345
    And ( OtherTable.Col = ....

In this case, SetId would represent the set of items that were created from the filtering routine.

Answer 3

You can create a table valued function that takes in the parameters and returns a table of matching a.id values. Then you can inner join that function onto the query in each of your stored procedures. For example:

create function dbo.GetMatches
(
@vrsta_id int,
@podvrsta_id int,
@podgrupa2_id int,
@podosobina_count int
)
returns table
as
return
Select 
a.id
from a
where
(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)
)

Then in this example query...

select
*
from
a
inner join dbo.GetMatches(1,2,3,4) matches
on a.id = matches.id
inner join b on a.bID = b.bID -- example other table

You could also use that function in the where statement like this...

where
    a.id in (select id from dbo.GetMatches(1,2,3,4))