A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Using Forms
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Is my query at fault for a slow form?



 
 
Thread Tools Display Modes
  #1  
Old October 30th, 2009, 04:30 PM posted to microsoft.public.access.forms
jenniferspnc
external usenet poster
 
Posts: 65
Default Is my query at fault for a slow form?

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!
  #2  
Old October 30th, 2009, 05:58 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?

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!



  #3  
Old October 30th, 2009, 08:56 PM posted to microsoft.public.access.forms
jenniferspnc
external usenet poster
 
Posts: 65
Default Is my query at fault for a slow form?

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.

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?

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.
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?

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!



.

  #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!



.



  #5  
Old November 2nd, 2009, 05:58 PM posted to microsoft.public.access.forms
jenniferspnc
external usenet poster
 
Posts: 65
Default Is my query at fault for a slow form?

Jeff,

Thank you for your patience in helping me troubleshoot. I've answered your
questions below:

How many records/rows are the comboboxes loading? approximately 200-250 on
each combo box

Your SQL statement uses an "ORDER BY" ... does your tbl_country have an
index on the [Countries] field? Yes, I have an index on Country_ID and
Countries - "Yes, No duplicates."
I've read but not understood much about removing the RowSource from a combo
box. How exactly do I do that while still enabling the user to view and
select a country? Would this save time?

What kind of data type is that field? Country_ID is AutoNumber and
Countries is Text

Which version of Access are you using? 2007

Where's the data located? On your PC? On another PC? On a server on the
network? On a server, but I do have the backend only 2 folders down (the
closest I can get it) and the BE name is shortened.

Does it appear I've done anything wrong with the combo boxes or the form
design? I didn't realize how much a pain it was to enter an order until I
had to do one. I don't think it's very efficient when one has to wait 10
seconds for each combo box (thank goodness I only have 2 of them).

Here is my query statement with the date limitation removed and a few other
things revised (still slow though)
SELECT tbl_salesorder.Sales_Order, tbl_salesorder.Client_ID,
tbl_salesorder.Country_ID, tbl_salesorder.Denied_Party_List_Date,
tbl_salesorder.Status, tbl_salesorder.Reviewer_Name, tbl_salesorder.Comments,
tbl_salesorder.[Date_Approved/Denied], tbl_salesorder.Date_Received
FROM tbl_salesorder
ORDER BY tbl_salesorder.Date_Received;

Thanks again!!

"Jeff Boyce" wrote:

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!


.



.

  #6  
Old November 4th, 2009, 07:44 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-in-line below...

"jenniferspnc" wrote in message
...
Jeff,

Thank you for your patience in helping me troubleshoot. I've answered
your
questions below:

How many records/rows are the comboboxes loading? approximately 200-250
on
each combo box


Unless your PC is a '286, that's not a lot to load...


Your SQL statement uses an "ORDER BY" ... does your tbl_country have an
index on the [Countries] field? Yes, I have an index on Country_ID and
Countries - "Yes, No duplicates."


OK, that rules out slow performance due to a lack of indexing...

I've read but not understood much about removing the RowSource from a
combo
box. How exactly do I do that while still enabling the user to view and
select a country? Would this save time?


While you can remove the RowSource ... , you have to put it back sometime!
Otherwise the combobox doesn't know its source. I don't think this would
matter.


What kind of data type is that field? Country_ID is AutoNumber and
Countries is Text

Which version of Access are you using? 2007

Where's the data located? On your PC? On another PC? On a server on the
network? On a server, but I do have the backend only 2 folders down (the
closest I can get it) and the BE name is shortened.


I've run into horribly slow performance over a network for a variety of
reasons. You might want to check out Tony Towes' website for suggestions on
performance.


Does it appear I've done anything wrong with the combo boxes or the form
design? I didn't realize how much a pain it was to enter an order until I
had to do one. I don't think it's very efficient when one has to wait 10
seconds for each combo box (thank goodness I only have 2 of them).


This kind of performance is not typical.


