Oracle 9.2 attached table real slow and data incorrect with Access 2003 SP1

[AnandaSim]

Hi All,

I've had Access 97, 2000 connections to the corporate Oracle database
for a few years now - but seldom use it. When I did use it years ago,
performance was not fast but the features were good enough. Now, the
version of Access is 2003 with Office SP1 applied, Windows XP against
Oracle 9.2. And attached tables are real slow - 5 mins to get to
datasheet view and data is incorrect - the same row is repeated several
times even in the same screenful.

I've checked that ODBC tracing is off.

In comparison, I tried Toad/Oracle and that was speedy - 1 second or
less and I've tried Pass Through Queries in Access - same deal, very
fast.

How can I diagnose or fix the normal attached tables feature?

Thanks

[Tim Marshall]

AnandaSim wrote:

Hi All,

I've had Access 97, 2000 connections to the corporate Oracle database
for a few years now - but seldom use it. When I did use it years ago,
performance was not fast but the features were good enough. Now, the
version of Access is 2003 with Office SP1 applied, Windows XP against
Oracle 9.2. And attached tables are real slow - 5 mins to get to
datasheet view and data is incorrect - the same row is repeated several
times even in the same screenful.

I've checked that ODBC tracing is off.

In comparison, I tried Toad/Oracle and that was speedy - 1 second or
less and I've tried Pass Through Queries in Access - same deal, very
fast.

How can I diagnose or fix the normal attached tables feature?

I'm in a very similar situation, though I don't experience the slowness
you describe. This sounds as if the Oracle table(s) don't have
appropriate indexing and a primary key and/or you've chosen an
inappropriate primary key if the DSN dialog didn't pick one for you. In
the latter case, if the DSN dialog prompts you to choose a primary key,
the Oracle table in question does not have one. If the Oracle table
does not have a primary key specified, you can probably count on other
gaffs that affect performance as well.

Another possibility could be the number of records in your Oracle tables
- my experience is with 100s of thousands of records as opposed to
millions. I'm not sure how Jet would fare with the latter. Others,
like Rick, would know better.

I tend to do all my Access development against Oracle using pass through
queries for the most part, anyway. I find this gives better performance
and I only have to think in one SQL dialect. I use linked tables only
for quick administrative stuff or for large update projects where I'm
the only user. There's nothing wrong with using linked tables, it's
just my preference.
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me

[Demboos]

Tim Marshall wrote:

I tend to do all my Access development against Oracle using pass through queries for the most part, anyway. I find this gives better performance
and I only have to think in one SQL dialect. I use linked tables only
for quick administrative stuff or for large update projects where I'm
the only user. There's nothing wrong with using linked tables, it's
just my preference.

What are Your general impressions about using Access with Oracle to
store data? Compared to Jet...

In my company we have 3 access databases (still on Jet) each with 20
concurent users and the worst problem besides performance is that we
have to repair the db at least once a week.

We already have Oracle servers for other purposes in company so I'm
considering export the data from jet store to oracle.
--
Szymon Dembek

[AnandaSim]

Thanks muchly Tim.

Yes, ok, re-concile identification of Oracle PKs with Access Jet
interpretations. I'll give that a go. There are hundreds of Views in
the coporate db with millions of records (well, I never counted) - I
just want decent performance with one or less than 10 at a time. Some
of the Views of course don't display the PK but one can guess what they
are.

My test case should be pretty simple - it's a Person table so there is
only one PK.

BTW, I thought PK was only if Jet needed clear instructions on how to
update the table? I am barred (am happy to be barred) from updating,
insertion, deleting - I just want reliable retrieval of small subsets.

Tim Marshall wrote:

I'm in a very similar situation, though I don't experience the slowness
you describe. This sounds as if the Oracle table(s) don't have
appropriate indexing and a primary key and/or you've chosen an
inappropriate primary key if the DSN dialog didn't pick one for you. In
the latter case, if the DSN dialog prompts you to choose a primary key,
the Oracle table in question does not have one. If the Oracle table
does not have a primary key specified, you can probably count on other
gaffs that affect performance as well.

[AnandaSim]

