SQL Database Integration

Re: InfoPath 2007 very slow performance with sql

saravanan4u83 — Thu, 31 Jul 2008 22:36:06 GMT

I couldn't see any of other option suit to my requirement.

But I wonder the performance diff between combobox and dropdown controls. As the user needed an option to enter new value directly, I tried using combobox instead of dropdown. The performance went down. Form takes 3 times of actual time with dropdown.

Do I need to do any addition to see good performance , when using combobox ?

Thanks, Saravanan

Re: InfoPath 2007 very slow performance with sql

saravanan4u83 — Tue, 29 Jul 2008 23:48:26 GMT

Thank you for quick reply Clay.

I tried separate secondary data sources for dropdown list in columns. I could see some performance increase now. I haven't try other choices you given. I will work on them and come back soon with result.

Thanks, Saravanan

Re: InfoPath 2007 very slow performance with sql

Clay Fox — Tue, 29 Jul 2008 15:06:38 GMT

This is typically due to having a lot of data in the DB. How many records are there? For your dropdowns you would want to limit your secondary connection, often to just the one column, so you may need a secondary for each.Or create a filtered or sorted view that has a smaller subset of choices if that is applicable. I would think that there are multiple records per partner, when really you just want a list of unique partners, so a view that gives you that, or even better create a sub-table with your partner data. I think that is your main issue. The query of the main data should be pretty fast. To really do it right you would want to move to Web services. Checkout www.qdabra.com. That is the best way to create SQL backed InfoPath solutions.

InfoPath 2007 very slow performance with sql

saravanan4u83 — Mon, 28 Jul 2008 17:53:42 GMT

I have developed a form in InfoPath 2007. This is a infopath client based form and not browser enabled.
This form is creaded based on sql database. Given below is SQL table that I am connecting here.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[P_C_Offer](
[Partner] [varchar](255) NOT NULL,
[site] [char](1) NULL,
[Invoice] [char](1) NULL,
[Salesperson] [varchar](50) NULL,
[Client] [varchar](255) NOT NULL,
[Offer] [varchar](255) NOT NULL,
CONSTRAINT [PK_P_C_Offer_1] PRIMARY KEY CLUSTERED
(
[Partner] ASC,
[Client] ASC,
[Offer] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

the form has two data connections. Main data connection for retieve and submit. Another one for look-up.

My form design is like this.. There will be a filter section at top of form with 'get data' button.
Filter section:
Partner - drop down list
Client - drop down list
Offer - drop down list

Get Data - this button click retrivs data from sql DB and shows in a repeating table.

Repeating table:
Partner - drop down list
Site - check box
Invoice - check box
salesperson - text box
Client - drop down list
Offer - text box

There is a rule set on Partner drop down list. Depending on its selection.. client drop down list will change.

This is the issue:
When user tried using this form. They feel it is not useful as it takes long time. The form takes very long time to retrieve data.
I tried to get data without any filter. Below is the result.

Memory usage = >100MB
There is 2 drop-down list, 2 check box and 1 text box controls in each row.
Currently >700 rows will be retrieved without any filter.
Every drop-down list for Partner column has >100 values.
Every drop-down list for Client column has 1-10 values depending on Partner selection.
The time taken to finish this retriavl is > 50 sec.

I have same issue with other 2 forms where the response time is > 4 min.
We are stated thinking that choosing InfoPath for this task is a bad choice.

Please help me out to solve it and continue..

Thanks,
Saravanan