首页 新闻 搜索 专区 学院

以下两段同意SQL为什么执行时间相差非常大?

0
悬赏园豆:15 [已解决问题] 解决于 2014-03-26 08:43

下面两段SQL为什么执行计划会不一样呢?语句块2中的执行计划多出了一个“哈希匹配”的操作,占用了几乎所有的时间。 想知道为什么会有这样的结果,其中加上了三个条件都是永真条件呀,SQL为什么这么傻?

1.下面这段SQL执行很快,从调试器看到不到一秒:

DECLARE @BookCategoryID INT
DECLARE @BookID INT
DECLARE @AuthorID INT
DECLARE @PageSize INT
DECLARE @PageIndex INT

SET @BookCategoryID = 0
SET @BookID = 0
SET @AuthorID = 0

SET @PageSize = 10
SET @PageIndex = 100

SELECT RowNum,
    BookID,BookName,
    CategoryID,CategoryName,
    PageID,Title,ParentPageID,
    BookPageDetailID,Content,Memo ,
    AuthorID,AuthorName
FROM (
    SELECT RowNum = ROW_NUMBER() OVER(ORDER BY C.BookID,C.OrderID),
        A.BookID,A.BookName,
        B.CategoryID,B.CategoryName,
        C.PageID,C.Title,C.ParentPageID,
        D.BookPageDetailID,D.Content,D.Memo,
        G.AuthorID,G.AuthorName
    FROM CM_Book A WITH(NOLOCK)
        INNER JOIN CM_BookCategory B WITH(NOLOCK)
            ON A.CategoryID = B.CategoryID
                AND (@BookCategoryID=0 OR B.CategoryID=@BookCategoryID)
                AND (@BookID=0 OR A.BookID=@BookID)
        INNER JOIN CM_BookPage C WITH(NOLOCK)
            ON A.BookID = C.BookID
        LEFT JOIN CM_BookPageDetail D WITH(NOLOCK)
            ON C.PageID = D.BookPageId
        LEFT JOIN
            (
                SELECT E.BookID,F.AuthorID,F.AuthorName
                FROM CM_BookAuthorRelation E WITH(NOLOCK)
                    INNER JOIN CM_BookAuthor F WITH(NOLOCK)
                        ON E.AuthorID = F.AuthorID 
            ) G
            ON A.BookID = G.BookID
                AND (@AuthorID=0 OR G.AuthorID=@AuthorID)
    WHERE CONTAINS(D.Content,N'')
) A
WHERE RowNum BETWEEN @PageSize*@PageIndex+1 AND @PageSize*@PageIndex+@PageSize

2.而下面这段代码功能相同却执行了4秒:

DECLARE @BookCategoryID INT
DECLARE @BookID INT
DECLARE @AuthorID INT
DECLARE @PageSize INT
DECLARE @PageIndex INT

SET @BookCategoryID = 0
SET @BookID = 0
SET @AuthorID = 0

SET @PageSize = 10
SET @PageIndex = 100

SELECT RowNum,
    BookID,BookName,
    CategoryID,CategoryName,
    PageID,Title,ParentPageID,
    BookPageDetailID,Content,Memo ,
    AuthorID,AuthorName
FROM (
    SELECT RowNum = ROW_NUMBER() OVER(ORDER BY C.BookID,C.OrderID),
        A.BookID,A.BookName,
        B.CategoryID,B.CategoryName,
        C.PageID,C.Title,C.ParentPageID,
        D.BookPageDetailID,D.Content,D.Memo,
        G.AuthorID,G.AuthorName
    FROM CM_Book A WITH(NOLOCK)
        INNER JOIN CM_BookCategory B WITH(NOLOCK)
            ON A.CategoryID = B.CategoryID
        INNER JOIN CM_BookPage C WITH(NOLOCK)
            ON A.BookID = C.BookID
        LEFT JOIN CM_BookPageDetail D WITH(NOLOCK)
            ON C.PageID = D.BookPageId
        LEFT JOIN
            (
                SELECT E.BookID,E.AuthorID,F.AuthorName
                FROM CM_BookAuthorRelation E WITH(NOLOCK)
                    INNER JOIN CM_BookAuthor F WITH(NOLOCK)
                        ON E.AuthorID = F.AuthorID 
            ) G
            ON A.BookID = G.BookID
    WHERE (@BookCategoryID=0 OR B.CategoryID=@BookCategoryID)
        AND (@BookID=0 OR A.BookID=@BookID)
        AND (@AuthorID=0 OR G.AuthorID=@AuthorID)
        AND CONTAINS(D.Content,N'')
) A
WHERE RowNum BETWEEN @PageSize*@PageIndex+1 AND @PageSize*@PageIndex+@PageSize
问题补充:

已经验证执行结果一模一样,包括数据总数(1万多条)和数据顺序。

空明流光的主页 空明流光 | 初学一级 | 园豆:48
提问于:2014-03-23 11:54
< >
分享
最佳答案
0

是因为本来应该放在JOIN中条件一起放在了WHERE中,造成了JOIN的数据量太大

收获园豆:15
dudu | 高人七级 |园豆:37106 | 2014-03-23 12:58

如果真是这个原因的话,那我想一定是我的SQL习惯有问题,以前这种情况我都是放在WHERE中的。

空明流光 | 园豆:48 (初学一级) | 2014-03-23 14:10
其他回答(1)
0

学习了dudu的分析。

但有个问题请教:在程序前端对查询中出现的参数过滤条件一般会合并拼串起来,放在where条件语句后(也就是第二种sql语句样式)。而不是人为的将相关的参数过滤条件放入到相关的表连接中(第一种sql语句样式),如……FROM CM_Book A WITH(NOLOCK)
        INNER JOIN CM_BookCategory B WITH(NOLOCK)
            ON A.CategoryID = B.CategoryID
                AND (@BookCategoryID=0 OR B.CategoryID=@BookCategoryID)
                AND (@BookID=0 OR A.BookID=@BookID)。

面对这种情况,该如何处理?

XagaNet | 园豆:202 (菜鸟二级) | 2014-03-24 23:07

其实我倒是不太认为dudu的回复是一定正确的,正在怀疑。

支持(0) 反对(0) 空明流光 | 园豆:48 (初学一级) | 2014-03-25 13:05
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册