科技行者

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

知识库

知识库 安全导航

至顶网网络频道提高"SQL Server"性能的几种实用技术

提高"SQL Server"性能的几种实用技术

  • 扫一扫
    分享文章到微信

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

有时,为了让应用程序运行得更快,所做的全部工作就是在这里或那里做一些很小调整。但关键在于确定如何进行调整!

作者:赛迪网 10633 来源:天新网 2008年3月22日

关键字: SQL Server SQL Mssql 数据库

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

在本页阅读全文(共4页)

拾遗补零

这里介绍其他一些可帮助提高 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领域最新产品与技术信息,那么订阅至顶网技术邮件将是您的最佳途径之一。

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