improving speed of queries of linked tables

[dpimental]

I have a number of databases that have back-end table to both access and oracle.

The oracle tables seem very slow, especially in larger queries. I am using the 10g driver for oracle.

Does anyone have any ideas about speeding up the connection or other ways of increasing the performance of the oracle tables?

David Pimental
(US, Oh)

 

[dhookom]

I haven't queried Oracle data for a number of years. I believe we had performance issues until we used the Microsoft drivers for Oracle. If your queries are limited to read-only Oracle data, you should be using pass-through queries.

I found significant performance improvement when I could use pass-through queries to populate temporary, local Access tables.

Duane
Hook'D on Access
MS Access MVP

 

[dpimental]

Yeah, unfortunately, the issues that I am having are with data entry on a form. Any other ideas?

David Pimental
(US, Oh)

 

[dhookom]

Did you try the Microsoft Drivers for Oracle or not?

Are you opening forms that are filter to one record?

Duane
Hook'D on Access
MS Access MVP

 

[dpimental]

I am using the oracle driver (10g) and no, I am not filtering the records.

David Pimental
(US, Oh)

 

[dhookom]

When I asked those questions, it was a hint that you should try them not just reply with a yes/no answer.

Duane
Hook'D on Access
MS Access MVP

 

[dpimental]

the microsoft driver is slower than the oracle driver. So, I am not going to switch. I have tested both. the microsoft driver also has other issues.

I can't use pass-through queries on updates and inserts.

It is not like I haven't tried this stuff before.

Thanks for the suggestions though.

David Pimental
(US, Oh)

 

[dhookom]

It is not like I haven't tried this stuff before.

I suppose you have open forms based on a very limited number of records also.

You might try to begin a thread with what you have tried so we don't waste our time.

Duane
Hook'D on Access
MS Access MVP

 

[jsteph]

dpimental,
Why can't you use PT queries on updates and inserts? I've done many mdbs that do this using unbound forms. dhookom's suggestion of using a temp Jet table will work on this as well.

In my opinion, when using Access as a front-end to a sql db, it's best to use the web paradigm--look at the forms like you might an html form--fetch/load the data, then on an update or insert button, scrape the screen, create the pt sql and do the update/insert.

The only place I'd be using directly linked tables is in a 'browse' form, where one chooses the data to edit or insert.

If you're editing or inserting multiple records, ie, in a continuous form or datasheet (like subform data), then the local jet table is easier to implement but it's still doable in unbound form--it's just more of a pain.

Bottom line--in my humble opinion it's best to look towards breaking away from the comfort of Access' form/subform model and look to doing as much as possible on the server and treating the UI as unbound. This makes the skillset very portable to web development as well.
--Jim

 

[dpimental]

Guys, thanks for the input. I certainly appreciate the experience and wisdom. I will review with my manager, as it is his call.

Thanks Again.

David Pimental
(US, Oh)