Slow Response to New and Delete operations

[nexus]

We're running through an Access front end against a large Oracle database. Lately, there's been a slowdown in the Access application when we create new records or delete old records. However, if we run the operations (create or delete) directly through the Oracle database, it takes much less time. Any suggestions on what could cause this? We're assuming it must have something to do with Access and how it's handling the SQL or something, but we're not sure how to debug it. TIA.<br>
<br>

 

[rhicks]

It may be a case of Access becoming fragmented from the deleting process. Access will not recover the space the deleted records occupied until you compact the database, thus making the program very sluggish and slow. You will need to compact the database on a regular basis to overcome this problem. However this may not be your problem.<br>
<br>
Good Luck<br>
RDH

 

[culshaja]

I have this problem all the time. It is a problem with the ODBC interfaceI think. It may be that Access tries to lock all the records before it does any work. Try a simple VB front end using OO4O which is native to Oracle - its faster than ADO I believe. It also more reliable and more powerful when it comes to Oracle databases, although you can only use it with Oracle databases.<br>
<br>
James

 

[DougP]

Yes culshaja ODBC is slow by nature.<br>
It is a translator.<br>
One suggestion is to use 100Base-T network components<br>
Get the fastest client CPU your budget can handle.<br>
Access is one of those programs that require the full Pentium II 0r III not a Celeron or AMD.<br>
Pentium III 500 with 128 RAM will be fine. <br>
Office '97 was designed to run on NT workstation. So if upgrading to NT is in the cards so much the better.<br>
<br>
See an Access database is dragged accrosss the network when it is loaded. It's not Client/Server like Oracle is.<br>
so everytime you do anything to update it, it will be slow. i.e. add records etc.<br>
<br>

 

[Zelandakh]

If you have an Access front end, stick it on your hard disk to speed up access times. Put SP2 on, repair and compact regularly and do as much of what Doug suggests as finances allow.

 

[culshaja]

Why pay a fortune when it is much simpler to build a simple VB front end and use OO4O. It is incredibly simple to use oo4O and will always give you better performance than Access and ODBC and will allow native ORACLE access rather than a cut down version. Better spend a week or so developing an VB app than spending all that money on hardware. That is a bit overkill. It might only take a day to do what you want. Hardware isn't always the answer to application speed problems, its too easy to just upgrade these days and mask problems rather than solve the fundamental problems in your application.<br>
<br>
James

 

[flk]

I'm new to Tek-Tips and you probably have already worked through this issue. For the sake of offering my two cents worth, here goes. I'm an Oracle DBA and my company has been building MS Access databases to handle smaller tasks not suited to a formal development effort. I suggested they try using Access as a front end to Oracle to see if it would allieviate many of the maintenance headaches they were having in Access. We hit the same problems mentioned here and worked around them by building a &quot;copy&quot; of the table in Oracle for any table/column we wanted to add records to and then had an Oracle Trigger manage the actual insertion/deletion from the main table(s). In this way, access is only dealing with 1 record and performs almost instantly.