0

I just don't get it. The results for calling the stored procedure below come back within a few seconds or less. But when the @SearchPhrase parameter gets changed, it takes minutes to complete. You can see I've used the WITH RECOMPILE option. The Execution Plan has the same percentages, whether it's fast or slow, so I think that it's not an indexing problem

    CREATE PROCEDURE [dbo].[getItemsSearchKeyword] 
        @MaxItems int = 100,
        @LatestDate datetime = null,
        @ItemTypeIds tinyint = 255,
        @SearchPhrase nvarchar(256) = null,
        @EarliestDate datetime = null

    WITH RECOMPILE
    AS
    BEGIN
    SET NOCOUNT ON;

    --avoid parameter sniffing problems...
    DECLARE @MaxItemsC int = @MaxItems
    DECLARE @LatestDateC datetime = ISNULL(@LatestDate, GETUTCDATE())
    DECLARE @ItemTypeIdsC tinyint = @ItemTypeIds
    DECLARE @SearchPhraseC nvarchar(256) = @SearchPhrase
    DECLARE @EarliestDateC datetime = ISNULL(@EarliestDate, '1753-01-01')

    IF (@SearchPhraseC IS NULL OR LEN(@SearchPhraseC) = 0) RETURN

    -- these are used to filter the primary FTS index (4-bytes TimeTaken and 4-bytes Id make up the primary FTS key)
    DECLARE @EarlyDate bigint = CONVERT(bigint, @EarliestDateC, 112) * POWER(CAST(2 AS BIGINT), 32)
    DECLARE @LateDate  bigint = CONVERT(bigint, DATEADD(day, 1, @LatestDateC), 112) * POWER(CAST(2 AS BIGINT), 32) - 1

    SELECT
        TOP(@MaxItemsC)
        i.ID,
        i.TimeTaken,
        i.Title,
        i.[Description],
        i.BaseURL, 
        i.LargeURL,
        i.ThumbURL,
        i.imageWidth,
        i.imageHeight,
        i.ItemTypeId,
        i.StatusTypeId,
        i.Username

    FROM 
        Item i WITH (READPAST)

    WHERE 
        FREETEXT((i.Title,i.Tags), @SearchPhraseC) AND i.DateAndId BETWEEN @EarlyDate AND @LateDate
        AND i.TimeTaken > @EarliestDateC AND i.TimeTaken <= @LatestDateC
        AND ((@ItemTypeIdsC & i.ItemTypeId) != 0)
        AND i.StatusTypeId IN (1, 6, 7, 8, 9, 10)

    ORDER BY i.TimeTaken DESC

END

The Item table is large -- about 40 million records. Here's a sample call. The first time it takes over 2 minutes to run. Subsequent runs are in the 5 seconds or less range. But when the @SearchPhrase changes, the time often (but not always) goes back up to a number of minutes to complete.

getItemsSearchKeyword @ItemTypeIds = 255, @LatestDate = '2013-10-07 11:32:59 AM', @EarliestDate = '2013-10-06 11:42:59 AM', @MaxItems = 50, @SearchPhrase = 'upper';

Any suggestions? Thanks.

Jarvis
  • 681
  • 8
  • 33
  • Note: I have the execution plan XML, but it's very large--so I didn't post it here. I'm hoping we can come up with a suggestion without it. – Jarvis Dec 04 '13 at 17:45
  • Sounds like you are getting the benefit of caching of data and indexes in memory. As for improving the general performance, how is the table indexed? – HABO Dec 04 '13 at 17:51
  • The FTS is using this index... CREATE UNIQUE NONCLUSTERED INDEX [IX_FTS] ON [dbo].[Item] ( [DateAndId] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] GO where the DateAndId field is ...[DateAndId] AS (CONVERT([bigint],[TimeTaken],(112))*power(CONVERT([bigint],(2)),(32))+[Id]) PERSISTED NOT NULL – Jarvis Dec 04 '13 at 18:00

0 Answers0