The function takes forever to run for 13,000 data

Question

The function takes forever to run for 13,000 data

bladerunner148

SSCommitted

Points: 1824
More actions
May 18, 2010 at 3:54 pm

#143160

The function below takes forever to run. However, if I comment the Update statements, then it runs in like 2 seconds. So, I can be sure that its the Update queries thats slowing the process. Just heads up, the other table used here "BillingHistory" has the same columns and the datatypes are same as temp table (@LENNOXBILLING).
Could you anyone advise what am I doing wrong here? Thanks in advance.
ALTER FUNCTION [dbo].[SA_FUNC_LennoxBilling]()
RETURNS
@LENNOXBILLING TABLE
(
DealerNo varchar(255),
BillingNo varchar(255),
[Transaction] varchar(255),
Medium varchar(255),
OrderNumber int,
MediaOrderNumber int,
RptDate varchar(255),
District varchar(255),
State varchar(255),
OrderType varchar(255),
OrderDate datetime,
Vendor varchar(500),
TotalUnits int,
ProdActualSpent money,
MediaActualSpent money,
ActualSpent money,
GST int,
PST int,
Gross money,
LastUpdate datetime,
Season varchar(255),
[Year] int
)
AS
BEGIN
INSERT INTO @LENNOXBILLING
SELECTc.ClientCenterId AS DealerNo,
c.ClientCenterId AS BillingNo,
'Add ' AS [Transaction],
mt.MediaTypeDesc AS Medium,
parents.AdvantageOrderNumber AS OrderNumber,
NULL AS MediaOrderNumber,
SUBSTRING(DATENAME(month, GETDATE()), 1, 3) + CAST(YEAR(GETDATE()) AS varchar(5)) AS RptDate,
'U4' + d.ClientDistrictId AS District,
NULL AS State,
'Actual' AS OrderType,
parents.StartDate AS OrderDate,
actuals.VendorPreference AS Vendor,
SUM(actuals.DropQuantity) AS TotalUnits,
SUM(actuals.DropQuantity * COALESCE(omt.CostPerPiece, 0.00)) AS ProdActualSpent,
CASE
WHEN mt.MediaTypeDesc = 'Inserts' THEN SUM(actuals.DropAmount)
ELSE 0.00
END AS MediaActualSpent,
CASE
WHEN mt.MediaTypeDesc = 'Inserts' THEN SUM(actuals.DropQuantity * omt.CostPerPiece) + SUM(actuals.DropAmount)
ELSE SUM(actuals.DropAmount)
END AS ActualSpent,
0 AS GST,
0 AS PST,
CASE
WHEN mt.MediaTypeDesc = 'Inserts' THEN SUM(actuals.DropQuantity * omt.CostPerPiece) + SUM(actuals.DropAmount)
ELSE SUM(actuals.DropAmount)
END AS Gross,
actuals.LastModifiedDate AS LastUpdate,
SeasonDesc AS Season,
p.[year] AS [Year]
FROM dbo.Centers c
INNER JOIN Center_Plans cp ON c.CenterId = cp.CenterId
INNER JOIN dbo.Plans p ON p.PlanId = cp.PlanId
INNER JOIN dbo.PlanDetails parents ON p.PlanId = parents.PlanId
INNER JOIN dbo.PlanDetails actuals ON parents.PlanDetailId = actuals.ParentDetailId
LEFT JOIN dbo.MediaTypes mt ON parents.MediaTypeId = mt.MediaTypeId
LEFT JOIN dbo.Organization_MediaTypes omt ON c.OrganizationId = omt.OrganizationId AND parents.MediaTypeId = omt.MediaTypeId
LEFT JOIN dbo.Districts d ON c.DistrictId = d.DistrictId
LEFT JOIN Seasons s ON parents.SeasonId = s.SeasonId
WHEREc.OrganizationId = 3
ANDp.Active = 1
ANDc.ClientCenterId <> '1 TROY'
GROUP BY parents.AdvantageOrderNumber, c.ClientCenterId, mt.MediaTypeDesc, d.ClientDistrictId, parents.StartDate, actuals.VendorPreference,
actuals.LastModifiedDate, omt.CostPerPiece, parents.PlanId, p.[year], s.SeasonDesc
--update total units
UPDATE@LENNOXBILLING
SETlb.TotalUnits = (lb.TotalUnits - COALESCE(bh.TotalUnits, 0)),
lb.[Transaction] = 'Change'
FROM@LENNOXBILLING lb
INNER JOIN BillingHistory bh ON lb.OrderNumber = bh.OrderNumber AND lb.Medium = bh.Medium
WHEREbh.TotalUnits <> lb.TotalUnits
--update prod actual spent
UPDATE@LENNOXBILLING
SETlb.ProdActualSpent = (lb.ProdActualSpent - COALESCE(bh.ProdActualSpent, 0)),
lb.[Transaction] = 'Change'
FROM@LENNOXBILLING lb
INNER JOIN BillingHistory bh ON lb.OrderNumber = bh.OrderNumber AND lb.Medium = bh.Medium
WHEREbh.ProdActualSpent <> lb.ProdActualSpent
--update media actual spent
UPDATE@LENNOXBILLING
SETlb.MediaActualSpent = (lb.MediaActualSpent - COALESCE(bh.MediaActualSpent, 0)),
lb.[Transaction] = 'Change'
FROM@LENNOXBILLING lb
INNER JOIN BillingHistory bh ON lb.OrderNumber = bh.OrderNumber AND lb.Medium = bh.Medium
WHEREbh.MediaActualSpent <> lb.MediaActualSpent
--update actual spent
UPDATE@LENNOXBILLING
SETlb.ActualSpent = (lb.ActualSpent - COALESCE(bh.ActualSpent, 0)),
lb.[Transaction] = 'Change'
FROM@LENNOXBILLING lb
INNER JOIN BillingHistory bh ON lb.OrderNumber = bh.OrderNumber AND lb.Medium = bh.Medium
WHEREbh.ActualSpent <> lb.ActualSpent
--update gross
UPDATE@LENNOXBILLING
SETlb.Gross = (lb.Gross - COALESCE(bh.Gross, 0)),
lb.[Transaction] = 'Change'
FROM@LENNOXBILLING lb
INNER JOIN BillingHistory bh ON lb.OrderNumber = bh.OrderNumber AND lb.Medium = bh.Medium
WHEREbh.Gross <> lb.Gross
RETURN
END

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323482 More actions · Answer 1

table variables are notoriously slow when they get a lot of data in them, because they cannot take advantage of statistics; how many rows are in the table variable @LENNOXBILLING? you said 13K, right?

i've heard rules of thumb that says no more than 1000 records to a table variable, but i think it has more to do with the number of pages of memory the table uses.

if you switched to a stored procedure and temp tables, i'd expect an improvement. i'm fiddling with it now to see if a cte would work better than what you've got here.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

LutzM SSC Guru Points: 107049 More actions · Answer 2

Here's a Q&D (quick and dirty) CTE approach using your INSERT statement to define the CTE.

The final SELECT actually uses a bunch of CASE statement to cover your various WHERE conditions.

I'm not sure if it actually will provide te same results since I have nothing to compare against... (Side note: the calculated columns are added as new cols)

@lowell: something along those lines?

;WITH cte AS

(SELECTc.ClientCenterId AS DealerNo,

c.ClientCenterId AS BillingNo,

'Add ' AS [TRANSACTION],

mt.MediaTypeDesc AS Medium,

parents.AdvantageOrderNumber AS OrderNumber,

NULL AS MediaOrderNumber,

SUBSTRING(DATENAME(MONTH, GETDATE()), 1, 3) + CAST(YEAR(GETDATE()) AS VARCHAR(5)) AS RptDate,

'U4' + d.ClientDistrictId AS District,

NULL AS STATE,

'Actual' AS OrderType,

parents.StartDate AS OrderDate,

actuals.VendorPreference AS Vendor,

SUM(actuals.DropQuantity) AS TotalUnits,

SUM(actuals.DropQuantity * COALESCE(omt.CostPerPiece, 0.00)) AS ProdActualSpent,

CASE

WHEN mt.MediaTypeDesc = 'Inserts' THEN SUM(actuals.DropAmount)

ELSE 0.00

END AS MediaActualSpent,

CASE

WHEN mt.MediaTypeDesc = 'Inserts' THEN SUM(actuals.DropQuantity * omt.CostPerPiece) + SUM(actuals.DropAmount)

ELSE SUM(actuals.DropAmount)

END AS ActualSpent,

0 AS GST,

0 AS PST,

CASE

WHEN mt.MediaTypeDesc = 'Inserts' THEN SUM(actuals.DropQuantity * omt.CostPerPiece) + SUM(actuals.DropAmount)

ELSE SUM(actuals.DropAmount)

END AS Gross,

actuals.LastModifiedDate AS LastUpdate,

SeasonDesc AS Season,

p.[year] AS [YEAR]

FROM dbo.Centers c

INNER JOIN Center_Plans cp ON c.CenterId = cp.CenterId

INNER JOIN dbo.Plans p ON p.PlanId = cp.PlanId

INNER JOIN dbo.PlanDetails parents ON p.PlanId = parents.PlanId

INNER JOIN dbo.PlanDetails actuals ON parents.PlanDetailId = actuals.ParentDetailId

LEFT JOIN dbo.MediaTypes mt ON parents.MediaTypeId = mt.MediaTypeId

LEFT JOIN dbo.Organization_MediaTypes omt ON c.OrganizationId = omt.OrganizationId AND parents.MediaTypeId = omt.MediaTypeId

LEFT JOIN dbo.Districts d ON c.DistrictId = d.DistrictId

LEFT JOIN Seasons s ON parents.SeasonId = s.SeasonId

WHEREc.OrganizationId = 3

ANDp.Active = 1

ANDc.ClientCenterId <> '1 TROY'

GROUP BY parents.AdvantageOrderNumber, c.ClientCenterId, mt.MediaTypeDesc, d.ClientDistrictId, parents.StartDate, actuals.VendorPreference,

actuals.LastModifiedDate, omt.CostPerPiece, parents.PlanId, p.[year], s.SeasonDesc

)

