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:
(sbuser.sf_displayname(a.memberid) @username + '%')
. me, implying each member id being passed function, , evaluated againstlike
condition.order by
2 different results of udfs -memberhasavatar
,memberhasmedia
-- wanting ordered @ top.the 4 tables:
member
,state
,country
,region
-- indexed onjoin
ed columns? howselect
perform when run statement without any/some/all ofwhere
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
onmember
table avoid having callsbuser.sf_displayname
.
Comments
Post a Comment