InfoPath Dev

We use a form on our WAN.  In one of our plants, our 2 table data connection takes minutes to retrieve 1-4 rows of data.

PurchaseOrders

           POItems

The field that links them together is an integer field called PRID

 If I create a new form and search on just PurchaseORders by PRID - 5 second retrieval GREAT

If I create a new form and search on just POItems by PRID - 5 second retrieval GREAT

If I use the form where both tables are linked to the MainDataConnection (retrieve and submit), - 3 minutes. BAD

WE ARE HOPING THERE IS A WAY TO FIX THIS WITHOUT RE-DESIGNING THE FORM.

What happens if you create a new form with the tables linked? Comparing like to like? I'm wondering if there isn't something else in your form causing the slowness, and that may be a way to start testing.

Thanks for replying Hilary.

We did what you suggested.  We created a new form.  Connected to only one table.  Speed was great.  Then we changed the data connection to retrieve from the second table.  Speed was great.  Then we changed the data connection to include both tables and the bottleneck appeard as it does in the regular form.

Are there any rules like foreign keys have to be defined in SQL Server?  We link the tables when we setup the data connection but foreign keys are not defined.

 Any suggestions are greatly appreciated.

E

Odd, odd, odd. And in SQL, if you run a query that joins those tables the same way you do in the form, what happens? As far as I know you shouldn't see an issue because you don't have keys defined, but it probably wouldn't hurt anything to add them and see if there is a difference.

I'm glad someone else thinks it odd too.  When you run the same queries through SQL Management Console...12 seconds over the WAN.  We will try to put in the foreign keys and see if that helps.  I'm doubtful.