InfoPath Dev

Hi all,

 I did some searching, but couldn't find anything on this topic. 

I have an InfoPath form that is growing in size.  It is being hosted on a SharePoint site and has multiple data connections to SharePoint lists, two of which are near or exceed 10000 rows (with many columns).  This is causing the form to take a while to open. 

I have checked the box "Automatically retrieve the data when the form is opened", and I am aware that this increases the time it takes to open the form.

My question is if anyone knows how to decrease the time it takes to get the data.  Is it faster to put the data directly in the SQL database, and make a data connection to it?

Thanks! 

Hey Patrick.

Nice to see you are still plugging away on Infopath :)

Microsoft says that the realistic limit on Sharepoint lists is 2000 items.  Otherwise the performance steadily degrades since the view loads all of the data and then filters the items.

Without web services, InfoPath must load the whole list, which takes a long time with that amount of data.

Sometimes you can not load on open but trigger the data right before you need it to help the users impression but that is going to be a slow process.

You could redesign as a data base type form but you can only query one table not multiple ones.

Best practice would be to put the data in SQL, which is made for large arrays, and then use web services to just query the subset you need, either the exact record or the smaller group.

Check out the Data Enumeration Service. How to: User Oriented Infopath example for using the Database Enumeration Service

As always, thanks Clay, for taking the time to help me out.  I think you're right about the web services portion.  The load time of the form is becoming a bit unbearable.  I have other SharePoint lists that I am loading as well, which doesn't help.

I have been using a web service for data retrieval on a different part of the form, and that is much faster.

Thanks!

In SharePoint, you can change the default view of the list to reduce the size and that will speed up performance, but if you need to get everything, you'll want to put it in a database and use filtering to progressively pull down the data in a series of cascading dropdowns. This makes it much less data intensive. Create a grouping/subgrouping (or taxonomy) on top of your list and use that in a dropdown to filter the total list into just what you need. Use our Database Enumeration web service to do the filtering.

 Hi Patrick,

 Thanks for your reply.  I have been thinking about how to make this work a bit faster.  Clay suggested I take a look at using a web service to pull my data, but unfortunately I have to exclude that option for this portion (but it's still a great way to receive data!).

To reiterate, I have many lists that I am calling to in SharePoint, but 2 of them are quite large (7000 and 10000 entries).  These are pricing tables.

I may be trying to do what you suggested in your last post, but I wasn't sure if this is exactly what you mean.  Now, in terms of how the form is currently working; The pricing tables are being loaded in their entirety on form open, there are a handful of dropdowns that must be selected before the tables populate the appropriate prices.  This is effectively the "filtering" part of the process, since the tables are being referenced based on the selections made in the dropdowns.

The trouble is that I think the entire pricing tables must be loaded either on form open, or by a refresh button, but either way is lengthy.  If the tables resided in SQL, would they load faster?  I'm not sure how I would go about using a drop down to filter from the start, but I will look into it. 

Right now I am looking to test the performance when the tables are loaded into SQL.  I'll be sure to update this thread with my results.

As always, thank you for your help! 

The data could be tables in SQL.

It would be interesting to see the performance difference between SP and SQL. Unfortunately without web services the whole list must be loaded as a secondary data connection into the form.

With the Enumeration Service.  The table would be queried dynamically by the parameters in the drop downs the user selected.  So if they specified a group and category the reslting data would be just the records where the group and category matched. That typically can greatly reduce the amount of data loaded.

This form and solution will save them a ton of time and money which can pay for the web services you need. Then they can use them for all sorts of other solutions too. :)

Good Luck

Hi all,

So I've been redesigning the form's data connections to use SQL connections.  The way I set it up is to have a Microsoft Office Data Connection file on a shared drive.  This points to the SQL server and supplies an authenticated password for the "SQL User" account specifically made for this project.

Then I set up the data connections to point to the .odc file and pull the necessary tables.  I couldn't believe the difference in performance!  I have only tested this on the LAN, so I still must test this remotely.  Here are some preliminary results, as I've been keeping track of loading times.

SharePoint List, LAN: 90+ sec

SQL, LAN: 28 sec

These are averages of many trials, but there is a dramatic improvement in performance :)

I'll update this thread when I see new results.  I hope this helps somebody! 

Thanks for posting this information, Patrick -- I look forward to seeing more results as you have them!

Hello

Here is one interesting post on Sharepoint List Performance

http://blog.solanite.com/keith/Lists/Posts/Post.aspx?ID=15