CODEWITHSUNDEEP
c#asp.net-web-apidatatables
Gerben Rampaart
Gerben Rampaart

Reputation: 9945

Get SELECT COUNT asynchronously continuously

I'm using jquery datatables to display a grid which uses webapi to retrieve it's data. The webapi uses linq to query a mssql database and it neatly uses filtering, sorting and skip/take to assemble it's query on a well-indexed table containing about a million records (and growing). A common scenario.

And it performs really well. The browser has to wait about 50 ms for the response (while paginating for example) to return.

However, after I took a look with a profiling tool I noticed about 25 ms to be used just selecting the total rowcount of the table. Which I want to know because I want the datatable to display something like: "displaying row 1 to 10 of 45.000 filtered out of 1.000.000" needing the total count.

I don't actually need to know the precise total count (it's just informative) every trip from the server so I perhaps could keep the value server side and refresh it every second in a different task without it interfering with the data retrieval of datatables. I would just return the 'close enough' value of the total row count.

Is there a solid mechanism for that? I've tried to put the total rowcount in a static used by multiple users during multiple callbacks and every time it was requested a async task was fired to refresh it.

That feels icky however, sharing the static and having a different thread update it doesn't feel all that stable to me. I've looked at SqlDependency to push the recordcount every time it changes from my data to my domain model but that doesn't seem to support SELECT COUNT(Id) FROM TABLE scenarios.

Any thoughts?

Upvotes: 1

Views: 743

Answers (2)

TechneWare
TechneWare

Reputation: 263

Have you considered taking the count when a query is performed and then echoing the value out to your clients via SignalR?

Basically, when the LINQ call returns get a .Count() and hand off the value to a background thread to let SignalR notify the clients of the update, at the same time you return the data to the requesting client.

SignalR will activate a javascript function in all of the client pages, where you can then take the passed in value and display it somewhere on the page.

http://www.asp.net/signalr

Upvotes: 1

Nick DeVore
Nick DeVore

Reputation: 10176

You could use one of the system tables if possible. You could ping this every minute and stick it in the cache. This article has two that it claims are sufficient options:

--The way the SQL management studio counts rows (look at table properties, storage
--, row count).  Very fast, but still an approximate number of rows.
SELECT CAST(p.rows AS float)
FROM sys.tables AS tbl
INNER JOIN sys.indexes AS idx ON idx.object_id = tbl.object_id and idx.index_id < 2
INNER JOIN sys.partitions AS p ON p.object_id=CAST(tbl.object_id AS int)
AND p.index_id=idx.index_id
WHERE ((tbl.name=N'Transactions'
AND SCHEMA_NAME(tbl.schema_id)='dbo'))

or

--Quick (although not as fast as method 2) operation and equally important, reliable.
SELECT SUM (row_count)
FROM sys.dm_db_partition_stats
WHERE object_id=OBJECT_ID('Transactions')   
AND (index_id=0 or index_id=1);

Upvotes: 1

Related Questions