[RESOLVED] Thought I improved performance, but I guess not?

[MMock]

Hi. I'm using SQL Server, I believe the version is 12.0. Front-end application is desktop windows C#.

My boss wanted a couple columns added to a sql query then complained she thought they were slowing it down. This is when the C# code populates a grid.

I was calling two functions to get the new data. So for the input we're testing with that returns 833 rows, it was making these two calls. From what I understand, on a row by row basis. They are commented out in the code below and replaced with the two lines following (the last two lines) which now use a table-valued function. I read that scalar functions can be slow and a table-valued function might be a better choice. I did see in SQL server that the time to run this stored procedure went down from over 15 seconds to 1 second. But when we run it from C#, it's still slow. Any idea what can be causing this, how I can troubleshoot the poor performance further? There are still 2 scalar function calls; should I replace those as well? But why is it quick in sql, slow in the application?

ALTER PROCEDURE xsp_JobsByCustomer
	@CustNo varchar(8),
	@bIncludeCP bit = null
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

SELECT
	Jobs.SONumber, 
	Jobs.CustomerName, 
	Jobs.JobType, 
	Jobs.CSEmp, 
	Jobs.Status, 
	Jobs.DateEntered, 
	Jobs.LastModifiedDateTime, 
	Jobs.Complaint, 
	Jobs.Control, 
	Jobs.NewControl, 
	ISNULL(xtblProjects.ProjectName, '') AS ProjectName, 
	Jobs.relProjectControl, 
	dbo.fn_GetJobShopTask(Jobs.Control, '') AS CST, 
	Jobs.RelatedCasesID,
	-- 07/11/24 - Four new columns
	Jobs.Details as CaseNotes,
	dbo.fn_GetP21OrderTotal(Jobs.SONumber) AS SOTotal,
	-- 07/29/24 This is what I am replacing
	--CASE WHEN [dbo].[fn_GetP21YesNoSABillableItems](Jobs.SONumber) = 0 THEN 'No' ELSE 'Yes' END AS SABillableItemsYN
	--COALESCE([dbo].[fn_GetP21TotalSABillableItems](Jobs.SONumber), 0) AS SABillableItemsTotal
	CASE WHEN P21.SONumberCount  = 0 THEN 'No' ELSE 'Yes' END AS SABillableItemsYN,
	P21.ExtPriceSum AS SABillableItemsTotal
FROM  Jobs 
LEFT OUTER JOIN xtblProjects ON xtblProjects.ProjectControl = Jobs.relProjectControl
CROSS APPLY fn_GetP21Fields(Jobs.SONumber) P21
WHERE        (Jobs.CustNo = @CustNo) 
AND  ((@bIncludeCP is null AND Jobs.Status <> 'CP') OR (@bIncludeCP = 1))
order by Jobs.Control desc
END
GO


ALTER FUNCTION [dbo].[fn_GetP21Fields](@SONO varchar(8))
RETURNS TABLE
AS 
RETURN (  
  SELECT Count(SONO) as SONumberCount, SUM(EXTPRICE) as ExtPriceSum
  FROM xqryP21LinesRelated s
  WHERE s.SONO = @SONO AND class_id4 = 'SA'
 )

[MMock]

I executed a recompile against it. It is possibly faster. So maybe hold on answering this so you're not wasting your time. Thanks.