InfoPath Dev

We have a Requisition Form used at all facilities on our WAN.

The main dataconnection is SQL Server DB Tables called PurchaseOrders and a POItems.  The POITems is a repeating child table under the PurchaseOrders.  PurchaseOrderID is an identify Field and is the forgein key in POITEMS.  Make sense?  e.g. 1 to many relationship.

A query to this dataconnection from one of our Remote sites, will take upwards of 60 seconds, much of which we have determined is the linked table.  If we remove POItems from Purchase ORders, the query drops to less than 15 seconds.

Any ideas on how to speed this up?  We are playing with the idea of converting to a Web Service which we've teseted is much faster, but that would require us re-working the entire form.

Well, run SQL analyzer on the SQL box and see what it is doing. If it's slow, consider indexing the tables? Probably not SQL, though. Probably data across the wire and InfoPath SQL data connection issues. After downloading, save your form out as XML. How big is it? If it's >500k, your SQL query may be a good place to restrict the amount of data.

For Web services, you don't necessarily need to completely rework your form. We have taken DB backed forms and converted to using our Web service. We could do that for you and provide a free trial to our Web service for 3 months so you could try it out. If you have a large form, it could take a week. If the tables are <20 columns each, it would likely take a couple days.

Patrick,  Thanks for the well thought out reply.  The sql query is very quick.  There is always one header record retrieved and probably on average 5 line items.  There are many columns of data returned, but few number of rows.

The xml form is only 35K.  Where can I get pricing on your webservice?

One thing we did was create a webservice for just the items, leaving the header row a sql Retrieve and Submit data connection.  This was faster, but the "Insert Rows" was not enabled on the repeating table, I'm guessing because it was a secondary dataconnection albiet a webservice. 

Hi again,

Sorry for more questions...

Are you storing the SQL DB in a main data source or a secondary data source? I'm guessing secondary based on your last sentence. If that is so, the size of the XML won't reflect the size of the data downloaded since that is stored in a secondary data connection. It's still possible that you are pulling down a huge amount of data.

Qdabra's Web Service pricing varies based on what support level you need. The price does not depend on number of users, so it's much less expensive than other packages out there (for example, SharePoint pricing is Client Access License based). In addition, DBXL is one Web Service for all of your forms - i.e. it's a meta Web Service. You can use it for all of your forms. No need to get a separate Web Service for each form. Just create a data mapping from each form to the DB using our mapping tools. DBXL does the mapping. I guarantee you that the price is a fraction of the expense you would have if you paid someone to develop andtest a standalone Web service in house and it's a hundred times faster to deploy and less risky because it's been deployed to mission critical environments for many years. Of course, you also get many extra features, tools and support from our seasoned staff. Send email to Sales at Qdabra.com and someone can build out a plan to meet your needs and budget.

Patrick, if your questions help speed up our connection, ask as many as you'd like.

The Main Data Source is a SQL Server Database Connection.  Right now, there are 2 tables in the main database connection.  PurchaseOrders and indented and linked underneath is "POItems".  When we remove the POITEMS table from the Main Data Source Connection, our retrieval speeds up considerably.  So our thought was to keep PurchaseOrders SQL Server Main Data connection and add a WebService to retrieve and submit POITEMS. 

We are not returning what I call large amounts of data.  It is always 1 row from PurchaseOrders, and maybe 5-10 rows of POITEMS.  Actually I believe that even on "New" forms in our remote plants, the connection is very slow causing a delay of 45 seconds before the form opens.  Again, I think this improves when you remove POITems linked table.  If you want to look at any specific screen shots or other information specific to the form, I'd be happy to provide.

 E