UDFs and SPs - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

UDFs and SPs

Author

Topic

wombel
Starting Member

14 Posts

Posted - 2002-03-01 : 15:42:52

Hi,

I had some problem with one of my SPs. I found out, that it had a very high recompile rate. It's task was to select some id into a temp table with an identity on it. After that, I selected/joined the data I needed for each id.

The reason why I did that was, that I neede to ouput the data pagewise on an ASP-page.

Now I thought it would be a good idea to break up the sttatements and I put the creation of the (temp)-table into an UDF that returned the table.

The stored procedure then just had the task to join the need information.

Does that make sense? Is that one of the ways to optimize SPs?

Thanks in advance for your opinion.

Thomas Wrobel
Senior IT Manager

Jay99

468 Posts

Posted - 2002-03-01 : 16:03:06

My guess is the UDF isn't gonna help you much (performance wise) . . . Could you post some code? Maybe you don't need the temp table to do the paging . . .

Depending on the amount of data, you might look at handling the paging on the web server rather than in the proc.

Jay

wombel
Starting Member

14 Posts

Posted - 2002-03-05 : 05:40:57

Hi Jay,
thanks fro your reply. We wanted to keep the amount of data being transfered from the db-server to the webserver as small as possible. That was why we decided to prepare the page on the db-server.

I enclose the could, I was talking about before and after my changes.

Before:
ALTER PROCEDURE usr_dtl_gbookselect_new
(
@intUserid int,
@intPage int,
@intRecordcount smallint output,
@intPagecount smallint output,
@intLastRecord int = 0,
@intGbArchiv int output,
@intGbArchivShowallUsers int output,
@strVorname varchar(100) output,
@strNachname varchar(100) output,
@intGbAktiv int output,
@intGbookNoHTML int output,
@intUbAktiv int output
)
AS

SET NOCOUNT ON

DECLARE @intIDTop int,
@intIDLow int,
@intSelRec int,
@intSelRecNext int,
@intRemainder smallint

SELECT
@strVorname = vorname,
@strNachname = name,
@intUbAktiv = userbase,
@intGbAktiv = gbook,
@intGbookNoHTML = gbookhtml,
@intGBArchiv = gbook_archiv,
@intGbArchivShowallUsers = gbook_archiv_allusers
FROM
member_user WITH(NOLOCK)
WHERE
id = @intUserID

create table #tmpGbook
(
rankid int IDENTITY NOT NULL PRIMARY KEY,
gbid int
)

INSERT INTO #tmpGbook(gbid)
SELECT mg.id FROM member_gbook mg WITH (NOLOCK)
WHERE
mg.userid = @intUserid
order by mg.zeit desc

create index [tmpidx] on #tmpGbook(rankid)

SELECT @intRecordcount = @@rowcount
SELECT @intPageCount = dbo.fn_runden(@intRecordCount,10)

IF @intLastRecord IN (0,1)
BEGIN
SELECT TOP 10 mg.id, mg.sender, mg.zeit, 'name'= mu.vorname + ' ' + mu.name, mg.eintrag, mu.aktiv
FROM #tmpGbook tgb WITH (NOLOCK)
JOIN member_gbook mg WITH (NOLOCK) ON tgb.gbid = mg.id
JOIN member_user mu WITH (NOLOCK) ON mg.sender = mu.id
WHERE rankid >= 0
ORDER BY rankid
END
ELSE
BEGIN
SELECT TOP 10 mg.id, mg.sender, mg.zeit, 'name'= mu.vorname + ' ' + mu.name, mg.eintrag, mu.aktiv
FROM #tmpGbook tgb WITH (NOLOCK)
JOIN member_gbook mg WITH (NOLOCK) ON tgb.gbid = mg.id
JOIN member_user mu WITH (NOLOCK) ON mg.sender = mu.id
WHERE rankid > @intLastRecord
ORDER BY rankid
END

AFTER:
ALTER PROCEDURE usr_dtl_gbookselect_new_thomas
(
@intUserid int,
@intCurrentPage int,
@intRecordcount smallint output,
@intPagecount smallint output,
@intLastRecord int = 0,
@intGbArchiv int output,
@intGbArchivShowallUsers int output,
@strVorname varchar(100) output,
@strNachname varchar(100) output,
@intGbAktiv int output,
@intGbookNoHTML int output,
@intUbAktiv int output
)
AS
SET NOCOUNT ON

DECLARE @intIDTop int,
@intIDLow int,
@intSelRec int,
@intSelRecNext int,
@intRemainder smallint,
@intMinId int

SELECT
@strVorname = vorname,
@strNachname = name,
@intUbAktiv = userbase,
@intGbAktiv = gbook,
@intGbookNoHTML = gbookhtml,
@intGBArchiv = gbook_archiv,
@intGbArchivShowallUsers = gbook_archiv_allusers
FROM
member_user WITH(NOLOCK)
WHERE
id = @intUserID

SET @intRecordCount = dbo.fn_usr_dtl_gbookselect_new_anzahl(@intUserid)

SELECT @intPageCount = dbo.fn_runden(@intRecordCount,10)

SET @intMinId = (@intCurrentPage - 1) * 10

SELECT TOP 10 mg.id, mg.sender, mg.zeit, 'name'= mu.vorname + ' ' + mu.name, mg.eintrag, mu.aktiv
FROM dbo.fn_usr_dtl_gbookselect_new_temp(@intUserid) as tgb
JOIN member_gbook mg WITH (NOLOCK) ON tgb.gbid = mg.id
JOIN member_user mu WITH (NOLOCK) ON mg.sender = mu.id
WHERE rankid >= @intMinId
ORDER BY rankid

quote:

My guess is the UDF isn't gonna help you much (performance wise) . . . Could you post some code? Maybe you don't need the temp table to do the paging . . .

Depending on the amount of data, you might look at handling the paging on the web server rather than in the proc.

Jay

quote:

Thomas Wrobel
Senior IT Manager

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources