ServerSide SQL View Taking a Long Time to Load

AKASecret

Link to test case: http://live.datatables.net/sirixake/2/edit?js,output (I did my best...this test case does not recreate the issue because the table data is generated with server side processing. The table in my actual HTML is the one without the <tr> rows, commented out.)
Debugger code (debug.datatables.net): esawag
Error messages shown:
Microsoft.Data.SqlClient.SqlException: 'Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding.'
ALSO
DataTables warning: table id=tblIdeas - Ajax error. For more information about this error, please see http://datatables.net/tn/7

Frameworks used: Entity Framework Core, C#, SQL Server, ASP.NET Core MVC
Description of problem:
When I search, it takes a long time to load and eventually times out. It seems that there are too many ajax calls being sent to the server with every key press. The only column that seems to be causing this issue is the "CreatedBy" column, index 5, because when I remove the column it works fine.

Setup of the datatable includes:

serverSide: true,
processing: true,
ajax: {
    url: '/Ideas/Data',
    type: "POST"
}

Ideas controller > Data method:

public IActionResult Data()
{
    var parser = new Parser<VwAllIdeas>(Request.Form, _context.VwAllIdeas);

    return Json(parser.Parse());
}

vwAllIdeas SQL view:

SELECT        i.Id, i.Title, i.Status, i.Reward, i.Created, CASE rtrim([EmployeeCommonName]) WHEN '' THEN [FirstName] ELSE + rtrim([EmployeeCommonName]) END + ' ' + LastName AS CreatedBy, i.WhatIsYourIdea AS Idea
FROM            dbo.Ideas AS i JOIN
                         Utility.dbo.HRExport AS v ON v.EmployeeID = i.SubmitterId
UNION
SELECT        i.Id, i.Title, i.Status, i.Reward, i.Created, v.FullName AS CreatedBy, i.WhatIsYourIdea AS Idea
FROM            dbo.Ideas AS i JOIN
                         dbo.OtherEmployees AS v ON v.EmployeeID = i.SubmitterId

What I have tried:

Does not work:
1. As above, using the vwAllIdeas view with the unions and the joins
2. Using only the top half of the vwAllIdeas view without the union
3. Using only the bottom half of the vwAllIdeas view without the union
4. Using just the last name for the CreatedBy column, without the SQL CASE statement
5. Adding searchDelay: 350 to the Datatable https://datatables.net/reference/option/searchDelay
6. Removing the columnDefs for the CreatedBy column

i.e. instead of:

{
    targets: [1, 2, 5],
    className: 'dt-body-nowrap',
    render: $.fn.dataTable.render.ellipsis(100)
},

I tried

{
    targets: [1, 2],
    className: 'dt-body-nowrap',
    render: $.fn.dataTable.render.ellipsis(100)
},

Works but not ideal configuration:
1. Removing the CreatedBy column from the Datatable
2. Pulling the data directly from the Ideas table instead of vwAllIdeas
3. vwAllIdeas works in SQL Server

Plan B option:
Add a CreatedBy varchar column to the Ideas table and save the data as text instead of joining it to other tables.

Any insight is appreciated. At this point, I assume I just can't pull data using a SQL view - it has to come directly from a table. I also have Datatables Editor, if that helps at all.

allan

Hi,

Thanks for posting this and my apologies for the delay in getting back to you about it.

You have server-side processing enabled - does your Parser class handle the server-side processing parameters that DataTables sends? How many records do you have in the table - does it need server-side processing?

Thanks,
Allan

AKASecret

@allan I assume the Parser class can handle the server-side parameters because I have this setup working in other tables. This is the only table I have that I'm pulling a SQL view with JOINs and UNIONs instead of from the table directly, and it's the only one that seems to be giving me trouble.

There are 1793 records that should appear in this view.