Data Connections

Re: Data Connection Performance

Patrick Halstead — Wed, 14 Feb 2007 01:28:48 GMT

Hi again,

Can you break up the 2000 items into two or more dropdowns and do some cascading filtering based on what the person selects in the first and second dropdowns? You could add a rule that doesn't populate the ID dropdown until after the first and second dropdowns are selectd. We generally recommend no more than ~40 items in a dropdown. Each item has a window handle and it can take a good deal of time to create and destroy that many window handles. That is probably why it is slow, but just guessing again.

To do filtering, you should be able to use the XML file as part of the solution and some declarative rules and the filter option. If this is slow, try to not apply the rule until a button is clicked or something and see if that improves it. If it does, we can make add it as an OnAfterChange rule to the second or last dropdown that sets the ID dropdown and only fill the dropdown when the one before it is filled in. That should make load time go down.

Re: Data Connection Performance

rpowell4600 — Tue, 13 Feb 2007 18:24:05 GMT

Hello Patrick,

Here is what I have tried.

1. I stored the XML files in the forms Template. When I bring up the Form in browser mode the response time did not improve enough to make a difference. When I open the form from the Client, the response time is good.

2. When I created the data connection to query the server where the data is located and selected TOP 3 my response time did improve.

Yes, I would like to select all 2000 rows, since this data is required to populate the DROP-DOWN boxes. The user may not know the ID of the location they are selecting. So I created a query to select the valid ID's and append the location description. This way the user can just select their data from the DROP-DOWN box.

I appreciate your help. Any other ideas for me to try? One other note. The response time for quering my data and tabbing from field to field is good when I perform all of the above tasks in the client. Problem is, not everyone has the client loaded on their local machine?

Re: Data Connection Performance

Patrick Halstead — Mon, 12 Feb 2007 06:19:34 GMT

Hi RSP,
I think it's because you are returning 2000 rows. Do you really need 2000 rows? Why not take the XML files you created and store them in the form template. That should be faster. Use TOP 3 to limit the query.

Re: Data Connection Performance

rpowell4600 — Mon, 12 Feb 2007 01:21:02 GMT

Thanks for the quick reply Patrick.

Wanted to make a couple of points.

1. The SQL server I am accessing is not located on the same box as sharepoint. So, I see that can be an issue.
2. The amount of data I am retrieveing is a total of 2000 rows, for all 3 queries.
3. As an alternative I created 3 XML files from each of my queries. I then upload these XML files to the Sharepoint library as Library List. Still no luck when in browser mode. The response time to set the focus when tabbing from one field to the next is 3-4 seconds.
4. I will try returning only 3 rows as suggested to see what the results maybe.

Re: Data Connection Performance

Patrick Halstead — Sun, 11 Feb 2007 20:43:25 GMT

Hi RSP and welcome to our forum,

I'm guessing that it's because you either a) have a lot of data you are pulling down, or b) there is some extra authentication happening. It's easy to rule out (b), just make sure you are domain authenticated and that the SQL server and SharePoint Form Server are on the same box. If you are accessing from Internet, make sure that you have a VPN connection, etc. I have seen a definite lag with people who were not VPN'ed in when they access our server -http://formsboard.com/. We have some samples up on the server that use UDCX files to pull down data in a SharePoint list (see Sales Order, username for site is:demo@qdabra.com with pw=InfoPath2007). I have not tested perf for populating SQL within browser based forms. To investigate (a), I recommend changing the query string for the data connection to only pull down the first 3 rows. If that doesn't speed things up, I will ask David Airapetyan who actually worked on coding the Form Server or Ed Essey who was the program manager. They no longer work in the InfoPath group, but they are available to answer questions. And, we can also ask the InfoPath team.

If it turns out to be a data amount issue, I recommend using our Database Accelerator Web service which supports field-based parameterized querying of the rows in SQL. It should reduce the amount of data that you pull down. A pre-release of the v2 free Developer Edition can be downloaded fromhttp://www.qdabra.com/proddetail.asp?prod=QDBXL1.

More soon,

Data Connection Performance

rpowell4600 — Sun, 11 Feb 2007 19:13:00 GMT

I have an InfoPath form where I have created 3 secondary data connections to populate 3 different drop-down boxes. I am connecting to a SQL server to query the server for the values in the drop-down boxes. The drop-down boxes a pre-populated just as I expect, with the selected values.

Problem:
When I open the form in a browser and try to tab to the next field on the form, the cursor response is very slow. It takes 3-4 seconds once I hit the tab button for the cursor to move focus to the next field? So, I tried creating new connections with XML files and the drop-down box values embedded in the template, no luck same problem. Next approach, I created UDCX's on the Sharepoint site and used these connections in hopes the response time would improve. You guessed it, no luck there. When I created these secondary connection I selected the option to retrieve the data upon form load. Figuring I would deal with the form taking a little longer to load, as the SQL server is queried just the one time. But it looks to me as if each time I tab to the next field on the form, based on the slow response, that the server is being called again.

Is there anyway to speed up the performance of the form? Has anyone encountered this problem. If so, what was your solution.