CODEWITHSUNDEEP
c#sqlkendo-uikendo-gridkendo-asp.net-mvc
Steve
Steve

Reputation: 1798

Prevent Kendo Grid from running two queries

Currently, when I attach a DataSource to a Kendo Grid, it runs two queries.

The First: is a Count(*) to create the correct number of pages and informing the user how many records exist.

The Second: is a TOP query based on how many rows per page will be displayed.

My problem is that even with .Pageable(p => p.Enabled(false).Info(false)) it is STILL running the Count(*) query.

The query I am running is very expensive and I would like to completely eliminate running of the first Count(*) query.

Upvotes: 1

Views: 662

Answers (2)

The_Black_Smurf
The_Black_Smurf

Reputation: 5259

If you want to separate data into pages, you have to find a way to get the count from the server. You just can't get around it.

As CodingWithSpike pointed out, displaying all the rows in a single page would probably take way more time that it would to get the count(*) so at this point you have few solutions.

A) You fix a maximum number of rows to be fetch in a single page. Just make sure that the user is aware of this limitation.

B) If the number of record in your table is stable, you may cache the count on the server.

C) You could also cache the table data on the server side. It would speed up the count and the data fetch. However, you need to consider it's weight on server's memory and the fact that the data taken from the cache might not be up to date (you could consider refreshing data every X minutes).

If you want to go with option B or C, you'll probably have to implement your own Read logic (of the dataSource.transport object) in order to fetch cached data on the server.

EDIT

Knowing that you'll never get more than 100 rows I just came out with an hybrid solution. Similar to A) except for the count that will be calculated on the client side:

D) Override the Read logic of the dataSource.transport in order to retrieve only the top 100 rows from the server. The Read function has an option parameter object that can be used to return the data in a format that will be handled by the grid (ex: OData object).

Usually, the count returned by the OData object would be an inline count calculated on the server to let you know how many records match your filter. In your case, it will always be the number of rows returned in your OData object so you'll be able to set it on the client side without any call to the server, before you call the success function.

Here's the Kendo documentation about the transport.read (see the set read as a function section)

Upvotes: 2

CodingWithSpike
CodingWithSpike

Reputation: 43698

Try turning off server paging on your dataSource (not just the grid). In javascript it would be the DataSource.serverPaging option. Not sure exactly what it is with MVC helpers.

However, I suspect that if getting a count(*) from your table takes too long, then you just have a LOT of rows, so turning off paging is going to make things worse because it will fetch all the rows and send them back to the client.

Upvotes: 1

Related Questions