Databases
StackOverflow2013
Postsdbo
POFull-text search Stored Procedure runs slowly, except when it runs fast
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POFull-text search Stored Procedure runs slowly, except when it runs fast
    primarykey
    data
    text
    <p>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</p> <pre><code> 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 &gt; @EarliestDateC AND i.TimeTaken &lt;= @LatestDateC AND ((@ItemTypeIdsC &amp; i.ItemTypeId) != 0) AND i.StatusTypeId IN (1, 6, 7, 8, 9, 10) ORDER BY i.TimeTaken DESC END </code></pre> <p>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.</p> <pre><code>getItemsSearchKeyword @ItemTypeIds = 255, @LatestDate = '2013-10-07 11:32:59 AM', @EarliestDate = '2013-10-06 11:42:59 AM', @MaxItems = 50, @SearchPhrase = 'upper'; </code></pre> <p>Any suggestions? Thanks.</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. This table or related slice is empty.
    1. USJarvis
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. CONote: 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.
      singulars
      1. POFull-text search Stored Procedure runs slowly, except when it runs fast
      1. USJarvis
    2. COSounds 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?
      singulars
      1. POFull-text search Stored Procedure runs slowly, except when it runs fast
      1. USHABO
    3. COThe 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
      singulars
      1. POFull-text search Stored Procedure runs slowly, except when it runs fast
      1. USJarvis
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload