Ryan
I'll jump back in for a re-hash.
When an Access database (i.e., tables) has slow queries, the first place I
look is at the indexing on the tables.
If the selection criteria, sorting columns, and join columns are not
indexed, Access will have to (internally) return all the rows to work with
them locally.
If the selection criteria are not applied until the very last step, Access
has to (internally) return all the rows to work with them.
If the query involves functions available in Access but NOT available in the
underlying data storage, Access has to (internally) return all the rows to
work with them.
Each of these provide potential speed ups. The first approach is to ensure
appropriate indexing. The second suggests putting selection early. The
third suggests putting off using internal Access functions until after all
other selection narrowing is done (if possible).
Best of luck!
Regards
Jeff Boyce
Microsoft Office/Access MVP
Ryan said:
I would love to run queries on the linked tables, but they just take way to
long. I have very complex expressions that need to be built into the
queries
and I cant even get sum and count totals to run smoothly. If someone
could
tell me way to make the linked table queries run without locking up access
and in a timely manner I would be forever indebted.
Jeff Boyce said:
Ryan
I confess to being a bit of a data bigot. I'd look for other solutions
than
replicating all the data in Access. But if the only way to get
acceptable
performance is to constantly re-synchronize/reload local tables from a
remote source, so be it!
(Hopefully other newsgroup readers have a more elegant solution!)
Regards & Good Luck!
Jeff Boyce
Microsoft Office/Access MVP
I have rebuilt every query and started over at least 10 times trying to
get
this to work. I have changed strategies. I ran a create table queary
on
all
6 queries, and now I'm working on update and append queries to keep the
tables in access current. When I run the queries in access it never
takes
longer than a few seconds. This seems like the best plan of attack
because
it will run faster and keep the connection to the AS400 open only long
enough
to run the update and append queries. Your suggestions on this
strategy.?.
:
Ryan
Rarely, but it's happened, I've had a query go "sour". Something is
wrong
somewhere, but I can't see it and Access doesn't fix it when I run
Compact &
Repair.
When a query just isn't getting the job done, I've been know to throw
it
away and start over with a brand new, never been touched query, built
up
one
small step at a time.
Can you create a new query that does what it should?
Regards
Jeff Boyce
Microsoft Office/Access MVP
Here is something interesting. I tried to run a find unmatched
query,
and
the field that is my PK ([MBRFAM#]) wasnt available. I deleted
every
field
in my query except that field and when I tried to build the find
unmatched
query again it told me "There are no fields available in this table
or
query
that are valid for a join. Select a talbe or query that contains at
least
one field that does not have the memo or OLE data type. The weird
thing
is
that the field is a numeric field. I have already tried the raw
data
queries
and the total and sum queries on top of them and had the same long
run
time
results. Please advise.
:
Ryan
One approach might be to create all the queries that return the
"raw"
records, then create new queries based on the first set that do the
totalling. It may be that trying to return rows AND total is
causing
Access
to download all the rows before totalling selected rows.
Regards
Jeff Boyce
Microsoft Office/Access MVP
I have narrowed the problem down to including totals in the query,
not
joins.
If I run any of my queries, joined or not, and do not include
totals,
then
the query runs very quickly, but as soon as I add totals it takes
forever.
I
have 10 very complex expressions to write so I have to have
totals.
Any
suggestions?
:
Ryan
If your multi-table query includes any Access-only functions, it
will
run
more slowly.
If your tables don't include indexing for your join fields, or
for
your
selection criteria fields, or for you sort-by fields, the query
will
run
more slowly.
It sounds like Access isn't able to optimize the query and so
must
resort
to
downloading all of the tables' rows to your PC before completing
the
query.
Can you create "views" in your AS/400 data set? If so, do the
joins
and
as
many of the selection and sorting criteria there as you can?
Then
link
to
the view.
Regards
Jeff Boyce
Microsoft Office/Access MVP
I am using Access 2003 through ODBC to link to an IBM AS/400.
If
I
query
only one table it takes about 15-20 seconds, but any joined or
sum
queries
can take up to 10 minutes and cause Access to temporarily not
respond.
Is
there a trick to making join or sum queries via ODBC or will
it
always
take a
long time to run?
