Solved

Access ODBC link effect on  system performance

Posted on 2007-04-04
6
217 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
The Ultimate Checklist to Optimize Your Website

Websites are getting bigger and complicated by the day. Video, images, custom fonts are all great for showcasing your product/service. But the price to pay in terms of reduced page load times and ultimately, decreased sales, can lead to some difficult decisions about what to cut.

 
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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …

726 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