Switching to Server Side binding gets "Object reference not set to an instance of an object."

[Andrew]

So, I had the Grid working with Ajax, and that was really awesome as the paging and sorting was very quick. However, we had a problem that larger data sets (30k and more) would have the Grid time out. I believe switching over to a server side binding and grabbing 20 at a time would allow users to get the data. (Please correct me if I'm wrong).

Here's what I had before:

@(Html.Kendo().Grid<vNPISearch>()
            .Name("npi-grid")
            .DataSource(dataSource => dataSource
            .Ajax()
            .Read(read => read.Action("NPI_Read", "Pecos"))
            .ServerOperation(false)
            .PageSize(20))
            .Columns(columns =>
            {
                columns.Template(x => { }).ClientTemplate("#=GetPecosStatus(PecosNPI) #").Width(50);
                columns.Bound(x => x.ProviderFirstName).Title("First Name");
                columns.Bound(x => x.ProviderLastName).Title("Last Name");
                columns.Bound(x => x.ProviderBusinessLocationAddressCity).Title("City");
                columns.Bound(x => x.ProviderBusinessLocationAddressState).Title("State");
                columns.Bound(x => x.NPI).Title("NPI");
            })
            .Scrollable()
            .Sortable()
            .Pageable(pageable => pageable
                .Refresh(true)
                .PageSizes(true)
                .ButtonCount(5))
      )

And the NPI_Read looked like:

public ActionResult NPI_Read([DataSourceRequest] DataSourceRequest request)
{
    if (!String.IsNullOrEmpty(SearchTerm))
    {
        return Json(oandpService.GetPecosSearchResults(SearchTerm).ToDataSourceResult(request), JsonRequestBehavior.AllowGet);
    }
    else
    {
        return null;
    }
}

However, the performance was ABSOLUTELY TERRIBLE! For it to grab 55 records it took almost 10 seconds, whereas if I just populated a simple table it would be almost instantly.

So, I wanted to try server side binding.

The view now looks like:

@(Html.Kendo().Grid<vNPISearch>()
            .Name("npi-grid")
            .Columns(columns =>
            {
                columns.Template(x => { }).ClientTemplate("#=GetPecosStatus(PecosNPI) #").Width(50);
                columns.Bound(x => x.ProviderFirstName).Title("First Name");
                columns.Bound(x => x.ProviderLastName).Title("Last Name");
                columns.Bound(x => x.ProviderBusinessLocationAddressCity).Title("City");
                columns.Bound(x => x.ProviderBusinessLocationAddressState).Title("State");
                columns.Bound(x => x.NPI).Title("NPI");
            })
            .Scrollable(src => src.Height("auto"))
            .Sortable()
            .Pageable()
            .DataSource(dataSource => dataSource
                .Server()
                .PageSize(20)
                .Total((int)ViewData["total"])
                .Read(read => read.Action("NPI_Read", "Pecos")))
)

And the NPI_Read looks like:

public ActionResult NPI_Read([DataSourceRequest] DataSourceRequest request)
{
    if (!String.IsNullOrEmpty(SearchTerm))
    {
        if (request.PageSize == 0)
        {
            request.PageSize = 20;
        }
        IQueryable<vNPISearch> searchResults = oandpService.GetPecosSearchResults(SearchTerm);

        if (request.Sorts.Any())
        {
            foreach (SortDescriptor sortDescriptor in request.Sorts)
            {
                if (sortDescriptor.SortDirection == ListSortDirection.Ascending)
                {
                    switch (sortDescriptor.Member)
                    {
                        case "ProviderFirstName":
                            searchResults = searchResults.OrderBy(x => x.ProviderFirstName);
                            break;
                        case "ProviderLastName":
                            searchResults = searchResults.OrderBy(x => x.ProviderLastName);
                            break;
                        case "ProviderBusinessLocationAddressCity":
                            searchResults = searchResults.OrderBy(x => x.ProviderBusinessLocationAddressCity);
                            break;
                        case "ProviderBusinessLocationAddressState":
                            searchResults = searchResults.OrderBy(x => x.ProviderBusinessLocationAddressState);
                            break;
                        case "NPI":
                            searchResults = searchResults.OrderBy(x => x.NPI);
                            break;
                    }
                }
                else
                {
                    switch (sortDescriptor.Member)
                    {
                        case "ProviderFirstName":
                            searchResults = searchResults.OrderByDescending(x => x.ProviderFirstName);
                            break;
                        case "ProviderLastName":
                            searchResults = searchResults.OrderByDescending(x => x.ProviderLastName);
                            break;
                        case "ProviderBusinessLocationAddressCity":
                            searchResults = searchResults.OrderByDescending(x => x.ProviderBusinessLocationAddressCity);
                            break;
                        case "ProviderBusinessLocationAddressState":
                            searchResults = searchResults.OrderByDescending(x => x.ProviderBusinessLocationAddressState);
                            break;
                        case "NPI":
                            searchResults = searchResults.OrderByDescending(x => x.NPI);
                            break;
                    }
                }
            }
        }
        else
        {
            //EF cannot page unsorted data.
            searchResults = searchResults.OrderBy(x => x.ProviderLastName);
        }

        //Apply paging.
        if (request.Page > 0)
        {
            searchResults = searchResults.Skip((request.Page - 1) * request.PageSize);
        }
        searchResults = searchResults.Take(request.PageSize);

        ViewData["total"] = searchResults.Count();

        var result = new DataSourceResult()
        {
            Data = searchResults,
            Total = Convert.ToInt32(ViewData["total"])
        };

        return Json(result, JsonRequestBehavior.AllowGet);
    }
    else
    {
        return Json(Enumerable.Empty<vNPISearch>().AsQueryable());
    }
}

However, when I run the page it immediately returns: Object reference not set to an instance of an object. With Line 21 being highlighted. That line is .DataSource(dataSource => dataSource

So, two questions:

1. Is there a way to improve performance with AJAX and large datasets?
2. If not, what am I not doing to get server side binding not working?

[Andrew]

I got it to load by changing the view to:

@(Html.Kendo().Grid<vNPISearch>()
            .Name("npi-grid")
            .DataSource(dataSource => dataSource
                .Server()
                .Read(read => read.Action("NPI_Read", "Pecos")))
            .Columns(columns =>
            {
                columns.Template(x => { }).ClientTemplate("#=GetPecosStatus(PecosNPI) #").Width(50);
                columns.Bound(x => x.ProviderFirstName).Title("First Name");
                columns.Bound(x => x.ProviderLastName).Title("Last Name");
                columns.Bound(x => x.ProviderBusinessLocationAddressCity).Title("City");
                columns.Bound(x => x.ProviderBusinessLocationAddressState).Title("State");
                columns.Bound(x => x.NPI).Title("NPI");
            })
            .Scrollable(src => src.Height("auto"))
            .Sortable()
            .Pageable()
)

But when I run the project in debug mode, after clicking "Search" it hits ActionResult Index(PecosViewModel pecosViewModel) and never even touches the NPI_Read ActionResult. So it now displays, but it doesn't return any results.

[Andrew]

Regarding performance, I've also tried:

@(Html.Kendo().Grid<vNPISearch>()
            .Name("npi-grid")
            .DataSource(dataSource => dataSource
            .Ajax()
            .Read(read => read.Action("NPI_Read", "Pecos"))
            .ServerOperation(true)
            .PageSize(20))
            .Columns(columns =>
            {
                columns.Template(x => { }).ClientTemplate("#=GetPecosStatus(PecosNPI) #").Width(50);
                columns.Bound(x => x.ProviderFirstName).Title("First Name");
                columns.Bound(x => x.ProviderLastName).Title("Last Name");
                columns.Bound(x => x.ProviderBusinessLocationAddressCity).Title("City");
                columns.Bound(x => x.ProviderBusinessLocationAddressState).Title("State");
                columns.Bound(x => x.NPI).Title("NPI");
            })
            .Scrollable(scr => scr.Height("auto"))
            .Sortable()
            .Pageable(pageable => pageable
                .Refresh(true)
                .PageSizes(true)
                .ButtonCount(5))
      )

The result set has ONLY 55 records. Initial load and paging takes ~15 seconds. It takes a second if I use the same query in SSMS. I'm returning an IQueryable. It's absolutely ridiculous.

[Andrew]

In the Service that actually queries the database, I have a DateTime begin and a DateTime end. Then I have a TimeSpace duration = end-start. That query takes 0.11 seconds. So why does the Grid take SO LONG to show the data. There has to be something I'm missing, I can't imagine anyone using this Grid in production with it being this slow.

[Andrew]

I posted a reply in https://www.telerik.com/account/support-tickets/view-ticket?threadid=1062216 with a copy of my project. I put the database's mdf into App_Data. If you try searching for "Backman" it returns 55 results and takes 8-10 seconds to load and page.

[Viktor Tachev]

Hi Andrew,

Note that when the data is requested initially a connection to the database needs to be initiated and opened. This is timely operation and is likely causing the initial delay. As you have observed, consequent operations do not have the same delay as the connection has been opened.

As for SSMS - similar delay is likely observed while connecting to a database (when you open Management studio and press Connect). Once the connection is initiated the queries are executed fast.

Regards,
Viktor Tachev
Telerik by Progress