科技行者

行者学院 转型私董会 科技行者专题报道 网红大战科技行者

知识库

知识库 安全导航

至顶网软件频道改善基于SQL Server应用程序可伸缩性(5)

改善基于SQL Server应用程序可伸缩性(5)

  • 扫一扫
    分享文章到微信

  • 扫一扫
    关注官方公众号
    至顶头条

有时,为了让应用程序运行得更快,所做的全部工作就是在这里或那里做一些很小调整。

作者:gaoyuan007 来源:赛迪网技术社区 2007年9月3日

关键字: 伸缩性 数据库 SQL Server SQL Server 各版本

  • 评论
  • 分享微博
  • 分享邮件
 

拾遗补零

这里介绍其他一些可帮助提高 SQL 查询效率的常用技术。假设您将按区域对所有销售人员进行分组并将他们的销售额进行小计,但是您只想要那些数据库中标记为处于活动状态的销售人员。您可以按区域对销售人员分组,并使用 HAVING 子句消除那些未处于活动状态的销售人员,也可以在 WHERE 子句中执行此操作。在 WHERE 子句中执行此操作会减少需要分组的行数,所以比在 HAVING 子句中执行此操作效率更高。HAVING 子句中基于行的条件的筛选会强制查询对那些在 WHERE 子句中会被去除的数据进行分组。

另一个提高效率的技巧是使用 DISTINCT 关键字查找数据行的单独报表,来代替使用 GROUP BY 子句。在这种情况下,使用 DISTINCT 关键字的 SQL 效率更高。请在需要计算聚合函数(SUM、COUNT、MAX 等)的情况下再使用 GROUP BY。另外,如果您的查询总是自己返回一个唯一的行,则不要使用 DISTINCT 关键字。在这种情况下,DISTINCT 关键字只会增加系统开销。

您已经看到了,有大量技术都可用于优化查询和实现特定的业务规则,技巧就是进行一些尝试,然后比较它们的性能。最重要的是要测试、测试、再测试。

Figure 2 Returning All Customers and Their Sales 

set nocount on

DECLARE @dtStartDate DATETIME, 

@dtEndDate DATETIME,

@dtDate DATETIME

SET @dtEndDate = '5/5/1997'

