With virtualized scrolling, Kendo Grids can be used to allow users to visualize and analyze data with extremely large datasets. A Kendo Grid with virtual scrolling minimizes the number of records by only retrieving records to populate the records within the browser window viewport which vastly improves rendering performance. In production, I use these virtualized grids, with a large number of columns, to render up to 75 million records! This article will demonstrate all of the steps necessary to deploy these powerful grids and teach you how to implement server-side paging, sorting, and filtering. 

Kendo Virtual Grid Example

This virtual Kendo grid has over 150k records showing all of the major countries, cities, and states. Server-side sorting and paging are supported, and each column in the grid provides filters to allow the user to quickly retrieve the records. Both the live demo and the code are available below.

Table of Contents

Benefits and Drawbacks of Virtualized Kendo Grids 

Kendo Grids with virtualized scrolling provide an enormous benefit allowing users to visualize data with millions of records in an easy-to-use and powerful HTML5 web-based grid. I use these types of Kendo grids often in enterprise finance departments to allow the administrators to analyze Payroll Subledger data. These grids have sorting and powerful search and filtering capabilities allowing users to isolate records from extremely large datasets. However, there are a few limitations to be aware of.

The main drawback to virtual grids is that users can't scroll over a million or so records, however, I don't know of a single user that will voluntarily scroll beyond several thousand records! The user will always seek to search the data instead of having to scroll through a ridiculously large set of records. To overcome this limitation, you can also provide server-side paging and implement column filtering as we have done here.

Differences Between Kendo Grid Pagination and Virtualization

To improve performance, Telerik suggests either using pagination or virtualization, but what is the actual difference here? Both techniques offer significant performance increases by limiting the amount of data required while rendering the Kendo grid. However, using virtual grids is the way to go if you are offering grid filtering or sorting (and with large datasets- you should). The reason is that while the pagination does limit the number of records, the default behavior sorts the data on the client side requiring all of the data to be analyzed before applying the search filters or sort order. On the other hand, since all of the data operations are offloaded to the server when using column virtualization, the client-side rendering is much more performant. Also, the database is generally superior in handling and messaging a large volume of data. As a general rule of thumb, if you are using more than 20k records, you should always consider using a virtual grid or having server-side pagination.

Kendo Virtual Grid Overview

The Kendo virtual grid sends a string of arguments as JSON to the server to determine which records should be displayed. The grid sends new arguments to the server whenever the user scrolls past a certain point or when sorting or filters are made. The server will take this JSON string and query the database to retrieve the records that need to be displayed. In this example, we will be using ColdFusion on the server. I have built many different functions to process the logic on the server. If you are using ColdFusion, this process is pretty much plug-and-play. Using this approach, implementing a virtual Kendo grid should only take 15 minutes or so. If you are using a different server-side language, such as PHP, I will explain the backend logic in a future article so that you develop your own logic to handle server-side logic for the Kendo virtual grid.

Client Side Logic

If you have been following along with our previous Kendo articles, the client-side code should be familiar to you and will explain the highlights of the code below. We are only going to highlight some important parts of the code, you may want to open up the code window and push it to the side when reading this to follow along.

CSS to Ensure that all Rows are Uniform in Size

Since the events are fired based on the user's current scroll position, each row in the grid should be uniform in height. This is accomplished using CSS by either eliminating word wrapping or making the grid wide enough so that no row wrapping exists. In this example, we are setting the minimum grid width to be at least 1190 pixels wide and explicitly setting the height of the row, we are also eliminating white space wrapping. 

<style>
	/*horizontal Grid scrollbar should appear if the browser window is shrunk too much*/
	#WorldDbGrid table
	{
		min-width: 1190px;
	}

	 .k-virtual-scrollable-wrap td {
		font-size: 14px;        
		white-space:nowrap;
		line-height: 13px;
	  }

	  #WorldDbGrid .k-virtual-scrollable-wrap tr td {
		height: 15px
	  }
</style>

Using a Kendo Template to Display the Country's Flag

Here we are using a Kendo template to display the country flag in the first column of the grid. The implementation is quite simple, however, you will need to have a flag image library matching the ISO2 name of the country. You can get such a library at https://github.com/hampusborgos/country-flags. See https://www.gregoryalexander.com/blog/2022/9/16/Cascading-Country-State-and-City-Kendo-UI-Dropdowns#mcetoc_1gf09bljtp for more information.

