Expensive View help

• ss-457805

  SSCertifiable

  Points: 5872

  More actions

  July 26, 2017 at 10:16 am

  #1952838

  Jason A. Long - Wednesday, July 26, 2017 7:59 AM

  ss-457805 - Wednesday, July 26, 2017 1:44 AM

  Jason A. Long - Tuesday, July 25, 2017 3:51 PM

  Next 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

  blog: http://sarveshsingh.comTwitter: @sarveshsing

• Jason A. Long

  SSC-Insane

  Points: 23789

  More actions

  July 26, 2017 at 10:45 am

  #1952843

  ss-457805 - Wednesday, July 26, 2017 10:16 AM

  Jason A. Long - Wednesday, July 26, 2017 7:59 AM

  From 5 mins + to 8 secs... I'd call that a win!

  Yes. Thanks for your help

  Glad to help.

• ScottPletcher

  SSC Guru

  Points: 101188

  More actions

  August 2, 2017 at 7:42 am

  #1953766

  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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".