?
Solved

Access ODBC link effect on  system performance

Posted on 2007-04-04
6
Medium Priority
?
219 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 200 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
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
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 300 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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
This theoretical tutorial explains exceptions, reasons for exceptions, different categories of exception and exception hierarchy.
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…
Suggested Courses
Course of the Month11 days, 9 hours left to enroll

752 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