Function within a Stored Procedure...

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 2005 Forums

Transact-SQL (2005)

Function within a Stored Procedure...

Author

Topic

paul_zonko
Starting Member

2 Posts

Posted - 2010-10-14 : 10:57:22

Hey - First post, please be kind.

Any help would be greatly appreciated...

I have a stored procedure that runs with 2 parameters: dbo.StoredProcedure @week, @year

Its a large procedure and takes about 1min 30secs to run (I'm OK with that)

I want this now to get the parameter values from todays date, so I have created a another table dbo.calander

I then created a function to find the week / year value from the table: dbo.getWeekYear

when i run like this...

--------------------------------
DECLARE @Year int
DECLARE @Week int
SET @Year = dbo.getWeekYear(Blah, Blah)
SET @Week = dbo.getWeekYear(Blah, Blah)
EXECUTE dbo.StoredProcedure @Year, @Week
--------------------------------

Runs fine @ 1min 30 Secs

now here's the problem

when I change the stored procedure to:

------------------------------
ALTER PROCEDURE dbo.StoredProcedure

AS

DECLARE @Year int
DECLARE @Week int
SET @Year = dbo.getWeekYear(Blah, Blah)
SET @Week = dbo.getWeekYear(Blah, Blah)

SELECT Blah, Blah
FROM Blah, Blah
WHERE blah.year = @year AND blah.week = @week
------------------------------

and execute as

EXECUTE dbo.StoredProcedure

it takes 9mins 45seconds!!

Any suggestions or reasons why declaring the functions parameters within the stored procedure would slow it down so much?

Cheers!

Dougy Z

DO IT, DO IT, DOOOOO IT!

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-10-14 : 12:07:51

Would need to see the code, both of the function and thr stored proc, but sounds like you're executing the function many times (against every row in your table(s)?) bad thing for performance.

We'd also need to know about the table structures and indexes to be real specific.

If you're just trying to get todays week and year, use the DATEPART() function instead of UDF, and be sure to execute it once, not once per record.

paul_zonko
Starting Member

2 Posts

Posted - 2010-10-15 : 11:32:06

"sounds like you're executing the function many times (against every row in your table(s)?) "

makes sense.

I have solved this by passing the query into a parameter

-----------
set @SQLQuery = 'SELECT Blah, Blah
FROM Blah, Blah
WHERE blah.year = ' + @year + ' AND blah.week = ' + @week
-----------

and then executing

-----------
EXECUTE sp_executesql @SQLQuery
-----------

runs in 1min 30secs!!

Thanks russell

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-10-15 : 20:02:32

I'm not sure why you would want to use dynamic sql when it seems you are very close to having a working solution that does not use dynamic sql.

If you want to elaborate more on the actual function or, probably more importantly, the stored procedure you are calling we might be able to explain what is happing. There could be any number of issues that using a function can cause if not used correctly. There are also other considerations like parameter sniffing.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources