Improving and Speeding Up Search Result in SQL Server 2008

Question

I am having issues getting good search results from my complex search algorithm. Understandably, I should be using FREETEXT, but I am fairly new to SQL Server and I don't know how to create a Full-Text-Search Index, and the server is also running low on hard drive space. So, I have to use what I have right now. The database Member Table has over a million entries, so results are slow to retrieve.

EXAMPLE INPUT

@CriteriaStr = 'Attorney New York'
@PageSize = 10
@PG = 1

Allow me to show you the code:

MAIN SEARCH SQL SCRIPT

IF @MODE = 'MEMBER-MAIN-SEARCH'
    BEGIN
        SELECT @TotalPages = CEILING(COUNT(*)/@PageSize)
        FROM Member
        WHERE MemberID IN (SELECT MemberID FROM sbuser.tf_MemberSearchCriteria(REPLACE(@CriteriaStr,' ',',')))
        AND Claimed = 'Y'
        AND Viewable = 'Y';

        WITH MemberSearchResults AS
        (
            SELECT ROW_NUMBER() OVER(ORDER BY a.Claimed DESC) AS RowNum,
            a.MemberID,                                 -- 1    
            a.FirstName,                                -- 2
            a.LastName,                                 -- 3
            a.MemberDisplayName AS DisplayName,         -- 4
            a.UserName,                                 -- 5
            a.LastLogin,                                -- 6
            a.PrCity,                                   -- 7
            a.BusinessName,                             -- 8
            a.ShortDesc,                                -- 9
            a.PrAddr1,                                  -- 10
            a.PrAddr2,                                  -- 11
            a.PrZip,                                    -- 12
            b.Abbr,                                     -- 13
            c.Country,                                  -- 14
            a.AvatarMed,                                -- 15
            a.Gender,                                   -- 16
            d.Domain,                                   -- 17
            a.Claimed,                                  -- 18
            a.PrPhone,                                  -- 19
            a.PrShowAddress,                            -- 20
            a.ProfileTypeID,                            -- 21
            @TotalPages AS TotalPages                   -- 22
            FROM Member a
            RIGHT JOIN State b ON b.StateID = a.PrStateID
            INNER JOIN Country c ON c.CountryID = a.PrCountryID
            INNER JOIN Region d ON d.RegionID = a.MemberRegionID
            WHERE a.MemberID IN (SELECT MemberID FROM sbuser.tf_MemberSearchCriteria(REPLACE(@CriteriaStr,' ',',')))
            AND Claimed = 'Y'
            AND Viewable = 'Y'
        )
        SELECT * 
        FROM MemberSearchResults
        WHERE RowNum BETWEEN (@PG - 1) * @PageSize + 1 AND @PG * @PageSize
        ORDER BY Claimed DESC
    END

TF_MEMBERSEARCHCRITERIA

