金沙棋牌app手机下载

当前位置:金沙棋牌 > 金沙棋牌app手机下载 > 性能的方法,万能分页存储过程的详解_MsSql_脚本

性能的方法,万能分页存储过程的详解_MsSql_脚本

来源:http://www.logblo.com 作者:金沙棋牌 时间:2019-11-23 02:25

在我的使用SQL Server2005的新函数构造分页存储过程中,我提到了使用ROW_NUMBER()函数来代替top实现分页存储过程。 但是时间长了,又发现了新问题,就是主子表的分页查询。例如:订单表和订单明细表,要求是查询订单,第二页,每页10条 复制代码 代码如下: --使用row_unmber()实现分页 --本来我们想要的结果是10条订单,结果却不是10条订单,而是10条明细 --其实是针对的子表进行分页了,订单并不是要显示的个数,出来的个数是明细的个数 --就是因为主表和子表联合查询的结果,主表记录和子表记录是1:N的关系,一个主表记录有多个明细 select * from OVER (ORDER BY oi.createdate DESC) AS rownumber,oi.orderseqno ,od.OrderDetailID FROM OrderInfo oi LEFT JOIN OrderDetail od ON oi.OrderSeqNO=od.OrderSeqNO WHERE oi.OrderSeqNO LIKE '%2%' ) AS o WHERE rownumber BETWEEN 10 AND 20 结果如下图

有人提出游标不好,会锁定行,幸亏我锁定的是临时表,不是数据表,不影响数据表的写操作。 下面是14楼的回复,让我茅塞顿开,于是有了今天的改进版,取消了游标的使用,临时表还是存在的,谢谢。 其实你只要分成两次查询即可: 1, 还是用Row_Number查主表分页 2, Row_Number查主表分页 inner join 明细表..用不到游标的. 复制代码 代码如下: --不用游标的分页 --先将分页的主表放在临时表中,然后用临时表和子表联合查询,来获取子表信息 --既保证了分页的正确性,也包括了子表信息 CREATE TABLE #order ( number BIGINT, orderseqno VARCHAR insert into #order SELECT * FROM OVER (ORDER BY oi.CreateDate DESC) AS rownumber,oi.OrderSeqNO FROM OrderInfo oi WHERE oi.OrderSeqNO LIKE '%2%' ) AS o WHERE o.rownumber BETWEEN 10 AND 20 SELECT * FROM #order INNER JOIN OrderDetail od ON od.OrderSeqNO=#order.orderseqno DROP TABLE #order 复制代码 代码如下: --SELECT TOP 10 oi.OrderSeqNO, oi.GoodsName ,ci.CompanyName,od.* --FROM OrderInfo oi INNER JOIN CompanyInfo ci ON oi.CompanyID=ci.CompanyID --LEFT JOIN OrderDetail od ON oi.OrderSeqNO=od.OrderSeqNO --使用row_unmber()实现分页 --本来我们想要的结果是10条订单,结果却不是10条订单,而是10条明细 --其实是针对的子表进行分页了,订单并不是要显示的个数,出来的个数是明细的个数 --就是因为主表和子表联合查询的结果,主表记录和子表记录是1:N的关系,一个主表记录有多个明细 --建立聚集索引 -- CLUSTERED INDEX INDEX_OrderInfo ON OrderInfo --显示查询执行计划 --SET STATISTICS IO ON select * from OVER (ORDER BY oi.createdate DESC) AS rownumber,oi.orderseqno ,od.OrderDetailID FROM OrderInfo oi LEFT JOIN OrderDetail od ON oi.OrderSeqNO=od.OrderSeqNO WHERE oi.OrderSeqNO LIKE '%2%' ) AS o WHERE rownumber BETWEEN 10 AND 20 --不用游标的分页 --先将分页的主表放在临时表中,然后用临时表和子表联合查询,来获取子表信息 --既保证了分页的正确性,也包括了子表信息 CREATE TABLE #order ( number BIGINT, orderseqno VARCHAR insert into #order SELECT * FROM OVER (ORDER BY oi.CreateDate DESC) AS rownumber,oi.OrderSeqNO FROM OrderInfo oi WHERE oi.OrderSeqNO LIKE '%2%' ) AS o WHERE o.rownumber BETWEEN 10 AND 20 SELECT * FROM #order INNER JOIN OrderDetail od ON od.OrderSeqNO=#order.orderseqno DROP TABLE #order --解决上面的问题,有以下几种办法 --1、先根据条件查询主表记录,然后在C#代码中循环,再次到数据库查询每条主表记录的明细信息,然后赋值给属性 --2、在数据库的存储过程中使用游标,也是先查询主表记录,然后使用游标循环的过程中,查询子表信息,然后在C#中 --集中处理 --很显然,后一种减少了数据库的往来开销,一次获取了想要的数据,个人认为要比第一种好,欢迎大家一起讨论更好的办法 --需要注意的就是ROW_NUMBER()返回的类型是bigint,而不是int --下面是游标的存储过程 --建立主表临时表 CREATE TABLE #temp ( rownumber bigint, orderseqno VARCHAR, goodsname VARCHAR, companyname VARCHAR --建立子表临时表 CREATE TABLE #detail ( orderseqno VARCHAR, detailid UNIQUEIDENTIFIER, unitprice DECIMAL --插入主表数据到主表临时表 insert into #temp SELECT * --oo.rownumber, oo.OrderSeqNO, oo.GoodsName, oo.CompanyName FROM OVER (ORDER BY oi.createdate DESC) AS rownumber, oi.OrderSeqNO, oi.GoodsName ,ci.CompanyName FROM OrderInfo oi INNER JOIN CompanyInfo ci ON oi.CompanyID=ci.CompanyID WHERE oi.CreateDate

