Query Performance

[Wendy Parry]

I have a set of linked tables (OMNIS) in my DB, the queries running on 3
tables (1:m:1) are incredibly slow. I can't change the indexes as they
are externally linked tables is there any other way of speeding up
queries running like this. I especially have problems where I then have
to an aggregate function (Count).

Tables: Pupils - SetLink - Sets

Volumes are 800 pupils, 700 Sets, each pupil may belong to an average 8
sets.

Cheers

Wendy

 

[Tom Ellison]

Dear Wendy:

It may be you should bring a copy of the relevant rows and columns of the
external tables into your database, index this copy, and query that.

Tom Ellison

 

[Wendy Parry]

Tom Ellison wrote:
the only problem with that is that there are many updates that occur on
the external tables throughout the week, this would then cause latency
problems.

 

[Tom Ellison]

Dear Wendy:

What I'm suggesting is to do this locally at the moment the query is being
executed. There would be no latency in this. This is often the fastest way
to get it done. You might put together the steps to do this in a macro:

1. delete all rows from the local table.

2. transfer the desired rows and columns to a local table

3. add indexing (if this helps)

4. query using local table.

Tom Ellison

 

[Wendy Parry]

thanks Tom, yup I did consider this earlier but didn't want to admit
defeat...probably best to write a trigger/stored procedure that gets the
data at the beginning of the day.

Thanks