Slow Query response using Access

[agfon]

I'm in the process on converting my access backend over to ORACLE. We're continuing to use access as a frontend interface.

The conversion went well and opening the ORACLE tables is immensely more quick. However, when we attempt to create queries in access using the linked tables, the response time is very slow.

We've looked into the indexing on the surface and that doesn't appear to be an issue.

Also, we've looked into creating views, but these don't appear to speed up the query response time.

Does anyone have a suggestion of where to look next.

Thanks.


-agfon

 

[cmmrfrds]

Linked tables are going to be slow in Access where there are large data volume since the criteria is applied on the client side. To overcome this use "pass-thru" queries in Access, which is a query in Oracle syntax using a where clause to limit the data being returned to Access.

 

[agfon]

cmmrfrds,

Actually, I've tried testing for that. I removed all of the criteria and it's still taking 5 minutes + to run a query that takes 8 seconds with the Access backend.

It seems that when I open each subquery individually, everything runs quickly. However, when I link them together, the query drags.



-agfon

 

[cmmrfrds]

When you say open a subquery individually, does this join tables that are linked to Oracle? Does it do any aggregation in these subqueries? If you are not doing aggregation is the subquery you will see data quickly, but try paging to the end of the dataset and denote the response time. Is this fast?

You need to apply criteria not remove criteria to limit the dataset size.

 

[agfon]

I understand that criteria limits the results. Your original response suggested that I use a pass-through query. I removed my criteria to determine if the response time was any worse -- it wasn't.

The sub-queries do join tables on the ORACLE backend. Then, the various sub-queries are joined. I'm not sure what you mean by "aggregation of subqueries".

-agfon

 

[cmmrfrds]

Aggregation is done when the Select list has a Sum, Max, or other aggregate function, or does a Order by, Group by which are things that cause the data be be aggregated before it can be returned to the client - ie. Access. In a pass-thru query this can be done on the server side otherwise all the data needs to be returned to Access to do the aggregations.

How is the response time when you page to the end of the subquery data?

 

[dbtoo2001]

Did you try adjusting the array size in the odbc connection?