May 19, 2017 at 7:45 am
I have a table with multiple columns that have a full text index on them.
Certain columns need to be searched for certain text(s) explicitly. but sometimes we don't want them to be evaluated and excluded from the search.
To keep it simple it looks something like this in the proc that is called from the API
SELECT COUNT(1) as totalcount
FROM dbo.Table
WHERE 1=1
AND CONTAINS(ColumnOne, @One)
AND CONTAINS(ColumnTwo, @Two)
AND CONTAINS(ColumnThree, @three)
AND CONTAINS(ColumnFour, @Four)
AND CONTAINS(ColumnFive, @Five)
A lot of the time some of the parameters will be NULL, there are times they can all be NULL so I changed the query to be as follows due to some reading on other sites about how to handle null in full text
SET @One = COALESCE(@One, '""');
SET @Two = COALESCE(@Two, '""');
SET @Three = COALESCE(@Three, '""');
SET @Four = COALESCE(@Four, '""');
SET @Five = COALESCE(@Five, '""');
SELECT COUNT(1) as totalcount
FROM dbo.Table
WHERE 1=1
AND (@One = '""' OR CONTAINS(ColumnOne, @One))
AND (@Two = '""' OR CONTAINS(ColumnTwo, @Two))
AND (@Three = '""' OR CONTAINS(ColumnThree, @Three))
AND (@Four = '""' OR CONTAINS(ColumnFour, @Four))
AND (@Five = '""' OR CONTAINS(ColumnFive, @Five))
this often became very slow for large returns. If I manually run the query with the AND clauses of parameters that exist it is much much faster than using the OR.
What is the best way of doing something like this?
I have used dynamic SQL to build up the query and it seems to run OK but I am worried that in large scale productions that there might be issues down the line with query plans getting mixed up or not uses if it stays in dynamic SQL?
May 19, 2017 at 8:21 am
rob.lewis 86087 - Friday, May 19, 2017 7:45 AMI have a table with multiple columns that have a full text index on them.Certain columns need to be searched for certain text(s) explicitly. but sometimes we don't want them to be evaluated and excluded from the search.
To keep it simple it looks something like this in the proc that is called from the API
SELECT COUNT(1) as totalcount
FROM dbo.Table
WHERE 1=1
AND CONTAINS(ColumnOne, @One)
AND CONTAINS(ColumnTwo, @Two)
AND CONTAINS(ColumnThree, @three)
AND CONTAINS(ColumnFour, @Four)
AND CONTAINS(ColumnFive, @Five)A lot of the time some of the parameters will be NULL, there are times they can all be NULL so I changed the query to be as follows due to some reading on other sites about how to handle null in full text
SET @One = COALESCE(@One, '""');
SET @Two = COALESCE(@Two, '""');
SET @Three = COALESCE(@Three, '""');
SET @Four = COALESCE(@Four, '""');
SET @Five = COALESCE(@Five, '""');SELECT COUNT(1) as totalcount
FROM dbo.Table
WHERE 1=1
AND (@One = '""' OR CONTAINS(ColumnOne, @One))
AND (@Two = '""' OR CONTAINS(ColumnTwo, @Two))
AND (@Three = '""' OR CONTAINS(ColumnThree, @Three))
AND (@Four = '""' OR CONTAINS(ColumnFour, @Four))
AND (@Five = '""' OR CONTAINS(ColumnFive, @Five))this often became very slow for large returns. If I manually run the query with the AND clauses of parameters that exist it is much much faster than using the OR.
What is the best way of doing something like this?
I have used dynamic SQL to build up the query and it seems to run OK but I am worried that in large scale productions that there might be issues down the line with query plans getting mixed up or not uses if it stays in dynamic SQL?
You can try by adding
OPTION (RECOMPILE)
in the end of the query, and watch it.
Igor Micev,My blog: www.igormicev.com
May 19, 2017 at 8:22 am
that will a new query plan each time though?
May 19, 2017 at 8:33 am
rob.lewis 86087 - Friday, May 19, 2017 8:22 AMthat will a new query plan each time though?
Not for the entire stored procedure, but a new execution context will be generated for that query (fragment of the stored procedure).
Execution contexts are reusable, so not always generation of a new one context.
Igor Micev,My blog: www.igormicev.com
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply