I have a fairly complex query which runs in just a few seconds on its own, but when wrapped in a table-valued function, it's far slower; I've not actually let it finish, but it's run for up to ten minutes without ending. The only change is replacing two date variables (initialized with date literals) with date parameters:
Runs in Seven Seconds
DECLARE @StartDate DATE = '2011-05-21'
DECLARE @EndDate DATE = '2011-05-23'
DECLARE @Data TABLE (...)
INSERT INTO @Data(...) SELECT...
SELECT * FROM @Data
Runs for At Least Ten Minutes
CREATE FUNCTION X (@StartDate DATE, @EndDate DATE)
RETURNS TABLE AS RETURN
SELECT ...
SELECT * FROM X ('2011-05-21', '2011-05-23')
I had previously written the function as a multi-statement TVF with a RETURNS @Data TABLE (…) clause, but swapping that for the inline structure has not made a noticeable change. The long run time of the TVF is the actual SELECT * FROM X time; actually creating the UDF just takes a few seconds.
I could post the query in question, but it's a bit long (~165 lines) and, based on the success of the first approach, I suspect something else is going on. Skimming through the execution plans, they appear to be identical.
I've tried breaking the query into smaller sections, without change. No single section takes more than a couple seconds when executed alone, but the TVF still hangs.
I see a very similar question, https://stackoverflow.com/questions/4190506/sql-server-2005-table-valued-function-weird-performance, but I'm not sure that the solution applies. Perhaps someone has seen this problem and knows a more general solution? Thanks!
Here's the dm_exec_requests after several minutes of processing:
session_id 59
request_id 0
start_time 40688.46517
status running
command UPDATE
sql_handle 0x030015002D21AF39242A1101ED9E00000000000000000000
statement_start_offset 10962
statement_end_offset 16012
plan_handle 0x050015002D21AF3940C1E6B0040000000000000000000000
database_id 21
user_id 1
connection_id 314AE0E4-A1FB-4602-BF40-02D857BAD6CF
blocking_session_id 0
wait_type NULL
wait_time 0
last_wait_type SOS_SCHEDULER_YIELD
wait_resource
open_transaction_count 0
open_resultset_count 1
transaction_id 48030651
context_info 0x
percent_complete 0
estimated_completion_time 0
cpu_time 344777
total_elapsed_time 348632
scheduler_id 7
task_address 0x000000045FC85048
reads 1549
writes 13
logical_reads 30331425
text_size 2147483647
language us_english
date_format mdy
date_first 7
quoted_identifier 1
arithabort 1
ansi_null_dflt_on 1
ansi_defaults 0
ansi_warnings 1
ansi_padding 1
ansi_nulls 1
concat_null_yields_null 1
transaction_isolation_level 2
lock_timeout -1
deadlock_priority 0
row_count 105
prev_error 0
nest_level 1
granted_query_memory 170
executing_managed_code 0
group_id 2
query_hash 0xBE6A286546AF62FC
query_plan_hash 0xD07630B947043AF0
Here's the complete query:
CREATE FUNCTION Routine.MarketingDashboardECommerceBase (@StartDate DATE, @EndDate DATE)
RETURNS TABLE AS RETURN
WITH RegionsByCode AS (SELECT CountryCode, MIN(Region) AS Region FROM Staging.Volusion.MarketingRegions GROUP BY CountryCode)
SELECT
D.Date, Div.Division, Region.Region, C.Category1, C.Category2, C.Category3,
COALESCE(V.Visits, 0) AS Visits,
COALESCE(Dem.Demos, 0) AS Demos,
COALESCE(S.GrossStores, 0) AS GrossStores,
COALESCE(S.PaidStores, 0) AS PaidStores,
COALESCE(S.NetStores, 0) AS NetStores,
COALESCE(S.StoresActiveNow, 0) AS StoresActiveNow
-- This line causes the run time to climb from a few seconds to over an hour!
--COALESCE(V.Visits, 0) * COALESCE(ACS.AvgClickCost, GAAC.AvgAdCost, 0.00) AS TotalAdCost
-- This line alone does not inflate the run time
--ACS.AvgClickCost
-- This line is enough to increase the run time to at least a couple minutes
--GAAC.AvgAdCost
FROM
--Dates AS D
(SELECT SQLDate AS Date FROM Dates WHERE SQLDate BETWEEN @StartDate AND @EndDate) AS D
CROSS JOIN (SELECT 'UK' AS Division UNION SELECT 'US' UNION SELECT 'IN' UNION SELECT 'Unknown') AS Div
CROSS JOIN (SELECT Category1, Category2, Category3 FROM Routine.MarketingDashboardCampaignMap UNION SELECT 'Unknown', 'Unknown', 'Unknown') AS C
CROSS JOIN (SELECT DISTINCT Region FROM Staging.Volusion.MarketingRegions) AS Region
-- Visitors
LEFT JOIN
(
SELECT
V.Date,
CASE WHEN V.Country IN ('United Kingdom', 'Guernsey', 'Ireland', 'Jersey') THEN 'UK'
WHEN V.Country IN ('United States', 'Canada', 'Puerto Rico', 'U.S. Virgin Islands') THEN 'US'
ELSE 'IN' END AS Division,
COALESCE(MR.Region, 'Unknown') AS Region,
C.Category1, C.Category2, C.Category3,
SUM(V.Visits) AS Visits
FROM
RawData.GoogleAnalytics.Visits AS V
INNER JOIN Routine.MarketingDashboardCampaignMap AS C ON V.LandingPage = C.LandingPage AND V.Campaign = C.Campaign AND V.Medium = C.Medium AND V.Referrer = C.Referrer AND V.Source = C.Source
LEFT JOIN Staging.Volusion.MarketingRegions AS MR ON V.Country = MR.CountryName
WHERE
V.Date BETWEEN @StartDate AND @EndDate
GROUP BY
V.Date,
CASE WHEN V.Country IN ('United Kingdom', 'Guernsey', 'Ireland', 'Jersey') THEN 'UK'
WHEN V.Country IN ('United States', 'Canada', 'Puerto Rico', 'U.S. Virgin Islands') THEN 'US'
ELSE 'IN' END,
COALESCE(MR.Region, 'Unknown'), C.Category1, C.Category2, C.Category3
) AS V ON D.Date = V.Date AND Div.Division = V.Division AND Region.Region = V.Region AND C.Category1 = V.Category1 AND C.Category2 = V.Category2 AND C.Category3 = V.Category3
-- Demos
LEFT JOIN
(
SELECT
OD.SQLDate,
G.Division,
COALESCE(MR.Region, 'Unknown') AS Region,
COALESCE(C.Category1, 'Unknown') AS Category1,
COALESCE(C.Category2, 'Unknown') AS Category2,
COALESCE(C.Category3, 'Unknown') AS Category3,
SUM(D.Demos) AS Demos
FROM
Demos AS D
INNER JOIN Orders AS O ON D."Order" = O."Order"
INNER JOIN Dates AS OD ON O.OrderDate = OD.DateSerial
INNER JOIN MarketingSources AS MS ON D.Source = MS.Source
LEFT JOIN RegionsByCode AS MR ON MS.CountryCode = MR.CountryCode
LEFT JOIN
(
SELECT
G.TransactionID,
MIN (
CASE WHEN G.Country IN ('United Kingdom', 'Guernsey', 'Ireland', 'Jersey') THEN 'UK'
WHEN G.Country IN ('United States', 'Canada', 'Puerto Rico', 'U.S. Virgin Islands') THEN 'US'
ELSE 'IN' END
) AS Division
FROM
RawData.GoogleAnalytics.Geography AS G
WHERE
TransactionDate BETWEEN @StartDate AND @EndDate
AND NOT EXISTS (SELECT * FROM RawData.GoogleAnalytics.Geography AS G2 WHERE G.TransactionID = G2.TransactionID AND G2.EffectiveDate > G.EffectiveDate)
GROUP BY
G.TransactionID
) AS G ON O.VolusionOrderID = G.TransactionID
LEFT JOIN RawData.GoogleAnalytics.Referrers AS R ON O.VolusionOrderID = R.TransactionID AND NOT EXISTS (SELECT * FROM RawData.GoogleAnalytics.Referrers AS R2 WHERE R.TransactionID = R2.TransactionID AND R2.EffectiveDate > R.EffectiveDate)
LEFT JOIN Routine.MarketingDashboardCampaignMap AS C ON MS.LandingPage = C.LandingPage AND MS.Campaign = C.Campaign AND MS.Medium = C.Medium AND COALESCE(R.ReferralPath, '(not set)') = C.Referrer AND MS.SourceName = C.Source
WHERE
O.IsDeleted = 'No'
AND OD.SQLDate BETWEEN @StartDate AND @EndDate
GROUP BY
OD.SQLDate,
G.Division,
COALESCE(MR.Region, 'Unknown'),
COALESCE(C.Category1, 'Unknown'),
COALESCE(C.Category2, 'Unknown'),
COALESCE(C.Category3, 'Unknown')
) AS Dem ON D.Date = Dem.SQLDate AND Div.Division = Dem.Division AND Region.Region = Dem.Region AND C.Category1 = Dem.Category1 AND C.Category2 = Dem.Category2 AND C.Category3 = Dem.Category3
-- Stores
LEFT JOIN
(
SELECT
OD.SQLDate,
CASE WHEN O.VolusionCountryCode = 'GB' THEN 'UK'
WHEN A.CountryShortName IN ('U.S.', 'Canada', 'Puerto Rico', 'U.S. Virgin Islands') THEN 'US'
ELSE 'IN' END AS Division,
COALESCE(MR.Region, 'Unknown') AS Region,
COALESCE(CpM.Category1, 'Unknown') AS Category1,
COALESCE(CpM.Category2, 'Unknown') AS Category2,
COALESCE(CpM.Category3, 'Unknown') AS Category3,
SUM(S.Stores) AS GrossStores,
SUM(CASE WHEN O.DatePaid <> -1 THEN 1 ELSE 0 END) AS PaidStores,
SUM(CASE WHEN O.DatePaid <> -1 AND CD.WeekEnding <> OD.WeekEnding THEN 1 ELSE 0 END) AS NetStores,
SUM(CASE WHEN O.DatePaid <> -1 THEN SH.ActiveStores ELSE 0 END) AS StoresActiveNow
FROM
Stores AS S
INNER JOIN Orders AS O ON S."Order" = O."Order"
INNER JOIN Dates AS OD ON O.OrderDate = OD.DateSerial
INNER JOIN Dates AS CD ON O.CancellationDate = CD.DateSerial
INNER JOIN Customers AS C ON O.CustomerNow = C.Customer
INNER JOIN MarketingSources AS MS ON C.Source = MS.Source
INNER JOIN StoreHistory AS SH ON S.MostRecentHistory = SH.History
INNER JOIN Addresses AS A ON C.Address = A.Address
LEFT JOIN RegionsByCode AS MR ON MS.CountryCode = MR.CountryCode
LEFT JOIN Routine.MarketingDashboardCampaignMap AS CpM ON CpM.LandingPage = 'N/A' AND MS.Campaign = CpM.Campaign AND MS.Medium = CpM.Medium AND CpM.Referrer = 'N/A' AND MS.SourceName = CpM.Source
WHERE
O.IsDeleted = 'No'
AND OD.SQLDate BETWEEN @StartDate AND @EndDate
GROUP BY
OD.SQLDate,
CASE WHEN O.VolusionCountryCode = 'GB' THEN 'UK'
WHEN A.CountryShortName IN ('U.S.', 'Canada', 'Puerto Rico', 'U.S. Virgin Islands') THEN 'US'
ELSE 'IN' END,
COALESCE(MR.Region, 'Unknown'),
COALESCE(CpM.Category1, 'Unknown'),
COALESCE(CpM.Category2, 'Unknown'),
COALESCE(CpM.Category3, 'Unknown')
) AS S ON D.Date = S.SQLDate AND Div.Division = S.Division AND Region.Region = S.Region AND C.Category1 = S.Category1 AND C.Category2 = S.Category2 AND C.Category3 = S.Category3
-- Google Analytics spend
LEFT JOIN
(
SELECT
AC.Date, C.Category1, C.Category2, C.Category3, SUM(AC.AdCost) / SUM(AC.Visits) AS AvgAdCost
FROM
RawData.GoogleAnalytics.AdCosts AS AC
INNER JOIN
(
SELECT Campaign, Medium, Source, MIN(Category1) AS Category1, MIN(Category2) AS Category2, MIN(Category3) AS Category3
FROM Routine.MarketingDashboardCampaignMap
WHERE Category1 <> 'Affiliate'
GROUP BY Campaign, Medium, Source
) AS C ON AC.Campaign = C.Campaign AND AC.Medium = C.Medium AND AC.Source = C.Source
WHERE
AC.Date BETWEEN @StartDate AND @EndDate
GROUP BY
AC.Date, C.Category1, C.Category2, C.Category3
HAVING
SUM(AC.AdCost) > 0.00 AND SUM(AC.Visits) > 0
) AS GAAC ON D.Date = GAAC.Date AND C.Category1 = GAAC.Category1 AND C.Category2 = GAAC.Category2 AND C.Category3 = GAAC.Category3
-- adCenter spend
LEFT JOIN
(
SELECT Date, SUM(Spend) / SUM(Clicks) AS AvgClickCost
FROM RawData.AdCenter.Spend
WHERE Date BETWEEN @StartDate AND @EndDate
GROUP BY Date
HAVING SUM(Spend) > 0.00 AND SUM(Clicks) > 0
) AS ACS ON D.Date = ACS.Date AND C.Category1 = 'PPC' AND C.Category2 = 'adCenter' AND C.Category3 = 'N/A'
WHERE
V.Visits > 0 OR Dem.Demos > 0 OR S.GrossStores > 0
GO
SELECT * FROM Routine.MarketingDashboardECommerceBase('2011-05-21', '2011-05-23')
Best Answer
I isolated the problem to one line in the query. Keeping in mind that the query is 160 lines long, and I'm including the relevant tables either way, if I disable this line from the SELECT clause:
...the run time drops from 63 minutes to five seconds (inlining a CTE has made it slightly faster than the original seven-second query). Including either
ACS.AvgClickCostorGAAC.AvgAdCostcauses the run time to explode. What makes it especially odd is that these fields come from two subqueries which have, respectively, ten rows and three! They each run in zero seconds when run independently, and with the row counts being so short I would expect the join time to be trivial even using nested loops.Any guesses as to why this seemingly-harmless calculation would throw off a TVF completely, while it runs very quickly as a stand-alone query?