The NSE CONTAINS function is a scalar UDF which will table scan any result set passed to it. Therefore care mush be taken when adding the NSE CONTAINS function to and SQL query.
One solution to minimize the impact of the NSE CONTAINS function is to minimize the result set size being passed to the NSE CONTAINS function.
SQL which uses the Net Search Extender CONTAINS function is slow.
The NSE CONTAINS function will execute a table scan on result set passed to it.
Diagnosing the problem
An SQL query will run significantly slower when a call to the NSE CONTAINS function is added to the query is such a manner that it results in a complete table scan .
Resolving the problem
When using the NSE CONTAINS function in the WHERE clause of an SQL Query, place the CONTAINS function so that the smallest result set is passed to the CONTAINS function.
In the following example the NSE CONTAINS function will table scan my.table.
" select * from my.table where CONTAINS(textData, 'help') = 1and (received_date = today and severity = 1)"
In the following example the NSE CONTAINS function will only need to process a subset of my.table.
" select * from my.table where (received_date = today and severity = 1) and CONTAINS(textData, 'help') = 1"