Hi Szymon,

I would expect these newsgroups abound with anecdotes on upsizing. If
you don't experience my very severe Access -> Oracle problems, *AND*
your Access databases are having problems because they are too large,
the network is too slow then the way to go is to upsize to
client/server.

However, you / I cannot expect to just load the Access data into
Oracle, SQL Server, Sybase, mySQL, Ingres etc... and still expect to be
completely in our comfort zone or to keep the Access + Jet "update
semantics" (I think that is the jargon). Meaning in a typical Access
data bound Form, there are particular before-update, after-update
events handled by the Jet engine and Access Form architecture. When you
have an Access data bound form to an ODBC server, those things don't
work the same which means re-programming your Forms.

If you would use "pass through queries" and "ODBC Direct", then the
amount of rework increases even more.

On a separate matter, does anyone have tips and urls on getting started
with Sybase? I have downloaded Sybase EAServer Developer, installed it
fine, but now don't have any clue on how to build tables, work with
data.

Thanks

Anananda

Demboos wrote:

What are Your general impressions about using Access with Oracle to
store data? Compared to Jet...

In my company we have 3 access databases (still on Jet) each with 20
concurent users and the worst problem besides performance is that we
have to repair the db at least once a week.

We already have Oracle servers for other purposes in company so I'm
considering export the data from jet store to oracle.
--
Szymon Dembek

[Tim Marshall]

Demboos wrote:

What are Your general impressions about using Access with Oracle to
store data? Compared to Jet...
Hi Szymon,

I can't really comment from experience on this issue as the vast
majority of my experience is Access against Oracle databases. Where
I've used Jet in the past has been mainly with taking "snapshots" of
Oracle data - because of the complexities of the queries I needed to run
and because ODBC will tend to die with a lot of ODBC linked tables, I
used to have "copies" of the Oracle tables in Jet or, more usually,
tables that corresponded to queries against Oracle tables. I'd delete
the data and then run Jet Append queries.

With respect to what I said about ODBC dying, when I use a lot of linked
Oracle tables I get results, but at 5-8 tables or more, random columns
might be missing data, for example, or the query just takes an
impossibly long time to run. These could well be the result of the
issues I brought up with the OP, ie, imsufficient indexing of the Oracle
tables (they at least all have defined primary keys), but smilar to the
OP, I don't have the ability to change much of the Oracle tables.

So anyway, as I mentioned, my Jet experience is largely using
"snapshots" of our main application's Oracle data for reporting
purposes. Because of the problems I experienced with linked tables,
I've tended to go more to using pass through queries which alleviates
the issues I've mentioned above and others. But PTQs introduce a couple
of their own issues for application development. The major one is a PTQ
is read only, making development of a continuous form in which one
wishes users to change or add data a bit more involved - having to write
and execute SQL for an Oracle update or insert statement, rather than
just a simple docmd.runcommand acsaverecord (I may have that command
wrong)...
In my company we have 3 access databases (still on Jet) each with 20
concurent users and the worst problem besides performance is that we
have to repair the db at least once a week.
We also have Oracle running everywhere, so my practice has generally
been to have all my data (outside of the above mentioned major
application or organization uses) in Oracle, but use Access as a front
end client tool on individual client machines.
We already have Oracle servers for other purposes in company so I'm
considering export the data from jet store to oracle.

Hope this helps you some. To summarize, I don't have a lot of
experience with very large Jet data storage.

--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me

[Tim Marshall]

Demboos wrote:

What are Your general impressions about using Access with Oracle to
store data? Compared to Jet...

One other thing, vaguely related to your question...

I'm not bashing Access or MS here, but I am very disappointed that ADO
was not taken further. Before I went from A97 to A2003, I dreamed of
using DSNless connections. Unfortunately, for some reason, MS decided
to stop development of ADO recordsets to apply to reports which cripples
me with respect to the idea of DSNless stuff. They've geared a lot of
stuff to work directly with SQL Server, but not with ORacle (ADPs, I
believe - I could have that wrong, and maybe one or two other things I
see discussed on cdma a fair bit). I thus use DAO methods with Oracle
to create and store PTQs to act as record sources for reports, the major
thing I do with Access/Oracle.

