1

I am trying to run a full-text search using the PHP sqlsrv driver for MS SQL. The query runs really slow. 5+ seconds to return any number of rows...

Query:

SELECT DISTINCT
  MAX(i.ID) AS ID,
  i.ItemLookupCode,
  MAX(i.DepartmentID) AS DepartmentID,
  MAX(i.CategoryID) AS CategoryID,
  MAX(i.Quantity) AS Quantity,
  MAX(CAST(i.Notes AS varchar(max))) AS Notes,
  MAX(CONVERT(varchar(30), i.Price, 1)) AS Price,
  MAX(i.SaleType) AS SaleType,
  MAX(i.SaleStartDate) AS SaleStartDate,
  MAX(i.SaleEndDate) AS SaleEndDate,
  MAX(CONVERT(varchar(30), i.SalePrice, 1)) AS SalePrice,
  MAX(i.PictureName) AS PictureName,
  MAX(n.SpoofStock) AS SpoofStock,
  MAX(n.PAM_Brand) AS Brand

FROM Item AS i

LEFT JOIN nitroasl_pamtable AS n
  ON i.ID = n.ItemID

WHERE CONTAINS(
  (i.ItemLookupCode, i.Notes, i.Description, i.ExtendedDescription),
  '"displayport" AND "hdmi"')

OR CONTAINS(
  (n.PAM_Brand, n.ManufacturerPartNumber, n.PAM_Keywords),
  '"displayport" AND "hdmi"')

AND ( i.WebItem = 1 AND i.Price > 0 )

GROUP BY i.ItemLookupCode

ORDER BY i.ItemLookupCode ASC

Thoughts

I am new to this SQL thing, and I only just learned that full-text search existed. It claimed to be faster when ran against larger databases and when returning large result sets. This query however, runs pretty slow (5+ seconds to return 42 rows).

I rebuilt the catalog's index using Server Management Studio and I updated the statistics for the table. These things were advised when queries were running slow.

Finally, I have two tables listed in the same FullTextCatalog. As the general functionality of this type of search escapes me, I am unsure if this is a bad thing, or a good thing... Why do I have two tables in the same catalog? I almost always query the two tables in the same query. Together, they hold our products' data.

Can anyone help me identify:

  1. Why this query takes forever to return rows locally

Application is hosted locally on IIS, PHP 5.6 with the 3.2 sqlsrv drivers used on SQL Server 2008 R2 (running on a server in our network).

UPDATE 1

When only using CONTAINS((row1,row2,etc), 'hdmi') the query runs a hair faster, but returns 543 rows? How does that work?

UPDATE 2

I ran the following series of queries to create my catalog and indexes:

use NWCA
create fulltext catalog FullTextCatalog as default

select *
from sys.fulltext_catalogs;

CREATE FULLTEXT INDEX ON on Item(ItemLookupCode, Notes, Description, ExtendedDescription)
KEY INDEX PK_Item;

CREATE FULLTEXT INDEX ON on nitroasl_pamtable(PAM_Brand, ManufacturerPartNumber, PAM_Keywords)
KEY INDEX PK_nitroasl_pamtable;

After deciding that queries were running super slow, I sought out solutions. I found recommendations stating that rebuilding the catalog would help; so I did so using the SMS UI.

I also read that I could try updating the statistics (mind you, I have no idea what these processes do as I am unfamiliar with Full Text Search in MS SQL):

UPDATE STATISTICS Item
GO

So yeah, anyone have any ideas or revelations after hearing the above update?

UPDATE Here is the results of the execution plan... Execution Plan

Derek Foulk
  • 1,892
  • 1
  • 19
  • 37
  • It could be related to the behavior of having not ONE fulltext index, but multiple. http://stackoverflow.com/questions/3162337/why-do-sql-fulltext-queries-slow-down-when-you-or – Brad D Oct 23 '15 at 18:50
  • I will check out these solutions. FYI, updated my post above with some additional details about how I built the catalog and indexes, as well as what I have tried... Thanks – Derek Foulk Oct 23 '15 at 19:02
  • Have a look here for some ways of optimising fulltext queries: http://sqlblog.com/blogs/joe_chang/archive/2012/02/19/query-optimizer-gone-wild-full-text.aspx – steoleary Oct 27 '15 at 14:18

0 Answers0