I am using data virtualization
. That means using the method query.IncludeTotalCount(true
On the server side OpenAccess ORM generates two queries. "SELECT COUNT (1) FROM ..."
and " __OAPAGED WITH AS (SELECT *, ROW_NUMBER () OVER (ORDER BY @ @ SPID) AS OA_ROWNUM FROM (SELECT TOP (@ __TAKE)..."
I tested these queries and are very fast (about 1 second for each
). But ORM returns results in about 30 seconds
. Time depends on the count of records returned by a query SELECT COUNT
. (30 seconds is for 961,169
records.) The problem is not the speed of queries
, but some strange delay
between SELECT COUNT
and the second SELECT
In SQL Profiler I see a "SELECT COUNT (1)..."
, then for about 30 seconds nothing happens
. And then follows " __OAPAGED WITH AS (SELECT *..."
If I set IncludeTotalCount(false
), then query SELECT COUNT
is not generated and ORM returns results quickly.
Sorry for my English.