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.
| Author |
Topic |
|
buus
Starting Member
4 Posts |
 Posted - 2007-03-24 : 12:25:57
|
I was investigating a slow stored procedure today, and came across some strange behavior. I have a function inside a SUM. It seems that if instead of SUMing the function, I insert the results into a temporary table and then SUM the contents of the temporary table, the query is 2x faster!Query 1: Old querySELECT SUM(dbo.GetFileBalanceFunc(L.LedgerID, FileID, @StartDate)) FROM Ledger L JOIN FileBalance FB ON FB.LedgerID = L.LedgerID WHERE L.BankAccountID = @BankAccountID Query 2: New query, 2x fastercreate table #foo (x decimal(15,2))INSERT INTO #fooSELECT dbo.GetFileBalanceFunc(L.LedgerID, FileID, @StartDate) FROM Ledger L JOIN FileBalance FB ON FB.LedgerID = L.LedgerID WHERE L.BankAccountID = @BankAccountIDSELECT SUM(x) FROM #foodrop table #foo Does anyone know why this would be the case? Seems quite odd, as if the function is being executed twice instead of once..? Thanks for any insights!Bryan |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2007-03-24 : 12:34:59
|
what does the query execution plan says ?
KH |
 |
|
|
buus
Starting Member
4 Posts |
Posted - 2007-03-24 : 13:12:06
|
Sorry, I should have anticipated that question!Slow query:---------------------------------------- 464067.90(1 row(s) affected)Rows Executes StmtText StmtId NodeId Parent PhysicalOp LogicalOp Argument DefinedValues EstimateRows EstimateIO EstimateCPU AvgRowSize TotalSubtreeCost OutputList Warnings Type Parallel EstimateExecutions ----------- ----------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------- ----------- ----------- ------------------------------ ------------------------------ --------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------ ------------------------ ------------------------ ----------- ------------------------ ------------------------------------ -------- ------------------------------ -------- ------------------------ 1 1 SELECT SUM(dbo.GetFileBalanceFunc(L.LedgerID, FileID, @StartDate)) FROM Ledger L JOIN FileBalance FB ON FB.LedgerID = L.LedgerID WHERE L.BankAccountID = @BankAccountID155187 1 0 NULL NULL NULL NULL 1.0 NULL NULL NULL 0.28155702 NULL NULL SELECT 0 NULL1 1 |--Compute Scalar(DEFINE:([Expr1002]=If ([Expr1007]=0) then NULL else [Expr1008])) 155187 2 1 Compute Scalar Compute Scalar DEFINE:([Expr1002]=If ([Expr1007]=0) then NULL else [Expr1008]) [Expr1002]=If ([Expr1007]=0) then NULL else [Expr1008] 1.0 0.0 3.161167E-4 24 0.28155702 [Expr1002] NULL PLAN_ROW 0 1.01 1 |--Stream Aggregate(DEFINE:([Expr1007]=COUNT_BIG([dbo].[GetFileBalanceFunc]([L].[LedgerID], [FB].[FileID], [@StartDate])), [Expr1008]=SUM([dbo].[GetFileBalanceFunc]([L].[LedgerID], [FB].[FileID], [@StartDate])))) 155187 3 2 Stream Aggregate Aggregate NULL [Expr1007]=COUNT_BIG([dbo].[GetFileBalanceFunc]([L].[LedgerID], [FB].[FileID], [@StartDate])), [Expr1008]=SUM([dbo].[GetFileBalanceFunc]([L].[LedgerID], [FB].[FileID], [@StartDate])) 1.0 0.0 3.161167E-4 24 0.28155702 [Expr1007], [Expr1008] NULL PLAN_ROW 0 1.09799 1 |--Hash Match(Inner Join, HASH:([L].[LedgerID])=([FB].[LedgerID])) 155187 4 3 Hash Match Inner Join HASH:([L].[LedgerID])=([FB].[LedgerID]) NULL 3161.167 0.0 0.10900188 19 0.28124088 [L].[LedgerID], [FB].[FileID] NULL PLAN_ROW 0 1.01 1 |--Clustered Index Scan(OBJECT:([ResWare].[dbo].[Ledger].[Ledger_PK] AS [L]), WHERE:([L].[BankAccountID]=[@BankAccountID])) 155187 5 4 Clustered Index Scan Clustered Index Scan OBJECT:([ResWare].[dbo].[Ledger].[Ledger_PK] AS [L]), WHERE:([L].[BankAccountID]=[@BankAccountID]) [L].[BankAccountID], [L].[LedgerID] 1.1666667 3.7578501E-2 8.6200002E-5 64 0.0376647 [L].[BankAccountID], [L].[LedgerID] NULL PLAN_ROW 0 1.019414 1 |--Clustered Index Scan(OBJECT:([ResWare].[dbo].[FileBalance].[FileBalance_PK] AS [FB])) 155187 6 4 Clustered Index Scan Clustered Index Scan OBJECT:([ResWare].[dbo].[FileBalance].[FileBalance_PK] AS [FB]) [FB].[LedgerID], [FB].[FileID] 19414.0 0.11313406 2.1433899E-2 52 0.13456796 [FB].[LedgerID], [FB].[FileID] NULL PLAN_ROW 0 1.0(6 row(s) affected) Fast Query:(9799 row(s) affected)Rows Executes StmtText StmtId NodeId Parent PhysicalOp LogicalOp Argument DefinedValues EstimateRows EstimateIO EstimateCPU AvgRowSize TotalSubtreeCost OutputList Warnings Type Parallel EstimateExecutions ----------- ----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------- ----------- ----------- ------------------------------ ------------------------------ ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------- ------------------------ ------------------------ ------------------------ ----------- ------------------------ ------------------------------------ -------- ------------------------------ -------- ------------------------ 9799 1 INSERT INTO #fooSELECT dbo.GetFileBalanceFunc(L.LedgerID, FileID, @StartDate) FROM Ledger L JOIN FileBalance FB ON FB.LedgerID = L.LedgerID WHERE L.BankAccountID = @BankAccountID103463 1 0 NULL NULL NULL NULL 3161.167 NULL NULL NULL 0.36416414 NULL NULL INSERT 0 NULL9799 1 |--Table Insert(OBJECT:([tempdb].[dbo].[#foo________________________________________________________________________________________________________________00000000D3A3]), SET:([#foo].[x]=[Expr1002])) 103463 2 1 Table Insert Insert OBJECT:([tempdb].[dbo].[#foo________________________________________________________________________________________________________________00000000D3A3]), SET:([#foo].[x]=[Expr1002]) NULL 3161.167 1.6756756E-2 3.1611668E-3 15 0.36416414 NULL NULL PLAN_ROW 0 1.09799 1 |--Table Spool 103463 3 2 Table Spool Eager Spool NULL NULL 3161.167 6.1234985E-2 1.1381201E-3 16 0.34424624 [Expr1002] NULL PLAN_ROW 0 1.09799 1 |--Top(ROWCOUNT est 0) 103463 4 3 Top Top NULL NULL 3161.167 0.0 3.161167E-4 16 0.28187314 [Expr1002] NULL PLAN_ROW 0 1.09799 1 |--Compute Scalar(DEFINE:([Expr1002]=[dbo].[GetFileBalanceFunc]([L].[LedgerID], [FB].[FileID], [@StartDate]))) 103463 5 4 Compute Scalar Compute Scalar DEFINE:([Expr1002]=[dbo].[GetFileBalanceFunc]([L].[LedgerID], [FB].[FileID], [@StartDate])) [Expr1002]=[dbo].[GetFileBalanceFunc]([L].[LedgerID], [FB].[FileID], [@StartDate]) 3161.167 0.0 3.161167E-4 16 0.28155702 [Expr1002] NULL PLAN_ROW 0 1.09799 1 |--Hash Match(Inner Join, HASH:([L].[LedgerID])=([FB].[LedgerID])) 103463 6 5 Hash Match Inner Join HASH:([L].[LedgerID])=([FB].[LedgerID]) NULL 3161.167 0.0 0.10900188 19 0.28124088 [L].[LedgerID], [FB].[FileID] NULL PLAN_ROW 0 1.01 1 |--Clustered Index Scan(OBJECT:([ResWare].[dbo].[Ledger].[Ledger_PK] AS [L]), WHERE:([L].[BankAccountID]=[@BankAccountID])) 103463 7 6 Clustered Index Scan Clustered Index Scan OBJECT:([ResWare].[dbo].[Ledger].[Ledger_PK] AS [L]), WHERE:([L].[BankAccountID]=[@BankAccountID]) [L].[BankAccountID], [L].[LedgerID] 1.1666667 3.7578501E-2 8.6200002E-5 64 0.0376647 [L].[BankAccountID], [L].[LedgerID] NULL PLAN_ROW 0 1.019414 1 |--Clustered Index Scan(OBJECT:([ResWare].[dbo].[FileBalance].[FileBalance_PK] AS [FB])) 103463 8 6 Clustered Index Scan Clustered Index Scan OBJECT:([ResWare].[dbo].[FileBalance].[FileBalance_PK] AS [FB]) [FB].[LedgerID], [FB].[FileID] 19414.0 0.11313406 2.1433899E-2 52 0.13456796 [FB].[LedgerID], [FB].[FileID] NULL PLAN_ROW 0 1.0(8 row(s) affected) ---------------------------------------- 464067.90(1 row(s) affected)Rows Executes StmtText StmtId NodeId Parent PhysicalOp LogicalOp Argument DefinedValues EstimateRows EstimateIO EstimateCPU AvgRowSize TotalSubtreeCost OutputList Warnings Type Parallel EstimateExecutions ----------- ----------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------- ----------- ----------- ------------------------------ ------------------------------ ----------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------- ------------------------ ------------------------ ------------------------ ----------- ------------------------ ----------------------- -------- ------------------------------ -------- ------------------------ 1 1 SELECT SUM(x) FROM #foo 103464 1 0 NULL NULL NULL NULL 1.0 NULL NULL NULL 3.7658349E-2 NULL NULL SELECT 0 NULL1 1 |--Compute Scalar(DEFINE:([Expr1002]=If ([Expr1003]=0) then NULL else [Expr1004])) 103464 2 1 Compute Scalar Compute Scalar DEFINE:([Expr1002]=If ([Expr1003]=0) then NULL else [Expr1004]) [Expr1002]=If ([Expr1003]=0) then NULL else [Expr1004] 1.0 0.0 0.00000025 24 3.7658349E-2 [Expr1002] NULL PLAN_ROW 0 1.01 1 |--Stream Aggregate(DEFINE:([Expr1003]=COUNT_BIG([#foo].[x]), [Expr1004]=SUM([#foo].[x]))) 103464 3 2 Stream Aggregate Aggregate NULL [Expr1003]=COUNT_BIG([#foo].[x]), [Expr1004]=SUM([#foo].[x]) 1.0 0.0 0.00000025 24 3.7658349E-2 [Expr1003], [Expr1004] NULL PLAN_ROW 0 1.09799 1 |--Table Scan(OBJECT:([tempdb].[dbo].[#foo________________________________________________________________________________________________________________00000000D3A3])) 103464 4 3 Table Scan Table Scan OBJECT:([tempdb].[dbo].[#foo________________________________________________________________________________________________________________00000000D3A3]) [#foo].[x] 1.0 3.7578501E-2 7.9600002E-5 16 3.7658099E-2 [#foo].[x] NULL PLAN_ROW 0 1.0(4 row(s) affected) |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-03-24 : 13:17:56
|
| Post the function code.Peter LarssonHelsingborg, Sweden |
|
|
|
buus
Starting Member
4 Posts |
Posted - 2007-03-24 : 13:32:59
|
Here you go...CREATE FUNCTION dbo.GetFileBalanceFunc (@LedgerID int, @FileID int, @AtDate DATETIME) RETURNS Decimal(15,2)ASBEGINDECLARE @UpdateDate DATETIMEDECLARE @Balance Decimal(15,2)SELECT @UpdateDate = ChangeDate, @Balance = Balance FROM FileBalance WHERE FileID = @FileID AND LedgerID = @LedgerID-- Need to calculate the balance if our date is later than the date we are looking forIF(@UpdateDate >= @AtDate OR @UpdateDate IS NULL)BEGIN SET @Balance = NULL SELECT @Balance = ISNULL(SUM(LEI.Amount), 0) FROM LedgerEntry LE JOIN LedgerEntryItem LEI ON LEI.LedgerEntryID = LE.LedgerEntryID WHERE FileID = @FileID AND LedgerID = @LedgerID AND ((TransactionTypeID = 5 AND LE.IssueDate < @AtDate) OR (TransactionTypeID <> 5 AND LE.CreateDate < @AtDate)) SET @Balance = @Balance - ISNULL((SELECT SUM(Amount) FROM LedgerEntry LE JOIN LedgerEntryItem LEI ON LEI.LedgerEntryID = LE.LedgerEntryID JOIN Invoice I ON I.InvoiceID = LE.InvoiceID AND I.IsVoid = 0 WHERE LE.TransactionTypeID = 4 AND LE.InvoiceID = I.InvoiceID AND LE.FileID = @FileID AND LE.LedgerID = @LedgerID AND LE.CreateDate < @AtDate), 0) SET @Balance = @Balance + ISNULL((SELECT SUM(Amount) FROM InvoicePayment IP JOIN Invoice I ON I.InvoiceID = IP.InvoiceID AND I.IsVoid = 0 JOIN LedgerEntry LE ON LE.InvoiceID = I.InvoiceID AND TransactionTypeID = 4 AND LE.FileID = @FileID AND LE.LedgerID = @LedgerID WHERE IP.PaymentDate < @AtDate), 0)ENDIF @Balance IS NULL SET @Balance = 0RETURN(@Balance) |
|
|
|
buus
Starting Member
4 Posts |
Posted - 2007-03-24 : 13:38:22
|
| One other odd thing that I noticed. About an hour ago, when I first performed this analysis, the slow query was taking 8 seconds, and the fast one 4 seconds. Now the slow one is taking 1 minute, and the fast one is taking 22 seconds. The stored procedure is also called from an application, and from the application it's still only taking 4 seconds, while in query analyzer it's taking 22 seconds. Any idea what would cause such a thing, or ideas about how to track down what might be causing it? I should probably start up the profiler and run it both ways to see what's different. Hmm.. |
|
|
|
|
|
|
|
|
SUM(function) slow?