SELECT cte.*,

CASE WHEN bh.TotalUnits <> lb.TotalUnits

THEN (lb.TotalUnits - COALESCE(bh.TotalUnits, 0)) ELSE lb.TotalUnits END AS TotalUnits_,

CASE WHEN bh.ProdActualSpent <> lb.ProdActualSpent

THEN (lb.ProdActualSpent - COALESCE(bh.ProdActualSpent, 0)) ELSE lb.ProdActualSpent END AS ProdActualSpent_,

CASE WHEN bh.MediaActualSpent <> lb.MediaActualSpent

THEN (lb.MediaActualSpent - COALESCE(bh.MediaActualSpent, 0)) ELSE lb.MediaActualSpent END AS MediaActualSpent_,

CASE WHEN bh.ActualSpent <> lb.ActualSpent

THEN (lb.ActualSpent - COALESCE(bh.ActualSpent, 0)) ELSE llb.ActualSpent END AS ActualSpent_ ,

CASE WHEN bh.Gross <> lb.Gross

THEN (lb.Gross - COALESCE(bh.Gross, 0)) ELSE lb.Gross END AS Gross_,

CASE WHEN

bh.TotalUnits <> lb.TotalUnits

OR bh.ProdActualSpent <> lb.ProdActualSpent

OR bh.MediaActualSpent <> lb.MediaActualSpent

OR bh.ActualSpent <> lb.ActualSpent

