Fast query but slow listbox

[Alessandro]

Hi everybody.

I have a query that works on linked tables from two distinct backends
(SQL Server and MySQL) and is very fast to fetch data, about 2" to be
executed. In a form this query act as rowsource for a listbox.

The problem is that when I try to open the form, it takes about 20" to
populate the listbox. There are 7 columns.

I've made several test and I'm quite sure the problem is in the GUI
that manages the listbox.
Watching with task manager and a network traffic monitor the form
opening process, in the first 2" since the double click on the form
the network traffic is intense (here MSACCESS fetches data from
backends), while in the remaining 18" the MSACCESS process takes about
90% of CPU time. Then the form opens.

I'd like to have some clue on how to improve the speed of this
listbox.

BTW my OS is Win XP Pro SP2 with all security patches and Access is
2003.

TIA
-- Alessandro

 

[Klatuu]

It may not be the Access UI since the form is loading quickly. It may be the
time it takes Access to connect to the SQL Server database and retrieve the
records.

If you have no tables, open, Access drops the connection to the Server.
Establishing a new connection takes a bit of time.

One technique to avoid this problem is to maintain a presistent connection.
That is, when your app first opens, open a hidden form that has one of your
tables as its record source. Keep this form open all the time. Since the
connection is already established, you don't have to worry about connect time.

 

[Rick Brandt]

Hi everybody.

I have a query that works on linked tables from two distinct backends
(SQL Server and MySQL) and is very fast to fetch data, about 2" to be
executed. In a form this query act as rowsource for a listbox.

The problem is that when I try to open the form, it takes about 20" to
populate the listbox. There are 7 columns.

I've made several test and I'm quite sure the problem is in the GUI
that manages the listbox.
Watching with task manager and a network traffic monitor the form
opening process, in the first 2" since the double click on the form
the network traffic is intense (here MSACCESS fetches data from
backends), while in the remaining 18" the MSACCESS process takes about
90% of CPU time. Then the form opens.

I'd like to have some clue on how to improve the speed of this
listbox.

BTW my OS is Win XP Pro SP2 with all security patches and Access is
2003.

How are you testing the query? If you just open the datasheet view you will
see results very quickly because Access will paint the screen after fetching
just a few data-pages of rows. That doesn't mean though that the query is
actually finished. See how long it takes to open the query and then scroll
to the bottom of the result set. That would be more of an apples to apples
comparison.

Are you doing anything in your form that retrieves the row count of the
ListBox? That will force all of the rows to be processed. Otherwise it
should behave much like the query datasheet where it is really only slow
when you try to scroll down a ways.

The reason I doubt the veracity of your tests thus far is that it is
completely expected for a query joining tables from two different databases
to perform poorly. Any other result would be the surprise.

 

[Alessandro]

How are you testing the query? If you just open the datasheet view you will
see results very quickly because Access will paint the screen after fetching
just a few data-pages of rows. That doesn't mean though that the query is
actually finished. See how long it takes to open the query and then scroll
to the bottom of the result set. That would be more of an apples to apples
comparison.

The number of rows returned by the query is not always the same, but
in my tests there are max 50 rows.
As I said before, just opening the query takes max 2 seconds.

Are you doing anything in your form that retrieves the row count of the
ListBox? That will force all of the rows to be processed. Otherwise it
should behave much like the query datasheet where it is really only slow
when you try to scroll down a ways.

My test form just contains a listbox whose RowSource property is set
to the name of the query. No other calculated controls are present.
The form has been created with just few clicks.

The reason I doubt the veracity of your tests thus far is that it is
completely expected for a query joining tables from two different databases
to perform poorly. Any other result would be the surprise.

This was also my first thought, but it should be slow even when I just
launch the query with no connection to my listbox.
BTW, fields used in the WHERE statement are indexed.

I've made an additional test. In the source query I rediced the number
of selected columns from 7 to 1, but I did not made any change to
WHERE criteria.
Now the form opens much faster than with 7 columns selected. But I
need to display data from these columns in my listbox.


-- Alessandro