function returning tables with fulltextsearch slow

Question

Hi,

I have a table returning function containing fulltext queries on 5 tables. 

How come in QA, it takes less than 3 seconds to select from the function while it takes 7 seconds(if I search for words) to more than a minute(if I search numbers) to execute in production?  The discrepencies of the main table is only 300.  QA has 2800, Production has 2500.  I already compared the primary keys, foreign keys of tables in that SQL and they match.

SQL is as follows:

  select distinct b.bulletinid, b.bulletinNo, b.createdDate, u.UnitName, b.Topic
  from dbo.BulletinContactPerson bcp inner join bulletin b on bcp.bulletinid = b.bulletinid
    inner join bulletinAgency ba on ba.bulletinid = b.bulletinid
    inner join Units u on b.attentionUnitCode = u.unitCode
    left outer join agency ag on ag.agencycode = ba.agencycode
  where --((b.[status] = @status) or (@status is null)) and
    ((b.[status] = 'P') and (@status is null) or
     (@status is not null)) and  
    (contains(bcp.*,@SearchWordMod) or
     contains(b.*,@SearchWordMod) or contains(ba.*,@SearchWordMod) or contains(u.UnitName,@SearchWordMod) or
     contains(ag.*,@SearchWordMod)) AND
    b.InactiveDate is null and bcp.InactiveDate is null and ba.InactiveDate is null

   union
  
  select distinct b.bulletinid, b.bulletinNo, b.createdDate, u.UnitName, b.Topic
  from dbo.BulletinContactPerson bcp inner join bulletin b on bcp.bulletinid = b.bulletinid
    inner join bulletinAgency ba on ba.bulletinid = b.bulletinid
    inner join Units u on b.attentionUnitCode = u.unitCode
    left outer join agency ag on ag.agencycode = ba.agencycode
  where --((b.[status] = @status) or (@status is null)) and
    ((b.[status] = 'P') and (@status is null) or
     (@status is not null)) and  
    (freetext(bcp.*,@search) or
     freetext(b.*,@search) or freetext(ba.*,@search) or freetext(u.UnitName,@search) or
     freetext(ag.*,@search)) and
    b.InactiveDate is null and bcp.InactiveDate is null and ba.InactiveDate is null and
    PATINDEX('%[^A-Z]%',upper(@search)) = 0

I looked at the Estimated Execution Plan and there are discrepencies also....
QA vs Production
Hash Match  vs Merge Join(Inner Join)
Nested Loop vs Merge Join

Index Spool <- Remote Scan  vs. Index Seek(nonClustered)
Index Spool <- Remote Scan vs. Table Valued Function (FullTextMatch)
Index Spool <- Remote Scan vs. Index Seek(nonClustered)
Index Spool <- Remote Scan vs. Table Valued Function (FullTextMatch)

If anyone can provide any leads on how to optimized production, please let me know.

Thanks!

Answer 1

Hi Tinac99,

Regarding to your description, the query takes longer when you execute the query. That might be related to the two servers with different data volume and one is used remote scan, which is a scan against a remote object so that might be takes longer.

Do your tables have statistics on them, and if so, are they being updated regularly? Statistics are what enable the server to choose good execution plans. Apart from analyzing above, you would start making sure statistics is up-to-date, all indexes are there, table definitions matches, amounts and skew of data is the same.

There are blog posts on the operators used to implement joins in SQL Server as below:
Nested Loops Join: http://blogs.msdn.com/craigfr/archive/2006/07/26/679319.aspx
Hash Join: http://blogs.msdn.com/craigfr/archive/2006/08/10/687630.aspx
Merge Join: http://blogs.msdn.com/craigfr/archive/2006/08/03/merge-join.aspx

Additional you can refer to 10 Ways to Optimize SQL Server Full-text Indexing

 

 

---

Regards, Amber zhang