View Single Post
  #4  
Old October 30th, 2009, 11:18 PM posted to microsoft.public.access.forms
Jeff Boyce
external usenet poster
 
Posts: 8,621
Default Is my query at fault for a slow form?

see comments in-line below...

"jenniferspnc" wrote in message
...
Hi Jeff,

I have about 2,000 records and I was limiting the number pulled in b/c I
honestly was trying to figure out how I could speed it up. Needless to
say
it's probably not related to the records.


Agreed ... 2,000 records shouldn't cause the delay.

The query takes about 10 seconds to run so may play a small role in the
problem. Is it okay to base the form directly off the query?


A query that returns (up to) 2,000 records should take ... sub-seconds.

Yes, a very good approach, basing a form on a query.


And a majority of the problem once the form opens is the delay in the
combo
boxes. For example, Country, a user goes to that combo box and there is
maybe a 10 second pause before they can begin to type and see a match
(i.e.
"canada"). Could this be the problem for slow form loading as well? I'm
needing help troubleshooting.


If the query that feeds the form takes ten seconds, that's part of the
problem. If the comboboxes take 10 seconds each to load, that's part of the
problem.

How many records/rows are the comboboxes loading?

For the Country combo box I have:
Control Source: Country_ID
Row Source: SELECT tbl_country.Country_ID, tbl_country.Countries FROM
tbl_country ORDER BY tbl_country.Countries;
Row Source Type: Table/Query
Bound Column: 1

I do have indexes on the primary key fields. Maybe I need to review
others?


Your SQL statement uses an "ORDER BY" ... does your tbl_country have an
index on the [Countries] field?

What kind of data type is that field?

Which version of Access are you using?

Where's the data located? On your PC? On another PC? On a server on the
network?

What kind of PC are you using?

More info, please...

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or psuedocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.


Thanks for your help and patience. It's a challenge to learn sometimess
because I don't always know where I'm going wrong.

"Jeff Boyce" wrote:

If I'm reading your SQL correctly, you want the form to "hold" all the
"salesorder + clients + country" data for all records with a datereceived
between the first of the current month and the first of two months from
now.

I can't see your database, so I don't know if that's likely to be 10
records
or 10,000 or 10,000,000.

I also wonder why you are looking for records that have a date
(datereceived) up to two months in the future. How can you receive an
order
in the future?

Have you tried running just a query, to see if the query is slow (vs. how
long the FORM takes)?

Have you checked the underlying tables to ensure that they have indexes
on
every field used for joins, selection criteria, and sorting?

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or psuedocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

"jenniferspnc" wrote in message
...
It never fails, I build my database to the best of my abilities, and
once
I
split things move terribly slow. So I once again come for assistance.
I've
going through the steps of things to improve speed (persistent
connection,
track name auto correct to "no", shorten BE name and not placed several
folders down.

I was hoping someone could look at the SQL of my query (tied to the
form)
to
see if anything might causing the form to take 40 seconds to load.
Once
open
it tends to move relatively quicker (well, two very slow combo boxes
which
I'm looking into).

SELECT tbl_salesorder.Sales_Order, tbl_salesorder.Client_ID,
tbl_salesorder.Country_ID, tbl_salesorder.Denied_Party_List_Date,
tbl_salesorder.Status, tbl_clients.Client, tbl_country.Countries,
tbl_salesorder.Reviewer_Name, tbl_salesorder.Comments,
tbl_salesorder.[Date_Approved/Denied], tbl_salesorder.Date_Received
FROM tbl_country INNER JOIN (tbl_clients INNER JOIN tbl_salesorder ON
tbl_clients.Client_ID = tbl_salesorder.Client_ID) ON
tbl_country.Country_ID =
tbl_salesorder.Country_ID
WHERE (((tbl_salesorder.Date_Received) Between
DateSerial(Year(Date()),Month(Date()),1) And
DateSerial(Year(Date()),Month(Date())+2,1)))
ORDER BY tbl_salesorder.Date_Received;

Thanks!



.