Parameterised sql server query

[MathiasB]

Resolved

 

[Chriss Miller]

Even if you solved your problem alone, it would be worth keeping the question and posting the solution. Or just delete your thread.

 

[MathiasB]

This was my question:
Evaluating one side of OR in my query. I have a SQL Server query with 2 separate complex filters. The intention to combine them in one as follows:

Declare @BoolSet Bit = 0

Select Distinct Top (1200)
a.called, a.engineer, a.response, a.call_no
From
[tablea] a
Inner Join
[tableb] b On a.id = b.id
Where
(@BoolSet = 1 And FirstFilter) Or (@BoolSet = 0 And SecondFilter)

FirstFilter and SecondFilter are a complex filters based purely on declared variables and columns value. I was hoping @BoolSet will essentially force SQL Server to only evaluate one side of where clause.

Run separately the queries take 2 or 3 seconds. But combined as above, the query takes minutes to run. Why? It appears SQL Server is evaluating both FirstFilter and SecondFilter completely disregarding @BoolSet. I expect it to be smarter than that. What am I missing?

 

[MathiasB]

Solution is split the query into 2 and do a Union

 

[Chriss Miller]

(@BoolSet = 1 And FirstFilter) Or (@BoolSet = 0 And SecondFilter)

Hmm, it should indeed be simple boolean algebra to disregard one branch of this, seems the SQL optimizer does not realize the variable is constant during the query. In theory it could change, but there would need to be some section in the query itself, setting @Boolset. I think it could differ if you actually make this a parameter of a table valued stored procedure.

And now, I assume, your union will essentially have one empty result and one actual result, depending on a bit value. I wonder whether it would be simpler to have two queries and call the one according to a bit, that you actually want to run.

 

[Chriss Miller]

Here's an article worth reading, more generally on the topic of optional conditions and how they are better optimized: https://www.sommarskog.se/dyn-search.html
One shot - not the only one - is forcing recompilation, i.e. make SQL Server optimize for the current case, not the general case, which is what's normal. It's not the only shot on the topic. But what you have is a common problem you find a lot of discussions about.

 

[MathiasB]

Hmm, it should indeed be simple boolean algebra to disregard one branch of this, seems the SQL optimizer does not realize the variable is constant during the query. In theory it could change, but there would need to be some section in the query itself, setting @Boolset. I think it could differ if you actually make this a parameter of a table valued stored procedure.

And now, I assume, your union will essentially have one empty result and one actual result, depending on a bit value. I wonder whether it would be simpler to have two queries and call the one according to a bit, that you actually want to run.

Yes that's correct only one of those produces and results. I tried a union it's as fast as running the single query. I wasn't convinced so I posted the same question on stackoverflow. Apparently union is the commonly known solution. I wanted the query to remain insi compliant so union it is.

 

[MathiasB]

You are right just running the right query would be best practice. But it is bound to a grid on vfp form with lots of filters i wanted to keep it that way. One requery() does the job.

 

[Chriss Miller]

Using VFP as a frontend you have completely different means, like defining a view with a macro substitution of the WHERE clause. Define a view with ?&lcWhere. lcWhere must be set at the time of defining the view - for example lcWhere="1=0" - but it won't be substituted once at definition for all future view uses, whatever you set into lcWhere will be used and that allows for view parameters within lcWhere, which involve further view parameters, i.e. you could later set lcWhere="customerid=?lnCustomerid" and lnCustomerid=1. It's compatible with REQUERY(), too.

The only downside is such views can't be edited in the view designer of VFP, but that's a low price for the possibilites.

You should ask about such things in a VFP thread.

By the way, the limitation of using macro substitution in view definitions is, that the result structure has to be constant, so you can't define a universal view like ?&lcSQL and let it do any SQL. You also can't obviously Select ?&lcClumns From Table to vary the list of columns.

Not sure, whether you use remote views or CA (which would make this even simpler) or parameterized table valued stored procedure or a view defined in MSSQL. Doing it on the VFP side is surely a well working solution, though.