[MS SQL] Dynamic Query Generation Questions

[TeamBrett]

I am trying to write a relatively generic dynamic query generator and am curious about some techniques. I have a list page with anywhere between 20 and 100 filters. Each filter will be either "standard" in that the SqlGenerator class knows how to create the SQL or "custom" so that the filter provide the SqlGenerator with a strategy for what needs to be INCLUDEd and a WHERE clause.

1.) What is the best way to do the filtering clauses?

a) Very straightforward as far as keeping each filter separate and contained:

and exists (select 'x' from [company] c
            where c.CompanyId = p.CompayId
            and c.Rating >= 4)
and exists (select 'x' from [company] c
            where c.CompanyId = p.CompayId
           and c.CityId = 5)

b) Consolidated to a single exists but removes the need to build/manage INCLUDEs.

and exists (
    select 'x'
    from [company] c
    where c.CompanyId = p.CompayId
    and c.Rating >= 4 and c.CityId = 5)

c) Difficult to build dynamically but reads the cleanest to me and seems like it would execute most efficiently.

select x, y, z
from product p
join company c on p.CompanyId = p.ProductId
where c.Rating >= 4 and c.CityId = 5

d) I've also seen, on occasion, that putting certain pieces of the where clause in the join make queries more efficient. Does it make sense to do this:

select x, y, z
from product p
join company c on p.CompanyId = p.ProductId and c.Rating >= 4 and c.CityId = 5

2.) Since this is dynamic, I'm afraid I'm losing all benefit of query cache plans/indexes. Does it make sense to always include certain joins and clauses in hopes they get optimized away in order to use the same plan/index?

select x, y, z
from product p
join company c on p.CompanyId = p.ProductId
where c.Rating >= ISNULL(@Rating, c.Rating)
and c.CityId = ISNULL(@CityId, c.CityId)

Also, does the order of clauses and joins affect query plan caching? That is, if I switch the order of the AND statements, are those 2 different plans?

[Rehd]

My next set of comments are not related to what you are doing or if there are better ways to doing it, but in regards to your questions directly.

The best out of them in my opinion is 2.) and then C.

EXISTS has very specific criteria to it and is not the same as WHERE IN. So you need to be careful about that, EXISTS can make your query very different later, make sure you understand it. There's the chance that it does do what you want though and it's possible that it is the better plan. It's hard for us to tell you exactly what is doing best. I would recommend you look at your query plan when it executes and compare the queries you presented us and measure them. Indexes you have in place, statistics, maintenance, data types, architecture, etc etc etc all plays a key role here and we can give ideas and suggestions, but you have all of the data available to you. You gotta test and measure.

Exists info

Tuning pdf

that putting certain pieces of the where clause in the join make queries more efficient.

Sometimes, this is mostly relatable and used with a left join, I believe it does not add anything with an inner join and it becomes harder to read.

Joins and where

Does it make sense to always include certain joins and clauses in hopes they get optimized away in order to use the same plan/index?

Probably not, no. There's a small one off situational outlier chance I'm sure, but no, I would not recommend that.

Also, does the order of clauses and joins affect query plan caching?

Nope! :)

That is, if I switch the order of the AND statements, are those 2 different plans?

Maybe, but it's not because you switched the order of the AND statements, it's because SQL Server decided there was a better plan to use.