Here is my query statement with the date limitation removed and a few
other
things revised (still slow though)
SELECT tbl_salesorder.Sales_Order, tbl_salesorder.Client_ID,
tbl_salesorder.Country_ID, tbl_salesorder.Denied_Party_List_Date,
tbl_salesorder.Status, tbl_salesorder.Reviewer_Name,
tbl_salesorder.Comments,
tbl_salesorder.[Date_Approved/Denied], tbl_salesorder.Date_Received
FROM tbl_salesorder
ORDER BY tbl_salesorder.Date_Received;


Nothing else comes to mind. Perhaps one of the other newsgroup readers has
experience/can spot something...

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.




Thanks again!!

"Jeff Boyce" wrote:

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!


.



.



  #7  
Old November 4th, 2009, 09:56 PM posted to microsoft.public.access.forms
FEleazer via AccessMonster.com
external usenet poster
 
Posts: 1
Default Is my query at fault for a slow form?

You said you are using Access 2007, but is the default file format 2007, or
an earlier version? (Under Access Options, Popular menu.)

I just fixed a problem like this where my form was taking a minute or more to
open over the network. I too, went through all of the tips and tricks for
optimizing, and nothing helped. The database was in Access 2003, but the
default file format was 2000 so that it would be compatible with other users.
I converted all of the databases to version 2003 and the thing just zips
right along now!

Good luck! It took me 4 years to figure this one out!

Fran

Jeff Boyce wrote:
see comments in-in-line below...

Jeff,

[quoted text clipped - 5 lines]
on
each combo box


Unless your PC is a '286, that's not a lot to load...

Your SQL statement uses an "ORDER BY" ... does your tbl_country have an
index on the [Countries] field? Yes, I have an index on Country_ID and
Countries - "Yes, No duplicates."


OK, that rules out slow performance due to a lack of indexing...

I've read but not understood much about removing the RowSource from a
combo
box. How exactly do I do that while still enabling the user to view and
select a country? Would this save time?


While you can remove the RowSource ... , you have to put it back sometime!
Otherwise the combobox doesn't know its source. I don't think this would
matter.

What kind of data type is that field? Country_ID is AutoNumber and
Countries is Text

[quoted text clipped - 4 lines]
network? On a server, but I do have the backend only 2 folders down (the
closest I can get it) and the BE name is shortened.


I've run into horribly slow performance over a network for a variety of
reasons. You might want to check out Tony Towes' website for suggestions on
performance.

Does it appear I've done anything wrong with the combo boxes or the form
design? I didn't realize how much a pain it was to enter an order until I
had to do one. I don't think it's very efficient when one has to wait 10
seconds for each combo box (thank goodness I only have 2 of them).


This kind of performance is not typical.

Here is my query statement with the date limitation removed and a few
other

[quoted text clipped - 6 lines]
FROM tbl_salesorder
ORDER BY tbl_salesorder.Date_Received;


Nothing else comes to mind. Perhaps one of the other newsgroup readers has
experience/can spot something...

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

Thanks again!!

[quoted text clipped - 142 lines]

.


--
Thanks,
Fran

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200911/1

  #8  
Old November 5th, 2009, 11:24 PM posted to microsoft.public.access.forms
jenniferspnc
external usenet poster
 
Posts: 65
Default Is my query at fault for a slow form?

Thank you for all your help!

At least I've done everything I can on my end to reduce the delay.

"Jeff Boyce" wrote:

see comments in-in-line below...

"jenniferspnc" wrote in message
...
Jeff,

Thank you for your patience in helping me troubleshoot. I've answered
your
questions below:

How many records/rows are the comboboxes loading? approximately 200-250
on
each combo box


Unless your PC is a '286, that's not a lot to load...


Your SQL statement uses an "ORDER BY" ... does your tbl_country have an
index on the [Countries] field? Yes, I have an index on Country_ID and
Countries - "Yes, No duplicates."


OK, that rules out slow performance due to a lack of indexing...

I've read but not understood much about removing the RowSource from a
combo
box. How exactly do I do that while still enabling the user to view and
select a country? Would this save time?