OR bh.Gross <> lb.Gross

THEN 'Change' ELSE lb.[Transaction] END AS [TRANSACTION_]

FROMcte lb

INNER JOIN BillingHistory bh ON lb.OrderNumber = bh.OrderNumber AND lb.Medium = bh.Medium

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

bladerunner148 SSCommitted Points: 1824 More actions · Answer 3

Hi Lowell,

I see your point. As soon as I ran that query using a temp table instead, it ran like in 2 secs. But my problem is I would have to use Views since we are using AdHoc report in a .net application. That requires a datasource in order to create a report.

Now, if I am correct, we can't use a temp table inside Views, right? I could have used this query all in a View but as you see I would have to do some updates which either I can store in a temp table or in a memory.

Whats the best way to go about this? Thanks for your help on this.

Sid

bladerunner148 SSCommitted Points: 1824 More actions · Answer 4

Sorry, just saw your way using cte...let me try this and I will get back to you.

Thanks again.

Lowell SSC Guru Points: 323482 More actions · Answer 5

damn Lutz; your's looks much better than mine, so i decline to post it 🙂

very nice job.

blade, if you look at Lutz's contribution, it's doing all your updates as calculations...no UPDATE statement anywhere. compare it and see if it produces the same results you were getting previously.

you could take Lutz's statement and put it in a view no problem:

create view myExample as

WITH cte AS

( SELECT c.ClientCenterId AS DealerNo,

c.ClientCenterId AS BillingNo,

'Add ' AS [TRANSACTION],

mt.MediaTypeDesc AS Medium,

parents.AdvantageOrderNumber AS OrderNumber,

NULL AS MediaOrderNumber,

SUBSTRING(DATENAME(MONTH, GETDATE()), 1, 3) + CAST(YEAR(GETDATE()) AS VARCHAR(5)) AS RptDate,

'U4' + d.ClientDistrictId AS District,

NULL AS STATE,

'Actual' AS OrderType,

parents.StartDate AS OrderDate,

actuals.VendorPreference AS Vendor,

SUM(actuals.DropQuantity) AS TotalUnits,

SUM(actuals.DropQuantity * COALESCE(omt.CostPerPiece, 0.00)) AS ProdActualSpent,

CASE

WHEN mt.MediaTypeDesc = 'Inserts' THEN SUM(actuals.DropAmount)

ELSE 0.00

END AS MediaActualSpent,

CASE

WHEN mt.MediaTypeDesc = 'Inserts' THEN SUM(actuals.DropQuantity * omt.CostPerPiece) + SUM(actuals.DropAmount)

ELSE SUM(actuals.DropAmount)

END AS ActualSpent,

0 AS GST,

0 AS PST,

CASE

WHEN mt.MediaTypeDesc = 'Inserts' THEN SUM(actuals.DropQuantity * omt.CostPerPiece) + SUM(actuals.DropAmount)

ELSE SUM(actuals.DropAmount)

END AS Gross,

actuals.LastModifiedDate AS LastUpdate,

SeasonDesc AS Season,

p.[year] AS [YEAR]

FROM dbo.Centers c

INNER JOIN Center_Plans cp ON c.CenterId = cp.CenterId

INNER JOIN dbo.Plans p ON p.PlanId = cp.PlanId

INNER JOIN dbo.PlanDetails parents ON p.PlanId = parents.PlanId

INNER JOIN dbo.PlanDetails actuals ON parents.PlanDetailId = actuals.ParentDetailId

LEFT JOIN dbo.MediaTypes mt ON parents.MediaTypeId = mt.MediaTypeId

LEFT JOIN dbo.Organization_MediaTypes omt ON c.OrganizationId = omt.OrganizationId AND parents.MediaTypeId = omt.MediaTypeId

LEFT JOIN dbo.Districts d ON c.DistrictId = d.DistrictId

LEFT JOIN Seasons s ON parents.SeasonId = s.SeasonId

WHERE c.OrganizationId = 3

AND p.Active = 1

AND c.ClientCenterId <> '1 TROY'

GROUP BY parents.AdvantageOrderNumber, c.ClientCenterId, mt.MediaTypeDesc, d.ClientDistrictId, parents.StartDate, actuals.VendorPreference,

actuals.LastModifiedDate, omt.CostPerPiece, parents.PlanId, p.[year], s.SeasonDesc

)

