You are viewing limited content. For full access, please sign in.

Question

Question

Slow Database Lookup With Multiple Items in Table

asked on April 3, 2014 Show version history

We have created a Mileage Reimbursement form for our organization. This form allows users to select a from and to location that does a database lookup and calculates the miles automatically for the user. After a user adds several rows into the table, when click on the dropdown for From and To it takes a long time for it to populate the dropdown lists. We allow the user to be able to save the form and complete it later. Below is the area of the form having the problem. Does anyone have some suggestions on how to speed this up?

 

0 0

Answers

APPROVED ANSWER
replied on April 7, 2014

Forms product builds a SQL statement according to the lookup rule and returns a result.  We attempt to have minimal overhead.  I believe if the queries are taking a long time one good place to check is the sql server itself.  

 

We have heard of a few customers that ran into slow performance and by following the suggestions in this link have resolved it specially if you are using sql express.

 

http://www.helpmasterpro.com/Community/Discussion-Board/aft/455.aspx

0 0
SELECTED ANSWER
replied on February 23, 2015

This seems to have been resolved in 9.2.

0 0
replied on November 12, 2015 Show version history

I seem to be having this issue as well.

I have created a text field to look up a specific column from a database, the lookup does appear although it takes about 2 minutes. I have attempted to run a test with firebug, here are the results that were concluded. I have profiled the page with firebug containing the form, and it appears there are no errors.

I would also like to mention I have moved the data source from view to a table. I also created a form from scratch with just one field that is the lookup from the database.

What are some other factors I need to look into in order to resolve this issue?

Replies

replied on April 3, 2014

Wow, I like that. It almost sounds like it must be re-calculating all the previous items each time a new item is entered. Do you have access to a profiler tool on the database engine? You could get better insight into what is going on.

0 0
replied on April 3, 2014

I don't think it would be the recalculating as the actual calculating is done with JavaScript. The Mileage for each row is all kept in a database, so there is no calculating going on, it's simply doing a database lookup.

0 0
replied on April 3, 2014

Here is the JavaScript that is running on the page.

$(document).ready(function () {
  
  //make fields with read-only class read-only
  $(".read-only *").prop("readonly", true);
  
  //disable submit button until signature is confirmed
    $('.Submit').attr('disabled', 'disabled');
    $('#donebutton').click(function () {
        $('.Submit').removeAttr('disabled');
        $('.sigwarning').hide();
    })
    //end disable submit button code
  
  //Start Add row mile fields together
    $(document).ready(function () { 
    $('.cf-table-block').change(sumtotal); 
    function sumtotal() { 
        var sum = 0; 
        $('td.sum').each(function () { 
            var s = 0; 
            $(this).find('input').each(function () { 
                if ($(this).closest('td').siblings('td.roundTripCheck').find('input').is(':checked')) { 
                    s += parseNumber($(this).val() * 2) 
                } 
                else { 
                    s += parseNumber($(this).val()); 
                } 
            }) 
            $(this).find('.subtotal input').val(s); 
            sum += s; 
        }); 
        $('.total input').val((sum).toFixed(2)); 
    } 
    function parseNumber(n) { 
        var f = parseFloat(n); //Convert to float number. 
        return isNaN(f) ? 0 : f; //treat invalid input as 0; 
    } 
}); 
  //End Add row mile fields together
  
  //Start Calculate mileage reimbursement
     $('.cf-table-block').on('blur', 'input', sumreimbursement);
     function sumreimbursement() {
     var s = 0.44;
     $('.totalmiles input').each(function () {
     s *= parseNumber($(this).val());
     });
     $('.totalreimbursement input').val((s).toFixed(2));
     }
     function parseNumber(n) {
     var f = parseFloat(n); //Convert to float number.
     return isNaN(f) ? 0 : f; //treat invalid input as 0;
     }

  //End Calculate mileage reimbursement
  
  //Start Auto Fill
   function tableAutofill() { 
      $('.cf-table-block tbody tr').each(function () { 
      $(this).find('button.autofill').trigger('click'); 
    }); 
    }
  
    function autofill() {  
    $('.autofill').trigger('click');
    }
  
    $(document).ready(function () { 
      $('.lookupCondition input').blur(autofill); 
      $('.cf-table-block').change(tableAutofill); 
    }); 
  //End Auto Fill
  
  //code for signature
    function htmlEncode(value) {
        return $('<div/>').text(value).html();
    }

    function htmlDecode(value) {
        return $('<div/>').html(value).text();
    }

    /**When the page loads, check if the sig data (hidden) field has a value.
       If it has a value, decode it and put it in the image, and remove the
       signature pad and its buttons.**/
    var sigvalue = $('.sigdata textarea').text();
    var sigrovalue = $('.sigdata .ro').text();
    if (sigvalue != '' || sigrovalue != '') {
      var decoded = htmlDecode(sigvalue == '' ? sigrovalue : sigvalue );
        var $img = $('<img class=imported></img>');
        $img.attr("src", decoded).appendTo('#sigimage');
        $('.sigGroup').remove(); //class added to the signature button and image custom HTML fields.
       $('.sigwarning').hide();
    }

    $.getScript('http://www2.d91.k12.id.us/forms/js/jSignature.min.js', function () {
        $('#signature').jSignature();
    });
    $('#donebutton').click(function () {
        var sigdata = $('#signature').jSignature('getData');
        $('.sigdata textarea').val(htmlEncode(sigdata));
        var $img = $('<img class=imported></img>');
        $img.attr("src", $('#signature').jSignature('getData')).appendTo('#sigimage');
        $('.sigGroup').remove(); //class added to the signature button and image custom HTML fields.

    });
    $('#clearbutton').click(function () {
        $('#signature').jSignature('clear');
    });

    //end code for signature
});

 

0 0
replied on April 3, 2014

Have you profiled this form in the F12 tools to see where it's spending it's time?

0 0
replied on April 3, 2014

It is hard to say for sure but it is either accessing the database for each entry and/or re-calculating. Since the performance is based on the number of existing entries in the form it must be doing some task per entry each time a new one is added.

0 0
replied on April 4, 2014 Show version history

I used Firebug to look to see what possibly could be going on and this is what I found:

 

So it looks like the POST requests are taking F-O-R-E-V-E-R (meant to sound like on the movie Sandlot)! Playing around with an empty form, the post requests show each time I add a row to a table and select a To or From field. Any ideas on how to speed this up?

0 0
APPROVED ANSWER
replied on April 7, 2014

Forms product builds a SQL statement according to the lookup rule and returns a result.  We attempt to have minimal overhead.  I believe if the queries are taking a long time one good place to check is the sql server itself.  

 

We have heard of a few customers that ran into slow performance and by following the suggestions in this link have resolved it specially if you are using sql express.

 

http://www.helpmasterpro.com/Community/Discussion-Board/aft/455.aspx

0 0
replied on April 28, 2014 Show version history

I'm not sure if it is the lookup that is causing the problem though. The screenshot of the form above is the entire form. If you look at the screenshot from Firebug, it is referencing the jquery file, and almost all 11378 entires it mentions deal with that same file. Why would it be doing lookups that many times? This is happening mainly when it hits around 18 rows being added to the table on the form.

 

Also, we did create a maintenance plan according to your link for the database and it does not seem to make a difference.

0 0
replied on August 25, 2022

Hi Blake, I work for a School Board and we also also trying to create a Mileage/Expense Reimbursement process within forms to help streamline and automate the the current process. Would you be open to sharing with me in greater detail how you went about implementing this process? Any advise and guidance would be greatly appreciated! Thanks very much  

0 0
You are not allowed to follow up in this post.

Sign in to reply to this post.