Form seems to render very slowly

[bwbob]

I have a form built, connected to a Access databse that retrives some sales
information. (Select Customer, retrieve item sales history) I have embedded
repeating tables (2) and everything works fine. However, the form reneders
very slowly if it returns more than about 50 lines of data. (by slow I mean
it can take 3 minutes to complete) I only have 8 data fields that get
populated, and the database is only 3 tables with about 97,000 records (9
fields per record). It would seem to me that it should be faster or is this
the best that Infopath can do. I used Infopath because it was a quick, easy
way to build the form and distribute it to users. Any input greatly
appreciated

 

[Greg Collins [InfoPath MVP]]

What is your view made up of? A series of drop-down list boxes, text boxes, or what?

--
Greg Collins [InfoPath MVP]
Visit http://www.InfoPathDev.com


I have a form built, connected to a Access databse that retrives some sales
information. (Select Customer, retrieve item sales history) I have embedded
repeating tables (2) and everything works fine. However, the form reneders
very slowly if it returns more than about 50 lines of data. (by slow I mean
it can take 3 minutes to complete) I only have 8 data fields that get
populated, and the database is only 3 tables with about 97,000 records (9
fields per record). It would seem to me that it should be faster or is this
the best that Infopath can do. I used Infopath because it was a quick, easy
way to build the form and distribute it to users. Any input greatly
appreciated

 

[bwbob]

One drop down box from the query side, "Select Customer Name"
That drop down pull in all the customer names and associated customer number
from a table in Access.
then, in the data fields, in a repeating section, I have two data controls,
"sales order number" and "Invoice Number"
Inside this repeating section is another repeating section with the following;
line number, item number, description, qty, invoice date.
All this information is pulled from one access database. It is set up in
three main tables with no primary keys. The customer table has 3 columns;
customer name, customer number, and a concantonated number-name.
Then I have a sales order headers table with columns order number, customer
number,invoice number. The third table is the sales order lines; order
number, line number, item, etc.

Everything actually works, and this form is only built to retrieve past
sales info that was unable to be imported into a new system.
It works fine for customers with less than 50 or so sales orders, but for
customers that have 100+ sales orders with maybe 10 lines each, it seems like
it hangs forever. It can take 5 minutes to render the form.

I know a query in Access would be faster, but IP let me build a form that
looks just like our old system, which requires no training for the end users,
plus the database was simple to build. My solution would be nearly perfect
if the form renders quickly.

 

[Greg Collins [InfoPath MVP]]

How many records are being loaded into the drop-down list? 25, 50, 100, 1000? The more you have here, the slower things will get when you hae more rows in your main table.

Also, if you have a lot of embedded sections or tables in the view, that can slow things down too.

What pieces of your form is getting slowed down? Insert/Delete? Drop-down selction, moving from field-to-field? Is it just certain parts or the entire form?

--
Greg Collins [InfoPath MVP]
Visit http://www.InfoPathDev.com


One drop down box from the query side, "Select Customer Name"
That drop down pull in all the customer names and associated customer number
from a table in Access.
then, in the data fields, in a repeating section, I have two data controls,
"sales order number" and "Invoice Number"
Inside this repeating section is another repeating section with the following;
line number, item number, description, qty, invoice date.
All this information is pulled from one access database. It is set up in
three main tables with no primary keys. The customer table has 3 columns;
customer name, customer number, and a concantonated number-name.
Then I have a sales order headers table with columns order number, customer
number,invoice number. The third table is the sales order lines; order
number, line number, item, etc.

Everything actually works, and this form is only built to retrieve past
sales info that was unable to be imported into a new system.
It works fine for customers with less than 50 or so sales orders, but for
customers that have 100+ sales orders with maybe 10 lines each, it seems like
it hangs forever. It can take 5 minutes to render the form.

I know a query in Access would be faster, but IP let me build a form that
looks just like our old system, which requires no training for the end users,
plus the database was simple to build. My solution would be nearly perfect
if the form renders quickly.

What is your view made up of? A series of drop-down list boxes, text boxes, or what?

--
Greg Collins [InfoPath MVP]
Visit http://www.InfoPathDev.com