<!-- Kendo Template to display the flag next to the country name -->
<script type="text/x-kendo-template" id="country-flag">
	<div class="country-flag">
		<img src="/common/assets/flags/png250px/#: data.ISO2 #.png" width="20" height="14"/>
	</div>
</script>

The Kendo DataSource

The virtual grid Kendo data source is nearly identical to the data sources handling the non-virtual grids. The differences are that we are using:

The server arguments, serverPaging, sorting, and filtering, defer all of the processing to the server. As we just mentioned, these settings send a string of arguments in JSON to the server when an action is needed to be performed. Schema.data refers to the data handle in the JSON string that contains the data object. Schema.total determines how many records we need to display. We will delve into the details of these settings later in the article. 

Since we are using virtual scrolling (see the notes in the grid initialization below), server paging is optional. However, here we are using both virtual scrolling and serverPaging. You can use the two options in tandem and set the page size to determine how far the user can virtually scroll. In this example, the user can scroll a bit but will come to the end of grid when the user is at the 100th record (set in the pageSize argument). The pageSize setting must be set higher than the number of rows that can visually be seen in the grid without scrolling. 

Note that the parameterMap logic is needed when using virtual grids. The rest of the settings have been covered in prior articles and should be self-explanatory. 

// Datasource Declaration ---------------------------------------------------------------------------------
WorldDbDs = new kendo.data.DataSource({
	type: "json",            
	serverPaging: true, // when set to true, the grid will automatically post the following json string to the processing page {"take":100,"skip":0,"page":1,"pageSize":100}         
	serverSorting: true,
	allowUnsort: true,  
	filterable: true,           
	serverFiltering: true,                        
	pageSize: 100, // If server paging is properly set above, the grid should send the following arguments to the cfc: "{"take":100,"skip":0,"page":1,"pageSize":100}"

	allowCopy: true,
	reorderable: true,
	resizable: true,
	columnMenu: true,
	transport: {
		read:  {
			// Note: since this template is in a different directory, we can't specify the subledger template without the full path name.
			url: "<cfoutput>#cfcUrl#</cfoutput>?method=getWorldDbForKendoGrid", // /cssweb/applications/subLedger/subLedger.cfc?method=getVBarInProgressForGrid the cfc component which processes the query and returns a json string. 
			dataType: "json",  
			contentType: "application/json; charset=utf-8", // Note: when posting json via the request body to a coldfusion page, we must use this content type or we will get a 'IllegalArgumentException' on the ColdFusion processing page.                 
			type: "POST"
		},
		 parameterMap: function (options) {                   
			return JSON.stringify(options);
		}
	},//transport
	cache: false,
	schema: {
		total: "total", // Needed on virtual grids. The total and data are being returned by the cfc. Note the total and data vars are wrapped in quotes.
		data: "data", // 'Data' is being returned by the Kendo virtual grid helper function.
		model: {
			id: "CountryId", // Note: in editable grids- the id MUST be put in here, otherwise you will get a cryptic error 'Unable to get value of the property 'data': object is null or undefined'
			fields: {
				CountryId: { type: "number", editable: false, nullable: false },
				Country: { type: "string", editable: false, nullable: false },
				Capital: { type: "string", editable: false, nullable: false },
				ISO2: { type: "string", editable: false, nullable: false },
				ISO3: { type: "string", editable: false, nullable: false },
				StateId: { type: "number", editable: false, nullable: false },
				State: { type: "string", editable: false, nullable: false },
				CountryId: { type: "number", editable: false, nullable: false },
				CityId: { type: "number", editable: false, nullable: false },
				City: { type: "string", editable: false, nullable: false },
				CityLatitude: { type: "number", editable: false, nullable: false },
				CityLongitude: { type: "string", editable: false, nullable: false }
			}//fields:
		}//model:
	}//schema
});//feedsDs = new kendo.data.DataSource

Grid Initialization

Most of these settings have been covered in prior articles, but there are a few things that are required when using virtual grids:

  • Virtual grids must use scrollable virtual:true. Since serverPaging is set to true in the Kendo data source, the user will have to click on the paging button at the bottom of the grid after scrolling to 100 records.
  • serverPaging is optional when using virtual scrolling, however, having endless scrolling can be confusing, so we are also using serverPaging here.
  • Setting the grid height is necessary when using virtual grids, and all visible columns are filterable allowing the users to search the records in the grid.
  • The flag column uses the Kendo template that we created earlier in this example to display the flag.
