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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|