SQL database connection speed

[AlexW]

I am a non-programmer, but have successfully created an Infopath form in
which the user enters a client ID number and the form retrieves basic name
address information (read only). Other fields in form can be updated and
submitted back to the SQL database. All very successful, works like a charm.
The only trouble is that the first retrieval of the ID information is very
slow. Only 40 seconds in the main building, but ten minutes from outside
using the VPN - too slow to be useful. Have I done something wrong? Or is
there a way to optimize data retrieval speeds? The form is linked to 6 tables
and I notice speed decreases with each additional table. Tried publishing to
Sharepoint, but no difference in speed. Would the web work better?

Alex.

 

[Art's Antique Radios]

Instead on linking to six tables, you need to either create a single table
or view that contains all the data.

Joins, which essentially is what you are doing, slows the process
tremendously.

 

[AlexW]

Thanks very much, that was very helpful, and the speed is now real quick.
Now I have another problem: I used the VIEW option suggested and it pulls
fields from 6 tables. When I click on SUBMIT I get the following error:

"onsubmitrequest event handler returned a value indicating that the submit
failed"

My two SQL gurus have conflicting opinions about whether or not you can
update through a VIEW to the underlying tables. Have we missed something?

Alex.
--
CNIB
vision help. vision hope

Instead on linking to six tables, you need to either create a single table
or view that contains all the data.

Joins, which essentially is what you are doing, slows the process
tremendously.

 

[AlexW]

More information on the submit issue: In the Infopath set up wizard on a
status page, is the message: "Submit Status not allowed - a SQL Join
statement was used."

Can anyone advise me on the correct syntax for creating the View?

Thanks, Alex.
--
CNIB
vision help. vision hope

Thanks very much, that was very helpful, and the speed is now real quick.
Now I have another problem: I used the VIEW option suggested and it pulls
fields from 6 tables. When I click on SUBMIT I get the following error:

"onsubmitrequest event handler returned a value indicating that the submit
failed"

My two SQL gurus have conflicting opinions about whether or not you can
update through a VIEW to the underlying tables. Have we missed something?

Alex.