Fast query but slow listbox

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Alessandro - 19 Mar 2008 16:47 GMT

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

Reply to this Message

Klatuu - 19 Mar 2008 19:07 GMT

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.

Signature

Dave Hargis, Microsoft Access MVP

> Hi everybody.
>
[quoted text clipped - 21 lines]
> TIA
> -- Alessandro

Reply to this Message

Rick Brandt - 20 Mar 2008 00:57 GMT

> Hi everybody.
>
[quoted text clipped - 18 lines]
> 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.

Signature

Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

Reply to this Message

Alessandro - 20 Mar 2008 07:48 GMT

> 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

Reply to this Message