function returning tables with fulltextsearch slow

• Friday, May 18, 2012 12:50 AM

   

   
  

  0

  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!

  • Reply
  • Quote