UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Access With Oracle Back End: Basic Query Optimization    
 
   
mempie
post Jan 20 2020, 01:57 PM
Post#1



Posts: 270
Joined: 27-September 01



Hello. I have developed small Access databases for years. I'm about to get my hands on my first Access database with an Oracle back end. It was built in Access 365 by a self-taught Oracle DBA who wasn't an Access expert. The managers are thinking of migrating the Access front end to APEX. That might be a good thing to do, and it gives me a chance to learn a new tool. But I wonder about their reasons. I can probably improve their Access application much faster than I can learn APEX and recreate their application.

The impetus for the change is an import process that is becoming very slow as data volumes increase. Big Excel files (exported from another Oracle database, I think) are being imported into the Access front end using a VBA procedure, and then put in the back end tables. This process is taking hours. I wonder if they shouldn't have made a PL/SQL procedure to suck the data straight to the Oracle database. However, the other programmer says they want to get this process out of this application anyway, and plans to write a Perl script to do the import. First question: What is the right way to do this import? Maybe I can do something in Access or in PL/SQL (which I also need to learn).

The application has lots of Access queries that are used as reports, and probably exported to Excel. From what I hear, these queries are not taking too long. (I don't know yet how long is "not too long") The managers, however, are concerned that as the tables and the result sets grow, these queries will become slow. They don't expect Access to handle it. Second question: will these Access queries slow down as they expect? Should I be replacing them with pass-through queries? If I make Oracle views, can I connect to those from Access? Should I do that instead? What's the right way to query a big Oracle database from Access? Bear in mind that users are probably providing search options in a form (such as date ranges) and if they aren't, they should be. A number of these queries are probably similar, maybe with different WHERE conditions, and can be consolidated.

Third question: this application is connected via ODBC, I think. Another thing I don't know much about is the distinction between ODBC and OLE DB, and what is available for use with Oracle. Is there an alternative to ODBC that might be faster?

Thank you.
Go to the top of the page
 
Jeff B.
post Jan 20 2020, 02:17 PM
Post#2


UtterAccess VIP
Posts: 10,379
Joined: 30-April 10
From: Pacific NorthWet


Just one person's experience …

When I was … "invited" … to migrate Access applications to SQL Server, I first pointed out that SQL Server holds data nicely, but didn't have a particularly user-friendly front-end. … so the front-end stayed in Access and I migrated the data to SQL Server. … and the applications performed … inadequately.

What I found was that Access front-end to Access back-end allows certain front-end structures that perform acceptably, but incur latency issues when working over a network against SQL Server data. So I modified the front-ends to better use SQL Server back-end data. … and I had to pay better attention to the use of indices to help speed queries. … and I had to make sure that I wasn't trying to use Access-specific functions against SQL Server data. In the end, apps worked faster and the data was more secure. It didn't happened automagically, though.

Good luck!

P.S. if the data is loading slowing into the back-end, perhaps the indexes are firing as each piece of data is added. That would run very slowly. If you (temporarily) shut off the indexes, data would probably load faster … but you risk the kinds of errors that indexing prevents!

--------------------
Regards

Jeff Boyce
Microsoft Access MVP (2002-2015)

Mention of hardware or software is, in no way, an endorsement thereof. The FTC of the USA made this disclaimer necessary/possible.
Go to the top of the page
 
GroverParkGeorge
post Jan 20 2020, 02:39 PM
Post#3


UA Admin
Posts: 36,775
Joined: 20-June 02
From: Newcastle, WA


Is this the Apex to which you refer?

If so, I have to question whether it's a viable alternative to MS Access. If they means something else, of course, that would be different.

Like Jeff, I quickly learned that although you CAN unplug ACE and plug in a different RSBMS for your database, it's going to take more than that to get good results.

Assuming that you retain the Access/Oracle configuration, I would push processing as much as possible back into stored procedures and views on the server.

It is, indeed, probably much more efficient to set up data transfers between two Oracle databases rather than pushing all that data down into Excel, then into Access, then back up again. If you want to move a trainload of coal from a mine to the factory, you don't offload it into trucks to haul it most of the distance.

You might set up Access to manage that process--but it's probably better to get a good DBA involved in order to set up an efficient process between Oracle databases.

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
nvogel
post Jan 20 2020, 02:49 PM
Post#4



Posts: 1,061
Joined: 26-January 14
From: London, UK


George, APEX in this case surely means Oracle Application Express:
https://apex.oracle.com/en/

APEX is an application development tool intended as a browser and client-server replacement for Access or similar applications.
https://docs.oracle.com/cd/E59726_01/doc.50...gr.htm#AEMIG105

The data import ETL part probably would sit better in PL/SQL or Perl but APEX is a decent solution for building an application over an Oracle database.
Go to the top of the page
 
GroverParkGeorge
post Jan 20 2020, 03:01 PM
Post#5


UA Admin
Posts: 36,775
Joined: 20-June 02
From: Newcastle, WA


thumbup.gif Much more logical. Thanks.

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
mempie
post Jan 20 2020, 03:06 PM
Post#6



Posts: 270
Joined: 27-September 01



Not that Apex. Oracle Application Express is the tool that interests the managers. This is an Oracle shop, with a server, site licenses, a group of Oracle programmers - in short, an existing capability. Oracle APEX builds on that capability, so it's probably strategic for them in the long run. I think I'm the only Access programmer. I'm thinking about what I'll immediately do when I finally get an account and permissions on the system. I also want to understand the various problems. If I can make it work much better soon, we can take our time rebuilding the whole thing in APEX, if they still want to.

Another programmer may, or may not, take care of the import process that is bogging down. I'd like to get going on learning PL/SQL anyhow. Learning Perl is something I might do some other day. It might not matter, but if I make a PL/SQL stored procedure to do the import, can I launch it from the Access application? If so, how?

I don't know what indexing is done in the front end tables that temporarily hold the imported data, or what validation is done. I'll check it right away when I finally have the code.

I assume that the two databases are on different, disconnected servers. I don't know how easy it would be to persuade the owners of the other database to export something different, if we needed it.

I figured I'd be replacing the Access queries with either SQL pass-through queries or Oracle views. If I choose the latter, can the ODBC driver connect to those views as if they were tables? What are the pros and cons of each?

The application has a simple interface. I think there is some user data entry, but not at a high volume. The bulk of the data is imported.
Go to the top of the page
 
GroverParkGeorge
post Jan 20 2020, 04:13 PM
Post#7


UA Admin
Posts: 36,775
Joined: 20-June 02
From: Newcastle, WA


I worked in a reporting department a few years ago. Data was stored in an oracle data warehouse. We pulled data into access from the DW to create reports. In that situation, we simply used ODBC Links to the oracle tables and views. IIRC we used a nonMicrosoft ODBC Driver.

It could be slow. However, because we only extracted data for reports it was not horrible.

The DW was rebuilt every night. That took hours. However that meant our reporting was faster. That transfer was managed by the DBAs. The DBAs can become your best friends Or your worst enemies

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
FrankRuperto
post Jan 20 2020, 06:12 PM
Post#8



Posts: 652
Joined: 21-September 14
From: (MilitaryBrat) Tampa Bay, Florida, USA


Hi mempie,

QUOTE
It was built in Access 365 by a self-taught Oracle DBA who wasn't an Access expert. The managers are thinking of migrating the Access front end to APEX. That might be a good thing to do, and it gives me a chance to learn a new tool.


Your IT shop is obviously experienced with Oracle and APEX is a mature Oracle-based RAD tool that runs on any web browsers and any device, so its the logical choice. I would embrace it if I were you. The feeling about Access by most corporate IT departments is that it's ancient tech, desktop only, and not secure.

--------------------
Currently supporting pawnbrokers that use my store management system developed with Access 2010 on Windows7. Experienced with Informix and Oracle DB's.
Go to the top of the page
 
PhilS
post Jan 21 2020, 05:47 PM
Post#9



Posts: 679
Joined: 26-May 15
From: The middle of Germany


QUOTE
What is the right way to do this import?

If the files for the import are huge, use Oracle's SQL*Loader. If there is complex processing involved, import to a temp table first an then write to the real tables using PL/SQL.

QUOTE
If I make Oracle views, can I connect to those from Access? Should I do that instead?

Yes, yes.

QUOTE
What's the right way to query a big Oracle database from Access?

Create a Oracle view, link the view to Access and then query the view. ;-)