var WorldDbGrid = $("#WorldDbGrid").kendoGrid({
	dataSource: WorldDbDs,
	// General grid elements.
	height: 775,
	sortable: true,
	filterable: true,
	pageable: true,
	groupable: true,
	// Virtualize the grid
	scrollable: {
		virtual: true
	},
	// Edit arguments
	editable: false,
	columns: [{
		// Hidden primary key columns
		field:"CountryId",
		title: "CountryId",
		filterable: false,
		hidden: true
	}, {
		field:"StateId",
		title: "StateId",
		filterable: false,
		hidden: true
	}, {
		field:"CityId",
		title: "CityId",
		filterable: false,
		hidden: true
	// Visible columns
	}, {
		field:"Flag",
		title: "Flag",
		template: kendo.template($("#country-flag").html()),
		filterable: true,
		width:"5%"//35
	}, {
		field:"Country",
		title: "Country",
		filterable: true,
		width:"15%"//15
	}, {
		field:"Capital",
		title: "Capital",
		filterable: true,
		width:"15%"//35
	}, {
		field:"ISO2",
		title: "ISO2",
		filterable: true,
		width:"10%"//55
	}, {
		field:"State",
		title: "State",
		filterable: true,
		width:"20%"//80
	}, {
		field:"City",
		title: "City",
		filterable: true,
		width:"15%"//85
	}, {
		field:"CityLatitude",
		title: "Latitude",
		filterable: true,
		format:"{0:n6}",
		width:"10%"//90
	}, {
		field:"CityLongitude",
		title: "Longitude",
		filterable: true,
		width:"10%"//
	}]// columns:
});// var WorldDbGrid = $("#WorldDbGrid").kendoGrid

Server-Side Logic

Unlike all of the other articles, the server-side logic required for virtual grids is quite extensive. I have developed various ColdFusion-based functions that are used that make implementing a virtual grid with ColdFusion a breeze, however, there is a lot of logic that takes place behind the scenes here. This approach also only works with MS SQL Server.

Create a Flat Table or a View as the Datasource

The approach that I have developed requires a SQL Server flat table or view. Here we are using a view that denormalizes our Country, State City SQL Server database. My virtual grid ColdFusion components only work with MS SQL server, and this MySql database was converted to MS SQL for this example.

Download ColdFusion Templates from GitHub

I have created four different files on GitHub to use to create Kendo virtualized grids.

  1. The first file, containing the getWorldDbForKendoGrid method that we are using here, can either be copied below or downloaded at https://github.com/GregoryAlexander77/KendoUtilities/blob/main/KendoVirtualGridServiceEndpoint. This file needs some minor modifications that we will cover below.
  2. You also need ou CfJson component if you don't already have it. This ColdFusion component is used for almost every Kendo widget that consumes JSON data using a ColdFusion service endpoint. This ColdFusion component is found at https://github.com/GregoryAlexander77/CfJson.
  3. The first function (getWorldDbForKendoGrid) relies upon the KendoUtils.cfc component containing the core logic to prepare the SQL statements based on the JSON arguments sent by the Kendo grid.
  4. Finally, the ColumnProperty.cfc component is used by the function to determine the column datatype. 

Create the ColdFusion Service Endpoint

The service endpoint, called from the Kendo Datasource, needs some minor modification to get it to work in your environment. This function relies upon the files that need to be downloaded above.

The ColdFusion-based server endpoint requires 4 arguments and you must specify the columns that you are selecting. 

  • The tableName should specify the name of the flat table or view.
  • Use the same table name as the tableAlias. This will create an alias name for the table. I put this in the logic as I wanted to use the same table for multiple grids and wanted to distinguish the queries separately.
  • The primaryKey argument is used to identify the selected row in the grid and is used if you use the grid for editing or having a master-detail interface. I will explain this concept later.
  • The defaultOrderByStatement allows you to specify how you want the records ordered. In this example, I am ordering the data by country. 
  • You need to specify all of the columns that you want in the sqlStatement. The column names must be in the proper case, and be sure to leave the 'SELECT * FROM ( SELECT' string at the top of the page. I may recode this with an extra requiredColumns argument to make it easier in the future, but I'll leave the code that I use in production alone for now.
  • You should not have to touch any of the code underneath the sqlStatement argument.
<!--- Function to populate the grid --->
<cffunction name="getWorldDbForKendoGrid" access="remote" returnformat="json" output="false">
    <!--- There are no arguments for this function. --->
	<cfsetting enablecfoutputonly="true" />
    
    <!--- Set params --->
    <cfparam name="take" default="100" type="string">
    <cfparam name="skip" default="0" type="string">
    <cfparam name="page" default="1" type="string">
    <cfparam name="pageSize" default="100" type="string">
    <cfparam name="whereClause" default="" type="string">
    <cfparam name="sortStatement" default="" type="string">
    <cfparam name="searchFilter" default="false" type="boolean">
    <cfparam name="logSql" default="true" type="boolean">
    
    <!--- The name of the view (or a table that is derived from a view. --->
    <cfset tableName = 'ViewCountryStateCity'>
    <cfset tableNameAlias = 'vCountryStateCity'>
    <cfset primaryKey = "CityId">
	<cfset defaultOrderByStatement = 'ORDER BY Country'>
    
    <!--- Get the number of records in the entire table, not just the top 100 for display purposes. We will overwrite this later if there are any new filters applied.  --->
    <cfquery name="getTotal" datasource="#dsn#">
    	SELECT count(#primaryKey#) as numRecords FROM [dbo].[#tableName#]
    </cfquery> 
    <cfset totalNumRecords = getTotal.numRecords>
    
    <!--- Make the query. Input the select statement *without* the from clause (or any other clauses) here. --->
    <cfset sqlStatement = '
		SELECT * FROM
			( 	SELECT
                CountryId
				,Country
				,Capital
				,Currency
				,CurrencyName
				,ISO2
				,ISO3
				,Flag
				,Latitude
				,Longitude
				,StateId
				,State
				,StateFlag
				,Type
				,CityId
				,City
				,CityLatitude
				,CityLongitude
				,CityFlag
	'>

	<!--- Note: you should not have to touch the following lines of this code. If you want a custom query name other than 'data', you will have to adjust tthe query name in two places.  --->
	
	<!---
	Get the HTTP request body content.
	The content in the request body should be formatted like so: {"take":100,"skip":9300,"page":94,"pageSize":100,"sort":[{"field":"ref2","dir":"desc"}]}
	
	NOTE: We have to use toString() as an intermediary method
	call since the JSON packet comes across as a byte array
	(binary data) which needs to be turned back into a string before
	ColdFusion can parse it as a JSON value.
	--->
	<cfset requestBody = toString( getHttpRequestData().content ) />
	
	<!--- Double-check to make sure it's a JSON value. --->
	<cfif isJSON( requestBody )>
		<!--- Deserialize the json in the request body.  --->
		<cfset incomingJson = deserializeJSON( requestBody )>
		
        <!--- Invoke the createSqlForVirtualGrid method in the kendoUtils.cfc component that will send back sql clauses.  --->
        <cfinvoke component="#KendoUtilsObj#" method="createSqlForVirtualGrid" returnvariable="sqlStruct">
            <cfinvokeargument name="jsonString" value="#requestBody#">
            <cfinvokeargument name="dsn" value="#dsn#">
            <cfinvokeargument name="tableName" value="#tableName#">
        </cfinvoke>
        
        <cfif structFind(sqlStruct, "take") neq ''>
			<cfset take = structFind(sqlStruct, "take")>
        </cfif>
        <cfif structFind(sqlStruct, "skip") neq ''>
            <cfset skip = structFind(sqlStruct, "skip")>
        </cfif>
        <cfif structFind(sqlStruct, "page") neq ''>
            <cfset page = structFind(sqlStruct, "page")>
        </cfif>
        <cfif structFind(sqlStruct, "pageSize") neq ''>
            <cfset pageSize = structFind(sqlStruct, "pageSize")>
        </cfif>
        <cfif structFind(sqlStruct, "whereClause") neq ''>
            <cfset whereClause = structFind(sqlStruct, "whereClause")>
        </cfif>
        <cfif structFind(sqlStruct, "sortStatement") neq ''>
            <cfset sortStatement = structFind(sqlStruct, "sortStatement")>
        </cfif>
        <cfif structFind(sqlStruct, "searchFilter") neq ''>
            <cfset searchFilter = structFind(sqlStruct, "searchFilter")>
        </cfif>
	</cfif><!--- <cfif isJSON( requestBody )> --->
    
	<!--- Build the over order by statement. Make sure that a closing bracket ')' is at the end of the string. --->
    <cfset overOrderStatement = ',ROW_NUMBER() OVER ('>
	<cfif sortStatement neq ''>
    	<cfset overOrderStatement = overOrderStatement & sortStatement & ")">
    <cfelse>
    	<!--- Default order by.  --->
        <cfset overOrderStatement = overOrderStatement & defaultOrderByStatement & ")">
    </cfif>
    <!--- Append it to the sqlStatement --->
    <cfset sqlStatement = sqlStatement & " " & overOrderStatement>
    <!--- Build the alias for the rownumber(). I am defaulting to 'as rowNumber' --->
    <cfset sqlStatement = sqlStatement & " AS RowNumber">
    <!--- Append the real and alias table name --->
    <cfset sqlStatement = sqlStatement & " FROM [dbo].[" & tableName & "]) " & tableNameAlias>
            
	<!--- Append the additional WHERE clause statement to it if necessary --->
    <cfif whereClause neq ''>
        <cfset sqlStatement = sqlStatement & " " & preserveSingleQuotes(whereClause)>
    </cfif>
    
    <!--- Log the sql when the logSql is set to true (on top of function) ---> 
    <!--- <cfif logSql>
		<cfset filePath = subledgerPath & 'logs'>
        <cffile action="append" addnewline="yes" file="#filePath#/virtualGridSql.txt" output="#Chr(13)##Chr(10)#'#myTrim(sqlStatement)#'#Chr(13)##Chr(10)#" fixnewline="yes">
    </cfif> --->
    
    <!--- Testing carriage. If this is not commented out, the grids will not populate.  --->
    <cfoutput>#preserveSingleQuotes(whereClause)#</cfoutput>
	
    <cfquery name="data" datasource="#dsn#">
    	#preserveSingleQuotes(sqlStatement)#
    </cfquery>
    
    <!--- Write the sql to the console log for debugging. Note: if you write this out- it will break the grid, so only do so in development.
	<cfoutput>
    <script>
		if ( window.console && window.console.log ) {
		  // console is available
		  console.log ('#preserveSingleQuotes(sqlStatement)#');
		}
	</script> 
    </cfoutput>
	 --->
                
    <!--- Using my jsonArray.cfc --->
    <cfinvoke component="#application.cfJsonComponentPath#" method="convertCfQuery2JsonStructForVirtualGrid" returnvariable="jsonString" >
        <cfinvokeargument name="queryObj" value="#data#">
        <cfinvokeargument name="includeTotal" value="true">
        <!--- When we use server side paging, we need to override the total and specify a new total which is the sum of the entire query. --->
        <cfinvokeargument name="overRideTotal" value="true">
        <!--- We set the totalNumRecords near the top of the function, however, if the filters were applied, the new total will be the number of records in the data query object. ---> 
        <cfif searchFilter>
        	<cfset totalRecords = data.recordcount>
        <cfelse>
        	<cfset totalRecords = totalNumRecords>
        </cfif>
        <cfinvokeargument name="newTotal" value="#totalRecords#">
        <!--- The includeDataHandle is used when the format is json (or jsonp), however, the data handle is not included when you want to make a javascript object embedded in the page. ---> 
        <cfinvokeargument name="includeDataHandle" value="true">
        <!--- If the data handle is not used, this can be left blank. If you are going to use a service on the cfc, typically, the value would be 'data'--->
        <cfinvokeargument name="dataHandleName" value="data">
		<!--- Keep the case of the columns the same as the database --->
        <cfinvokeargument name="convertColumnNamesToLowerCase" value="false">
    </cfinvoke>

	<cfreturn jsonString>
    
</cffunction>

In a future article, I will analyze the code on the server that prepares the data for this function. The goal of this article is to allow developers to fully implement a Kendo virtual grid without detailed elaboration on the backend.

Further Reading