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
|
|||
|
|||
Row source Best practice??
Since i am new to all this access stuff i have been tending to create new
queries everywhere i need them instead of just creating stand alone queries (in the database window) and referring back to them... For example every form that uses a employee name combobox will have its own query for the rowsource (in the combos properties) rather than having a employee names query in the database window and having all employee combos reference that query.. the same goes for report data sources where each report has its own query (in properties) rather than referencing a query in the database window's Queries page.. I guess i really have three related questions: 1. will all of these queries eventually cause me problems or do they really not matter?? 2. Are there design rules or methods to keep queries efficient. 3. in a report is it better to just use the table as the reports record source with dlookups for related data or to use ALL related tables in a query in the reports datasource? im sorry if the question is vague or off, i am just trying to keep Dumb from running wild.. Thanks Barry |
#2
|
|||
|
|||
Row source Best practice??
Good questions.
Re #1 & #2: No -- using saved queries won't cause you grief later. If you use a SQL statement directly in the RecordSource of a form/report or the RowSource property of a combo/listbox, Access creates a hidden query and saves it anyway. Therefore there is no practical difference, and it's a matter of style. Advantages of using a saved query: a) Single maintenance point. Particularly for combos, I like to create a saved query and re-use it throughout the application (e.g. for other forms where you also want a combo for clients also.) If you need to make a change later (e.g. to sort all inactive clients to the bottom of the list), there's only one query to change, and all your client combos get the same treatment. b) May actually result in fewer saved queries (since they are reused.) Disadvantage of saved query: a) You have to manually copy the source queries also if you reuse the form/report in another database. b) There are more visible items to sort through on the Queries tab of the Database Window / Navigation Pane. Re #3 You *definitely* want to use a source query rather than have a bunch of DLookup()s in your form/report. DLookups are slow and resource hungry. The multi-table query will give you orders of magnitude better performance than a table with a bunch of DLookups. -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Barry A&P" wrote in message ... Since i am new to all this access stuff i have been tending to create new queries everywhere i need them instead of just creating stand alone queries (in the database window) and referring back to them... For example every form that uses a employee name combobox will have its own query for the rowsource (in the combos properties) rather than having a employee names query in the database window and having all employee combos reference that query.. the same goes for report data sources where each report has its own query (in properties) rather than referencing a query in the database window's Queries page.. I guess i really have three related questions: 1. will all of these queries eventually cause me problems or do they really not matter?? 2. Are there design rules or methods to keep queries efficient. 3. in a report is it better to just use the table as the reports record source with dlookups for related data or to use ALL related tables in a query in the reports datasource? im sorry if the question is vague or off, i am just trying to keep Dumb from running wild.. Thanks Barry |
#3
|
|||
|
|||
Row source Best practice??
Thank you allen,
In regard to my question #2 I have come across talk somewhere about query performance. and SQL ordering to keep queries efficient. maybe running a query with criteria first then joining larger tables later or sorting after a using filtering criteria? i dont really know what the difference is as the access "design View" doesnt allow many options for the SQL arrangement. If design view is good enough then design view is good enough.. Ill spend a little time on your tips page.. Thanks again for your earlier response Barry "Allen Browne" wrote: Good questions. Re #1 & #2: No -- using saved queries won't cause you grief later. If you use a SQL statement directly in the RecordSource of a form/report or the RowSource property of a combo/listbox, Access creates a hidden query and saves it anyway. Therefore there is no practical difference, and it's a matter of style. Advantages of using a saved query: a) Single maintenance point. Particularly for combos, I like to create a saved query and re-use it throughout the application (e.g. for other forms where you also want a combo for clients also.) If you need to make a change later (e.g. to sort all inactive clients to the bottom of the list), there's only one query to change, and all your client combos get the same treatment. b) May actually result in fewer saved queries (since they are reused.) Disadvantage of saved query: a) You have to manually copy the source queries also if you reuse the form/report in another database. b) There are more visible items to sort through on the Queries tab of the Database Window / Navigation Pane. Re #3 You *definitely* want to use a source query rather than have a bunch of DLookup()s in your form/report. DLookups are slow and resource hungry. The multi-table query will give you orders of magnitude better performance than a table with a bunch of DLookups. -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Barry A&P" wrote in message ... Since i am new to all this access stuff i have been tending to create new queries everywhere i need them instead of just creating stand alone queries (in the database window) and referring back to them... For example every form that uses a employee name combobox will have its own query for the rowsource (in the combos properties) rather than having a employee names query in the database window and having all employee combos reference that query.. the same goes for report data sources where each report has its own query (in properties) rather than referencing a query in the database window's Queries page.. I guess i really have three related questions: 1. will all of these queries eventually cause me problems or do they really not matter?? 2. Are there design rules or methods to keep queries efficient. 3. in a report is it better to just use the table as the reports record source with dlookups for related data or to use ALL related tables in a query in the reports datasource? im sorry if the question is vague or off, i am just trying to keep Dumb from running wild.. Thanks Barry |
#4
|
|||
|
|||
Row source Best practice??
Yes, JET exposes limited optimization techniques
This particular page may be of interest: http://allenbrowne.com/QueryPerfIssue.html Towards the end, there's a link for the JET Showplan, which could take you further. -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Barry A&P" wrote in message ... Thank you allen, In regard to my question #2 I have come across talk somewhere about query performance. and SQL ordering to keep queries efficient. maybe running a query with criteria first then joining larger tables later or sorting after a using filtering criteria? i dont really know what the difference is as the access "design View" doesnt allow many options for the SQL arrangement. If design view is good enough then design view is good enough.. Ill spend a little time on your tips page.. Thanks again for your earlier response Barry "Allen Browne" wrote: Good questions. Re #1 & #2: No -- using saved queries won't cause you grief later. If you use a SQL statement directly in the RecordSource of a form/report or the RowSource property of a combo/listbox, Access creates a hidden query and saves it anyway. Therefore there is no practical difference, and it's a matter of style. Advantages of using a saved query: a) Single maintenance point. Particularly for combos, I like to create a saved query and re-use it throughout the application (e.g. for other forms where you also want a combo for clients also.) If you need to make a change later (e.g. to sort all inactive clients to the bottom of the list), there's only one query to change, and all your client combos get the same treatment. b) May actually result in fewer saved queries (since they are reused.) Disadvantage of saved query: a) You have to manually copy the source queries also if you reuse the form/report in another database. b) There are more visible items to sort through on the Queries tab of the Database Window / Navigation Pane. Re #3 You *definitely* want to use a source query rather than have a bunch of DLookup()s in your form/report. DLookups are slow and resource hungry. The multi-table query will give you orders of magnitude better performance than a table with a bunch of DLookups. -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Barry A&P" wrote in message ... Since i am new to all this access stuff i have been tending to create new queries everywhere i need them instead of just creating stand alone queries (in the database window) and referring back to them... For example every form that uses a employee name combobox will have its own query for the rowsource (in the combos properties) rather than having a employee names query in the database window and having all employee combos reference that query.. the same goes for report data sources where each report has its own query (in properties) rather than referencing a query in the database window's Queries page.. I guess i really have three related questions: 1. will all of these queries eventually cause me problems or do they really not matter?? 2. Are there design rules or methods to keep queries efficient. 3. in a report is it better to just use the table as the reports record source with dlookups for related data or to use ALL related tables in a query in the reports datasource? im sorry if the question is vague or off, i am just trying to keep Dumb from running wild.. Thanks Barry |
#5
|
|||
|
|||
Row source Best practice??
Allen Browne has largely provided fine answers to your questions,
but I'd like to add a number of points. =?Utf-8?B?QmFycnkgQSZQ?= wrote in : 1. will all of these queries eventually cause me problems or do they really not matter?? It depends on your app. I used to make saved queries for everything, on the theory that they were more efficiently optimized. But since Jet saves optimization plans for recordsource and rowsource queries, there was never much advantage there. Where you have the same rowsource used on multiple forms, it does make sense to use a saved query for that as long as there's never any reason the instance of your combo box ever need to fork. I recently screwed up a combo box on one form that used a saved query that I edited for use in a combo box on a different form that needed an altered rowsource. I simply forgot that the changes I was making to the saved query were going to impact the other use of it. So, you really have to keep track of your usage of shared QueryDefs, because the sharing is a two-edged sword -- great when all uses are identical, problematic when there needs to be variation. In general, I don't create many shared queries any more. My most recent complex project has a grand total of one combo box saved QueryDef. That's about par for the course. 2. Are there design rules or methods to keep queries efficient. Saved or otherwise? In general, I use saved queries when I have a common central set of data that gets used in multiple locations. I then write an individual SQL string for each form/report/combo/listbox that uses that saved query as a source. This has worked quite well as it leaves the full control in the UI object, but still allows a common core datasource that is edited in one place. Efficient queries in general is a huge question and deserves a thread of its own, seems to me. 3. in a report is it better to just use the table as the reports record source with dlookups for related data or to use ALL related tables in a query in the reports datasource? As Allen said, DLookups are to be avoided. My philosophy of DLookups() is that they should never be used in a SQL SELECT statement. They only belong in the UI, as the Controlsource of a control on a form or a report, or in code that is doing a single DLookup() for some logical purpose (e.g., you might use a DLookup() to see if you have existing records matching certain criteria). A DLookup() in a SELECT statement will likely execute once per each row returned (as long as it accepts row-based arguments, which is the usual scenario), and that's just way too inefficient. If the data you're looking for is accessible via data stored in the current records in your SELECT statement, then you can join the data and get to it directly, without needing to look up the values row-by-row. With a join, Jet will pull the indexes for the joined tables, and return in a batch the records that need to be joined. So, if you join two tables, you're likely pulling two sets of data in large batches. With a DLookup(), Jet pulls the set of data and then Access executes the DLookup() for each row of your dataset. By the time Access *starts* executing the first DLookup(), you could likely already have the full resultset with the joined version. -- David W. Fenton http://www.dfenton.com/ usenet at dfenton dot com http://www.dfenton.com/DFA/ |
#6
|
|||
|
|||
Row source Best practice??
On 30 Sep 2009 02:12:38 GMT, "David W. Fenton"
wrote: They only belong in the UI, as the Controlsource of a control on a form Another nice thing about a DLookup on a form is that they are asynchronous. That is, you can have one (or any function call, for that matter) as a column on a continuous form. You'll see the other columns appear, then a moment later (while you see "calculating" in the status bar) the calculated columns will appear. This means that you can navigate, double-click, etc. without waiting for the late-arriving values. The trade-off is that you can't sort on a Controlsource column. If you want to allow the user to sort on that column, you need to do it down in the query and try to make it go as fast as possible. Armen Stein Microsoft Access MVP www.JStreetTech.com |
#7
|
|||
|
|||
Row source Best practice??
David
ThankYou for the info regarding my post.. Orginization (or my lack of) is deffinately a good reason to keep SQL attached to what it is used for Barry "David W. Fenton" wrote: Allen Browne has largely provided fine answers to your questions, but I'd like to add a number of points. =?Utf-8?B?QmFycnkgQSZQ?= wrote in : 1. will all of these queries eventually cause me problems or do they really not matter?? It depends on your app. I used to make saved queries for everything, on the theory that they were more efficiently optimized. But since Jet saves optimization plans for recordsource and rowsource queries, there was never much advantage there. Where you have the same rowsource used on multiple forms, it does make sense to use a saved query for that as long as there's never any reason the instance of your combo box ever need to fork. I recently screwed up a combo box on one form that used a saved query that I edited for use in a combo box on a different form that needed an altered rowsource. I simply forgot that the changes I was making to the saved query were going to impact the other use of it. So, you really have to keep track of your usage of shared QueryDefs, because the sharing is a two-edged sword -- great when all uses are identical, problematic when there needs to be variation. In general, I don't create many shared queries any more. My most recent complex project has a grand total of one combo box saved QueryDef. That's about par for the course. 2. Are there design rules or methods to keep queries efficient. Saved or otherwise? In general, I use saved queries when I have a common central set of data that gets used in multiple locations. I then write an individual SQL string for each form/report/combo/listbox that uses that saved query as a source. This has worked quite well as it leaves the full control in the UI object, but still allows a common core datasource that is edited in one place. Efficient queries in general is a huge question and deserves a thread of its own, seems to me. 3. in a report is it better to just use the table as the reports record source with dlookups for related data or to use ALL related tables in a query in the reports datasource? As Allen said, DLookups are to be avoided. My philosophy of DLookups() is that they should never be used in a SQL SELECT statement. They only belong in the UI, as the Controlsource of a control on a form or a report, or in code that is doing a single DLookup() for some logical purpose (e.g., you might use a DLookup() to see if you have existing records matching certain criteria). A DLookup() in a SELECT statement will likely execute once per each row returned (as long as it accepts row-based arguments, which is the usual scenario), and that's just way too inefficient. If the data you're looking for is accessible via data stored in the current records in your SELECT statement, then you can join the data and get to it directly, without needing to look up the values row-by-row. With a join, Jet will pull the indexes for the joined tables, and return in a batch the records that need to be joined. So, if you join two tables, you're likely pulling two sets of data in large batches. With a DLookup(), Jet pulls the set of data and then Access executes the DLookup() for each row of your dataset. By the time Access *starts* executing the first DLookup(), you could likely already have the full resultset with the joined version. -- David W. Fenton http://www.dfenton.com/ usenet at dfenton dot com http://www.dfenton.com/DFA/ |
#8
|
|||
|
|||
Row source Best practice??
=?Utf-8?B?QmFycnkgQSZQ?= wrote
in : Orginization (or my lack of) is deffinately a good reason to keep SQL attached to what it is used for But that still means you're have a DRY problem, i.e., breaking the "Don't Repeat Yourself" rule. In certain situations, that can be a more serious drawback than the organizational problem. -- David W. Fenton http://www.dfenton.com/ usenet at dfenton dot com http://www.dfenton.com/DFA/ |
Thread Tools | |
Display Modes | |
|
|