SQL优化/调整 [英] SQL optimization/tweaking

问题描述

我有这个SQL Server查询

I have this SQL Server query

SELECT count(distinct [IP]) as GlobalUniqueIPcount, 
    --RangeUniqueIPcount
    (SELECT count(distinct [IP]) FROM [tblSequence] WHERE SiteID = @siteID AND ([Timestamp] > DATEADD(dd, -@days, (LEFT(GETDATE(),12))))) as RangeUniqueIPcount,
    --RangeUrlUniqueIPcount
    (SELECT count(distinct [IP]) FROM [tblSequence] WHERE SiteID = @siteID AND ([Timestamp] > DATEADD(dd, -@days, (LEFT(GETDATE(),12)))) AND Url = @Url) as RangeUrlUniqueIPcount,
    --RangeUniquePageviews
    (SELECT count (distinct url + SessionGuid) FROM [tblSequence] WHERE SiteID = @siteID AND ([Timestamp] > DATEADD(dd, -@days, (LEFT(GETDATE(),12))))) as RangeUniquePageViews,
    --RangeUrlUniquePageviews
    (SELECT count (distinct url + SessionGuid) FROM [tblSequence] WHERE SiteID = @siteID AND ([Timestamp] > DATEADD(dd, -@days, (LEFT(GETDATE(),12)))) AND Url = @Url) as RangeUrlUniquePageViews,  
    --GlobalUniquePageViews
    (SELECT count (distinct url + SessionGuid) FROM [tblSequence] WHERE SiteID = @siteID) as GlobalUniquePageViews
FROM [tblSequence] WHERE SiteID = @siteID

我有超过1,000,000行，它的表现就像废话一样. 该怎么办-请帮忙.

I have more than 1,000,000 rows and it performs like crap. What to do - please help.

非常感谢

推荐答案

难怪它运行缓慢:您有5个相关的子查询，其中2个是不必要的，而3个可以重写.试试这个.

No wonder it runs slow: you have 5 correlated subqueries, 2 of which are unnecessary and 3 can be re-written. Try this.

此外，您需要在其中之一上建立索引，无法确切说出哪个

Also, you need an index on one of these, can't say exactly which

1. (SiteID, Timestamp, Url)，其中(IP, SessionGuid)包含INCLUDE
   • (SiteID, Timestamp)，在(IP, SessionGuid, Url)上包含INCLUDE
   >上的
   • (SiteID) with INCLUDE

1. (SiteID, Timestamp, Url) with INCLUDE on (IP, SessionGuid)
   • (SiteID, Timestamp) with INCLUDE on (IP, SessionGuid, Url)
   • (SiteID) with INCLUDE on (IP, Url, SessionGuid, Timestamp)

这取决于是否将使用第一拖把RangeMatch和URLmatch.我的猜测是将需要2或3.对于索引大小很重要.

It depends on whether the 1st tow would be used RangeMatch and URLmatch. My guess is number 2 or 3 will be needed. It matters for index size.

不使用*时，Count将忽略NULL.

Count will ignore NULLs when * is not used.

SELECT
    count(distinct [IP]) as GlobalUniqueIPcount, 

    --RangeUniqueIPcount
    count (distinct CASE
             WHEN RangeMatch = 1 
             THEN IP ELSE NULL
           END ) AS RangeUniqueIPcount,

    --RangeURLUniqueIPcount
    count (distinct CASE
             WHEN RangeMatch = 1  AND UrlMatch = 1
             THEN IP ELSE NULL
           END ) AS RangeURLUniqueIPcount,

    --RangeUniquePageviews
    count (distinct CASE
             WHEN RangeMatch = 1 
             THEN url + SessionGuid ELSE NULL
           END ) RangeUniquePageViews,

    --RangeUrlUniquePageviews
    count (distinct CASE
             WHEN RangeMatch = 1 AND UrlMatch = 1
             THEN url + SessionGuid ELSE NULL
           END ) RangeUrlUniquePageViews,

    --GlobalUniquePageViews
    count (distinct url + SessionGuid) as GlobalUniquePageViews
FROM
  (SELECT
    *, 
    CASE WHEN  Url = @Url THEN 1 ELSE 0 END AS UrlMatch,
    CASE WHEN [Timestamp] > DATEADD(dd, -@days, (LEFT(GETDATE(),12))) THEN 1 ELSE 0 END AS RangeMatch
  FROM
     [tblSequence]
  WHERE SiteID = @siteID
  ) foo

这篇关于SQL优化/调整的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！