Inproving speed of T-SQL script

I need to improve the speed dramatically for the following script. I am thinking about removing the table valued function and placing everything in the stored procedure. But before I do it I wanted to get the experts to take a look and provide me with a solution or a few pointers. The scripting has paging functionality which needs to remain in place somehow.

Here firstly is the Store procedure:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROC [sbuser].[sp_MemberSearch]
@UserName varchar(200) = null,
@MemberID bigint = null,
@PG int = 1,
@ROWCT numeric(18,2) = 1,
@COLCT numeric(18,2) = 1,
@MODE varchar(50)

AS

IF @MODE = 'MEMBERSEARCH'
BEGIN
    SELECT
    MemberID,       -- 0
    UserName,       -- 1
    LastLogin,      -- 2
    PrCity,         -- 3
    Abbr,           -- 4
    Country,        -- 5
    AvatarMed,      -- 6
    Gender,         -- 7
    HasImages,      -- 8
    HasVideo,       -- 9
    HasAudio,       -- 10
    Domain,         -- 11 
    DisplayName,    -- 12
    CreateDate,     -- 13
    Claimed,        -- 14
    PG,             -- 15
    MAXPG,          -- 16
    TOTALRECS,      -- 17
    ProfileTypeID,  -- 18
    Zip,            -- 19
    PhoneNbr,   开发者_运维百科    -- 20
    PrPhone         -- 21
    FROM sbuser.tf_FindMember(@UserName,@MemberID,@PG,@ROWCT,@COLCT)

END

and here is the table valued function as named above:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO

ALTER FUNCTION [sbuser].[tf_FindMember] (
    @UserName varchar(200) = null,
@MemberID bigint = null,
@PG int = 1,
@ROWCT numeric(18,2) = 1,
@COLCT numeric(18,2) = 1 )

RETURNS @OUT TABLE (
MemberID bigint,          -- 0
UserName varchar(200),    -- 1
LastLogin datetime,       -- 2
PrCity varchar(50),       -- 3
Abbr varchar(5),          -- 4
Country varchar(50),      -- 5
AvatarMed varchar(50),    -- 6
Gender varchar(50),       -- 7
HasImages bit,            -- 8
HasVideo bit,             -- 9
HasAudio bit,             -- 10
Domain varchar(100),      -- 11
DisplayName varchar(255), -- 12
CreateDate datetime,      -- 13
Claimed varchar(1),       -- 14
PG int,                   -- 15
MAXPG int,                -- 16
TOTALRECS int,            -- 17
ProfileTypeID bigint,     -- 18
Zip varchar(50),          -- 19
PhoneNbr varchar(50),     -- 20
PrPhone varchar(25))      -- 21

AS

BEGIN

DECLARE @START numeric(18,2)
DECLARE @END numeric(18,2)
DECLARE @SIZE numeric(18,2)
DECLARE @MAXPG numeric(18,2)
DECLARE @TOTALRECS numeric(18,2)    
DECLARE @TOTALRECS_INT int
DECLARE @MAXPG_INT int
DECLARE @TOTALRECS_REMAINDER numeric(18,2)
SET @SIZE = @ROWCT * @COLCT
SET @Start = (((@PG - 1) * @Size) + 1)
SET @END = (@START + @SIZE - 1)


DECLARE @TMP1 TABLE (
    TMPID bigint  primary key identity(1,1),
    MemberID bigint,
    UserName varchar(200),
    LastLogin datetime,
    PrCity varchar(50),
    Abbr varchar(5),
    Country varchar(50),
    AvatarMed varchar(50),
    Gender varchar(50),
    HasImages bit,
    HasVideo bit,
    HasAudio bit,
    Domain varchar(100),
    DisplayName varchar(255),
    CreateDate datetime,
    Claimed varchar(1),
    ProfileTypeID bigint,
    Zip varchar(50),
    PhoneNbr varchar(50),
    PrPhone varchar(25))

        BEGIN

            INSERT INTO @TMP1
            SELECT 
            a.MemberID,
            a.UserName,
            a.LastLogin,
            a.PrCity,
            b.Abbr,
            c.Country,
            a.AvatarMed,
            a.Gender,
            sbuser.sf_MemberHasImages(a.MemberID),
            sbuser.sf_MemberHasVideo(a.MemberID),
            sbuser.sf_MemberHasAudio(a.MemberID),
            d.Domain,
            sbuser.sf_DisplayName(a.MemberID),
            a.CreateDate,
            a.Claimed,
            a.ProfileTypeID,
            a.Zip,
            a.PhoneNbr,
            a.PrPhone
            FROM Member a
            LEFT JOIN State b ON b.StateID = a.StateID
            INNER JOIN Country c ON c.countryID = a.CountryID
            INNER JOIN Region d ON d.RegionID = a.MemberRegionID
            WHERE (sbuser.sf_DisplayName(a.MemberID) LIKE @UserName + '%')
            AND a.MemberID <> @MemberID
            ORDER BY a.Claimed DESC, sbuser.sf_MemberHasAvatar(a.MemberID) DESC, sbuser.sf_MemberHasMedia(a.MemberID) DESC
        END

    SELECT @TOTALRECS = MAX(TMPID) FROM @TMP1
    SELECT @MAXPG = @TOTALRECS / @SIZE
    SET @TOTALRECS_REMAINDER = @TOTALRECS % @SIZE
    SET @MAXPG_INT = CAST(@MAXPG AS INT)
    SET @TOTALRECS_INT = CAST(@TOTALRECS AS INT)

    IF @TOTALRECS_REMAINDER > 0 
        BEGIN
            SET @MAXPG_INT = @MAXPG_INT + 1
        END


    INSERT INTO @OUT
    SELECT
    MemberID,
    UserName,
    LastLogin,
    PrCity,
    Abbr,
    Country,
    AvatarMed,
    Gender,
    HasImages,
    HasVideo,
    HasAudio,
    Domain,
    DisplayName,
    CreateDate,
    Claimed,
    @PG,
    @MAXPG_INT,
    @TOTALRECS_INT,
    ProfileTypeID,
    Zip,
    PhoneNbr,
    PrPhone
    FROM @TMP1
    WHERE (TmpID >= @Start) AND (TmpID <= @END)