--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me

[Tim Marshall]

AnandaSim wrote:

just want decent performance with one or less than 10 at a time. Some
of the Views of course don't display the PK but one can guess what they
are.
Views are a slightly different animal - I started out as you mentioned
(not doing updates or anything, just viewing data), but evolved into
performing those tasks in oracle and doing a fair bit of DDL (data
definition language - setting up tables and such) for appications meant
to supplement my reporting type activity. I know how views are
constructed, and have done some work in this, but I'm pretty sure you
can't set up a separate primary key for a view.

So what you're faced with is making sure that in the resultant view
there is a column that can "act as a PK", ie, there is only one
occurrance. Oracle views are analagous to stored querydefs in
Access/Jet and you have to be careful in selecting which item to use as
a unique identifier, depending on which table(s) data gets repeated.
Indeed, to use the view properly, it's vital that the designer of the
view when writing the Create Or Replace View statement in oracle SQL
does indeed select such a column to be included or you're euchred.
My test case should be pretty simple - it's a Person table so there is
only one PK.
Again, from my first post, hopefully that table *HAS* a PK. Do you have
access to Oracle Enterprise manager? If not, get it (I'm pretty sure
it's in the installation disks from which you install things like Net
Client and SQL Plus and is probably available from the Oracle site as
well) and use the schema adminstrator a lot - it'll help you find what
the PKs and other important table characteristics are.
BTW, I thought PK was only if Jet needed clear instructions on how to
update the table? I am barred (am happy to be barred) from updating,
insertion, deleting - I just want reliable retrieval of small subsets.

You know, I'm not 100% sure on that. I had always thought that ODBC
needed the PK to be able to properly bring the Oracle data into Jet. I
do know that in goofy tables I or my Oracle DBA have quickly set up
without a properly defined PK constraint, linked tables behave as you've
described.

--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me

[Demboos]

Tim Marshall wrote:

Demboos wrote:

What are Your general impressions about using Access with Oracle to
store data? Compared to Jet...

One other thing, vaguely related to your question...

I'm not bashing Access or MS here, but I am very disappointed that ADO
was not taken further. Before I went from A97 to A2003, I dreamed of
using DSNless connections. Unfortunately, for some reason, MS decided
to stop development of ADO recordsets to apply to reports which cripples
me with respect to the idea of DSNless stuff. They've geared a lot of
stuff to work directly with SQL Server, but not with ORacle (ADPs, I
believe - I could have that wrong, and maybe one or two other things I
see discussed on cdma a fair bit). I thus use DAO methods with Oracle
to create and store PTQs to act as record sources for reports, the major
thing I do with Access/Oracle.

Thanks a lot for those information. I'll probably spend some time on
evaluating access+oracle on some simple db and then decide if it is the
right way to go.

I've found an oracle migration tool for MS Access and will evaluate this
too.

Once again thanks!

--
Szymon Dembek

[AnandaSim]

Hi Tim,

Thanks muchly for the insights and by them, the encouragement.
I've resolved the problem. All the tables are pretty large and I am not
Oracle admin (far from it). All but one of the tables respond very well
to Jet/ODBC - fast "enough" and data is accurate. That one table,
dunno, it should be PK on PersonID in my opinion but Jet thinks it is
PK on OracleUsername. Hence aberrant data and very slow to the point of
timeout.

With Views, I can report that if you choose correct unique columns, the
View is fast for read only use.

So the problem is resolved - I switched to another table/view.

I used ADO in .asp webpages and .adp to sql server. I am sooo used to
DAO, I never liked ADO although it has many features. Now that ADO.NET
is the go, I use DAO in Office apps and ADO.NET when I do asp.net

One final note is to differentiate between ODBC and Jet. Jet does not
always have to be the middle layer and debugging / troubleshooting
procedures should take that into account.

Thanks again.

Ananda

Tim Marshall wrote:

So what you're faced with is making sure that in the resultant view
there is a column that can "act as a PK", ie, there is only one
occurrance. Oracle views are analagous to stored querydefs in