Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

DB Optimization Questions on reducing Table/Query count

Discussion in 'Information Technology' started by cefrancke@yahoo.com, Jul 28, 2006.

  1. cefrancke@yahoo.com

    cefrancke@yahoo.com
    Expand Collapse
    Guest

    I'm trying to do some optimizations on my Access DB.
    After reading on table/query counts, I decided to examine all queries
    and reuse common queries instead of re-creating the same query with
    another name.

    I have found a number of queries used in forms that could easily
    (apparently) be used in a corresponding report. A good thing, of
    course.

    Questions:
    1. Combo boxes:
    I read about using defined queries instead of SQL in combo boxes
    (Access compiles the query the first time the form is opened, as
    opposed to compiling every time the SQL statement is used. Please
    confirm)
    I assume refering directly to a table, would be efficient as well, for
    example in a simple combo lookup, where just the ID (bound column) and
    a description field (display list) are necessary.
    However, what if I want the description field to be sorted, should I
    create a query just for that, or use SQL or is there a way to have the
    table sort in the combo automatically on the display field?
    I have many different "pick box" combos where I need the display field
    sorted and this could imply that I may have to create a stored query
    for each combo, if this is most efficient.

    2. Reports
    Is it faster to allow the report controls/code do as many calculations
    as possible or let the
    underlying query do all the calculations? My intuition tells me to let
    the report do it if it can because the query will return filtered data
    and the report will do the calculations on the smaller filtered
    recordset, as opposed to the query doing calculations on all the data
    and the report filtering the results. I'm assuming there is some order
    to the query event, report filter event, and report calculation events.


    Thanks in advance for any insight.

    Christopher
     
  2. Loading...

    Similar Threads Forum Date
    Marriage And Discrimination Related Questions Blogs Mar 21, 2016
    Entering into Sikhism: Questions on Reht Maryada. Blogs Oct 22, 2015
    A Few Questions About Racism, Prejudice, Sikh, Sikhi, Sikhism Questions and Answers Mar 30, 2015
    Questions From A Non-sikh Questions and Answers Mar 23, 2015
    Questions... Sikhism And Saiva Siddhanta New to Sikhism Feb 8, 2015

  3. Douglas J. Steele

    Douglas J. Steele
    Expand Collapse
    Guest

    I don't think you'll notice any real difference with your combo boxes (and,
    for what it's worth, even if you put SQL there, Access is actually going to
    store a hidden query under the covers).

    I also don't think you'll notice significant difference with your reports
    either. Access returns the rows before it does the calculations, so your
    concern about how many function calls there will be isn't an issue.
    Personally, I like putting the calculations in the queries, because I can
    use the queries elsewhere if necessary.

    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no private e-mails, please)


    <cefrancke@yahoo.com> wrote in message
    news:1153831628.142908.155740@s13g2000cwa.googlegroups.com...
    > I'm trying to do some optimizations on my Access DB.
    > After reading on table/query counts, I decided to examine all queries
    > and reuse common queries instead of re-creating the same query with
    > another name.
    >
    > I have found a number of queries used in forms that could easily
    > (apparently) be used in a corresponding report. A good thing, of
    > course.
    >
    > Questions:
    > 1. Combo boxes:
    > I read about using defined queries instead of SQL in combo boxes
    > (Access compiles the query the first time the form is opened, as
    > opposed to compiling every time the SQL statement is used. Please
    > confirm)
    > I assume refering directly to a table, would be efficient as well, for
    > example in a simple combo lookup, where just the ID (bound column) and
    > a description field (display list) are necessary.
    > However, what if I want the description field to be sorted, should I
    > create a query just for that, or use SQL or is there a way to have the
    > table sort in the combo automatically on the display field?
    > I have many different "pick box" combos where I need the display field
    > sorted and this could imply that I may have to create a stored query
    > for each combo, if this is most efficient.
    >
    > 2. Reports
    > Is it faster to allow the report controls/code do as many calculations
    > as possible or let the
    > underlying query do all the calculations? My intuition tells me to let
    > the report do it if it can because the query will return filtered data
    > and the report will do the calculations on the smaller filtered
    > recordset, as opposed to the query doing calculations on all the data
    > and the report filtering the results. I'm assuming there is some order
    > to the query event, report filter event, and report calculation events.
    >
    >
    > Thanks in advance for any insight.
    >
    > Christopher
    >
     
  4. Larry Linson

    Larry Linson
    Expand Collapse
    Guest

    It's not always a good idea to "make the most use of Queries" by using the
    same query for a Form and a Report. On the surface, that seems to be A Good
    Idea because it will save the storage space for another Query, and because
    if you change the Query, both the Form and Report will automatically include
    the change. But, at some later point, if you modify the Query because of a
    change to the Form, and the Report suddenly has problems, you may think it
    was A Bad Idea.

    And, by the way, the storage space for an extra Query, with the larger size
    of Access since Access 2000 and the significantly lower cost of computer
    memory and disk is not much of an issue.

    The only issue is whether you are likely to make exactly the same changes on
    the Form and Report that share the Query, or whether you may make different
    changes... it's a trade-off that only you can make, based on your
    application and your environment. My personal practice is, generally, to
    use separate Queries for each object that has a Record Source, and spend a
    little extra time changing them if the objects _do_ both require the same
    changes. YMMV.

    Larry Linson
    Microsoft Access MVP


    <cefrancke@yahoo.com> wrote in message
    news:1153831628.142908.155740@s13g2000cwa.googlegroups.com...
    > I'm trying to do some optimizations on my Access DB.
    > After reading on table/query counts, I decided to examine all queries
    > and reuse common queries instead of re-creating the same query with
    > another name.
    >
    > I have found a number of queries used in forms that could easily
    > (apparently) be used in a corresponding report. A good thing, of
    > course.
    >
    > Questions:
    > 1. Combo boxes:
    > I read about using defined queries instead of SQL in combo boxes
    > (Access compiles the query the first time the form is opened, as
    > opposed to compiling every time the SQL statement is used. Please
    > confirm)
    > I assume refering directly to a table, would be efficient as well, for
    > example in a simple combo lookup, where just the ID (bound column) and
    > a description field (display list) are necessary.
    > However, what if I want the description field to be sorted, should I
    > create a query just for that, or use SQL or is there a way to have the
    > table sort in the combo automatically on the display field?
    > I have many different "pick box" combos where I need the display field
    > sorted and this could imply that I may have to create a stored query
    > for each combo, if this is most efficient.
    >
    > 2. Reports
    > Is it faster to allow the report controls/code do as many calculations
    > as possible or let the
    > underlying query do all the calculations? My intuition tells me to let
    > the report do it if it can because the query will return filtered data
    > and the report will do the calculations on the smaller filtered
    > recordset, as opposed to the query doing calculations on all the data
    > and the report filtering the results. I'm assuming there is some order
    > to the query event, report filter event, and report calculation events.
    >
    >
    > Thanks in advance for any insight.
    >
    > Christopher
    >
     

Share This Page