I have search page with some filters where the user can refine the search.
This is implemented with an advanced query that has some conditional filters, and using this pattern
SELECT....FROM...WHERE....
AND @colum1filter = 0 OR {tableA}.{column1} = @colum1filter
AND @colum2filter = 0 OR {tableB}.{column2} = @colum2filter
AND @colum3filter = 0 OR {tableC}.{column3} = @colum3filter
...
The columns are from different tables, there are a lot of JOINs, and thousands of records in the database
This query throws a timeout exception.
What I realized is that it has something to do with the way the parameters are passed/handled.
I made some tests with Service Studio and if instead of using parameters I actually put the specified values in the advanced query and test, the timeout doesn't occur
AND 0 = 0 OR {tableA}.{column1} = 0
AND 123 = 0 OR {tableB}.{column2} = 123
AND 0 = 0 OR {tableC}.{column3} = 0
Running the query like this in Service Studio is fast. Even putting all "0" (making the search without filters)
I tried using separate parameters for applying the filter and the filter value like this
AND @applycolum1filter = 0 OR {tableA}.{column1} = @colum1filter
AND @applycolum2filter = 0 OR {tableB}.{column2} = @colum2filter
AND @applycolum3filter = 0 OR {tableC}.{column3} = @colum3filter
And it's the same. Timeout error
Also tried the @applycolum#filter parameters of different types (bool, int, text) and the result is the same. Always a timeout exception
But running the query like this
No timeout. Very fast
Anyone have experienced something like this?
I have a solution: Having text parameters, expand inline, and pass "" or "AND {tableA}.{column1} = 123"
and have the SQL statement like this
@colum1filter
@colum2filter
@colum3filter
But I don't like this.
Thanks
Hi Ricardo,
Assuming you have the litteral SQL WHERE statements as in your mail, you forgot the parentheses around the OR statements, as they have lower priority over AND. You want:
AND (@colum1filter = 0 OR {tableA}.{column1} = @colum1filter) AND (@colum2filter = 0 OR {tableB}.{column2} = @colum2filter) AND (@colum3filter = 0 OR {tableC}.{column3} = @colum3filter)
Apart from that, you could've been hit by a bad query plan in SQL caused by using the parameters. You could try to use an "OPTION (OPTIMIZE FOR UNKNOWN)" at the end of the SQL to see if that helps, and otherwise the second option you used isn't so bad, as it could trigger different query plans depending on whether there's a filter or not (which could also speed up things).
Hi Kilian,
It's just my mistake when writing this post (I simplified a lot). I actually have the parentheses in the query.
But thanks
Ricardo
Good :). But the other stuff I wrote still stands.
Hi @Ricardo Costa ,
have you checked if using CASE WHEN instead of OR improves it ?
so something like
AND
CASE WHEN @applycolum1filter = 1 THEN
CASE WHEN {tableA}.{column1} = @colum1filter THEN 1
END
ELSE 1
END = 1
Thanks Dorine
I didn't try that but I will.
I'll keep you posted