c# - 您可以禁用 ServiceStack AutoQuery 的计数(总计)吗？

Question

我针对具有大约 130 万行的两个表的简单连接设置了 AutoQuery。使用内置的迷你分析器来测量 SQL 时间，返回前 100 行(无过滤)的查询需要 3 毫秒，计数需要额外的 341 毫秒。

是否可以在不获取计数的情况下使用 AutoQuery？我实际上不需要知道完整计数。

编辑

所以我在想，找出是否还有更多的剩余行与完整计数可能会更快。我使用 SSMS 对我们的 MSSQL 数据库进行了计时测试。

--Generated by ServiceStack
set statistics time on
SELECT COUNT(*) "COUNT(*)" 
FROM "table1" INNER JOIN "table2" ON
("table1"."PrimaryKey" = "table2"."ForeignKey")
set statistics time off

--Skipping 100
set statistics time on
SELECT CASE WHEN EXISTS(
  SELECT "table1"."PrimaryKey"
  FROM "table1" INNER JOIN "table2" ON
  ("table1"."PrimaryKey" = "table2"."ForeignKey")
  ORDER BY "table1"."PrimaryKey" OFFSET 100 ROWS FETCH NEXT 1 ROWS ONLY
) 
THEN CAST(1 AS BIT) 
ELSE CAST(0 AS BIT) END
set statistics time off

--Skipping 100000
set statistics time on
SELECT CASE WHEN EXISTS(
  SELECT "table1"."PrimaryKey"
  FROM "table1" INNER JOIN "table2" ON
  ("table1"."PrimaryKey" = "table2"."ForeignKey")
  ORDER BY "table1"."PrimaryKey" OFFSET 100000 ROWS FETCH NEXT 1 ROWS ONLY
) 
THEN CAST(1 AS BIT) 
ELSE CAST(0 AS BIT) END
set statistics time off

--Skipping 1000000
set statistics time on
SELECT CASE WHEN EXISTS(
  SELECT "table1"."PrimaryKey"
  FROM "table1" INNER JOIN "table2" ON
  ("table1"."PrimaryKey" = "table2"."ForeignKey")
  ORDER BY "table1"."PrimaryKey" OFFSET 1000000 ROWS FETCH NEXT 1 ROWS ONLY
) 
THEN CAST(1 AS BIT) 
ELSE CAST(0 AS BIT) END
set statistics time off

输出:

(1 row(s) affected)

 SQL Server Execution Times:
   CPU time = 203 ms,  elapsed time = 200 ms.

(1 row(s) affected)

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

(1 row(s) affected)

 SQL Server Execution Times:
   CPU time = 16 ms,  elapsed time = 19 ms.

(1 row(s) affected)

 SQL Server Execution Times:
   CPU time = 203 ms,  elapsed time = 193 ms.

Answer 1

不只是要知道完整计数还是不要，还需要ServiceClient API's like GetLazy()的总数。因此它能够在多个分页查询后面透明地流式传输 AutoQuery 结果。

这以前不是一个明确的选项，但您可以通过添加一个预先填充它的 ResponseFilter 来避免 AutoQuery 查询总计，例如:

Plugins.Add(new AutoQueryFeature {
    MaxLimit = 100,
    ResponseFilters = {
        ctx => { ctx.Response.Meta["COUNT(*)"] = "0"; }
    }
});

我还在 this commit 中添加了对此选项的支持。所以在未来的版本中，您可以删除总计:

Plugins.Add(new AutoQueryFeature {
    MaxLimit = 100,
    IncludeTotal = false,
});

此更改从现在的 v4.0.61 开始可用 available on MyGet .