I have a form built, connected to a Access databse that retrives some sales
information. (Select Customer, retrieve item sales history) I have embedded
repeating tables (2) and everything works fine. However, the form reneders
very slowly if it returns more than about 50 lines of data. (by slow I mean
it can take 3 minutes to complete) I only have 8 data fields that get
populated, and the database is only 3 tables with about 97,000 records (9
fields per record). It would seem to me that it should be faster or is this
the best that Infopath can do. I used Infopath because it was a quick, easy
way to build the form and distribute it to users. Any input greatly
appreciated

 

[bwbob]

About 800 get loaded into the drop down list.(Customer Name). That part
loads fast.
After selecting the customer name, they click on the "run query" button,
which I have remamed "Get Customer Sales History"
This is the part that can take forever.
There are only 2 reapeating sections, one inside of the other.
The outer section has 2 fields, one row.
The inner section has 6 fields, and the number of rows is determined by the
number of "line items" on the original sales order. Max is about 10. For
the slowest customer, the outer section is repeated about 200 times, with the
inner section repeated once each time with 1-10 lines. So all total I could
be trying to display about 2200 lines of information. For the users, the
scrolling is not an issue, since in most cases they will use the "cntrl-F"
function to seach for a particular part number or descriptive term, then
"Next" through till they spot the original sales order they are looking for.
Most of those sales orders contained "Memo's" as line items that need to be
checked when the customer re-orders a product (special information not
included in the original item description.
Thanks for your help by the way.

How many records are being loaded into the drop-down list? 25, 50, 100, 1000? The more you have here, the slower things will get when you hae more rows in your main table.

Also, if you have a lot of embedded sections or tables in the view, that can slow things down too.

What pieces of your form is getting slowed down? Insert/Delete? Drop-down selction, moving from field-to-field? Is it just certain parts or the entire form?

--
Greg Collins [InfoPath MVP]
Visit http://www.InfoPathDev.com


One drop down box from the query side, "Select Customer Name"
That drop down pull in all the customer names and associated customer number
from a table in Access.
then, in the data fields, in a repeating section, I have two data controls,
"sales order number" and "Invoice Number"
Inside this repeating section is another repeating section with the following;
line number, item number, description, qty, invoice date.
All this information is pulled from one access database. It is set up in
three main tables with no primary keys. The customer table has 3 columns;
customer name, customer number, and a concantonated number-name.
Then I have a sales order headers table with columns order number, customer
number,invoice number. The third table is the sales order lines; order
number, line number, item, etc.

Everything actually works, and this form is only built to retrieve past
sales info that was unable to be imported into a new system.
It works fine for customers with less than 50 or so sales orders, but for
customers that have 100+ sales orders with maybe 10 lines each, it seems like
it hangs forever. It can take 5 minutes to render the form.

I know a query in Access would be faster, but IP let me build a form that
looks just like our old system, which requires no training for the end users,
plus the database was simple to build. My solution would be nearly perfect
if the form renders quickly.

What is your view made up of? A series of drop-down list boxes, text boxes, or what?

--
Greg Collins [InfoPath MVP]
Visit http://www.InfoPathDev.com


I have a form built, connected to a Access databse that retrives some sales
information. (Select Customer, retrieve item sales history) I have embedded
repeating tables (2) and everything works fine. However, the form reneders
very slowly if it returns more than about 50 lines of data. (by slow I mean
it can take 3 minutes to complete) I only have 8 data fields that get
populated, and the database is only 3 tables with about 97,000 records (9
fields per record). It would seem to me that it should be faster or is this
the best that Infopath can do. I used Infopath because it was a quick, easy
way to build the form and distribute it to users. Any input greatly
appreciated

 

[Greg Collins [InfoPath MVP]]

Okay - my initial worry was that you had a large (800 is considered large) drop-down list *inside* the repeating section. This can slow things down quite a bit. But it sounds like it is outside and populates and displays quickly.

So the problem comes when they query on a particular value from the drop-down list. Right?

The next step would be to identify whether it is the query or the display (or both) which are taking too long.

The easiest way to do this is to make a backup copy of your template, and then remove those 2 repeating sections -- basically a view with nothing but the drop-down list and the query button. Time how long it takes to run the query. If it is snappy, then it is the display which is taking long. If not, discover why your query is taking so long.

If it is just the display, then you need to limit how much is shown on the page at one time. You can do this by creating paging (i.e. filter the outer repeating section based on position, against 2 secondary data fields to specify the range to show (first, last). This way you can show, say 50 - 100 items at a time. You can also hook up a search text box and button to filter the list to only show those items the user wants to see (instead of having them use CTRL+F).

Good luck!

--
Greg Collins [InfoPath MVP]
Visit http://www.InfoPathDev.com


About 800 get loaded into the drop down list.(Customer Name). That part
loads fast.
After selecting the customer name, they click on the "run query" button,
which I have remamed "Get Customer Sales History"
This is the part that can take forever.
There are only 2 reapeating sections, one inside of the other.
The outer section has 2 fields, one row.
The inner section has 6 fields, and the number of rows is determined by the
number of "line items" on the original sales order. Max is about 10. For
the slowest customer, the outer section is repeated about 200 times, with the
inner section repeated once each time with 1-10 lines. So all total I could
be trying to display about 2200 lines of information. For the users, the
scrolling is not an issue, since in most cases they will use the "cntrl-F"
function to seach for a particular part number or descriptive term, then
"Next" through till they spot the original sales order they are looking for.
Most of those sales orders contained "Memo's" as line items that need to be
checked when the customer re-orders a product (special information not
included in the original item description.
Thanks for your help by the way.

How many records are being loaded into the drop-down list? 25, 50, 100, 1000? The more you have here, the slower things will get when you hae more rows in your main table.

Also, if you have a lot of embedded sections or tables in the view, that can slow things down too.

What pieces of your form is getting slowed down? Insert/Delete? Drop-down selction, moving from field-to-field? Is it just certain parts or the entire form?

--
Greg Collins [InfoPath MVP]
Visit http://www.InfoPathDev.com


One drop down box from the query side, "Select Customer Name"
That drop down pull in all the customer names and associated customer number
from a table in Access.
then, in the data fields, in a repeating section, I have two data controls,
"sales order number" and "Invoice Number"
Inside this repeating section is another repeating section with the following;
line number, item number, description, qty, invoice date.
All this information is pulled from one access database. It is set up in
three main tables with no primary keys. The customer table has 3 columns;
customer name, customer number, and a concantonated number-name.
Then I have a sales order headers table with columns order number, customer
number,invoice number. The third table is the sales order lines; order
number, line number, item, etc.

Everything actually works, and this form is only built to retrieve past
sales info that was unable to be imported into a new system.
It works fine for customers with less than 50 or so sales orders, but for
customers that have 100+ sales orders with maybe 10 lines each, it seems like
it hangs forever. It can take 5 minutes to render the form.

I know a query in Access would be faster, but IP let me build a form that
looks just like our old system, which requires no training for the end users,
plus the database was simple to build. My solution would be nearly perfect
if the form renders quickly.

What is your view made up of? A series of drop-down list boxes, text boxes, or what?

--
Greg Collins [InfoPath MVP]
Visit http://www.InfoPathDev.com


I have a form built, connected to a Access databse that retrives some sales
information. (Select Customer, retrieve item sales history) I have embedded
repeating tables (2) and everything works fine. However, the form reneders
very slowly if it returns more than about 50 lines of data. (by slow I mean
it can take 3 minutes to complete) I only have 8 data fields that get
populated, and the database is only 3 tables with about 97,000 records (9
fields per record). It would seem to me that it should be faster or is this
the best that Infopath can do. I used Infopath because it was a quick, easy
way to build the form and distribute it to users. Any input greatly
appreciated

 

[bwbob]

Can you feed me a bit more on hooking a text box for searching? That would
help with more than one form I am working on.

Okay - my initial worry was that you had a large (800 is considered large) drop-down list *inside* the repeating section. This can slow things down quite a bit. But it sounds like it is outside and populates and displays quickly.

So the problem comes when they query on a particular value from the drop-down list. Right?

The next step would be to identify whether it is the query or the display (or both) which are taking too long.

The easiest way to do this is to make a backup copy of your template, and then remove those 2 repeating sections -- basically a view with nothing but the drop-down list and the query button. Time how long it takes to run the query. If it is snappy, then it is the display which is taking long. If not, discover why your query is taking so long.

If it is just the display, then you need to limit how much is shown on the page at one time. You can do this by creating paging (i.e. filter the outer repeating section based on position, against 2 secondary data fields to specify the range to show (first, last). This way you can show, say 50 - 100 items at a time. You can also hook up a search text box and button to filter the list to only show those items the user wants to see (instead of having them use CTRL+F).

Good luck!

--
Greg Collins [InfoPath MVP]
Visit http://www.InfoPathDev.com


About 800 get loaded into the drop down list.(Customer Name). That part
loads fast.
After selecting the customer name, they click on the "run query" button,
which I have remamed "Get Customer Sales History"
This is the part that can take forever.
There are only 2 reapeating sections, one inside of the other.
The outer section has 2 fields, one row.
The inner section has 6 fields, and the number of rows is determined by the
number of "line items" on the original sales order. Max is about 10. For
the slowest customer, the outer section is repeated about 200 times, with the
inner section repeated once each time with 1-10 lines. So all total I could
be trying to display about 2200 lines of information. For the users, the
scrolling is not an issue, since in most cases they will use the "cntrl-F"
function to seach for a particular part number or descriptive term, then
"Next" through till they spot the original sales order they are looking for.
Most of those sales orders contained "Memo's" as line items that need to be
checked when the customer re-orders a product (special information not
included in the original item description.
Thanks for your help by the way.

How many records are being loaded into the drop-down list? 25, 50, 100, 1000? The more you have here, the slower things will get when you hae more rows in your main table.

Also, if you have a lot of embedded sections or tables in the view, that can slow things down too.

What pieces of your form is getting slowed down? Insert/Delete? Drop-down selction, moving from field-to-field? Is it just certain parts or the entire form?

--
Greg Collins [InfoPath MVP]
Visit http://www.InfoPathDev.com


One drop down box from the query side, "Select Customer Name"
That drop down pull in all the customer names and associated customer number
from a table in Access.
then, in the data fields, in a repeating section, I have two data controls,
"sales order number" and "Invoice Number"
Inside this repeating section is another repeating section with the following;
line number, item number, description, qty, invoice date.
All this information is pulled from one access database. It is set up in
three main tables with no primary keys. The customer table has 3 columns;
customer name, customer number, and a concantonated number-name.
Then I have a sales order headers table with columns order number, customer
number,invoice number. The third table is the sales order lines; order
number, line number, item, etc.

Everything actually works, and this form is only built to retrieve past
sales info that was unable to be imported into a new system.
It works fine for customers with less than 50 or so sales orders, but for
customers that have 100+ sales orders with maybe 10 lines each, it seems like
it hangs forever. It can take 5 minutes to render the form.

I know a query in Access would be faster, but IP let me build a form that
looks just like our old system, which requires no training for the end users,
plus the database was simple to build. My solution would be nearly perfect
if the form renders quickly.

What is your view made up of? A series of drop-down list boxes, text boxes, or what?

--
Greg Collins [InfoPath MVP]
Visit http://www.InfoPathDev.com


I have a form built, connected to a Access databse that retrives some sales
information. (Select Customer, retrieve item sales history) I have embedded
repeating tables (2) and everything works fine. However, the form reneders
very slowly if it returns more than about 50 lines of data. (by slow I mean
it can take 3 minutes to complete) I only have 8 data fields that get
populated, and the database is only 3 tables with about 97,000 records (9
fields per record). It would seem to me that it should be faster or is this
the best that Infopath can do. I used Infopath because it was a quick, easy
way to build the form and distribute it to users. Any input greatly
appreciated

 

[Greg Collins [InfoPath MVP]]

You add a text box named "Query" or whatever you want to call it.
Then add a filter to your repeating table that uses the contains() function or starts-with() function to compare the value in the Query field against the value of one of the fields in your table.

So for example, you have an email address field in your table that you want to filter by. Create a text box for users to type in all or part of an email address, add a filter to your table that filters for Table/Row/Email[contains(., QueryEmail)]. The preceding is not an exact XPath that you should use but only acts to represent what needs to be done.

--
Greg Collins [InfoPath MVP]
Visit http://www.InfoPathDev.com


Can you feed me a bit more on hooking a text box for searching? That would
help with more than one form I am working on.

Okay - my initial worry was that you had a large (800 is considered large) drop-down list *inside* the repeating section. This can slow things down quite a bit. But it sounds like it is outside and populates and displays quickly.

So the problem comes when they query on a particular value from the drop-down list. Right?

The next step would be to identify whether it is the query or the display (or both) which are taking too long.

The easiest way to do this is to make a backup copy of your template, and then remove those 2 repeating sections -- basically a view with nothing but the drop-down list and the query button. Time how long it takes to run the query. If it is snappy, then it is the display which is taking long. If not, discover why your query is taking so long.

If it is just the display, then you need to limit how much is shown on the page at one time. You can do this by creating paging (i.e. filter the outer repeating section based on position, against 2 secondary data fields to specify the range to show (first, last). This way you can show, say 50 - 100 items at a time. You can also hook up a search text box and button to filter the list to only show those items the user wants to see (instead of having them use CTRL+F).

Good luck!

--
Greg Collins [InfoPath MVP]
Visit http://www.InfoPathDev.com


About 800 get loaded into the drop down list.(Customer Name). That part
loads fast.
After selecting the customer name, they click on the "run query" button,
which I have remamed "Get Customer Sales History"
This is the part that can take forever.
There are only 2 reapeating sections, one inside of the other.
The outer section has 2 fields, one row.
The inner section has 6 fields, and the number of rows is determined by the
number of "line items" on the original sales order. Max is about 10. For
the slowest customer, the outer section is repeated about 200 times, with the
inner section repeated once each time with 1-10 lines. So all total I could
be trying to display about 2200 lines of information. For the users, the
scrolling is not an issue, since in most cases they will use the "cntrl-F"
function to seach for a particular part number or descriptive term, then
"Next" through till they spot the original sales order they are looking for.
Most of those sales orders contained "Memo's" as line items that need to be
checked when the customer re-orders a product (special information not
included in the original item description.
Thanks for your help by the way.

How many records are being loaded into the drop-down list? 25, 50, 100, 1000? The more you have here, the slower things will get when you hae more rows in your main table.

Also, if you have a lot of embedded sections or tables in the view, that can slow things down too.

What pieces of your form is getting slowed down? Insert/Delete? Drop-down selction, moving from field-to-field? Is it just certain parts or the entire form?

--
Greg Collins [InfoPath MVP]
Visit http://www.InfoPathDev.com


One drop down box from the query side, "Select Customer Name"
That drop down pull in all the customer names and associated customer number
from a table in Access.
then, in the data fields, in a repeating section, I have two data controls,
"sales order number" and "Invoice Number"
Inside this repeating section is another repeating section with the following;
line number, item number, description, qty, invoice date.
All this information is pulled from one access database. It is set up in
three main tables with no primary keys. The customer table has 3 columns;
customer name, customer number, and a concantonated number-name.
Then I have a sales order headers table with columns order number, customer
number,invoice number. The third table is the sales order lines; order
number, line number, item, etc.

Everything actually works, and this form is only built to retrieve past
sales info that was unable to be imported into a new system.
It works fine for customers with less than 50 or so sales orders, but for
customers that have 100+ sales orders with maybe 10 lines each, it seems like
it hangs forever. It can take 5 minutes to render the form.

I know a query in Access would be faster, but IP let me build a form that
looks just like our old system, which requires no training for the end users,
plus the database was simple to build. My solution would be nearly perfect
if the form renders quickly.

What is your view made up of? A series of drop-down list boxes, text boxes, or what?

--
Greg Collins [InfoPath MVP]
Visit http://www.InfoPathDev.com


I have a form built, connected to a Access databse that retrives some sales
information. (Select Customer, retrieve item sales history) I have embedded
repeating tables (2) and everything works fine. However, the form reneders
very slowly if it returns more than about 50 lines of data. (by slow I mean
it can take 3 minutes to complete) I only have 8 data fields that get
populated, and the database is only 3 tables with about 97,000 records (9
fields per record). It would seem to me that it should be faster or is this
the best that Infopath can do. I used Infopath because it was a quick, easy
way to build the form and distribute it to users. Any input greatly
appreciated