提高SQL Server性能的五种方法
2008-09-02 09:59:02 来源:WEB开发网另一个提高效率的技巧是使用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
- ››sql server自动生成批量执行SQL脚本的批处理
- ››sql server 2008亿万数据性能优化
- ››SQL Server 2008清空数据库日志方法
- ››sqlserver安装和简单的使用
- ››SQL Sever 2008 R2 数据库管理
- ››SQL SERVER无法安装成功,sqlstp.log文件提示[未发...
- ››Sql Server中通过父记录查找出所有关联的子记录
- ››SqlServer触发器、存储过程和函数
- ››SQL Server 中的事务(含义,属性,管理)
- ››Sqlite数据库插入和读取图片数据
- ››Sql server 2005拒绝了对对象 'xx表' (数...
- ››Sql server 2005拒绝了对对象 'xx表' (数...
更多精彩
赞助商链接