sql server - Inproving speed of T-SQL script -

i need improve speed dramatically following script. thinking removing table valued function , placing in stored procedure. before wanted experts take , provide me solution or few pointers. scripting has paging functionality needs remain in place somehow.

here firstly 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)   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     sbuser.tf_findmember(@username,@memberid,@pg,@rowct,@colct)  end 

and here table valued function 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   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 @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             member             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             (sbuser.sf_displayname(a.memberid) @username + '%')             , a.memberid <> @memberid             order a.claimed desc, sbuser.sf_memberhasavatar(a.memberid) desc, sbuser.sf_memberhasmedia(a.memberid) desc         end      select @totalrecs = max(tmpid) @tmp1     select @maxpg = @totalrecs / @size     set @totalrecs_remainder = @totalrecs % @size     set @maxpg_int = cast(@maxpg int)     set @totalrecs_int = cast(@totalrecs int)      if @totalrecs_remainder > 0          begin             set @maxpg_int = @maxpg_int + 1         end       insert @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     @tmp1     (tmpid >= @start) , (tmpid <= @end)  return   end 

i believe script written software, predecessor. don't have experience t-sql. appreciate can give improve execution speed our system has on 40,000 members has gotten extremely slow.

many taking look. appreciate it!

best regards,

paul jacobs

to further assist here missing sf scripts:

set ansi_nulls on set quoted_identifier on go alter function [sbuser].[sf_memberhasimages](@memberid bigint)  returns bit begin     declare @out bit     set @out = (select cast(count(a.memberimgid) bit) memberimg inner join memberimggallery b on b.memberimggalleryid=a.memberimggalleryid                 b.memberid = @memberid)     return @out end 

---

set ansi_nulls on set quoted_identifier on go alter function [sbuser].[sf_memberhasaudio](@memberid bigint)  returns bit begin declare @out bit set @out = (select cast(count(memberaudioid) bit) memberaudio memberid = @memberid) return @out end 

---

set ansi_nulls on set quoted_identifier on go alter function [sbuser].[sf_memberhasvideo](@memberid bigint)  returns bit begin declare @out bit set @out = (select cast(count(membervideoid) bit) membervideo memberid = @memberid) return @out end 

looking @ code you've posted, flow basically this:

• a stored proc calls udf.
• ---- not perf related, why string being sent stored proc named same? if @mode = 'membersearch' in proc called [sp_membersearch]. seems superfluous once-over review of code.

• this should not call udf. suggest refactoring code have searching in stored proc. today it's being used many stored procs, it's re-used in current setup. not horrible, can way, if you're using sql server 2008. try re-architecting own stored proc.

• the udf performs select 3 important pieces/considerations:

  1. (sbuser.sf_displayname(a.memberid) @username + '%'). me, implying each member id being passed function, , evaluated against like condition.

  2. order by 2 different results of udfs - memberhasavatar , memberhasmedia -- wanting ordered @ top.

  3. the 4 tables: member, state, country, region -- indexed on joined columns? how select perform when run statement without any/some/all of where clause , order by clause?

• the paging aspects improved use newer tsql language features, more wanted take on. row_number() being part of improvement.

• it's not clear real-world meaning of clause is: where (sbuser.sf_displayname(a.memberid) @username + '%') , a.memberid <> @memberid mean don't want include @memberid in search results because they're member performing search? better variable name suitable @searchperformedbymemberid?

the math part, know, won't have measurable performance impact on function/proc.

some suggestions improvement, ymmv!

• suggest storing displayname on member table avoid having call sbuser.sf_displayname.