CODEWITHSUNDEEP
sqlsql-serverfull-text-search
TomR
TomR

Reputation: 11

SQL text search speed

I am trying to improve the speed of a text search procedure in a MSSQL database.

The procedure queries multiple tables to search for any matching records (properties, users, businesses etc.). For example the users table has over 400,000 rows and we are searching 6 nvarchar columns for the specified search criteria. Also if there are spaces we search for each word in the search criteria seperately to improve accuracy of results. The same procedure searches a property table that holds over 120,000 rows and queries a single address column.

I have implemented full text search to the appropriate tables and columns, however the search can take over 7 to 10 seconds to return results. Idealy I would like this to be below 2 seconds.

This is an example of the query I am using to find users:

SELECT
    u.[UserId]
FROM [mgr].[Users] u
LEFT JOIN [mgr].[UserAddresses] ua ON ua.[UserId] = u.[UserId]
LEFT JOIN [mgr].[vAddress] ad ON ad.[AddressId] = ua.[AddressId]
WHERE CONTAINS((u.[Forename], u.[Surname], u.[Email], u.[Phone], u.[WPhone], u.[MPhone]), @SearchCriteria)
OR CONTAINS(ad.[FullAddress], @SearchCriteria);

Is there anything I can do to improve the speed of this? We are expecting the amount of data in these tables to increase quite rapidly so any help to improve scalability would be great.

Upvotes: 0

Views: 332

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270873

I wonder if it would be faster to split this into two queries:

SELECT u.[UserId]
FROM [mgr].[Users] u
WHERE CONTAINS((u.[Forename], u.[Surname], u.[Email], u.[Phone], u.[WPhone], u.[MPhone]), @SearchCriteria)
UNION  -- on purpose to remove duplicates
SELECT ua.[UserId]
FROM [mgr].[UserAddresses] ua ON  = u.[UserId] JOIN
     [mgr].[vAddress] ad
     ON ad.[AddressId] = ua.[AddressId]
WHERE CONTAINS(ad.[FullAddress], @SearchCriteria);

Upvotes: 1

Related Questions