SET @dtEndDate = DATEADD(DD, -1, CAST(CAST((MONTH(@dtEndDate) + 1) 

AS VARCHAR(2)) + '/01/' + CAST(YEAR(@dtEndDate) AS VARCHAR(4)) + ' 

23:59:59' AS DATETIME))

SET @dtStartDate = DATEADD(MM, -1 * 12, @dtEndDate)

SELECT CAST(YEAR(o.OrderDate) AS VARCHAR(4)) + '-' +

CASE 

WHEN MONTH(o.OrderDate) < 10 

THEN '0' + CAST(MONTH(o.OrderDate) AS VARCHAR(2))

ELSE CAST(MONTH(o.OrderDate) AS VARCHAR(2))

END AS sMonth,

c.CustomerID,

c.CompanyName,

c.ContactName,

SUM(od.Quantity * od.UnitPrice) AS mSales

FROM Customers c

INNER JOIN Orders o ON c.CustomerID = o.CustomerID

INNER JOIN [Order Details] od ON o.OrderID = od.OrderID

WHERE o.OrderDate BETWEEN @dtStartDate AND @dtEndDate

GROUP BY

CAST(YEAR(o.OrderDate) AS VARCHAR(4)) + '-' +

CASE 

WHEN MONTH(o.OrderDate) < 10 

THEN '0' + CAST(MONTH(o.OrderDate) AS VARCHAR(2))

ELSE CAST(MONTH(o.OrderDate) AS VARCHAR(2))

END,

c.CustomerID,

c.CompanyName,

c.ContactName

ORDER BY

c.CompanyName,

sMonth

Figure 3 Cartesian Product at Work

DECLARE @tblMonths TABLE (sMonth VARCHAR(7))

DECLARE @tblCustomers TABLE ( CustomerID CHAR(10),

CompanyName VARCHAR(50),

ContactName VARCHAR(50))

DECLARE @tblFinal TABLE ( sMonth VARCHAR(7), 

CustomerID CHAR(10),

CompanyName VARCHAR(50),

ContactName VARCHAR(50),

mSales MONEY)

DECLARE @dtStartDate DATETIME, 

@dtEndDate DATETIME,

@dtDate DATETIME,

@i INTEGER

SET @dtEndDate = '5/5/1997'

SET @dtEndDate = DATEADD(DD, -1, CAST(CAST((MONTH(@dtEndDate) + 1) AS 

VARCHAR(2)) + '/01/' + CAST(YEAR(@dtEndDate) AS VARCHAR(4)) + ' 

23:59:59' AS DATETIME))

SET @dtStartDate = DATEADD(MM, -1 * 12, @dtEndDate)

— Get all months into the first table

SET @i = 0

WHILE (@i < 12)

BEGIN

SET @dtDate = DATEADD(mm, -1 * @i, @dtEndDate)

INSERT INTO @tblMonths SELECT CAST(YEAR(@dtDate) AS VARCHAR(4)) + '-' +

CASE 

WHEN MONTH(@dtDate) < 10 

THEN '0' + CAST(MONTH(@dtDate) AS VARCHAR(2))

ELSE CAST(MONTH(@dtDate) AS VARCHAR(2))

END AS sMonth

SET @i = @i + 1

END

— Get all clients who had sales during that period into the "y" table

INSERT INTO @tblCustomers

SELECT DISTINCT

c.CustomerID,

c.CompanyName,

c.ContactName

FROM Customers c

INNER JOIN Orders o ON c.CustomerID = o.CustomerID

WHERE o.OrderDate BETWEEN @dtStartDate AND @dtEndDate

INSERT INTO @tblFinal

SELECT m.sMonth,

c.CustomerID,

c.CompanyName,

c.ContactName,

0

FROM @tblMonths m CROSS JOIN @tblCustomers c

 

UPDATE @tblFinal SET 

mSales = mydata.mSales

FROM @tblFinal f INNER JOIN 

(

SELECT c.CustomerID,

CAST(YEAR(o.OrderDate) AS VARCHAR(4)) + '-' +

CASE WHEN MONTH(o.OrderDate) < 10 

THEN '0' + CAST(MONTH(o.OrderDate) AS VARCHAR(2))

ELSE CAST(MONTH(o.OrderDate) AS VARCHAR(2))

END AS sMonth,

SUM(od.Quantity * od.UnitPrice) AS mSales

FROM Customers c

INNER JOIN Orders o ON c.CustomerID = o.CustomerID

INNER JOIN [Order Details] od ON o.OrderID = od.OrderID

WHERE o.OrderDate BETWEEN @dtStartDate AND @dtEndDate

GROUP BY

c.CustomerID,

CAST(YEAR(o.OrderDate) AS VARCHAR(4)) + '-' +

CASE WHEN MONTH(o.OrderDate) < 10 

THEN '0' + CAST(MONTH(o.OrderDate) AS VARCHAR(2))

ELSE CAST(MONTH(o.OrderDate) AS VARCHAR(2))

END

) mydata on f.CustomerID = mydata.CustomerID AND f.sMonth = 

mydata.sMonth 

SELECT f.sMonth,

f.CustomerID,

f.CompanyName,

f.ContactName,

f.mSales

FROM @tblFinal f

ORDER BY

f.CompanyName,

f.sMonth
    • 评论
    • 分享微博
    • 分享邮件
    邮件订阅

    如果您非常迫切的想了解IT领域最新产品与技术信息,那么订阅至顶网技术邮件将是您的最佳途径之一。

    重磅专题
    往期文章
    最新文章