0

I pass in aaa,bbb,ccc to @Tag for the stored procedure SearchNewsByTag, and it needs to select all news items that have at least one similar keywords (say like 'aaa' or 'bbb' or 'ccc'...)

ALTER PROCEDURE [dbo].[SearchNewsByTag]
    (@PageIndex         int = 0, 
     @PageSize          int = 2147483644,
     @Tag               nvarchar(400) = null,   
     @TotalRecords      int = null OUTPUT)
AS
BEGIN
    DECLARE @PageLowerBound int
    DECLARE @PageUpperBound int 

    SET @PageLowerBound = @PageSize * @PageIndex
    SET @PageUpperBound = @PageLowerBound + @PageSize + 1

    declare @FilteredTags as TagTable

    INSERT INTO @FilteredTags (Tag)
        SELECT data 
        FROM dbo.nop_splitstring_to_table(@Tag, ',')    -- split the input Tag into separate keywords

    SELECT * 
    FROM
        (SELECT 
             ROW_NUMBER() OVER (ORDER BY id) AS RowNum, *  
         FROM News n
         WHERE dbo.HasSimilarTag(@FilteredTags,n.Tags) > 0) p    -- pass the table with a news tag to check 
    WHERE 
        [p].RowNum > @PageLowerBound AND [p].RowNum < @PageUpperBound

and a scalar function to check if a news has a similar keyword(tag) with one of the input keywords (@Tag parameter)

ALTER FUNCTION [dbo].[HasSimilarTag]
    (@Tags TagTable READONLY, @TagFromRecord nvarchar(10) = null)
RETURNS int
AS
BEGIN
    DECLARE @name int

    declare @TagsFromRecord as TagTable
    INSERT INTO @TagsFromRecord (Tag)
    SELECT data FROM [nop_splitstring_to_table](dbo.RemoveDauTVCLR(@TagFromRecord), ',')    

    declare @TagsResult as TagTable
    INSERT INTO @TagsResult (Tag)
    SELECT Tag FROM @Tags intersect SELECT Tag from @TagsFromRecord   

    select @name= COUNT(1) from @TagsResult
    RETURN @name
END

Everything works correctly but it's rather slow, usually take up to 5 seconds to finish. News table has around 110k records only.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
nam vo
  • 3,271
  • 12
  • 49
  • 76
  • It runs too slow, something is not right there. – nam vo Jul 30 '14 at 08:16
  • 2
    Did you check the *Execution Plan*? – Christian Phillips Jul 30 '14 at 08:16
  • @christiandev i have no idea about it, thanks for the suggestion, i can learn something new. – nam vo Jul 30 '14 at 08:20
  • @namvo, take a look here: http://stackoverflow.com/questions/7359702/how-do-i-obtain-a-query-execution-plan – Christian Phillips Jul 30 '14 at 08:24
  • 1
    If you're using SQL Server **2008** or newer (you didn't say), you should check out the [table-valued parameter](http://msdn.microsoft.com/en-us/library/bb675163%28v=vs.110%29.aspx) feature (instead of passing in a comma-separated string which you have to then parse out) – marc_s Jul 30 '14 at 09:19
  • There are too many pieces here to narrow down without more information. Meaning: 1) what is the definition of the Table Type [TagTable], 2) what is the definition of the TVF [nop_splitstring_to_table], 3) what is the definition of the CLR UDF [RemoveDauTVCLR]? Also: why is the T-SQL UDF [HasSimilarTag] a multiline UDF instead of an line TVF used with CROSS APPLY in [SearchNewsByTag]? – Solomon Rutzky Jul 30 '14 at 16:17

0 Answers0