SELECT cte.*,

CASE WHEN bh.TotalUnits <> lb.TotalUnits

THEN (lb.TotalUnits - COALESCE(bh.TotalUnits, 0)) ELSE lb.TotalUnits END AS TotalUnits_,

CASE WHEN bh.ProdActualSpent <> lb.ProdActualSpent

THEN (lb.ProdActualSpent - COALESCE(bh.ProdActualSpent, 0)) ELSE lb.ProdActualSpent END AS ProdActualSpent_,

CASE WHEN bh.MediaActualSpent <> lb.MediaActualSpent

THEN (lb.MediaActualSpent - COALESCE(bh.MediaActualSpent, 0)) ELSE lb.MediaActualSpent END AS MediaActualSpent_,

CASE WHEN bh.ActualSpent <> lb.ActualSpent

THEN (lb.ActualSpent - COALESCE(bh.ActualSpent, 0)) ELSE llb.ActualSpent END AS ActualSpent_ ,

CASE WHEN bh.Gross <> lb.Gross

THEN (lb.Gross - COALESCE(bh.Gross, 0)) ELSE lb.Gross END AS Gross_,

CASE WHEN

bh.TotalUnits <> lb.TotalUnits

OR bh.ProdActualSpent <> lb.ProdActualSpent

OR bh.MediaActualSpent <> lb.MediaActualSpent

OR bh.ActualSpent <> lb.ActualSpent

OR bh.Gross <> lb.Gross

THEN 'Change' ELSE lb.[Transaction] END AS [TRANSACTION_]

FROM cte lb

INNER JOIN BillingHistory bh ON lb.OrderNumber = bh.OrderNumber AND lb.Medium = bh.Medium

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

LutzM SSC Guru Points: 107049 More actions · Answer 6

Lowell (5/18/2010)
damn Lutz; your's looks much better than mine, so i decline to post it 🙂
very nice job.
blade, if you look at Lutz's contribution, it's doing all your updates as calculations...no UPDATE statement anywhere. compare it and see if it produces the same results you were getting previously.
you could take Lutz's statement and put it in a view no problem:
...

So we'll never know if your code might perform better or not. :crying:

In the field we're working in appearance means nothing, performance means everything. 😀 (opposite to some other so-called "professions" 😉 )

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

bladerunner148 SSCommitted Points: 1824 More actions · Answer 7

Hey Lowell,

I don't get any result when I run this view.

WITH cte AS

