Solved

Access ODBC link effect on  system performance

Posted on 2007-04-04
6
214 Views
Last Modified: 2008-02-01
I have been working on a accounting system with web front end  which runs on Oracle database 4.5GB in size.  The web front end  restricts only 5000 lines of data to be exported to CSV from a query on the web tool. I set up an ODBC link through an access database linking ALL items table in the oracle database.  From this link table, i created a make table query in ACCESS to produce + filter the data set i need.  Coinciding with my actions,  users of the web tool reported the web front end was becoming v slow and unable toi use.

Q:Having created the ODBC link table- would this affect overall system preformance.  Slow it down?
Q:Opening the ODBC link table itself only - would slow system perfomance?
Q: ODBC has no effect?
0
Comment
Question by:yasanthax
6 Comments
 
LVL 9

Expert Comment

by:TheSloath
ID: 18849312
I think Server attached tables can be very inefficient.
I would use Passthrough queries.
0
 
LVL 10

Assisted Solution

by:Jaax
Jaax earned 50 total points
ID: 18849329
Are you using the JDBC-ODBC bridge supplied by Sun along with its JDK ?
Then it does affect the performance, as acknowledged here:
http://java.sun.com/products/jdbc/overview.html

It would be a good idea to use Type 2 or Type 4 drivers instead for your case.
0
 

Author Comment

by:yasanthax
ID: 18849701
Server attached tables inefficient?  Is that for running, or slowing down the Database system to all users who use it as a whole?
My Bridge is from Windows>control panel>Data Sourec (ODBC)>[Driver Oracle in Orahome92]
suppose same as  a ping connection
Database on Sun/PC etc Unknown.  All i know is located somewhere in Germany.

Sorry i dont sound as technical as those who replied to my question.
All i need to know can a ODBC link, slow down the whole database environment and other users using it. (Not how efficient it works for me as its a temporary tool)

0
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

 
LVL 10

Expert Comment

by:Jaax
ID: 18850477
ODBC access does slow down than using the client API of the DB as the query needs to pass two layers of transformation. Moreover, you are using a ODBC link, that will even slow down.
In case of DB LInks, it is only the peer tables access that is affected. This is in addition to the penalty of using ODBC itself.

But the question is weather the speed is acceptable even after performance depreciation - probably compensated by superior hardware configurations.

Here are two links that may point to the potential causes, other than the links themselves and tips to fine tune the DB

http://www.windowsitpro.com/Articles/ArticleID/14050/14050.html
http://www.thescripts.com/forum/thread198664.html
0
 
LVL 30

Expert Comment

by:Mayank S
ID: 18861282
To add to it, Access is not really a production database so you should look at something like Oracle/ SQL Server/ MySQL etc to store the data later, and they have their own JDBC drivers from the vendors, so you do not need to use ODBC
0
 
LVL 30

Accepted Solution

by:
Mayank S earned 75 total points
ID: 18861289
>> Q:Having created the ODBC link table- would this affect overall system preformance.  Slow it down?

True.

>> Q:Opening the ODBC link table itself only - would slow system perfomance?

True.

>> Q: ODBC has no effect?

False.

Jaax's second comment is good in explanation
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question