Low performance when using IncludeTotalCount(true)

[Petr Šatka]

Hi Telerik.
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.

Regards
Peter

[Thomas]

Hi Petr Šatka,

do you mean there is a 30 second delay on the client side before the next server side query is issued?
Can you try to increase the connection pool size on your application? If there is no free connection we might wait for executing the second query.

Greetings,
Thomas
the Telerik team

[Petr Šatka]

Hello Thomas.
It seems that the problem was with the database server.

I appreciate your help.

Regards
Peter