我有一个相当复杂的查询,它可以在几秒钟内自行运行,但是当它包含在一个表值函数中时,它的速度要慢得多;我实际上并没有让它完成,但它运行了长达十分钟而没有结束.唯一的变化是用日期参数替换两个日期变量(用日期文字初始化):
在七秒钟内运行
DECLARE @StartDate DATE = '2011-05-21' DECLARE @EndDate DATE = '2011-05-23' DECLARE @Data TABLE (...) INSERT INTO @Data(...) SELECT... SELECT * FROM @Data
运行至少十分钟
CREATE FUNCTION X (@StartDate DATE,@EndDate DATE) RETURNS TABLE AS RETURN SELECT ... SELECT * FROM X ('2011-05-21','2011-05-23')
我之前使用RETURNS @Data TABLE(…)子句将该函数编写为多语句TVF,但是为内联结构交换该函数并未发生明显变化. TVF的长时间是实际的SELECT * FROM X时间;实际上创建UDF只需要几秒钟.
我可以发布有问题的查询,但它有点长(约165行),并且基于第一种方法的成功,我怀疑还有其他事情正在发生.略过执行计划,它们似乎完全相同.
我已经尝试将查询分成更小的部分,没有任何变化.单独执行时,没有一个部分需要超过几秒钟,但TVF仍然会挂起.
我看到一个非常相似的问题,https://stackoverflow.com/questions/4190506/sql-server-2005-table-valued-function-weird-performance,但我不确定该解决方案是否适用.也许有人看到了这个问题并且知道一个更通用的解决方案?谢谢!
这是处理几分钟后的dm_exec_requests:
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
这是完整的查询:
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,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,'U.S. Virgin Islands') THEN 'US' ELSE 'IN' END,'Unknown'),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(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','Jersey') THEN 'UK' WHEN G.Country IN ('United States','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,'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.',COALESCE(CpM.Category1,COALESCE(CpM.Category2,COALESCE(CpM.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,'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,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,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.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')
解决方法
我希望这与参数嗅探有关.
有些人在这里谈论问题(你可以在SO上搜索参数嗅探.)
http://blogs.msdn.com/b/queryoptteam/archive/2006/03/31/565991.aspx