Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

We have a performance problem with SQL Server (2008 R2) Full text search. When we have additional where conditions to full-text search condition, it gets too slow.

Here is my simplified query:

SELECT * FROM Calls C
WHERE (C.CallTime BETWEEN '2013-08-01 00:00:00' AND '2013-08-07 00:00:00')
AND CONTAINS(CustomerText, '("efendim")')

Calls table's primary key is CallId (int, clustered index) and also Calls table indexed by CallTime. We have 16.000.000 rows and CustomerText is about 10KB for each row.

When I see execution plan, first it finds full-text search resultset and then joins with Calls table by CallId. Because of that, if first resultset has more rows, query gets slower (over a minute).

This is the execution plan:

enter image description here

When I run where conditions seperately, it returns 360.000 rows for CallTime condition:

SELECT COUNT(*) FROM Calls C
WHERE (C.CallTime BETWEEN '2013-08-01 00:00:00' AND '2013-08-07 00:00:00')

and 1.200.000 rows for Contains condition:

SELECT COUNT(*) FROM Calls C
WHERE CONTAINS(AgentText, '("efendim")')

What can I do to increase performance of my query?

share|improve this question
    
Have you tried updatating Statistics and rerun the query ? Also try using containstable to see if it makes difference in execution time. –  Kin Sep 23 '13 at 16:44

4 Answers 4

Kin made a couple of suggestions which should be helpful.

First: Make sure that your statistics for the CallTime index are up to date.

Your plan shows that the time filter is being made by seeks to the clustered index. So, for some reason the CallTime index is not being used. What is the definition of that index? If it is a multicolumn index, be sure that the most specific column is first. Example:

IDX_Calls_CallTime
  NOT: CallID, CallTime
  USE: CallTime, CallID

Second: Consider using CONTAINSTABLE.

I doubt that you will ever want to return 1.200.000 rows for "efendim". By using CONTAINSTABLE and ranking you can set the top number of rows that you want, thus reducing the number of FullText results to process to 10, 100, 1000, or whatever fits your needs.

share|improve this answer

Consider using a hint to remove nested loops as a choice for the optimizer, eg

 -- Hint means Nested Loop join will never be chosen, but still leaves the Optimizer some choices
SELECT * FROM Calls C
WHERE (C.CallTime BETWEEN '2013-08-01 00:00:00' AND '2013-08-07 00:00:00')
AND CONTAINS(CustomerText, '("warning")')
OPTION ( MERGE JOIN, HASH JOIN )

Be aware of the potential negative impact of using this hint, eg some of your queries with lower rowcount may actually perform better with Nested Loops, so test before implementing.

To make better use of your non-clustered index you could try a rewrite. I experimented with the options below, ie a more prescriptive query but still found the above query quicker. YMMV.

-- CONTAINSTABLE; as already suggested by others
SELECT * 
FROM dbo.Calls C
    INNER JOIN CONTAINSTABLE(dbo.Calls, CustomerText, '("warning")') ft ON c.callId = ft.[KEY]
WHERE (C.CallTime BETWEEN '2013-08-01 00:00:00' AND '2013-08-07 00:00:00')


-- This query is more likely to use the non-clustered index as it is covering for CallTime 
-- and callId only (not SELECT *); you could then join back to main table
SELECT callId
FROM Calls C
WHERE (C.CallTime BETWEEN '2013-08-01 00:00:00' AND '2013-08-07 00:00:00')
INTERSECT
SELECT callId
FROM Calls C
WHERE CONTAINS(CustomerText, '("warning")')


-- Or explicitly persist the results to a temp table; this may be slower on occasions than the 
--'all-in-one' queries but would probably make for more consistent results
IF OBJECT_ID('tempdb..#tmp') IS NOT NULL DROP TABLE #tmp 
CREATE TABLE #tmp ( callId INT PRIMARY KEY )
GO

INSERT INTO #tmp
SELECT callId
FROM Calls C
WHERE (C.CallTime BETWEEN '2013-08-01 00:00:00' AND '2013-08-07 00:00:00')

SELECT * 
FROM Calls C
WHERE CONTAINS(CustomerText, 'warning')
AND EXISTS ( SELECT * FROM #tmp t WHERE c.callId = t.callId )
share|improve this answer

don't use * (asterisk) for count or any other operation until and unless you want all column values .Because whenever you use asterisk(*) It takes too much time for execution. Define column names whose value you want.

For count function Use particular a column where value exists in each rows.

share|improve this answer
    
Actually, i break down my main query into 2 example queries to emphasize the record counts i'm dealing with. Count(*) is not the part of my problem. –  Cankut Sep 23 '13 at 14:04
    
And, of course, SELECT COUNT(*) which returns a single column is quite a different operation than SELECT * which returns every column in the table. –  RLF Sep 23 '13 at 17:02

You can use Containstable. It is recommended to increase performances.

Here is the documentation : http://technet.microsoft.com/fr-fr/library/ms189760%28v=sql.100%29.aspx

SELECT select_list
FROM table AS FT_TBL INNER JOIN
CONTAINSTABLE(table, column, contains_search_condition) AS KEY_TBL
ON FT_TBL.unique_key_column = KEY_TBL.[KEY]
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.