Slow SQL Query Response

[Ben]

Hi -
I have a pretty extensive form that loads a lot of data from two different
SQL views on a seperate SQL server. When I open the form and it makes its
initial data connection, it uses one dataconnection and populates a drop down
list with names. From there you select the name and it populates another
view in infopath with the appropriate fields. This process takes about 2
minutes on the initial load. Its then pretty quick switching users (5
seconds). The query is quick when run on the SQL server so I know its not a
performance issue on there.

My guess is that once you select a name (first query), it sorts all the
information returned in the second query when is everything. Is there a way
to put in the select statement to only return data based on the name selected
in the first query? Thanks!

Ben

 

[Renee in Juneau]

For TWO minutes?

Perhaps its the form design rather than the queries that are causing
you performance problems? Scott Roberts has a "Tips and Tricks" (or
maybe its "Best Practices") webcast available that you can find on
msevents via the Office Dev/Infopath Multimedia page.

At some point he was talking about ways to avoid performance hits when
creating extensive forms. Rather than paraphrase what I remember, you
should check it out for yourself.

Actually, if you haven't seen these yet, they may be pertinent to your
task:

Best Practices for Designing InfoPath Forms
http://go.microsoft.com/?linkid=2316308

Building Advanced Dynamic Solutions in InfoPath 2003
http://go.microsoft.com/?linkid=2316310

Database Connectivity in InfoPath Through ADO.NET DataSet Support
http://go.microsoft.com/?linkid=2316317

 

[Ben]

Yes what a slow query. I will definately check out those tips and tricks.
I appreciate your help!

Ben