Answers

• allan Posts: 64,285Questions: 1Answers: 10,614 Site admin

  April 13

  Would it be possible to provide some kind of "hybrid" serverSide processing that searches for server side getFormatter values (if provided) instead of database values?

  The problem is that you need to get all of the values from the database, render and then search them, throwing away the ones you don't want. So yes, it could be done with a custom server-side script, but the benefit of getting the SQL engine to do it all is gone and with it the majority of the speed benefit.

  Your best bet with this is to format the data in the SQL and have the search performed on that. Keep as much of the processing as possible in SQL - you only want it to return 10 (or however many) rows, for maximum performance.

  Allan
• rf1234 Posts: 3,086Questions: 90Answers: 427

  April 13

  So yes, it could be done with a custom server-side script, but the benefit of getting the SQL engine to do it all is gone and with it the majority of the speed benefit.

  I am aware that some of the speed benefit is gone, but the alternative is extremely cumbersome:

  Your best bet with this is to format the data in the SQL and have the search performed on that.

  Here is an example of what this can look like. Not nice but I am using it right now.

  CONCAT(REPLACE(LEFT( FORMAT(a.annual_cost_income, 2), POSITION('.' IN FORMAT(a.annual_cost_income, 2)) - 1 ), ',', '.'), ',', SUBSTR(FORMAT(a.annual_cost_income, 2), POSITION('.' IN FORMAT(a.annual_cost_income, 2)) + 1))
                                          AS annual_cost_income_german,
  FORMAT(a.annual_cost_income, 2)         AS annual_cost_income_english,
  CAST(a.annual_cost_income AS CHAR)      AS annual_cost_income_english_short,
  REPLACE(a.annual_cost_income, '.', ',')  AS annual_cost_income_german_short,   
  

  This is required if you want to use serverSide and be able to search for German and English formatted numbers (e.g. 1.000,00 and 1,000.00) and short notations of those numbers e.g. 1000 or 1000,00 and 1000.00).

  In particular "annual_cost_income_german" looks like a nightmare, doesn't it. The formatting with PHP in a getFormatter is much simpler.

  While I agree that some of the benefit would be sacrificed I think that there should still be a speed benefit working with rendered values on the server. In many use cases this should be fast enough, don't you think?
• kthorngren Posts: 21,900Questions: 26Answers: 5,060

  April 13

  I'm not familiar with the PHP scripts but possibly you can create a deformatter, for the desired fields, to revert the search term back to the original source value. Not sure how easy or if possible this would be.

  Kevin
• rf1234 Posts: 3,086Questions: 90Answers: 427

  April 13 edited April 13

  possibly you can create a deformatter, for the desired fields, to revert the search term back to the original source value.

  Tried that, too. I converted the user entries to the database format before sending the search values to the server. It was even more cumbersome. And not really workable if you have many rendered fields.

  Here is an example of a "deformatter" that I dismissed a while ago to implement the server side views above.

  You see how complex this can be - hence I found it was worse than the SQL approach. You need to make sure you are only converting the right entries e.g. dates and not mistakenly convert a number, for example.

  var forexTable = $('#tblForex').DataTable( {
      dom: "Bfrltip",
      serverSide: true,    //server side only works well with type "POST" !!!
      ajax: {
          url: 'actions.php?action=tblForex',
          type: 'POST',
          data: function ( d ) {
      //allow searching for dates with server side processing
              var dFs = ['D/M/YYYY', 'DD/MM/YYYY', 'D/M/YY', 'DD/MM/YY', 'D/M', 'DD/MM', 'D/M/', 'DD/MM/'];
              var toBeFormat = ''; var sepCounter = 0;
      //No commas allowed as date separator; if English: no period either!
              if ( ( ! ( d.search.value.indexOf(',') >= 0 ) ) &&
                   ( ! ( lang !== 'de' && d.search.value.indexOf('.') >= 0 ) )    )   {
                  if ( moment(d.search.value, dFs).isValid() ) {
                      toBeFormat = 'MM-DD';
                      for (i=0; i < d.search.value.length; i++) {
                          //counting the separators in the search string
                          if ( isNaN (d.search.value.substr(i, 1)) ) {
                              sepCounter++;
                      //if we find two separators and the second one is not at the
                      //end of the string we have a long date otherwise only a short one
                              if ( sepCounter === 2 && i < (d.search.value.length-1) ) {
                                  toBeFormat = 'YYYY-MM-DD';
                              }
                          }                        
                      }
                      if (sepCounter > 0) { //we need to have found at least one separator
                          d.search.value = moment(d.search.value, dFs).format(toBeFormat);
                      } else {
                          toBeFormat = '';
                      }
                  }
              }
      //not a date: we check for a number
              if (toBeFormat <= '') {
                  var number;
                  if (lang == 'de') {
                      number = d.search.value.toString().replace( /[\.]/g, "" );
                      number = d.search.value.toString().replace( /[\,]/g, "." );
                  } else {
                      number = d.search.value.toString().replace( /[\,]/g, "" );
                  }
                  if ( ! isNaN( parseFloat(number) ) ) {             
                      d.search.value = number;
                  }
              }
          }
      },
  
• allan Posts: 64,285Questions: 1Answers: 10,614 Site admin

  April 14 Answer ✓

  It would be really interesting to do an analysis of this. It might be a good project for a summer student... My gut feeling is that doing server-side processing with all the data being read into the script (PHP, .NET, whatever), being parsed and filtered there, will not offer any significant improvement over client-side processing, up to a cross over, when the initial load time becomes unsuitably long.

  Doing it all in SQL would be preferable, but as you say, it can quickly get ugly (and ugly SQL often isn't particularly performant itself!).

  I'll have a bit of a think about this and maybe that is some research that is worth doing.

  Allan