Performance: Call function twice in query or...

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

Transact-SQL (2008)

Performance: Call function twice in query or...

Author

Topic

ferrethouse
Constraint Violating Yak Guru

352 Posts

Posted - 2011-09-08 : 12:42:21

I've been performance tuning the database and have determined that one function is being called 9 million times per hour and represents 33% of all SQL waits. The function itself can't be optimized any more so now I'm trying to determine if it is being called excessively.

I found a trigger that contains this query...


SELECT Students.StudentsID, Students.DepartmentID, Courses.CourseID, Inserted.LearningPathID, GETDATE(), Inserted.ReferenceNum FROM Inserted
				INNER JOIN Students ON Students.StudentsID = Inserted.StudentsID
				LEFT JOIN Courses ON Courses.CourseID IN (SELECT lpc.CourseID FROM FuncLearningPathCourses(Inserted.LearningPathID) lpc)
			WHERE Students.ActiveStatus = 1			
				AND (SELECT COUNT(*) FROM Subscription WHERE Subscription.StudentsID = Inserted.StudentsID AND Subscription.CourseID = Courses.CourseID) = 0
				AND EXISTS (SELECT NULL FROM FuncLearningPathCourses(Inserted.LearningPathID))

Notice that it is calling the function twice. Would it be better to call it once and store in a local temp table and then reference the local temp table in the query? The function itself it pretty lightweight - there are a couple of inner joins but nothing much else.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-09-08 : 13:13:03

Couple things:

1. Can you post the entire trigger code? Unless that query is inserting into another table, it shouldn't be returning results from a trigger.
2. Can you post the code of the function?

Depending on what the function does, it may be more efficient to put the logic in the trigger and not call the function.

You can also slightly optimize the statement:

SELECT Students.StudentsID, Students.DepartmentID, Courses.CourseID, Inserted.LearningPathID, GETDATE(), Inserted.ReferenceNum FROM Inserted
INNER JOIN Students ON Students.StudentsID = Inserted.StudentsID
LEFT JOIN Courses ON Courses.CourseID
IN (SELECT lpc.CourseID FROM FuncLearningPathCourses(Inserted.LearningPathID) lpc)
WHERE Students.ActiveStatus = 1
AND NOT EXISTS(SELECT * FROM Subscription WHERE Subscription.StudentsID = Inserted.StudentsID AND Subscription.CourseID = Courses.CourseID)
AND EXISTS (SELECT NULL FROM FuncLearningPathCourses(Inserted.LearningPathID))

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-09-08 : 13:29:08

Is this in a trigger?

You definitely need to remove the function. You likely don't need it at all and can instead do a derived table or something similar to achieve the same results.

What does the execution plan show?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

ferrethouse
Constraint Violating Yak Guru

352 Posts

Posted - 2011-09-08 : 13:29:37

Hi, here is the entire trigger...