(

SELECT c.ClientCenterId AS DealerNo,

c.ClientCenterId AS BillingNo,

'Add ' AS [TRANSACTION],

mt.MediaTypeDesc AS Medium,

parents.AdvantageOrderNumber AS OrderNumber,

NULL AS MediaOrderNumber,

SUBSTRING(DATENAME(MONTH, GETDATE()), 1, 3) + CAST(YEAR(GETDATE()) AS VARCHAR(5)) AS RptDate,

'U4' + d.ClientDistrictId AS District,

NULL AS STATE,

'Actual' AS OrderType,

parents.StartDate AS OrderDate,

actuals.VendorPreference AS Vendor,

SUM(actuals.DropQuantity) AS TotalUnits,

SUM(actuals.DropQuantity * COALESCE(omt.CostPerPiece, 0.00)) AS ProdActualSpent,

CASE

WHEN mt.MediaTypeDesc = 'Inserts' THEN SUM(actuals.DropAmount)

ELSE 0.00 END AS MediaActualSpent,

CASE

WHEN mt.MediaTypeDesc = 'Inserts' THEN SUM(actuals.DropQuantity * omt.CostPerPiece) + SUM(actuals.DropAmount)

ELSE SUM(actuals.DropAmount) END AS ActualSpent,

0 AS GST,

0 AS PST,

CASE

WHEN mt.MediaTypeDesc = 'Inserts' THEN SUM(actuals.DropQuantity * omt.CostPerPiece) + SUM(actuals.DropAmount)

ELSE SUM(actuals.DropAmount) END AS Gross,

actuals.LastModifiedDate AS LastUpdate,

SeasonDesc AS Season,

p.[year] AS [YEAR]

FROM dbo.Centers c

INNER JOIN Center_Plans cp ON c.CenterId = cp.CenterId

INNER JOIN dbo.Plans p ON p.PlanId = cp.PlanId

INNER JOIN dbo.PlanDetails parents ON p.PlanId = parents.PlanId

INNER JOIN dbo.PlanDetails actuals ON parents.PlanDetailId = actuals.ParentDetailId

LEFT JOIN dbo.MediaTypes mt ON parents.MediaTypeId = mt.MediaTypeId

LEFT JOIN dbo.Organization_MediaTypes omt ON c.OrganizationId = omt.OrganizationId AND parents.MediaTypeId = omt.MediaTypeId

LEFT JOIN dbo.Districts d ON c.DistrictId = d.DistrictId

LEFT JOIN Seasons s ON parents.SeasonId = s.SeasonId

WHERE c.OrganizationId = 3

AND p.Active = 1

AND c.ClientCenterId <> '1 TROY'

GROUP BY parents.AdvantageOrderNumber, c.ClientCenterId, mt.MediaTypeDesc, d.ClientDistrictId, parents.StartDate, actuals.VendorPreference,

actuals.LastModifiedDate, omt.CostPerPiece, parents.PlanId, p.[year], s.SeasonDesc

)

SELECT lb.*,

CASE WHEN bh.TotalUnits <> lb.TotalUnits

THEN (lb.TotalUnits - COALESCE(bh.TotalUnits, 0)) ELSE lb.TotalUnits END AS TotalUnits_,

CASE WHEN bh.ProdActualSpent <> lb.ProdActualSpent

THEN (lb.ProdActualSpent - COALESCE(bh.ProdActualSpent, 0)) ELSE lb.ProdActualSpent END AS ProdActualSpent_,

CASE WHEN bh.MediaActualSpent <> lb.MediaActualSpent

THEN (lb.MediaActualSpent - COALESCE(bh.MediaActualSpent, 0)) ELSE lb.MediaActualSpent END AS MediaActualSpent_,

CASE WHEN bh.ActualSpent <> lb.ActualSpent

THEN (lb.ActualSpent - COALESCE(bh.ActualSpent, 0)) ELSE lb.ActualSpent END AS ActualSpent_ ,

CASE WHEN bh.Gross <> lb.Gross

THEN (lb.Gross - COALESCE(bh.Gross, 0)) ELSE lb.Gross END AS Gross_,

CASE WHEN

bh.TotalUnits <> lb.TotalUnits

OR bh.ProdActualSpent <> lb.ProdActualSpent

OR bh.MediaActualSpent <> lb.MediaActualSpent

OR bh.ActualSpent <> lb.ActualSpent

OR bh.Gross <> lb.Gross

THEN 'Change' ELSE lb.[Transaction] END AS [TRANSACTION_]

FROM cte lb

INNER JOIN BillingHistory bh ON lb.OrderNumber = bh.OrderNumber AND lb.Medium = bh.Medium

bladerunner148 SSCommitted Points: 1824 More actions · Answer 8

Never mind...i changed the INNER JOIN to BillingHistory to LEFT JOIN..it seems like there were no match..but now its working. I will check the data but this is great stuff. Thanks to both of you.:-)