USE [storeboard]
GO
/****** Object:  UserDefinedFunction [sbuser].[tf_MemberSearchCriteria]    Script Date: 11/30/2012 08:22:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [sbuser].[tf_MemberSearchCriteria](@CriteriaStr varchar(255))
RETURNS @OUT TABLE (MemberID bigint)

AS

BEGIN
    DECLARE @Keyword varchar(150)


    DECLARE CUR CURSOR FOR SELECT Keyword from sbuser.tf_Keywords(@CriteriaStr)
    OPEN CUR
    FETCH NEXT FROM CUR INTO @Keyword
    WHILE @@FETCH_STATUS = 0
        BEGIN
            --INSERT INTO @OUT
            --SELECT MemberID FROM Member WHERE ShortDesc LIKE '%' + @Keyword + '%' AND Claimed = 'Y'  AND MemberID NOT IN (Select MemberID FROM @OUT)

            --INSERT INTO @OUT
            --SELECT MemberID FROM Member WHERE UserName LIKE '%' + @Keyword + '%' AND Claimed = 'Y'  AND memberID NOT IN (Select MemberID FROM @OUT)

            INSERT INTO @OUT
            SELECT MemberID FROM Member WHERE BusinessName LIKE '%' + @Keyword + '%' AND Claimed = 'Y' AND memberID NOT IN (SELECT MemberID FROM @OUT)

            INSERT INTO @OUT
            SELECT MemberID FROM Member WHERE FirstName + LastName LIKE '%' + REPLACE(@Keyword,' ','') + '%' AND Claimed = 'Y' AND memberID NOT IN (SELECT MemberID FROM @OUT)

            INSERT INTO @OUT
            SELECT a.MemberID
            FROM MemberBusinessCat a
            LEFT JOIN BusinessCat b ON b.BusinessCatID = a.BusinessCatID
            LEFT JOIN BusinessCat c ON c.BusinessCatID = b.ParentID
            WHERE b.CategoryName LIKE '%' + @Keyword + '%' OR c.CategoryName LIKE '%' + @Keyword + '%'
            AND a.MemberID NOT IN (SELECT MemberID FROM @OUT)


            FETCH NEXT FROM CUR INTO @Keyword

        END
    CLOSE CUR
    DEALLOCATE CUR


    DELETE FROM @OUT WHERE MemberID IS NULL

    RETURN
END

TF_KEYWORDS

USE [storeboard]
GO
/****** Object:  UserDefinedFunction [sbuser].[tf_Keywords]    Script Date: 11/30/2012 08:50:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [sbuser].[tf_Keywords] 
    (@KeywordList varchar(4000)
)
RETURNS @OUT TABLE (
    Keyword varchar(50))

AS
    BEGIN
        DECLARE @NEXTPOS bigint
        DECLARE @NEXTWORD varchar(50)
        DECLARE @LISTLEN int
        DECLARE @FAILSAFE int

        SET @FAILSAFE = 0

        SET @KeywordList = @KeywordList + ','
        WHILE @KeywordList <> '' AND @FAILSAFE < 50
            BEGIN
                SET @NEXTPOS = CHARINDEX(',',@KeywordList)
                SET @LISTLEN = LEN(@KEYWORDLIST)
                SET @NEXTWORD = LTRIM(RTRIM(REPLACE(LEFT(@KeywordList,@NEXTPOS),',','')))
                SET @KEYWORDLIST = RIGHT(@KEYWORDLIST,@LISTLEN-@NEXTPOS)


                INSERT INTO @OUT
                SELECT @NEXTWORD

                SET @FAILSAFE = @FAILSAFE + 1

            END

    RETURN
    END

I was wondering if it possible to order TF_MEMBERSEARCHCRITERIA by the number of result for any one member. A member could by an attorney and be in New York, which would make them show in results 3 times. The script deletes repeats, but could there be a way of counting the 3 results and then order then in DESC Order..

I am still relatively new, so I still don't fully understand the complex parts of the SQL, so please go easy on me.

Many thanks in advance, neojakey

Answer 1

Hi i am giving a big answer for this sorry if any mistakes let me know i commented the improvements area

Advice1 :-
1) Use indexed view and add the searchable columns to 1 coumn like u must have column memberID as primary and searchtext nvarchar(MAX) -- ADD all search coumns like exm
-- FirstName + LastName + BusinessName 2) create fulltext index over it and use contains for partial word search or for ranking order results freetextable you can lot of help how to write queries
3) Its easy and simple if you afford small disk space

Advice2 :- 1) use all joins for only last 10 rows . INTHEMO_Authentication your code

  ;    WITH 
    MemberSearchResults AS
        (
            SELECT ROW_NUMBER() OVER(ORDER BY a.Claimed DESC) AS RowNum,
           a.MemberID,  ----   bring  the primary  and  the columns need to  orderby  only  dont join  with unnessary tables inthis step 
           a.Claimed

            FROM Member a
            --RIGHT JOIN State b ON b.StateID = a.PrStateID
            --INNER JOIN Country c ON c.CountryID = a.PrCountryID
            --INNER JOIN Region d ON d.RegionID = a.MemberRegionID
            WHERE a.MemberID IN (SELECT MemberID FROM sbuser.tf_MemberSearchCriteria(REPLACE(@CriteriaStr,' ',','))  group by MemberID)
    ----  in that   function   dont use like --NOT IN (SELECT MemberID FROM @OUT)     its a small improvement  u can do here 
            AND Claimed = 'Y'    ----  ur checking this condition  on function tooo  then no need here  i think 
            AND Viewable = 'Y'  ----  ur checking this condition  on function tooo  then no need here  i think 
        )
        SELECT   --a.MemberID,                                 -- 1    
            --a.FirstName,                                -- 2
            --a.LastName,                                 -- 3
            --a.MemberDisplayName AS DisplayName,         -- 4
            --a.UserName,                                 -- 5
            --a.LastLogin,                                -- 6
            --a.PrCity,                                   -- 7
            --a.BusinessName,                             -- 8
            --a.ShortDesc,                                -- 9
            --a.PrAddr1,                                  -- 10
            --a.PrAddr2,                                  -- 11
            --a.PrZip,                                    -- 12
            --b.Abbr,                                     -- 13
            --c.Country,                                  -- 14
            --a.AvatarMed,                                -- 15
            --a.Gender,                                   -- 16
            --d.Domain,                                   -- 17
            --a.Claimed,                                  -- 18
            --a.PrPhone,                                  -- 19
            --a.PrShowAddress,                            -- 20
            --a.ProfileTypeID,                            -- 21
            --@TotalPages AS TotalPages                   -- 22
        FROM MemberSearchResults cross join (SELECT  COUNT(*) FROM MemberSearchResults) AS @TotalPages --  u have to do total page count like this 
          --RIGHT JOIN State b ON b.StateID = a.PrStateID
            --INNER JOIN Country c ON c.CountryID = a.PrCountryID
            --INNER JOIN Region d ON d.RegionID = a.MemberRegionID
        WHERE RowNum BETWEEN (@PG - 1) * @PageSize + 1 AND @PG * @PageSize 
        ORDER BY Claimed DESC

last thing you can use nolock if you are ok with dity reads.