Re: Very slow query

Hi Tina!

Unfortunately, that didn't help either.

Thanks for all the help provided. I will soon start a new thread.

Happy New Year!

Alain T.


"tina" <nospam@xxxxxxxxxxx> a écrit dans le message de news:
kvnlh.604983$QZ1.171497@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
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: 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 forms, base the form on the primary data table, and use combobox or ...
    (microsoft.public.access.queries)
  • Re: problem using identity column as primary key
    ... >> I am thinking of creating an identity column to use it as primary key ... More and more programmers who have absolutely no database training are ... the gap in the sequence is not filled in and the sequence ... vin CHARNOT NULL REFERENCES Motorpool); ...
    (microsoft.public.sqlserver.programming)
  • 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: How do I add an auto primary key to data imported on a regular
    ... database and recompile my specific data into the reports I need so that I can ... it into an existing table that has all the fields except the primary key. ... Use an Append query to copy the data into your production table that has the ...
    (microsoft.public.access.externaldata)
  • 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)
Quantcast