Solved

Access ODBC link effect on  system performance

Posted on 2007-04-04
6
211 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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
Basic understanding on "OO- Object Orientation" is needed for designing a logical solution to solve a problem. Basic OOAD is a prerequisite for a coder to ensure that they follow the basic design of OO. This would help developers to understand the b…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
The viewer will learn how to implement Singleton Design Pattern in Java.

867 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now