21

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')
7
  • Can you show us the text query plans please? And in the first query, what types are @StartDate + @EndDate
    – gbn
    Commented May 24, 2011 at 18:58
  • @gbn: Sorry, the plan is too long, at about 32K characters. Is there some subset that would be most useful? Also, would you prefer the plan for the stand-alone query or the TVF? Commented May 24, 2011 at 20:04
  • Running the execution plan on the TVF form of the query returns no useful information, so I assume you're looking for the query plan for the non-TVF version. Or is there some way to get to the execution plan actually used by a TVF? Commented May 24, 2011 at 23:18
  • No waiting tasks. I'm not familiar with dm_exec_requests, but I've appended the output as of the five-minute mark in the TVF's execution. Commented May 25, 2011 at 18:15
  • @Martin: Yes; the stand-alone query had CPU time of 7021 (2% of the partial TVF version) and 154K logical reads (0.5%). I recently left the TVF version to run, and it finished after 27 minutes. So it's definitely churning through far more data... but how can I get it to use a better plan? I'll study the good execution plan in detail and see if a few hints help. Commented May 25, 2011 at 20:10

5 Answers 5

4

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:

COALESCE(V.Visits, 0) * COALESCE(ACS.AvgClickCost, GAAC.AvgAdCost, 0.00)

...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.AvgClickCost or GAAC.AvgAdCost causes 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?

3
  • I've posted the query, but as you can see it draws on a dozen tables, including some views and one other TVF, so I fear it won't be helpful. The part I don't understand is how wrapping a query in a TVF can multiply the run time by 750. It only happens if I include GAAC.AvgAdCost (today; yesterday ACS.AvgClickCost was also a problem), so that subquery seems to be throwing off the execution plan. Commented May 27, 2011 at 14:48
  • 1
    I guess you have to look at the join clause for the subqueries. If you get a many to many relation between any of the tables, you'll get 10 times more records to handle.
    – Hakan Winther
    Commented Aug 9, 2011 at 9:10
  • At some point on our project (which has lots of nested views and inline TVFs), we found ourselves replacing COALESCE() with ISNULL() to help the query optimizer draft better plans. I think it had to do with ISNULL() having a more predictable output type than COALESCE(). Worth a try? I know this is vague, but in our limited experience, influencing the query optimizer toward better plans seems like a fuzzy art, so trying a bunch of vague crazy ideas out of desperation is the only way we've made progress.
    – Woody Zenfell III
    Commented Sep 29, 2011 at 14:00
2

I expect this has to do with parameter sniffing.

Some talk about the issues are here (and you can search SO for parameter sniffing.)

Link

7
  • You don't get parameter sniffing with inline TVFs: they are just macros that expand like views.
    – gbn
    Commented May 24, 2011 at 18:57
  • @gbn: It may be true that the TVF itself is expanded like a macro, but (as I understand it) the query or sproc that ultimately executes that expansion is subject to planning and potential parameterization. (We fought with this in SQL Server 2005 a while back. The fight was especially difficult until we found SQL Server Management Studio using different session settings (ARITHABORT maybe?) than Reporting Services and/or jTDS, so one of them would sometimes come up with a "bad" plan but others would (infuriatingly) do okay "on the same query".)
    – Woody Zenfell III
    Commented May 24, 2011 at 19:59
  • It smells like sniffing to me....
    – Hogan
    Commented May 24, 2011 at 21:37
  • Hmm, much reading to do. For what it's worth, there's no great difference in cardinality for the parameterized values: the query includes a Dates table, with a single row per date, and several other tables with many rows per date, but about the same number for any given date. I use the same params (05/21 to 05/23) in a test execution immediately after (re-)creating the UDF, so if anything it should be "primed" for those values. Commented May 24, 2011 at 22:37
  • One more note: assigning the values of the parameters to local variables as described by Jetson in stackoverflow.com/questions/211355/… did not have a material impact. Commented May 24, 2011 at 23:30
1

Unfortunately SQL's query optimisation engine can't see inside functions.

So I'd use the execution plan from the fast one to figure out what hints to apply in the TF. Rinse & repeat until the TF's execution plan approximates the faster one.

http://sqlblog.com/blogs/tibor_karaszi/archive/2008/08/29/execution-plan-re-use-sp-executesql-and-tsql-variables.aspx

2
  • 2
    The SQL Server Query Optimizer can see inside ITVF's (inline table-valued functions), but not any others.
    – RBarryYoung
    Commented Sep 13, 2011 at 3:56
  • Note: inline table functions with cross apply when correctly designed can lead to a huge boost in performance. For example, a nonsargable expression on a join like your coalesce, could be wrapped in an apply statement, evaluated as a set, and then joined against in the next query without it becoming RBAR. Experiment a little. Cross apply is hard to master, but so worth it! Commented Apr 14, 2015 at 15:01
1

For me, this was solved as I changed the function and then changing it back... So yes, looks like the optimizing engine is messing it up really bad. I guess it will start running slow again...

1
  • SQL Server 2008 R2 -- I tried changing my TVF like you said, and it fixed my issue. The raw sql code took 90ms to run. But in the TVF took 1100ms, until I rebuilt the function and now it takes 100ms. The issue only happened on 1 of 3 servers in 1 of over 100 databases. I had just recently restored that 1 database. I wonder if that had something to do with the poor performance.
    – James L.
    Commented May 25 at 5:53
0

What are the differences in these values please?

arithabort              1
ansi_null_dflt_on       1
ansi_defaults           0
ansi_warnings           1
ansi_padding            1
ansi_nulls              1

These (especially arithabort) have been shown to seriously affect query performance in this manner.

5
  • This is because it is a plan cache key rather than anything about arithabort itself though isn't it? Since SQL Server 2005 I thought this setting had no effect as long as ansi_warnings is on. (In 2000 indexed views wouldn't be used if set incorrectly) Commented May 25, 2011 at 20:23
  • @Martin: I have no direct experience of this but recalled reading stuff recently. And finding some SO answers on it. It may help OP, it may not... Edit: sqlblog.com/blogs/kalen_delaney/archive/2008/06/19/… sigh
    – gbn
    Commented May 25, 2011 at 20:27
  • I have read similar quite unambiguous claims on SO. I've never seen any thing that would allow me to reproduce it for myself or any logical explanation as to why the arithabort setting should have such a dramatic influence on performance though so I'm a bit sceptical about it at the moment. Commented May 25, 2011 at 20:31
  • ARITHABORT, ANSI_WARNINGS, ANSI_PADDING, and ANSI_NULL are 1, the rest are NULL. Commented May 25, 2011 at 22:14
  • FYI, I'm working entirely in SSMS, so different settings in VS or other clients are not at issue. Commented May 25, 2011 at 22:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.