USE [absorb]
GO
/****** Object:  Trigger [dbo].[LearningPathSubscription_Trigger]    Script Date: 09/08/2011 10:36:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER TRIGGER [dbo].[LearningPathSubscription_Trigger]
ON [dbo].[LearningPathSubscription]

FOR INSERT,UPDATE, DELETE
AS 
BEGIN

	SET NOCOUNT ON;

	-- only executes on insert and update
	IF EXISTS (SELECT null FROM INSERTED)
		BEGIN
			-- does this need to be called on update
			INSERT INTO Subscription (StudentsID, DepartmentID, CourseID, LearningPathID, StartDate, ReferenceNum)
			SELECT Students.StudentsID, Students.DepartmentID, Courses.CourseID, Inserted.LearningPathID, GETDATE(), Inserted.ReferenceNum FROM Inserted
				INNER JOIN Students ON Students.StudentsID = Inserted.StudentsID
				LEFT JOIN Courses ON Courses.CourseID IN (SELECT lpc.CourseID FROM FuncLearningPathCourses(Inserted.LearningPathID) lpc)
			WHERE Students.ActiveStatus = 1			
				AND (SELECT COUNT(*) FROM Subscription WHERE Subscription.StudentsID = Inserted.StudentsID AND Subscription.CourseID = Courses.CourseID) = 0
				AND EXISTS (SELECT NULL FROM FuncLearningPathCourses(Inserted.LearningPathID))
			
			INSERT INTO Certificates  (LearningPathID, StudentsID, Valid, Expires, CertificateStatus)
			SELECT	Inserted.LearningPathID,
					Inserted.StudentsID, 
					CASE WHEN Inserted.Completed IS NOT NULL THEN Inserted.Completed ELSE GETDATE() END,
					CASE WHEN LearningPaths.CertificateExpires IS NOT NULL
						THEN
							DATEADD	   (yy,
										LearningPaths.CertificateExpires,
										CASE WHEN Inserted.Completed IS NOT NULL THEN Inserted.Completed ELSE GETDATE() END)
						ELSE NULL
						END,
					1
			FROM Inserted
			INNER JOIN LearningPaths ON LearningPaths.LearningPathID = Inserted.LearningPathID
			WHERE LearningPaths.Certificate = 1 AND ROUND(Inserted.Progress, 0) = 100 AND
			(SELECT COUNT(*) FROM Certificates WHERE Certificates.LearningPathID = Inserted.LearningPathID AND Certificates.StudentsID = Inserted.StudentsID) = 0
			
			-- temporarily added for farmers -- remove once we can set completion criteria for LPs
			INSERT INTO Certificates (LearningPathID, StudentsID, Valid, Expires, CertificateStatus)
			SELECT Inserted.LearningPathID, Inserted.StudentsID, Inserted.Completed, DATEADD (yy, LearningPaths.CertificateExpires, Inserted.Completed), 1 FROM Inserted
			INNER JOIN LearningPaths ON LearningPaths.LearningPathID = Inserted.LearningPathID
			WHERE LearningPaths.Certificate = 1 AND ROUND(Inserted.Progress, 0) >= 50 AND LearningPaths.LearningPathID = 545 AND
			(SELECT COUNT(*) FROM Certificates WHERE Certificates.LearningPathID = Inserted.LearningPathID AND Certificates.StudentsID = Inserted.StudentsID) = 0
		END
	ELSE
		BEGIN
			DELETE FROM Subscription WHERE SubID IN
			(SELECT SubID FROM Subscription
			INNER JOIN Deleted ON Deleted.StudentsID = Subscription.StudentsID AND Deleted.LearningPathID = Subscription.LearningPathID AND Deleted.AutomaticAssignment = 0)
		END

END

And here is the entire function (that is being called twice)...

USE [absorb]
GO
/****** Object: UserDefinedFunction [dbo].[FuncLearningPathCourses] Script Date: 09/08/2011 11:29:28 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER FUNCTION [dbo].[FuncLearningPathCourses] (@LearningPathId INT)
RETURNS @CourseIds TABLE
(
CourseID INT,
LessonID INT NULL
)

AS
BEGIN
IF (@LearningPathId > 0)
BEGIN
INSERT INTO @CourseIds SELECT LearningPathCourses.CourseID, LessonID FROM LearningPathCourseGroups INNER JOIN LearningPathCourseGroupCourses ON LearningPathCourseGroupCourses.LearningPathCourseGroupID = LearningPathCourseGroups.LearningPathCourseGroupID INNER JOIN LearningPathCourses ON LearningPathCourses.LearningPathCourseID = LearningPathCourseGroupCourses.LearningPathCourseID WHERE LearningPathCourseGroups.LearningPathID = @LearningPathId
END
RETURN
END

Kristen
Test

22859 Posts

Posted - 2011-09-08 : 13:32:39

"The function itself it pretty lightweight - there are a couple of inner joins but nothing much else."

You may think that ... wouldn't surprise me that there is more to it (performance-wise) than that.

You might be able to CROSS APPLY the function so it is only used once.

But we need to see all the source code to form an informed-opinion.

ferrethouse
Constraint Violating Yak Guru

352 Posts

Posted - 2011-09-08 : 13:33:15

quote:
Originally posted by tkizer

Is this in a trigger?

You definitely need to remove the function. You likely don't need it at all and can instead do a derived table or something similar to achieve the same results.

What does the execution plan show?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

It is strange. I tried flattening the table so that I could just query the flat table directly instead of calling the function and surprisingly the comparative cost of these two calls...


select * from LearningPathCourseFlat where LearningPathID = 2
select * from FuncLearningPathCourses(2)

Shows 76% cost for the first query and 24% for the second. The execution plans look optimized (only index seeks).

Not sure the best way to post the execution plans into the forums.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-09-08 : 14:02:10

This version of the function may be a quick fix:

ALTER FUNCTION [dbo].[FuncLearningPathCourses] (@LearningPathId INT)
RETURNS TABLE
AS RETURN(SELECT LearningPathCourses.CourseID, LessonID
FROM LearningPathCourseGroups
INNER JOIN LearningPathCourseGroupCourses ON LearningPathCourseGroupCourses.LearningPathCourseGroupID = LearningPathCourseGroups.LearningPathCourseGroupID
INNER JOIN LearningPathCourses ON LearningPathCourses.LearningPathCourseID = LearningPathCourseGroupCourses.LearningPathCourseID
WHERE @LearningPathId > 0 AND LearningPathCourseGroups.LearningPathID = @LearningPathId)

It's an inline-table version for which the optimizer may generate a better plan.

Regarding the 2 plans, were they actual plans or estimated? The relative cost isn't as useful as the actual plan.

If the plan is relatively small, you can paste the XML version in the forums (less than 50K). If it's larger than that you could try posting it on PasteBin and posting that link to these forums instead. Post the XML plan output rather than text or graphical.

ferrethouse
Constraint Violating Yak Guru

352 Posts

Posted - 2011-09-08 : 15:29:32

XML plan for the select...


<?xml version="1.0" encoding="utf-16"?>
<ShowPlanXML xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" Version="1.1" Build="10.0.4272.0" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">
  <BatchSequence>
    <Batch>
      <Statements>
        <StmtSimple StatementCompId="1" StatementEstRows="3" StatementId="1" StatementOptmLevel="FULL" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" StatementSubTreeCost="0.010203" StatementText="SELECT * FROM [LearningPathCourseFlat] WHERE [LearningPathID]=@1" StatementType="SELECT" QueryHash="0x4BB672661F8C7215" QueryPlanHash="0xF037C68849334783">
          <StatementSetOptions ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="true" />
          <QueryPlan DegreeOfParallelism="1" CachedPlanSize="24" CompileTime="2" CompileCPU="2" CompileMemory="160">
            <RelOp AvgRowSize="23" EstimateCPU="1.254E-05" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="3" LogicalOp="Inner Join" NodeId="0" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="0.010203">
              <OutputList>
                <ColumnReference Database="[absorb]" Schema="[dbo]" Table="[LearningPathCourseFlat]" Column="LearningPathCourseFlatID" />
                <ColumnReference Database="[absorb]" Schema="[dbo]" Table="[LearningPathCourseFlat]" Column="LearningPathID" />
                <ColumnReference Database="[absorb]" Schema="[dbo]" Table="[LearningPathCourseFlat]" Column="CourseID" />
                <ColumnReference Database="[absorb]" Schema="[dbo]" Table="[LearningPathCourseFlat]" Column="LessonID" />
              </OutputList>
              <RunTimeInformation>
                <RunTimeCountersPerThread Thread="0" ActualRows="3" ActualEndOfScans="1" ActualExecutions="1" />
              </RunTimeInformation>
              <NestedLoops Optimized="false">
                <OuterReferences>
                  <ColumnReference Database="[absorb]" Schema="[dbo]" Table="[LearningPathCourseFlat]" Column="LearningPathCourseFlatID" />
                </OuterReferences>
                <RelOp AvgRowSize="15" EstimateCPU="0.0001603" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="3" LogicalOp="Index Seek" NodeId="1" Parallel="false" PhysicalOp="Index Seek" EstimatedTotalSubtreeCost="0.0032853" TableCardinality="6220">
                  <OutputList>
                    <ColumnReference Database="[absorb]" Schema="[dbo]" Table="[LearningPathCourseFlat]" Column="LearningPathCourseFlatID" />
                    <ColumnReference Database="[absorb]" Schema="[dbo]" Table="[LearningPathCourseFlat]" Column="LearningPathID" />
                  </OutputList>
                  <RunTimeInformation>
                    <RunTimeCountersPerThread Thread="0" ActualRows="3" ActualEndOfScans="1" ActualExecutions="1" />
                  </RunTimeInformation>
                  <IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" NoExpandHint="false">
                    <DefinedValues>
                      <DefinedValue>
                        <ColumnReference Database="[absorb]" Schema="[dbo]" Table="[LearningPathCourseFlat]" Column="LearningPathCourseFlatID" />
                      </DefinedValue>
                      <DefinedValue>
                        <ColumnReference Database="[absorb]" Schema="[dbo]" Table="[LearningPathCourseFlat]" Column="LearningPathID" />
                      </DefinedValue>
                    </DefinedValues>
                    <Object Database="[absorb]" Schema="[dbo]" Table="[LearningPathCourseFlat]" Index="[cds_LPCF_LearningPathID]" IndexKind="NonClustered" />
                    <SeekPredicates>
                      <SeekPredicateNew>
                        <SeekKeys>
                          <Prefix ScanType="EQ">
                            <RangeColumns>
                              <ColumnReference Database="[absorb]" Schema="[dbo]" Table="[LearningPathCourseFlat]" Column="LearningPathID" />
                            </RangeColumns>
                            <RangeExpressions>
                              <ScalarOperator ScalarString="(2)">
                                <Const ConstValue="(2)" />
                              </ScalarOperator>
                            </RangeExpressions>
                          </Prefix>
                        </SeekKeys>
                      </SeekPredicateNew>
                    </SeekPredicates>
                  </IndexScan>
                </RelOp>
                <RelOp AvgRowSize="15" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="2" EstimateRewinds="0" EstimateRows="1" LogicalOp="Clustered Index Seek" NodeId="3" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="0.00690521" TableCardinality="6220">
                  <OutputList>
                    <ColumnReference Database="[absorb]" Schema="[dbo]" Table="[LearningPathCourseFlat]" Column="CourseID" />
                    <ColumnReference Database="[absorb]" Schema="[dbo]" Table="[LearningPathCourseFlat]" Column="LessonID" />
                  </OutputList>
                  <RunTimeInformation>
                    <RunTimeCountersPerThread Thread="0" ActualRows="3" ActualEndOfScans="0" ActualExecutions="3" />
                  </RunTimeInformation>
                  <IndexScan Lookup="true" Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" NoExpandHint="false">
                    <DefinedValues>
                      <DefinedValue>
                        <ColumnReference Database="[absorb]" Schema="[dbo]" Table="[LearningPathCourseFlat]" Column="CourseID" />
                      </DefinedValue>
                      <DefinedValue>
                        <ColumnReference Database="[absorb]" Schema="[dbo]" Table="[LearningPathCourseFlat]" Column="LessonID" />
                      </DefinedValue>
                    </DefinedValues>
                    <Object Database="[absorb]" Schema="[dbo]" Table="[LearningPathCourseFlat]" Index="[PK_LearningPathCourseFlat]" TableReferenceId="-1" IndexKind="Clustered" />
                    <SeekPredicates>
                      <SeekPredicateNew>
                        <SeekKeys>
                          <Prefix ScanType="EQ">
                            <RangeColumns>
                              <ColumnReference Database="[absorb]" Schema="[dbo]" Table="[LearningPathCourseFlat]" Column="LearningPathCourseFlatID" />
                            </RangeColumns>
                            <RangeExpressions>
                              <ScalarOperator ScalarString="[absorb].[dbo].[LearningPathCourseFlat].[LearningPathCourseFlatID]">
                                <Identifier>
                                  <ColumnReference Database="[absorb]" Schema="[dbo]" Table="[LearningPathCourseFlat]" Column="LearningPathCourseFlatID" />
                                </Identifier>
                              </ScalarOperator>
                            </RangeExpressions>
                          </Prefix>
                        </SeekKeys>
                      </SeekPredicateNew>
                    </SeekPredicates>
                  </IndexScan>
                </RelOp>
              </NestedLoops>
            </RelOp>
            <ParameterList>
              <ColumnReference Column="@1" ParameterCompiledValue="(2)" ParameterRuntimeValue="(2)" />
            </ParameterList>
          </QueryPlan>
        </StmtSimple>
      </Statements>
    </Batch>
    <Batch>
      <Statements>
        <StmtSimple StatementCompId="2" StatementEstRows="1" StatementId="3" StatementOptmLevel="FULL" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" StatementSubTreeCost="0.00328626" StatementText="select * from FuncLearningPathCourses(2)" StatementType="SELECT" QueryHash="0x4CC893158D58F06E" QueryPlanHash="0x0918ED67C03AA91C">
          <StatementSetOptions ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="true" />
          <QueryPlan DegreeOfParallelism="1" CachedPlanSize="8" CompileTime="0" CompileCPU="0" CompileMemory="96">
            <RelOp AvgRowSize="15" EstimateCPU="2E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Sequence" NodeId="0" Parallel="false" PhysicalOp="Sequence" EstimatedTotalSubtreeCost="0.00328626">
              <OutputList>
                <ColumnReference Database="[absorb]" Schema="[dbo]" Table="[FuncLearningPathCourses]" Column="CourseID" />
                <ColumnReference Database="[absorb]" Schema="[dbo]" Table="[FuncLearningPathCourses]" Column="LessonID" />
              </OutputList>
              <RunTimeInformation>
                <RunTimeCountersPerThread Thread="0" ActualRows="3" ActualEndOfScans="1" ActualExecutions="1" />
              </RunTimeInformation>
              <Sequence>
                <RelOp AvgRowSize="9" EstimateCPU="1.157E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Table-valued function" NodeId="1" Parallel="false" PhysicalOp="Table-valued function" EstimatedTotalSubtreeCost="1.157E-06">
                  <OutputList />
                  <RunTimeInformation>
                    <RunTimeCountersPerThread Thread="0" ActualRebinds="1" ActualRewinds="0" ActualRows="0" ActualEndOfScans="0" ActualExecutions="1" />
                  </RunTimeInformation>
                  <TableValuedFunction>
                    <DefinedValues />
                    <Object Database="[absorb]" Schema="[dbo]" Table="[FuncLearningPathCourses]" />
                    <ParameterList>
                      <ScalarOperator ScalarString="(2)">
                        <Const ConstValue="(2)" />
                      </ScalarOperator>
                    </ParameterList>
                  </TableValuedFunction>
                </RelOp>
                <RelOp AvgRowSize="15" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Table Scan" NodeId="3" Parallel="false" PhysicalOp="Table Scan" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="0">
                  <OutputList>
                    <ColumnReference Database="[absorb]" Schema="[dbo]" Table="[FuncLearningPathCourses]" Column="CourseID" />
                    <ColumnReference Database="[absorb]" Schema="[dbo]" Table="[FuncLearningPathCourses]" Column="LessonID" />
                  </OutputList>
                  <RunTimeInformation>
                    <RunTimeCountersPerThread Thread="0" ActualRows="3" ActualEndOfScans="1" ActualExecutions="1" />
                  </RunTimeInformation>
                  <TableScan Ordered="false" ForcedIndex="false" NoExpandHint="false">
                    <DefinedValues>
                      <DefinedValue>
                        <ColumnReference Database="[absorb]" Schema="[dbo]" Table="[FuncLearningPathCourses]" Column="CourseID" />
                      </DefinedValue>
                      <DefinedValue>
                        <ColumnReference Database="[absorb]" Schema="[dbo]" Table="[FuncLearningPathCourses]" Column="LessonID" />
                      </DefinedValue>
                    </DefinedValues>
                    <Object Database="[absorb]" Schema="[dbo]" Table="[FuncLearningPathCourses]" />
                  </TableScan>
                </RelOp>
              </Sequence>
            </RelOp>
          </QueryPlan>
        </StmtSimple>
      </Statements>
    </Batch>
  </BatchSequence>
</ShowPlanXML>

XML plan for the function...


<?xml version="1.0" encoding="utf-16"?>
<ShowPlanXML xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" Version="1.1" Build="10.0.4272.0" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">
  <BatchSequence>
    <Batch>
      <Statements>
        <StmtSimple StatementCompId="1" StatementEstRows="3" StatementId="1" StatementOptmLevel="FULL" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" StatementSubTreeCost="0.010203" StatementText="SELECT * FROM [LearningPathCourseFlat] WHERE [LearningPathID]=@1" StatementType="SELECT" QueryHash="0x4BB672661F8C7215" QueryPlanHash="0xF037C68849334783">
          <StatementSetOptions ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="true" />
          <QueryPlan DegreeOfParallelism="1" CachedPlanSize="24" CompileTime="2" CompileCPU="2" CompileMemory="160">
            <RelOp AvgRowSize="23" EstimateCPU="1.254E-05" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="3" LogicalOp="Inner Join" NodeId="0" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="0.010203">
              <OutputList>
                <ColumnReference Database="[absorb]" Schema="[dbo]" Table="[LearningPathCourseFlat]" Column="LearningPathCourseFlatID" />
                <ColumnReference Database="[absorb]" Schema="[dbo]" Table="[LearningPathCourseFlat]" Column="LearningPathID" />
                <ColumnReference Database="[absorb]" Schema="[dbo]" Table="[LearningPathCourseFlat]" Column="CourseID" />
                <ColumnReference Database="[absorb]" Schema="[dbo]" Table="[LearningPathCourseFlat]" Column="LessonID" />
              </OutputList>
              <RunTimeInformation>
                <RunTimeCountersPerThread Thread="0" ActualRows="3" ActualEndOfScans="1" ActualExecutions="1" />
              </RunTimeInformation>
              <NestedLoops Optimized="false">
                <OuterReferences>
                  <ColumnReference Database="[absorb]" Schema="[dbo]" Table="[LearningPathCourseFlat]" Column="LearningPathCourseFlatID" />
                </OuterReferences>
                <RelOp AvgRowSize="15" EstimateCPU="0.0001603" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="3" LogicalOp="Index Seek" NodeId="1" Parallel="false" PhysicalOp="Index Seek" EstimatedTotalSubtreeCost="0.0032853" TableCardinality="6220">
                  <OutputList>
                    <ColumnReference Database="[absorb]" Schema="[dbo]" Table="[LearningPathCourseFlat]" Column="LearningPathCourseFlatID" />
                    <ColumnReference Database="[absorb]" Schema="[dbo]" Table="[LearningPathCourseFlat]" Column="LearningPathID" />
                  </OutputList>
                  <RunTimeInformation>
                    <RunTimeCountersPerThread Thread="0" ActualRows="3" ActualEndOfScans="1" ActualExecutions="1" />
                  </RunTimeInformation>
                  <IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" NoExpandHint="false">
                    <DefinedValues>
                      <DefinedValue>
                        <ColumnReference Database="[absorb]" Schema="[dbo]" Table="[LearningPathCourseFlat]" Column="LearningPathCourseFlatID" />
                      </DefinedValue>
                      <DefinedValue>
                        <ColumnReference Database="[absorb]" Schema="[dbo]" Table="[LearningPathCourseFlat]" Column="LearningPathID" />
                      </DefinedValue>
                    </DefinedValues>
                    <Object Database="[absorb]" Schema="[dbo]" Table="[LearningPathCourseFlat]" Index="[cds_LPCF_LearningPathID]" IndexKind="NonClustered" />
                    <SeekPredicates>
                      <SeekPredicateNew>
                        <SeekKeys>
                          <Prefix ScanType="EQ">
                            <RangeColumns>
                              <ColumnReference Database="[absorb]" Schema="[dbo]" Table="[LearningPathCourseFlat]" Column="LearningPathID" />
                            </RangeColumns>
                            <RangeExpressions>
                              <ScalarOperator ScalarString="(2)">
                                <Const ConstValue="(2)" />
                              </ScalarOperator>
                            </RangeExpressions>
                          </Prefix>
                        </SeekKeys>
                      </SeekPredicateNew>
                    </SeekPredicates>
                  </IndexScan>
                </RelOp>
                <RelOp AvgRowSize="15" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="2" EstimateRewinds="0" EstimateRows="1" LogicalOp="Clustered Index Seek" NodeId="3" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="0.00690521" TableCardinality="6220">
                  <OutputList>
                    <ColumnReference Database="[absorb]" Schema="[dbo]" Table="[LearningPathCourseFlat]" Column="CourseID" />
                    <ColumnReference Database="[absorb]" Schema="[dbo]" Table="[LearningPathCourseFlat]" Column="LessonID" />
                  </OutputList>
                  <RunTimeInformation>
                    <RunTimeCountersPerThread Thread="0" ActualRows="3" ActualEndOfScans="0" ActualExecutions="3" />
                  </RunTimeInformation>
                  <IndexScan Lookup="true" Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" NoExpandHint="false">
                    <DefinedValues>
                      <DefinedValue>
                        <ColumnReference Database="[absorb]" Schema="[dbo]" Table="[LearningPathCourseFlat]" Column="CourseID" />
                      </DefinedValue>
                      <DefinedValue>
                        <ColumnReference Database="[absorb]" Schema="[dbo]" Table="[LearningPathCourseFlat]" Column="LessonID" />
                      </DefinedValue>
                    </DefinedValues>
                    <Object Database="[absorb]" Schema="[dbo]" Table="[LearningPathCourseFlat]" Index="[PK_LearningPathCourseFlat]" TableReferenceId="-1" IndexKind="Clustered" />
                    <SeekPredicates>
                      <SeekPredicateNew>
                        <SeekKeys>
                          <Prefix ScanType="EQ">
                            <RangeColumns>
                              <ColumnReference Database="[absorb]" Schema="[dbo]" Table="[LearningPathCourseFlat]" Column="LearningPathCourseFlatID" />
                            </RangeColumns>
                            <RangeExpressions>
                              <ScalarOperator ScalarString="[absorb].[dbo].[LearningPathCourseFlat].[LearningPathCourseFlatID]">
                                <Identifier>
                                  <ColumnReference Database="[absorb]" Schema="[dbo]" Table="[LearningPathCourseFlat]" Column="LearningPathCourseFlatID" />
                                </Identifier>
                              </ScalarOperator>
                            </RangeExpressions>
                          </Prefix>
                        </SeekKeys>
                      </SeekPredicateNew>
                    </SeekPredicates>
                  </IndexScan>
                </RelOp>
              </NestedLoops>
            </RelOp>
            <ParameterList>
              <ColumnReference Column="@1" ParameterCompiledValue="(2)" ParameterRuntimeValue="(2)" />
            </ParameterList>
          </QueryPlan>
        </StmtSimple>
      </Statements>
    </Batch>
    <Batch>
      <Statements>
        <StmtSimple StatementCompId="2" StatementEstRows="1" StatementId="3" StatementOptmLevel="FULL" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" StatementSubTreeCost="0.00328626" StatementText="select * from FuncLearningPathCourses(2)" StatementType="SELECT" QueryHash="0x4CC893158D58F06E" QueryPlanHash="0x0918ED67C03AA91C">
          <StatementSetOptions ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="true" />
          <QueryPlan DegreeOfParallelism="1" CachedPlanSize="8" CompileTime="0" CompileCPU="0" CompileMemory="96">
            <RelOp AvgRowSize="15" EstimateCPU="2E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Sequence" NodeId="0" Parallel="false" PhysicalOp="Sequence" EstimatedTotalSubtreeCost="0.00328626">
              <OutputList>
                <ColumnReference Database="[absorb]" Schema="[dbo]" Table="[FuncLearningPathCourses]" Column="CourseID" />
                <ColumnReference Database="[absorb]" Schema="[dbo]" Table="[FuncLearningPathCourses]" Column="LessonID" />
              </OutputList>
              <RunTimeInformation>
                <RunTimeCountersPerThread Thread="0" ActualRows="3" ActualEndOfScans="1" ActualExecutions="1" />
              </RunTimeInformation>
              <Sequence>
                <RelOp AvgRowSize="9" EstimateCPU="1.157E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Table-valued function" NodeId="1" Parallel="false" PhysicalOp="Table-valued function" EstimatedTotalSubtreeCost="1.157E-06">
                  <OutputList />
                  <RunTimeInformation>
                    <RunTimeCountersPerThread Thread="0" ActualRebinds="1" ActualRewinds="0" ActualRows="0" ActualEndOfScans="0" ActualExecutions="1" />
                  </RunTimeInformation>
                  <TableValuedFunction>
                    <DefinedValues />
                    <Object Database="[absorb]" Schema="[dbo]" Table="[FuncLearningPathCourses]" />
                    <ParameterList>
                      <ScalarOperator ScalarString="(2)">
                        <Const ConstValue="(2)" />
                      </ScalarOperator>
                    </ParameterList>
                  </TableValuedFunction>
                </RelOp>
                <RelOp AvgRowSize="15" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Table Scan" NodeId="3" Parallel="false" PhysicalOp="Table Scan" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="0">
                  <OutputList>
                    <ColumnReference Database="[absorb]" Schema="[dbo]" Table="[FuncLearningPathCourses]" Column="CourseID" />
                    <ColumnReference Database="[absorb]" Schema="[dbo]" Table="[FuncLearningPathCourses]" Column="LessonID" />
                  </OutputList>
                  <RunTimeInformation>
                    <RunTimeCountersPerThread Thread="0" ActualRows="3" ActualEndOfScans="1" ActualExecutions="1" />
                  </RunTimeInformation>
                  <TableScan Ordered="false" ForcedIndex="false" NoExpandHint="false">
                    <DefinedValues>
                      <DefinedValue>
                        <ColumnReference Database="[absorb]" Schema="[dbo]" Table="[FuncLearningPathCourses]" Column="CourseID" />
                      </DefinedValue>
                      <DefinedValue>
                        <ColumnReference Database="[absorb]" Schema="[dbo]" Table="[FuncLearningPathCourses]" Column="LessonID" />
                      </DefinedValue>
                    </DefinedValues>
                    <Object Database="[absorb]" Schema="[dbo]" Table="[FuncLearningPathCourses]" />
                  </TableScan>
                </RelOp>
              </Sequence>
            </RelOp>
          </QueryPlan>
        </StmtSimple>
      </Statements>
    </Batch>
  </BatchSequence>
</ShowPlanXML>

ferrethouse
Constraint Violating Yak Guru

352 Posts

Posted - 2011-09-08 : 15:32:40

quote:
Originally posted by robvolk

This version of the function may be a quick fix:

ALTER FUNCTION [dbo].[FuncLearningPathCourses] (@LearningPathId INT)
RETURNS TABLE
AS RETURN(SELECT LearningPathCourses.CourseID, LessonID
FROM LearningPathCourseGroups
INNER JOIN LearningPathCourseGroupCourses ON LearningPathCourseGroupCourses.LearningPathCourseGroupID = LearningPathCourseGroups.LearningPathCourseGroupID
INNER JOIN LearningPathCourses ON LearningPathCourses.LearningPathCourseID = LearningPathCourseGroupCourses.LearningPathCourseID
WHERE @LearningPathId > 0 AND LearningPathCourseGroups.LearningPathID = @LearningPathId)

It's an inline-table version for which the optimizer may generate a better plan.

Regarding the 2 plans, were they actual plans or estimated? The relative cost isn't as useful as the actual plan.

If the plan is relatively small, you can paste the XML version in the forums (less than 50K). If it's larger than that you could try posting it on PasteBin and posting that link to these forums instead. Post the XML plan output rather than text or graphical.

It is interesting Rob. I made this change and now the function has the same plan as the direct query above which in terms of reported "cost" is worse. I'm using the actual plans not the estimated plans.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-09-08 : 15:59:07

Yeah, it seems whenever I say "it should improve" it turns out it doesn't.

Does the performance change at all? Better or worse? Have you tested it (are you able to?) with a cold data and procedure cache? (DBCC FREEPROCCACHE and DBCC DROPCLEANBUFFERS, don't run these in production)

These really should be tested in the same context that the trigger uses, and the plan captured from that execution. You'd have to get plans for both versions of the function and post them. I'd suggest using PasteBin because those will be a lot larger and I'm not sure the forum software will preserve everything correctly.

Kristen
Test

22859 Posts

Posted - 2011-09-09 : 01:21:35

Any reason not to use a View instead of a Function? Not sure it will impact performance though.


CREATE VIEW dbo.V_FuncLearningPathCourses
AS
SELECT [V_LearningPathID] = G.LearningPathID, C.CourseID, LessonID 
FROM LearningPathCourseGroups AS G
	INNER JOIN LearningPathCourseGroupCourses AS GC
		ON GC.LearningPathCourseGroupID = G.LearningPathCourseGroupID 
	INNER JOIN LearningPathCourses AS C
		ON C.LearningPathCourseID = GC.LearningPathCourseID 
WHERE	G.LearningPathID > 0

and replace


FROM	FuncLearningPathCourses(Inserted.LearningPathID) lpc

with


FROM	dbo.V_FuncLearningPathCourses AS lpc
WHERE	V_LearningPathID = Inserted.LearningPathID

Can a LearningPathID return multiple instances of a specific value for CourseID? if not just JOINing [Courses] to the VIEW may be better than using "IN"

and also


AND EXISTS (SELECT NULL FROM FuncLearningPathCourses(Inserted.LearningPathID))

with


AND EXISTS
(
	SELECT	*
	FROM	dbo.V_FuncLearningPathCourses
	WHERE	V_LearningPathID = Inserted.LearningPathID
)

Changing


(SELECT COUNT(*) FROM Certificates WHERE Certificates.LearningPathID = Inserted.LearningPathID 
                                         AND Certificates.StudentsID = Inserted.StudentsID) = 0

to NOT EXISTS will be faster I think


NOT EXISTS
(
	SELECT	*
	FROM	dbo.Certificates
	WHERE	    Certificates.LearningPathID = Inserted.LearningPathID
		AND Certificates.StudentsID = Inserted.StudentsID
)

Dunno how important it is these days? but we prefix all tables / functions with "dbo." to save the query optimiser trying to resolve user-specific ownership.

Changing


CASE WHEN Inserted.Completed IS NOT NULL THEN Inserted.Completed ELSE GETDATE() END


COALESCE(Inserted.Completed, GETDATE() )

will probably be more efficient - but it won't change the query plan.

ferrethouse
Constraint Violating Yak Guru

352 Posts

Posted - 2011-09-12 : 11:50:19

Thanks so much Kristen for your efforts here. I will try out your suggestions and report the results.

ferrethouse
Constraint Violating Yak Guru

352 Posts

Posted - 2011-09-12 : 12:07:29

The "not exists" is 30% faster than the "select count" :)

Kristen
Test

22859 Posts

Posted - 2011-09-12 : 12:31:53

Excellent! I did rather think that it might be ...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources