Database/Controller/Front End Design Question For "Pre-Filtering" Large Datasets

iqvct

I'm currently using Datatables & Editor in a .NET Framework application.

For most tables, new rows of data are pushed to the tables quarterly.

Server side, I currently have a table valued function (access predicate) and security policy working in tandem so that all select queries only retrieve the records that are from the newest quarter. I've also tried implementing a where clause inside of a view or directly in the controller, but handling through a predicate/security policy has yielded better performance.

Users are now requesting to see historical data. Some of these tables are far too lengthy for the browser to have to handle the retrieval of a million rows of data.

A possible solution is if I were to remove the back end predicate and allow all of the data to be accessible by the front end, but only retrieve the data in chunks. Meaning that the front end would default to only retrieve the current period, but if user modified a front end drop down and selected "Prior Period", the table could be redrawn and retrieve prior period data.

What would be the best place to start with attempting to implement this type of functionality? Essentially full table access with an initial defaulted where clause that the user can modify so that their initial load does no crash the browser?

allan

Excellent question - thank you. They way I'd approach this is to have a parameter set to the server indicating the quarter they want to get the data from. The ajax.data property can be used for that (in this case, use it as a function).

Let's say you have a list:

<select id="quarter">
  <option>Q1 2021</option>
  <option>Q2 2021</option>
  <option>Q3 2021</option>
  <option>Q4 2021</option>
  <option selected>Q1 2022</option>
</select>

You'd out an event handler on it:

$('#quarter').on('change', function () {
  table.ajax.reload();
});

And your ajax.data might look like:

data: function (d) {
  d.quarter = $('#quarter').val();
}

Then on the server-side you'd check the quarter parameter submitted and use that in the query. How that could be done with a predicate, I'm not sure. It might need a different one per quarter.

Allan

iqvct

@allan Thanks for the reply and a good starting point.

Ignoring the current predicate which I would likely drop, what would be the optimal way to introduce this newly defined quarter variable into the query?

Can the parameter be passed to the controller and integrated into a simple where clause that could then re-draw the table under the new constraint?

I'm still trying to understand how the front end option selected is passed back to the server to modify the query.

iqvct

Redacted

allan

Can the parameter be passed to the controller and integrated into a simple where clause that could then re-draw the table under the new constraint?

Yes. With the above code, it will submit a quarter parameter to the server with every Ajax request. You would then use that in a where() condition.

Allan

iqvct

@allan I was able to get this to work from your suggestions!

Mostly. Now I created a new issue.

Below is how I modified the controller to receive input from the user so that the tables would only be loaded with data being called from the server.

Lines 17-23 below show the added where clause and the retrieval of the data from the front end:

    public class QuestionnaireResponseController : ApiController
    {
        [Route("api/QuestionnaireResponse")]
        [HttpGet]
        [HttpPost]
        public IHttpActionResult QuestionnaireResponse()
        {
            var request = HttpContext.Current.Request;
            var settings = Properties.Settings.Default;

            using (var db = new Database(settings.DbType, settings.DbConnection))
            {
                var response = new Editor(db, "VW_QuestionnaireResponse", "QuestionnaireResponseId" )
                    .Model<QuestionnaireResponseModel>("VW_QuestionnaireResponse")
                    .Field(new Field("(select Max(Year) from VW_QuestionnaireResponse)", "VW_QuestionnaireResponse.MaxYear").Set(false))
                    .Field(new Field("(select max(quarter) from VW_QuestionnaireResponse where year = (select max(year) from VW_QuestionnaireResponse))", "VW_QuestionnaireResponse.MaxQuarter").Set(false))
                    .Where(q => {
                        q.Where(r =>
                        {
                            r.Where("Quarter", request.Form["quarter"], "=");
                            r.AndWhere("Year", request.Form["year"], "=");
                        });
                    })
                
                    .Debug(true)
                    .Process(request)
                    .Data();

                return Json(response);
            }
        }
    }
}

Unfortunately, this seems to have created an unintended side effect. Whenever there is an update to a record in a datatable, the row disappears and only reappears upon browser refresh.

I did some digging to try and find the source of the issue. My assumption is that editor is not happy with what is being returned by the server now that we have made this edit to the controller.

Even though the table populates, when I now go to the API for the table, I no longer see the data in JSON format, but instead mostly blank data. It seems that the data being passed into the where clause is defaulting to NULL before reading the data from the front end causing this issue.

