WEB开发网
开发学院数据库MSSQL Server 提高SQL Server性能的五种方法 阅读

提高SQL Server性能的五种方法

 2008-09-02 09:59:02 来源:WEB开发网   
核心提示: 另一个提高效率的技巧是使用DISTINCT关键字查找数据行的单独报表,来代替使用GROUP BY子句,提高SQL Server性能的五种方法(7),在这种情况下,使用DISTINCT关键字的SQL效率更高,技巧就是进行一些尝试,然后比较它们的性能,请在需要计算聚合函数(SUM、count、

另一个提高效率的技巧是使用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

上一页  2 3 4 5 6 7 

Tags:提高 SQL Server

编辑录入:爽爽 [复制链接] [打 印]
赞助商链接