Solved

Access ODBC link effect on  system performance

Posted on 2007-04-04
6
209 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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
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:mayankeagle
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:
mayankeagle 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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

708 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

18 Experts available now in Live!

Get 1:1 Help Now