July 26, 2017 at 10:16 am
Jason A. Long - Wednesday, July 26, 2017 7:59 AMss-457805 - Wednesday, July 26, 2017 1:44 AMJason A. Long - Tuesday, July 25, 2017 3:51 PMNext question... Are you familiar with the syntax differences between scalar & table functions, with regard to referencing them in your code?Changing both both the functions to TVF brings the result back in 8 secs.
From 5 mins + to 8 secs... I'd call that a win! 🙂
Yes. Thanks for your help 🙂
July 26, 2017 at 10:45 am
ss-457805 - Wednesday, July 26, 2017 10:16 AMJason A. Long - Wednesday, July 26, 2017 7:59 AMFrom 5 mins + to 8 secs... I'd call that a win! 🙂Yes. Thanks for your help 🙂
Glad to help. 🙂
August 2, 2017 at 7:42 am
In cases where you do need a scalar function, for max efficiency, get rid of any local variables that are not absolutely required:
CREATE FUNCTION [dbo].[ufn_GetPlannersName](@intFeasibilityRequestId BigInt)
RETURNS NVarChar(1000)
AS
BEGIN
RETURN (
SELECT TOP (1) u.full_name
FROM tbFeasibility_Request_Response frr
INNER JOIN tbUser u
ON frr.planner_user_id_site_a = u.[user_id]
WHERE frr.feasibility_request_id = @intFeasibilityRequestId
)
END
GO
CREATE FUNCTION [dbo].[ufn_IsWithSLA](@teamId Int, @datExpectedDate DateTime, @datActualCompletionDate DateTime)
RETURNS Bit
AS
BEGIN
-- Currently this function is not using team id but in future
-- if different teams have got different requirement then we could
-- use this parameter
RETURN (
SELECT CASE WHEN DATEDIFF(dd, @datExpectedDate, @datActualCompletionDate) > 0 THEN 0 ELSE 1 END
)
END
GO
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
Viewing 3 posts - 16 through 17 (of 17 total)
You must be logged in to reply to this topic. Login to reply