While you can remove the RowSource ... , you have to put it back sometime!
Otherwise the combobox doesn't know its source. I don't think this would
matter.


What kind of data type is that field? Country_ID is AutoNumber and
Countries is Text

Which version of Access are you using? 2007

Where's the data located? On your PC? On another PC? On a server on the
network? On a server, but I do have the backend only 2 folders down (the
closest I can get it) and the BE name is shortened.


I've run into horribly slow performance over a network for a variety of
reasons. You might want to check out Tony Towes' website for suggestions on
performance.


Does it appear I've done anything wrong with the combo boxes or the form
design? I didn't realize how much a pain it was to enter an order until I
had to do one. I don't think it's very efficient when one has to wait 10
seconds for each combo box (thank goodness I only have 2 of them).


This kind of performance is not typical.


Here is my query statement with the date limitation removed and a few
other
things revised (still slow though)
SELECT tbl_salesorder.Sales_Order, tbl_salesorder.Client_ID,
tbl_salesorder.Country_ID, tbl_salesorder.Denied_Party_List_Date,
tbl_salesorder.Status, tbl_salesorder.Reviewer_Name,
tbl_salesorder.Comments,
tbl_salesorder.[Date_Approved/Denied], tbl_salesorder.Date_Received
FROM tbl_salesorder
ORDER BY tbl_salesorder.Date_Received;


Nothing else comes to mind. Perhaps one of the other newsgroup readers has
experience/can spot something...

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.




Thanks again!!

"Jeff Boyce" wrote:

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!


.



.



.

  #9  
Old November 5th, 2009, 11:25 PM posted to microsoft.public.access.forms
jenniferspnc
external usenet poster
 
Posts: 65
Default Is my query at fault for a slow form?

Thaks for the suggestion, it does say 2007 for the file format.

I'm guessing it's related to the network...

"FEleazer via AccessMonster.com" wrote:

You said you are using Access 2007, but is the default file format 2007, or
an earlier version? (Under Access Options, Popular menu.)

I just fixed a problem like this where my form was taking a minute or more to
open over the network. I too, went through all of the tips and tricks for
optimizing, and nothing helped. The database was in Access 2003, but the
default file format was 2000 so that it would be compatible with other users.
I converted all of the databases to version 2003 and the thing just zips
right along now!

Good luck! It took me 4 years to figure this one out!

Fran

Jeff Boyce wrote:
see comments in-in-line below...

Jeff,

[quoted text clipped - 5 lines]
on
each combo box


Unless your PC is a '286, that's not a lot to load...

Your SQL statement uses an "ORDER BY" ... does your tbl_country have an
index on the [Countries] field? Yes, I have an index on Country_ID and
Countries - "Yes, No duplicates."


OK, that rules out slow performance due to a lack of indexing...

I've read but not understood much about removing the RowSource from a
combo
box. How exactly do I do that while still enabling the user to view and
select a country? Would this save time?


While you can remove the RowSource ... , you have to put it back sometime!
Otherwise the combobox doesn't know its source. I don't think this would
matter.

What kind of data type is that field? Country_ID is AutoNumber and
Countries is Text

[quoted text clipped - 4 lines]
network? On a server, but I do have the backend only 2 folders down (the
closest I can get it) and the BE name is shortened.


I've run into horribly slow performance over a network for a variety of
reasons. You might want to check out Tony Towes' website for suggestions on
performance.

Does it appear I've done anything wrong with the combo boxes or the form
design? I didn't realize how much a pain it was to enter an order until I
had to do one. I don't think it's very efficient when one has to wait 10
seconds for each combo box (thank goodness I only have 2 of them).


This kind of performance is not typical.

Here is my query statement with the date limitation removed and a few
other

[quoted text clipped - 6 lines]
FROM tbl_salesorder
ORDER BY tbl_salesorder.Date_Received;


Nothing else comes to mind. Perhaps one of the other newsgroup readers has
experience/can spot something...

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

Thanks again!!

[quoted text clipped - 142 lines]

.


--
Thanks,
Fran

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200911/1

.

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 01:35 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.