InfoPath Dev

Hello Everyone.  I am having some serious performance issues.  I am running IP2007, SQL 2005 and C# behind the form.  I have a SQL database with 7 tables to submit and retrive.  When I created the form, i created a new balnk form based on a database.  I then selected the database, added the tables and built the relationships.  I left the SQL statement that IP created for me alone, but was left wondering about some of the syntax - shape and append and all that.  I don't know much about SQL, but it looked very clunky to me.  At any rate, after I had the form up and running things were great for awhile, but after a couple of months, I have been having some serious performance issues.  At first, my query took about 4 seconds to complete.  The main table had about 21K records and others had a comparable number, but three of them had zero records - this is where most of my submit data goes.  As those latter three tables start to fill up, the query time gets longer and longer now taking 55-65 seconds to complete.  I know that it is these last three tables due to some quick sleuthing.  If I create a new form based on the same database and just add one table at a time and test the query after each add, the query completes very quickly up until I add the first of the last three tables and just gets worse from there.  The kicker is that each of those tables contain less than 2K records each.  The first three contain over 20K each.  What gives?  I work with a guy that 'used to work with SQL' and he says that my SQL statement is suspect, but can add no assistance as to why.  It seems as though the performance degrades more every day and it is causing my users to pull their hair out and curse me.  Does anyone have any ideas where to go next?

Here is a sample of my SQL statement:

shape
 {select "BuyerNumber","mnBuyerNumber_201" from "dbo"."Buyers" as "Buyers"} as "Buyers"
 append
  ({select "BuyerPhoneID","BuyerNumber","PhoneType","Phone","Preferred" from "dbo"."BuyerPhones" as "BuyerPhones"}
   relate "BuyerNumber" TO "BuyerNumber") as "BuyerPhones",
  ({select "LotNumber","szCommunity_201","BuyerNumber" from "dbo"."Lots" as "Lots"}
   relate "BuyerNumber" TO "BuyerNumber") as "Lots",
  ((shape
   {select "WorkOrderNumber","LongWorkOrderNumber","LotNumber","WorkOrderType","BuyerNumber" from "dbo"."WorkOrders" as "WorkOrders"}
   append
    ({select "AvailabilityID","WorkOrderNumber" from "dbo"."WorkOrderAvailability" as "WorkOrderAvailability"}
     relate "WorkOrderNumber" TO "WorkOrderNumber") as "WorkOrderAvailability",
    ((shape
     {select "ConcernID","WorkOrderNumber","ServiceItemNumber" from "dbo"."WorkOrderConcern" as "WorkOrderConcern"}
     append
      ({select "InfoID","ConcernID","UpdatedInfo","DateUpdated" from "dbo"."WorkOrderConcernInfo" as "WorkOrderConcernInfo"}
       relate "ConcernID" TO "ConcernID") as "WorkOrderConcernInfo")
     relate "WorkOrderNumber" TO "WorkOrderNumber") as "WorkOrderConcern")
   relate "BuyerNumber" TO "BuyerNumber") as "WorkOrders"

Any help would be greatly appreciated.

Too bad you didn't use our Database Accelerator Web Service.

Seems like the query is pulling too much data down, so I'd look at restricting the query. Too bad that InfoPath doesn't pass parameterized filtering of SQL. That's why you really should have done this via a Web Service (which would allow that), but anyway, here are some ideas ...

Look at how big the form is. Do file save after it opens and measure the size of the XML. If it's really large, then the query is pulling down too much data. You'll want to restrict it by using some filters either by editing the SQL text and adding some hardcoded filters or by creating a new form that calls a Web Service which maps to your existing database.

If the data is not large, consider indexing the keys in your database. You can run SQL Analyzer to see what's going on when the query is run, but it will require some SQL guru to decipher the info.

Send us a support pack and one of our guys can spend an hour looking at it.

Most importantly, next time you'll want to use a Web Service.

I hope this helps!

Hello Patrick and thanks for the response.  I took a hard look at your Database Accelerator and even presented a proposal to our CIO regarding its implementation.  Unfortunately, we are a homebuilder and as I am sure that you are aware, we aren't exactly swimming in cash right now and IT spending is all but zero.  There were technical issues that created obstacles as well, but primarily it was a cost issue.

 If I fill out the form and save it to my desk top, it is about 64kb - pretty small I think.  Additionally, all of my tables already have indexes on them.

I have never created a web service, but I think that I might take a stab at it if it really is the way to go.

As always, thanks for the input.