SQL which uses the Net Search Extender CONTAINS function is slow

Technote (troubleshooting)


Problem(Abstract)

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.

Symptom

SQL which uses the Net Search Extender CONTAINS function is slow.


Cause

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.


Bad Example:

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)"

Good Example:

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"


Rate this page:

(0 users)Average rating

Add comments

Document information


More support for:

DB2 for Linux, UNIX and Windows
Extenders - Net Search Extender

Software version:

9.1, 9.5, 9.7, 9.8

Operating system(s):

AIX, HP-UX, Linux, Linux iSeries, Linux pSeries, Linux zSeries, Solaris, Windows

Software edition:

Advanced Enterprise Server, Enterprise Server, Express, Express-C, Personal, Workgroup Server

Reference #:

1643930

Modified date:

2013-07-18

Translate my page

Machine Translation

Content navigation