Re: Very slow query

i saw your additional post re #6 below; that's good, you *do* need to set
relationships between all those parent (secondary) tables, and the child
(primary data) table - so good job there.

7. make sure that table fields that should be indexed, *are*, and fields
that don't need an index, *are not*. (only one field in the main table is
indexed for all the secondary tables, only the primary key is indexed).

i'm not following this one. are you saying that only the primary key field
in the main table is indexed? if so, try indexing the foreign key fields
(that link to those other tables) in the main table, as well.

if the above suggestions yields no improved results, then it'll be time for
you to begin a new thread, as i previously suggested you might need to do.

hth


"Alain T." <thibault.alain@xxxxxxxxx> wrote in message
news:uLD0YH4KHHA.4848@xxxxxxxxxxxxxxxxxxxxxxx
Hi Tina!

I followed your instructions but unfortunately, I still get the speed
problem. What I have done precisely :

1. create a new blank database and immediately turn OFF the Name
Autocorrect
option
2. import all objects from my current database into the new one I just
created.
3. compact the database.
4. remove all the Lookup fields in my main table, as you described (open
the
table in Design view, click on the field I want to change, then
click on the Lookup tab at the bottom and change the DisplayControl
setting
to Text Box). Done that on 21 fields of the main table.
5. compact the database
6. make sure my tables' relationships are correctly set up and
referential
integrity enforced, at the table level, using the Relationships
window (didn't have to do that as I have only one main table. All the
others are for the links).
7. make sure that table fields that should be indexed, *are*, and fields
that don't need an index, *are not*. (only one field in the main table is
indexed for all the secondary tables, only the primary key is indexed).
8) in a query, add the "secondary" tables that are needed so that I can
show
the "text value" fields associated with the key values stored in the
primary
data table(s).

With 14 secondary tables or less, my query runs in 1 sec or less.
With 15 " ", it takes about 2 sec.
With 16 " ", it takes about 5 sec.
With 17 " ", it takes about 20 sec.
With 18 " ", it takes about 1 minute 20 sec.
I didn't take the risk with more than that...

I didn't do yet the next steps as I am still blocked at eigth..
9) in reports, base the report on the type of query described above
(ususally).
10) in forms, base the form on the primary data table, and use combobox or
listbox controls with RowSource property set to the appropriate
secondary table, so that the user has access to the "text values" stored
in
the secondary table.

Did I misunderstood something? By the way, in the query, when I added a
secondary table, it linked automatically on the good field from the main
table. And this was done perfectly for all the 21 tables. How was it
able
to do that as I removed all the lookup fields from the main table before
that?

Thanks!

Alain T.

"tina" <nospam@xxxxxxxxxxx> a écrit dans le message de news:
h%Hkh.591156$QZ1.395476@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
you're welcome, and good luck! :)
if you don't get the results you were hoping for after following the
suggestions i posted, don't give up. start a new thread in this
newsgroup,
explain the problem, *and* include the steps you've taken to try to
improve
the situation. someone else may be able to help at that point.

hth




.

Relevant Pages

  • Re: Query to merge
    ... Thanks for that insite - I am sure that the additional customers (that would ... simply build your query to give you all records from the one, ... Say it was a customer database. ... customers with the same primary key. ...
    (microsoft.public.access.queries)
  • Re: Is this bad design?
    ... application which is making heavy use of an Access database. ... design but there might be good reasons for doing things this ... Tables with a generated primary key i.e. an id where a number ... Relations can be created on the fly in the query builder and/or ...
    (comp.databases.ms-access)
  • Re: Very slow query
    ... indexed for all the secondary tables, only the primary key is indexed). ... create a new blank database and immediately turn OFF the Name ... With 14 secondary tables or less, my query runs in 1 sec or less. ... in reports, base the report on the type of query described above ...
    (microsoft.public.access.queries)
  • Re: Other options beside seek
    ... inserts into the database I am using a simple query returning the ... based upon the primary key fieldso that I do not have to change ... the index back and forth and then from my query results I can either ... I know that the RecNo property ...
    (borland.public.delphi.database.ado)
  • Re: Two Different Queries used in Report?
    ... Make a new query, bring the two initial queries. ... is associated by a primary key (both queries have the primary key ... I only get the data that corresponds to the primary data, ...
    (microsoft.public.access.queries)
Quantcast