QUOTE
Is there an alternative to ODBC that might be faster?

No.
PS/Edit: Use Oracle's own ODBC Driver, not Microsoft's!
This post has been edited by PhilS: Jan 21 2020, 05:49 PM

--------------------
A professional Access developer tool: Find and Replace for Access and VBA
Go to the top of the page
 
mempie
post Jan 22 2020, 05:20 PM
Post#10



Posts: 270
Joined: 27-September 01



1. Does an Oracle view present as another attached table in Access? If not, how will I find it?
2. If a user enters filter values, such as a date range or the name of a group of data, in a form, I typically use the values to build a WHERE condition, applied to some basic query or table. If the recordsource is an Oracle view, do I (a) apply the WHERE condition to the view, just the same as if it were a local Access table, or (b) make the WHERE condition part of a SQL pass-through, or © somehow get the WHERE condition directly into the back end and apply it to the view with PL/SQL or something?
Go to the top of the page
 
FrankRuperto
post Jan 22 2020, 06:43 PM
Post#11



Posts: 652
Joined: 21-September 14
From: (MilitaryBrat) Tampa Bay, Florida, USA


QUOTE
1. Does an Oracle view present as another attached table in Access? If not, how will I find it?


Yes, you create the view in Oracle using the SQLDeveloper management tool. This view appears in Access table design view as any other ODBC linked table, (see images)

