Can you disable count (Total) for ServiceStack AutoQuery?

Question

I have AutoQuery setup against a simple join of two tables with approximately 1.3 million rows. Using the built in mini profiler to measure the SQL timings, the query to return the first 100 rows (no filtering) takes 3ms and the count takes an additional 341ms.

Is it possible to use AutoQuery without fetching the count? I don't actually need to know the full count.

EDIT

So I was thinking that finding out if there are more rows remaining vs a full count may be faster. I tested this out against our MSSQL database using SSMS for the timings.

--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

Output:

(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

It's not just whether you need to know the full count or not, but the Total is also required for ServiceClient API's like GetLazy() so it's able to transparently stream AutoQuery results behind multiple paged queries.

This wasn't an explicit option before but you can avoid AutoQuery querying for the Total by adding a ResponseFilter that pre-populates it with one, e.g:

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

I've also just added support for this option in this commit so in future releases you can remove the Total with:

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

This change is available from v4.0.61 that's now available on MyGet.