Fulltext search via ADO.NET

Fulltext search via ADO.NET

Post by Dan » Wed, 11 Mar 2009 05:13:54


I have a VB.NET Windows Forms application that needs to utilize SQL Server
Full text indexing to peform full text searches across many different
columns. We are currently experiencing slow performance. Is there any
recommendations as to speeding up the results?
 
 
 

Fulltext search via ADO.NET

Post by Dan » Wed, 11 Mar 2009 05:33:08

More specifically, the full text query I'm issuing is as follows:

SELECT *
FROM vwFolderFilesNew v
INNER JOIN [Server1].dbo.ObjectsVersionHistory a ON v.ID = a.ObjectID AND
v.VersionID = a.VersionID
WHERE v.Path = 'root\user' AND Contains(a.TextImage, 'performance')

It takes about 30 seconds and only 189 files get returned. Is there any
improvements on the above statement that can be made?

 
 
 

Fulltext search via ADO.NET

Post by Russell Fi » Wed, 11 Mar 2009 06:14:47

Dan,

The SELECT * will return the contents of every column, including the
TextImage. This could easily be what is consuming the most time, especially
if TextImage values are quite large. If you do not need all the columns,
reduce to the bare mimimum column list.

Then, when you need to examine the contents of TextImage, just select the
single row that you need for any one display. Otherwise, just let it
satisfy the Contains statement.

By the way, your explanation says that you are using full text indexing
across many columns, but in this case you are only searching one column.

RLF
 
 
 

Fulltext search via ADO.NET

Post by Dan » Wed, 11 Mar 2009 06:39:41

Thanks for the response. Actually, the SELECT * is on a View called
vwFolderFilesNew, which does not contain the TextImage field. I need to
return * from vwFolderFilesNew in order to populate the user interface.
Seems like the bulk of the time is on the Contains query, as when I isolate
that, it takes long.
 
 
 

Fulltext search via ADO.NET

Post by Russell Fi » Wed, 11 Mar 2009 23:46:24

Dan,

Perhaps you simplified the code for posting here, but it you are only
selecting from the view, the first line should read:
SELECT v.*

Since the sample code only says "SELECT *" it will be selecting all columns
from both vwFolderFilesNew and ObjectsVersionHistory.

For another tack, since you are joining between the view and a table, you
should also check the indexes on the tables that underly the
vwFolderFilesNew view as well as the ObjectsVersionHistory table. Examine
the query execution plan to make sure that the joins are using the indexes.

RLF