Importing definitions only makes query super SLOW!

[Robbie]

I setup a db system including forms and everything. I did all of my
debugging in the system, but now it's time to put the db to use. Wanting a
clean slate, I create a new access file and import everything from the
"prototype" database. I have about five tables for which I import the data
and definition; after importing those, I import all of the other tables using
just the definition. I also use make sure that in all cases I am importing
relationships. Finally, I import all other objects.

However, for some very strange reason, some of my queries drastically slow
down. In the prototype system the only took about 5 secs to run, now they
are taking over 15 minutes and they still haven't stopped! (Believe it or
not, most of the data is exactly the same, I re-entered it all, but did not
import it).

This particularly query is based on a query joined with a linked Oracle
table. I have tried importing the linked table from the prototype database
as well as just creating a new linked table. Neither way seems to work.

Does anyone know why importing only the definition might make my query run
so slow????

 

[MGFoster]

Robbie wrote:

Does anyone know why importing only the definition might make my query run
so slow????

You have to recreate the table's indexes.

 

[Robbie]

Thank you for the quick reply. Unfortunately, I checked the table indices
and they appear to be in tact.

Any other ideas?

Robbie

 

[Robbie]

I found the problem and it WAS related to indexes. However, it was the
opposite of what I would have expected. On import, an index was
automatically created for a table that didn't have one. To be honest, it
should have in the first place, it was just an oversight on my part. I took
of the index and presto, it is back to speed.

Why should taking off an index speed things up?

Robbie

 

[John Vinson]

Why should taking off an index speed things up?

Indexes speed data *retrieval* (because the program can use the index
to zero in on just the few records which need to be retrieved, rather
than examining every record in the table).

However, they slow data *updating* - because every record which is
added or changed requires that the index (or indexes!) must be updated
in addition to updating the table itself.

It's a tradeoff; you pay while adding data, or you pay while
retrieving it.

John W. Vinson[MVP]

 

[Robbie]

Thank you for your kind reply. I am aware of that fact, but interestingly,
the query is a select query. Any idea why adding an index slows down
retrieval?

 

[John Vinson]

Thank you for your kind reply. I am aware of that fact, but interestingly,
the query is a select query. Any idea why adding an index slows down
retrieval?

I missed the earlier parts of this discussion, so my answer may be way
off base. Could you post the SQL of the query and (re?)describe the
steps by which you're importing the data?

John W. Vinson[MVP]

 

[Robbie]

To make a long story short, I have a SELECT query that draws from 4 tables:
(1) a local table: ConceptQuestionCriteria, (2) a VIEW (that also draws from
local tables) : RaterQueue and (3) two tables linked to an Oracle database
over a low bandwidth (~10 Mbps) connection; both of the tables have an
EXTREMELY large number of rows: Concept and TextContent.

If I index the field that I am joining on from ConceptQuestionCriteria, the
select query is SLOW (I've let it run for 15 minutes before haveing to
force-quit Access from task manager). Presumably Access is inefficiently
drawing in most of the rows from the low-bandwidth linked tables.

Interestingly, when if I take off the index of the linked field in
ConceptQuestionCriteria, the query runs in < 5 secs (which is acceptable).

I realize that adding indices slows updates, but I simply can't explain
under what circumstances it slows down select queries.

Any insights?
Robbie

 

[John Vinson]

To make a long story short, I have a SELECT query that draws from 4 tables:
(1) a local table: ConceptQuestionCriteria, (2) a VIEW (that also draws from
local tables) : RaterQueue and (3) two tables linked to an Oracle database
over a low bandwidth (~10 Mbps) connection; both of the tables have an
EXTREMELY large number of rows: Concept and TextContent.

If I index the field that I am joining on from ConceptQuestionCriteria, the
select query is SLOW (I've let it run for 15 minutes before haveing to
force-quit Access from task manager). Presumably Access is inefficiently
drawing in most of the rows from the low-bandwidth linked tables.

Interestingly, when if I take off the index of the linked field in
ConceptQuestionCriteria, the query runs in < 5 secs (which is acceptable).

I realize that adding indices slows updates, but I simply can't explain
under what circumstances it slows down select queries.

Any chance that you could link to a selective Oracle View rather than
forcing Access to resolve the records on the Oracle host?

This is a pretty complex setup and I can see why it may be difficult
to optimize!

John W. Vinson[MVP]

 

[Robbie]

A fine idea indeed! At first I thought this was going to be impossible
because the pertinent clause that could limit the records depends on tables
local to the Access database; in other words, data that isn't in Oracle and
hence adding a VIEW would do no good.

But after thinking about it for a second, I realized that there are tricks I
could play to properly limit the number of records in an Oracle View.

Thanks for the tip!

Robbie