and to answer your #2, your view can consist of a query with multiple filters. Views are usually flat file representations of multiple fields from different tables. The view can be whatever you want it to be according to the query.

However, keep an open mind about developing the app with APEX.
This post has been edited by FrankRuperto: Jan 22 2020, 07:31 PM
Attached File(s)
Attached File  SampleViewCreation.PNG ( 54.14K )Number of downloads: 13
Attached File  SampleViewInAccess.png ( 126.45K )Number of downloads: 14
Attached File  SQLdeveloper.png ( 226.07K )Number of downloads: 13
 

--------------------
Currently supporting pawnbrokers that use my store management system developed with Access 2010 on Windows7. Experienced with Informix and Oracle DB's.
Go to the top of the page
 
FrankRuperto
post Jan 23 2020, 12:05 AM
Post#12



Posts: 652
Joined: 21-September 14
From: (MilitaryBrat) Tampa Bay, Florida, USA


To clarify answer to #2, your Access reports are going to build dynamic pass-through queries that will execute in Oracle against the view(s) you create there. I suggest you make use of temp tables in Access reports and forms so that every time a user launches the app a fresh copy of the master Access frontend is loaded.

The sample images in my previous post was with Oracle 11g. The Oracle DBA has to create a user account for you and grant you a system privilege to create views on the tables you need for the views, otherwise DBA will have to create the views for you. Each view that's created must also grant your user account select permission in order to query the view, example:

CODE
GRANT SELECT ON TableSpaceName.ViewName TO YourUserAccount;


These same above priviliges apply even if you create your own db schema (a.k.a TABLESPACE) and load in the data from the production system.

As is the case with complex queries, sometimes its necessary to create stacked queries in order to, for example, eliminate duplicate results. By the same token, you can create a view of another view.

Here's the Oracle docs on creating views: https://docs.oracle.com/cd/B19306_01/server...ements_8004.htm

Have fun!
This post has been edited by FrankRuperto: Jan 23 2020, 12:30 AM

--------------------
Currently supporting pawnbrokers that use my store management system developed with Access 2010 on Windows7. Experienced with Informix and Oracle DB's.
Go to the top of the page
 
mempie
post Jan 24 2020, 02:05 PM
Post#13



Posts: 270
Joined: 27-September 01



One of you said that you "modified the front-ends to better use SQL Server back-end data". What kind of changes would that be?
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    22nd February 2020 - 09:17 PM