–建立主表临时表CREATE TABLE #temp(rownumber bigint,orderseqno VARCHAR,goodsname VARCHAR,companyname VARCHAR–建立子表临 时表CREATE TABLE #detail(orderseqno VARCHAR,detailid UNIQUEIDENTIFIER,unitprice DECIMAL–插入主表数据到主表临时表insert into #tempSELECT oo.rownumber, oo.OrderSeqNO, oo.GoodsName, oo.CompanyName FROM OVER (ORDER BY oi.createdate DESC) AS rownumber,oi.OrderSeqNO, oi.GoodsName ,ci.CompanyNameFROM OrderInfo oi INNER JOIN CompanyInfo ci ON oi.CompanyID=ci.CompanyIDWHERE oi.CreateDate

Five Ways to Rev up Your SQL Performance

很明显不是10条订单,而是10条明细。

–定义游标DECLARE @temp_cursor CURSOR–给游标赋值SET @temp_cursor=CURSOR FOR SELECT #temp.orderseqno,#temp.goodsname FROM #temp

本文节选自MSDN的文章《五种提高 SQL 性能的方法》,提出如何提高基于SQL Server应用程序的运行效率,非常值得推荐。对一些Traffic很高的应用系统而言,如何提高和改进SQL指令,是非常重要的,也是一个很好的突破点。
*文章主要包括如下一些内容(如感兴趣,请直接访问下面的URL阅读完整的中英文文档):

下面是对查询的一点改进,使用了游标复制代码 代码如下: --解决上面的问题,有以下几种办法 --1、先根据条件查询主表记录,然后在C#代码中循环,再次到数据库查询每条主表记录的明细信息,然后赋值给属性 --2、在数据库的存储过程中使用游标,也是先查询主表记录,然后使用游标循环的过程中,查询子表信息,然后在C#中 --集中处理 --很显然,后一种减少了数据库的往来开销,一次获取了想要的数据,个人认为要比第一种好,欢迎大家一起讨论更好的办法 --需要注意的就是ROW_NUMBER()返回的类型是bigint,而不是int --下面是游标的存储过程 --建立主表临时表 CREATE TABLE #temp ( rownumber bigint, orderseqno VARCHAR, goodsname VARCHAR, companyname VARCHAR --建立子表临时表 CREATE TABLE #detail ( orderseqno VARCHAR, detailid UNIQUEIDENTIFIER, unitprice DECIMAL --插入主表数据到主表临时表 insert into #temp SELECT oo.rownumber, oo.OrderSeqNO, oo.GoodsName, oo.CompanyName FROM OVER (ORDER BY oi.createdate DESC) AS rownumber, oi.OrderSeqNO, oi.GoodsName ,ci.CompanyName FROM OrderInfo oi INNER JOIN CompanyInfo ci ON oi.CompanyID=ci.CompanyID WHERE oi.CreateDate

–定义游标循环过程中所需保存的临时数据DECLARE @orderseqno VARCHAR,@goodsname varchar

1, 从 INSERT 返回 IDENTITY  SELECT @@IDENTITY

上面的T-SQL只在SQL Server 2005上调试成功。推荐一篇MS SQL Server的查询计划的相关内容,可以利用它优化SQL,写的不错。引用:SqlServer 执行计划及Sql查询优化初探

–打开游标OPEN @temp_cursor

2, 内嵌视图与临时表  临时表 - 在 tempdb 中的临时表会导致查询进行大量 I/O 操作和磁盘访问,临时表会消耗大量资源。
内嵌视图 -使用内嵌视图取代临时表。内嵌视图只是一个可以联接到 FROM 子句中的查询。如果只需要将数据联接到其他查询,则可以试试使用内嵌视图,以节省资源。

FETCH NEXT FROM @temp_cursor INTO @orderseqno,@goodsname–循环游标,查询子表数据,然后插入子表临时表WHILE @@FETCH_STATUS=0BEGININSERT INTO #detailSELECT od.OrderSeqNO,od.OrderDetailID, od.UnitPrice,od.QtyFROM OrderDetail odWHERE od.OrderSeqNO=@orderseqno

3, 避免 LEFT JOIN 和 NULL 
LEFT JOIN 消耗的资源非常之多,因为它们包含与 NULL(不存在)数据匹配的数据。在某些情况下,这是不可避免的,但是代价可能非常高。LEFT JOIN 比 INNER JOIN 消耗资源更多,所以如果您可以重新编写查询以使得该查询不使用任何 LEFT JOIN,则会得到非常可观的回报。

FETCH NEXT FROM @temp_cursor INTO @orderseqno,@goodsnameEND

加快使用 LEFT JOIN 的查询速度的一项技术涉及创建一个 TABLE 数据类型,插入第一个表(LEFT JOIN 左侧的表)中的所有行,然后使用第二个表中的值更新 TABLE 数据类型。此技术是一个两步的过程,但与标准的 LEFT JOIN 相比,可以节省大量时间。一个很好的规则是尝试各种不同的技术并记录每种技术所需的时间,直到获得用于您的应用程序的执行性能最佳的查询。
DECLARE @tblMonths TABLE (sMonth VARCHAR(7))

–关闭游标CLOSE @temp_cursorDEALLOCATE @temp_cursor

4, 灵活使用笛卡尔乘积 
对于此技巧,我将进行非常详细的介绍,并提倡在某些情况下使用笛卡尔乘积。出于某些原因,笛卡尔乘积 (CROSS JOIN) 遭到了很多谴责,开发人员通常会被警告根本就不要使用它们。在许多情况下,它们消耗的资源太多,从而无法高效使用。但是像 SQL 中的任何工具一样,如果正确使用,它们也会很有价值。

SELECT * FROM #tempSELECT * FROM #detail–删除临时表DROP TABLE #tempDROP TABLE #detail上面的T-SQL只在SQL Server 2005上调试成功。

其中一段示例代码,值得效仿:

笛卡尔乘积则可以返回所有月份的所有客户。笛卡尔乘积基本上是将第一个表与第二个表相乘,生成一个行集合,其中包含第一个表中的行数与第二个表中的行数相乘的结果。因此,笛卡尔乘积会向表 @tblFinal 返回 12(所有月份)*81(所有客户)=972 行。最后的步骤是使用此日期范围内每个客户的月销售额总计更新 @tblFinal 表,以及选择最终的行集。

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

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

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

英文URL:

本文由金沙棋牌发布于金沙棋牌app手机下载,转载请注明出处:性能的方法,万能分页存储过程的详解_MsSql_脚本

关键词:

上一篇:没有了

下一篇:没有了