{"draw":null,"data":[],"recordsTotal":null,"recordsFiltered":null,"error":null,"fieldErrors":[],"id":null,"meta":{},"options":{},"files":{},"upload":{"id":null},"debug":[{"Query":"SELECT  [QuestionnaireResponseId] as 'QuestionnaireResponseId', (select Max(Year) from VW_QuestionnaireResponse) as '(select Max(Year) from VW_QuestionnaireResponse)', (select max(quarter) from VW_QuestionnaireResponse where year = (select max(year) from VW_QuestionnaireResponse)) as '(select max(quarter) from VW_QuestionnaireResponse where year = (select max(year) from VW_QuestionnaireResponse))', [VW_QuestionnaireResponse].[QuestionnaireResponseId] as 'VW_QuestionnaireResponse.QuestionnaireResponseId', [VW_QuestionnaireResponse].[OrderBy] as 'VW_QuestionnaireResponse.OrderBy', [VW_QuestionnaireResponse].[Response] as 'VW_QuestionnaireResponse.Response', [VW_QuestionnaireResponse].[Year] as 'VW_QuestionnaireResponse.Year', [VW_QuestionnaireResponse].[Quarter] as 'VW_QuestionnaireResponse.Quarter', [VW_QuestionnaireResponse].[Region] as 'VW_QuestionnaireResponse.Region', [VW_QuestionnaireResponse].[CountryName] as 'VW_QuestionnaireResponse.CountryName', [VW_QuestionnaireResponse].[EntityCodeOtp] as 'VW_QuestionnaireResponse.EntityCodeOtp', [VW_QuestionnaireResponse].[Question] as 'VW_QuestionnaireResponse.Question', [VW_QuestionnaireResponse].[QuestionnaireQuestionId] as 'VW_QuestionnaireResponse.QuestionnaireQuestionId', [VW_QuestionnaireResponse].[EntityName] as 'VW_QuestionnaireResponse.EntityName', [VW_QuestionnaireResponse].[Category] as 'VW_QuestionnaireResponse.Category', [VW_QuestionnaireResponse].[Type] as 'VW_QuestionnaireResponse.Type' FROM  [VW_QuestionnaireResponse] WHERE ([Quarter] IS NULL AND [Year] IS NULL )","Bindings":[]}],"cancelled":[]}

Any thoughts on a resolution?

allan

Whenever there is an update to a record in a datatable, the row disappears and only reappears upon browser refresh.

It sounds like the condition is not being met for that row when Editor is requesting the data for the row,immediately after having written it.

Are you using any triggers to modify values on this table?

Allan

iqvct

@allan

This issues persists amongst all tables where I have leveraged the below logic in the controller:

q.Where(r =>
              {
               r.Where("Quarter", request.Form["quarter"], "=");
               r.AndWhere("Year", request.Form["year"], "=");
              });

These tables do have triggers, but none of which impact these fields. Simple triggers to append data based on the user & date/time of when field is modified.

These triggers never previously impacted the ability of the table to edit a field and have that record persist after the edit without needing a refresh.

Also to reiterate, any table using this logic in the controller does not show data at the /api route because it appears the where clause defaults to NULL rather than the default SELECTED value from the HTML form.

Below is the end of the snippet from the debug I posted previously of the /api route.

FROM  [VW_QuestionnaireResponse] 
WHERE ([Quarter] IS NULL AND [Year] IS NULL )
","Bindings":[]}],"cancelled":[]}

One other item to point out is the new fields I am creating in the controller:

.Model<QuestionnaireResponseModel>("VW_QuestionnaireResponse")
.Field(new Field("(select Max(Year) from VW_QuestionnaireResponse)", "VW_QuestionnaireResponse.MaxYear").Set(false))
.Field(new Field("(select max(quarter) from VW_QuestionnaireResponse where year = (select max(year) from VW_QuestionnaireResponse))", "VW_QuestionnaireResponse.MaxQuarter").Set(false))

I'm sure there's a better way, but the purpose of this is to allow the user to select specifically what data to see based on parameters Year and Quarter without overloading the client with potentially hundreds of thousands rows of data if this filter did not exist.

The new field is created so that within the related javascript file, editor is only enabled when new fields of MaxYear and MaxQuarter are equal to the fields Year and Quarter on that specific row.

If there are 10,000 rows of data with quarter/year set to 2021/Q3, 10,000 rows of data for 2021/Q4, and 10,000 rows of data with 2022/Q1, a user can select which Year/Quarter combination to load to the front end. Editor is only enabled when Year/Quarter are equal to the new fields made of MaxYear and MaxQuarter.

In this case the only subset of data that would be editable would be that of the 2022 Q1 combination.

All of this works as intended (probably better ways to do this though). Except /api routes are now returning blank JSON, and any time a row is edited it disappears from the front end until refresh.

allan

Also to reiterate, any table using this logic in the controller does not show data at the /api route because it appears the where clause defaults to NULL rather than the default SELECTED value from the HTML form.

Ah - I wonder if you are using the ajax.data option as a function in DataTables, but not in Editor. The two are separate configurations. ajax.data will also need to be configured to send the year and quarter values if it isn't already?

Allan

iqvct

@allan 99% there!

Adding the ajax data option as shown below has resolved the disappearing row issue:

ajax: {
                'url': '/api/QuestionnaireResponse',
                "data": function (d) {
                    d.quarter = $('#quarter').val();
                    d.year = $('#year').val();
                }

However, when accessing the route /api/QuestionnaireResponse, the very end of the debug part still shows:

WHERE ([Quarter] IS NULL AND [Year] IS NULL )","Bindings":[]}],"cancelled":[]}

And the entirety of the response has no data.

This doesn't seem to have any adverse effects at the moment, but I assume this is not the optimal way to run things.

Any thoughts on if there is a way for the where clause to either have something manually set for default, or to read the HTML Select and use this rather than NULL?

allan

Yes, you could use the method described here to check if the form parameters have been submitted or not. If not then use a default value (or reject the request with an error).

Allan