April 28, 2011 at 10:18 am
I remember hearing that having functions in a stored procedure is no bueno.
In the past I would just use a subquery but I notice sometimes it looks cleaner to use a function.
ex:
CREATE PROCEDURE usp_name
AS
SET NOCOUNT ON
BEGIN
SELECT a.accountid,
a.name,
a.accountnumber,
DATEDIFF(YEAR, r.pa_originaljoindate, GETDATE()) AS years,
udf_CorporateAccountGetRepTotal(a.accountid) AS memberreps,
udf_CorporateAccountGetCIPTotal(a.accountid) AS cipreps,
udf_CorporateAccountGetCPTotal(a.accountid) AS cpreps
FROM Account a
INNER JOIN Renewalbilling r
ON a.accountid = r.accountid
END
Looks cleaner than
CREATE PROCEDURE usp_name
AS
SET NOCOUNT ON
BEGIN
SELECT a.accountid,
a.name,
a.accountnumber,
DATEDIFF(YEAR, r.pa_originaljoindate, GETDATE()) AS years,
(SELECT COUNT(*)......) AS memberreps,
(SELECT COUNT(*)......) AS cipreps,
(SELECT COUNT(*)......) AS cpreps
FROM Account a
INNER JOIN Renewalbilling r
ON a.accountid = r.accountid
END
I just remember hearing or reading briefly about functions are slow in stored procedures but I can't find any resources online. Thanks
April 28, 2011 at 10:59 am
There is nothing wrong with using a function in a stored proc nowadays. However, there have always been issues with substituting subqueries with scalar functions that included select statements. The optimizer cannot correctly assess the workload of the queries embedded in the scalar function to come up with an efficient execution plan. While this was particularly true of functions that included select statements, there was also a lesser performance hit when the function just did calculations.
In 2005-2008, you can use inline table valued functions and avoid the performance penalties of scalar functions.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
May 1, 2011 at 10:49 am
CELKO (5/1/2011)
Really awful T-SQL people will use both a real key and an IDENTITY property. We do not use th4e old Sybase getdate() any more.
You have a very broad definition of 'really awful'. I certainly fall into it, so do many other good people. I do have my reasons and they are informed ones. Oh, and you may not use getdate any longer, but that certainly doesn't apply to everyone.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 1, 2011 at 12:03 pm
CELKO (5/1/2011)
Instead of trying to make SQLK look like the procedural language you know...
BWAA-HAAA!!!! Listen to you "Mr. PushStack to make a Nested Set". And then you write a full blown Triangular Join which generates 500,000,000,000 internal rows to solve a simple problem like "Todd's Division" on a mere 1,000,000 rows at the following post...
http://qa.sqlservercentral.com/Forums/FindPost1101199.aspx
... and you have the gall to talk about someone else using procedural code?
Stop being such a jerk, Joe. Clean up your own code first and then be nice to people because you're making the same and worse mistakes as they are.
--Jeff Moden
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply