代码之家 › 专栏 › 技术社区 › Tom H

获取分页SQL搜索存储过程的计数

paging stored-procedures search tsql sql

Tom H · 技术社区 · 16 年前

我使用SQLServer2005编写了一个分页搜索存储过程。它需要许多参数,并且搜索条件相当复杂。

由于前端架构,我需要能够返回返回的结果数没有实际返回结果。然后前端将再次调用存储过程以获取实际结果。

一方面,我可以编写两个存储过程——一个用于处理计数,另一个用于处理实际数据,但是我需要在至少两个不同的地方维护搜索逻辑。或者,我可以编写存储过程,这样它就需要一个位参数,并基于该参数返回数据或仅返回一个计数。也许用数据填充一个临时表,如果它是count,只需从中进行计数,否则就从中进行选择。这里的问题是,可以优化计数过程,这样会产生很多额外的开销(必须获得不需要的列等)。此外,在存储过程中使用这种逻辑可能会导致错误的查询计划,因为它在两次使用之间来回切换。

系统中的数据量并不太高(对于更大的表,只有几百万行)。但是可能有许多并发用户。

人们对这些方法有什么看法?以前有人用我没想到的方法解决过这个问题吗?

他们不能从单个调用中同时获取结果和计数。

谢谢!

6 回复 | 直到 14 年前

Mitchel Sellers 16 年前

我个人使用的是两种查询方法,是的,您必须在两个地方维护搜索逻辑,但我发现性能优化的好处是,代码的整体清洁最终会得到回报。

使用传递给单个过程的标志是一个潜在的解决方案,但我发现这很难维护,特别是对于复杂的搜索逻辑。

使用临时表等的路径,这只会增加比所需开销更多的开销。

因此,为什么我使用了两个查询方法。我在网上找到的所有东西都推荐这种方法。

ChrisLively 16 年前

这不是一个正常的问题,您通常希望在获取页面的同时获得总计数。

也就是说,使用两种不同的程序。原因是你有两个非常不同的行动,只是表面上彼此相似。

Kristen 15 年前

我相信您已经考虑过了:如果数据正在更改计数,任何后续的实际分页都可能不同(如果添加/删除了行)

您可以有一个用户定义的函数,该函数返回匹配行的pks,相对容易执行

SELECT COUNT(*) FROM dbo.MyQueryFunction(@Param1, @Param2)

去数数,然后

SELECT Col1, Col2, ...
FROM dbo.MyQueryFunction(@Param1, @Param2) AS FN
     JOIN dbo.MyTable AS T
         ON T.ID = FN.ID
     ... more JOINs ...

获取数据。

不知道它在后续分页中与行编号的关系有多好,但它将保留myqueryfunction中包含的实际“查询逻辑”-您仍然可以在存储过程和函数中检索任何列的所有联接。

PhilGriffin 16 年前

可能对您的特定问题没有帮助,但是SQL 2005引入了行编号函数,这对于分页检查很方便。

Row_number example

比临时桌容易得多。

cloggins 15 年前

我发现这个线程在研究其他的东西,我想我会提到有可能用一个查询返回结果集和记录计数。您只需要一个“out”参数来携带该值。下面是一个Oracle示例的复制/粘贴,但是对于SQL Server来说,这项技术非常类似(我没有访问SQL Server ATM的权限)。

对于SQL Server,最重要的是您可能需要使用row_number()和rownum。

procedure get_sample_results (
    startrow in number default 1,
    numberofrows in number default 10,
    whereclause in varchar2,
    matchingrows out number,
    rc  out sys_refcursor
)
is
    stmnt varchar2(5000);
    endrow number;
begin

    stmnt := stmnt || 'select * from table t where 1=1';
    if whereclause is not null then
        stmnt := stmnt || ' and ' || whereclause;
    end if;

    execute immediate 'select count(*) from (' || stmnt || ')' into matchingrows;

    stmnt := 'select * from (' || stmnt || ') where rownum between :1 and :2';        

    -- must subtract one to compenstate for the inclusive between clause
    endrow := startrow + numberofrows - 1;
    open rc for stmnt using startrow, endrow;

end get_sample_results;

Pure.Krome 14 年前

我知道这是一个旧问题(已经标记好了),但是您可以返回一个记录集(也就是结果),并有一个输出(或者多个输出)值,这意味着您只需要一次到数据库的往返行程。

这是我在想的事情(现在已经过了我的睡觉时间…)

CREATE PROCEDURE WhatEver
(
   @SomeParam1 NVARCHAR(200),
   ....
   @SomeParam_X INT,
   @NumberOfResults INTEGER OUTPUT
)
BEGIN
    SET NOCOUNT ON

    -- Do your search stuff.
    -- ....
    SELECT Whatever
    FROM WhatWhat
    ...

    -- Ok, the results/recordset has been sent prepared.
    -- Now the rowcount
    SET @NumberOfResults = @@ROWCOUNT
END

Hth.