RETURN 

END

I believe this script was written by software, by my predecessor. I don't have much experience with T-SQL. I would really appreciate any help you can give to improve the execution speed as now our system has over 40,000 members it has gotten extremely slow.

Many thanks for taking a look. I really appreciate it!

Best Regards,

Paul Jacobs

To further assist here are the missing sf scripts:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [sbuser].[sf_MemberHasImages](@MemberID bigint)

RETURNS BIT
AS
BEGIN
    DECLARE @OUT BIT
    SET @OUT = (SELECT CAST(COUNT(a.MemberImgID) AS BIT) From MemberImg a INNER JOIN MemberImgGallery b ON b.MemberImgGalleryID=a.MemberImgGalleryID
                WHERE b.MemberID = @MemberID)
    RETURN @out
END

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [sbuser].[sf_MemberHasAudio](@MemberID bigint)

RETURNS BIT
AS
BEGIN
DECLARE @OUT BIT
SET @OUT = (SELECT CAST(COUNT(MemberAudioID) AS BIT) FROM MemberAudio WHERE MemberID = @MemberID)
RETURN @OUT
END

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [sbuser].[sf_MemberHasVideo](@MemberID bigint)

RETURNS BIT
AS
BEGIN
DECLARE @OUT BIT
SET @OUT = (SELECT CAST(COUNT(MemberVideoID) AS BIT) FROM MemberVideo WHERE MemberID = @MemberID)
RETURN @OUT
END

Looking at the code you've posted, the flow basically is like this:

• a stored proc calls a UDF.
• ---- not perf related, but why is this string being sent to a stored proc named the same? IF @MODE = 'MEMBERSEARCH' in a proc called [sp_MemberSearch]. Seems superfluous from this once-over review of the code.

• this should NOT call a UDF. Suggest refactoring the code to have all the searching in the stored proc. Likely today it's being used from many stored procs, so it's easily re-used in the current setup. Not horrible, but you can do it another way, especially if you're using SQL Server 2008. Try re-architecting it as its own stored proc.

• The UDF basically performs a select with 3 important pieces/considerations:

  1. (sbuser.sf_DisplayName(a.MemberID) LIKE @UserName + '%'). To me, this is implying that EACH member ID is being passed into the function, and evaluated against the LIKE condition.

  2. ORDER BY 2 different results of UDFs - MemberHasAvatar and MemberHasMedia -- obviously wanting those to be ordered at the top.

  3. The 4 tables: Member, State, Country, Region -- are they indexed properly on the JOINed columns? How well does this SELECT perform when you run this statement without any/some/all of that WHERE clause and ORDER BY clause?

• the paging aspects could be improved to use newer TSQL language features, but it could be more than you wanted to take on. ROW_NUMBER() being part of that improvement.

• it's not clear what the real-world meaning of this clause is: WHERE (sbuser.sf_DisplayName(a.MemberID) LIKE @UserName + '%') AND a.MemberID <> @MemberID Does it mean that we don't want to include the @MemberID in the search results because they're the member performing the search? Probably a better variable name is suitable like @SearchPerformedByMemberID?

The math part, as you probably already know, won't have any measurable performance impact on this function/proc.

Some suggestions for improvement, YMMV!

• Suggest storing the DisplayName on the Member table to avoid having to call sbuser.sf_DisplayName.