Redesigning MS Access + Oracle app - advices sought

Hello

I will need some help in redesigning an application I have written in Acess to work with data in an Oracle database.

The app is working fine but we start to have some performance issues due to the fact that I am doing a big part of the business logic in Access (vs on the oracle server).

In practice I am aggregating / sorting / processing data in access based on some very raw views existing in Oracle.

I am now planning to "move back" this processing server side, as it should most likely yield some significant performances advantages. So I guess it is time for PL/SQL 101…

I guess what I am looking at is stored procedures ? Mostly what I need is performed via tacked SELECT queries and some "inline" access variables. I'm pretty sure this can be done in PL/SQL too. Is it possible to pass parameters to views for Access to Oracle ? To have "global variables" (ie persisting across calls).

Here is a typical query I am doing at the moment
SELECT 1 AS NO_UNIQUE_REGROUPEMENT, fnRefDate()+1 AS DATE_TRANSACTION, 2009 AS Annee, 9999 AS TYPE_TRANSACTION, 0 AS NO_COMPTE, "Initial" AS NATURE_TRANSACTION, ("Report from " & Format(fnRefDate()+1,"dd\.mm\.yyyy")) AS LIBELLE_COURT, "" AS LIBELLE_LONG, "###" AS NO_POLICE_LIEE, "###" AS NO_POLICE_LIEE_CLEAN, "###" AS BRANCHE, Sum(qTransactions.MONT_TRANS) AS MONT_TRANS, qTransactions.NO_ADRESSE, qTransactions.NOM_TIERS, "###" AS ASSUREUR, 2 AS DATE_TRANSACTION_NUM
FROM qTransactions
WHERE (((qTransactions.[DATE_REF])<=fnRefDate()))
GROUP BY qTransactions.NO_ADRESSE, qtransactions.NOM_TIERS;

Open in new window


Can something like this be recreated easily ? Note the use of custom functions (fnRefDate()) and "on the fly" calculations (Sum).

Any feedback / suggestion most welcome
LVL 2
Alexandre TakacsCTOAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

johnsoneSenior Oracle DBACommented:
Can it be done?  Most likely.  Without the details of fnRefDate, I'm not sure what it is trying to accomplish.

SUM is a standard aggregation function.  No change should be necessary for that.
awking00Information Technology SpecialistCommented:
Can you provide us with some sample data of a view that exists in Oracle and what the data looks like in Access after it has been processed? A description of the custom function, fnRefDate() could also help.
PatHartmanCommented:
Access attempts to pass through all queries and have the server return only the requested rows but some things prevent that.  One of them is the use of VBA or UDF functions in a where clause.  Since the function cannot be sent to the server, Access requests all the rows (after applying the remainder of the join and where clause) and then applies the function locally.  So, to improve this process, you will need to create a stored procedure on the server to supply the argument that your function is supplying.
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

Alexandre TakacsCTOAuthor Commented:
Thanks for the comments so far,
fnRefDate()  is simply a way to have a "static variable" to be used in the queries - ie I request some date value via the UI and put it into a VBA static variable - that function simply returns that value when performing the query. Don't know if this is the "cleanest" way to do it but it works so far. I guess I will have to define a global variable in PL/SQL or more likely to actually store the value in a temporary table and reference it in my query ?

I'm starting to rebuild my queries and here is one giving me some difficulties to put into Oracle syntax - any suggestions welcome !

SELECT TR.NO_UNIQUE_LIGNE, TR.NO_UNIQUE_REGROUPEMENT, IIf(TR.DATE_SAISIE>#4/1/2009#,IIf([TR.DATE_TRANSACTION]<>[TR.DATE_SAISiE],[TR.DATE_SAISIE],[TR.DATE_TRANSACTION]),TR.DATE_TRANSACTION) AS DATE_REF, TR.SOCIETE, TR.ANNEE_EXERCICE, Choose(([COMPTE_MVT_DEBIT]="")+2,"CRED","DEB") AS DC_FLAG, Choose(([COMPTE_MVT_DEBIT]="")+2,[COMPTE_MVT_CREDIT],[COMPTE_MVT_DEBIT]) AS NO_COMPTE, TR.COMPTE_MVT_CREDIT, TR.COMPTE_MVT_DEBIT, Choose(([COMPTE_MVT_DEBIT]="")+2,[DESIGN_COMPTE_MVT_CREDIT],[DESIGN_COMPTE_MVT_DEBIT]) AS DESI_COMPTE, TR.TYPE_TRANSACTION, TR.NATURE_TRANSACTION, TR.DEVISE, CDbl(Choose(([COMPTE_MVT_DEBIT]="")+2,[MONT_TRANSACTION_CREDIT],[MONT_TRANSACTION_DEBIT]*-1)) AS MONT_TRANS, TR.LIBELLE_COURT, TR.LIBELLE_LONG, TR.NOM_DOC_ELEC_LIE, TR.NO_POLICE_LIEE, TR.NO_POLICE_LIEE_CLEAN, TR.NO_INTERNE_POLICE_LIEE, TR.BRANCHE_ASSURANCE, TR.OBJET_ASSURE, TR.ASSUREUR, TR.TYPE_TIERS, TR.NO_ADRESSE, TR.NOM_TIERS, cdbl(IIf(TR.DATE_SAISIE>#4/1/2009#,IIf([TR.DATE_TRANSACTION]<>[TR.DATE_SAISiE],[TR.DATE_SAISIE],[TR.DATE_TRANSACTION]),TR.DATE_TRANSACTION)) AS DATE_TRANSACTION_NUM, Year([DATE_REF]) AS Annee
FROM OASIS70_AS_VUE_ECRITURES_CG AS TR
WHERE (TR.SOCIETE=1) And (Choose(([COMPTE_MVT_DEBIT]="")+2,[COMPTE_MVT_CREDIT],[COMPTE_MVT_DEBIT])<"9000") And (Not ((TYPE_TRANSACTION="201") And (MONT_TRANSACTION_CREDIT<0))) And ((COMPTE_MVT_DEBIT<>"1010") And (COMPTE_MVT_CREDIT<>"1010")) And ((COMPTE_MVT_DEBIT<>"1020") And (COMPTE_MVT_CREDIT<>"1020")) And ((COMPTE_MVT_DEBIT<>"1130") And (COMPTE_MVT_CREDIT<>"1130")) And ((COMPTE_MVT_DEBIT<>"1140") And (COMPTE_MVT_CREDIT<>"1140")) and ((COMPTE_MVT_DEBIT<>"1110") And (COMPTE_MVT_CREDIT<>"1110")) And (TR.TYPE_TIERS="Débiteur")
ORDER BY TR.NO_ADRESSE, cdbl(IIf(TR.DATE_SAISIE>#4/1/2009#,IIf([TR.DATE_TRANSACTION]<>[TR.DATE_SAISiE],[TR.DATE_SAISIE],[TR.DATE_TRANSACTION]),TR.DATE_TRANSACTION)), TR.NO_UNIQUE_LIGNE;

Open in new window


Are the Iff / Choose syntax supported ? Date constants ?
PatHartmanCommented:
If you refer to a form field instead of using a function, Access will simply send the value along with the query.  By using a function, Access cannot tell whether each time you run it a different value will be returned.  By referring to a form field, Access knows the argument is static and can handle it appropriately.

Try it with one of the queries and see if it makes a difference.

The IIf() translates to If Then Else
Alexandre TakacsCTOAuthor Commented:
Thanks for your help so far.

What about "translating"

Choose ()
Year ()
cdbl ()
Date constants

I'm sure it can all be done but help from experienced persons should certainly be appreciated
johnsoneSenior Oracle DBACommented:
Choose() - I don't think there is a direct replacement, but it could easily be done with a DECODE or CASE
Year() - TO_CHAR(<date>, 'yyyy') or EXTRACT(YEAR FROM <date>)
cdbl() - I don't think this is necessary.  Given the example I found here -> http://www.techonthenet.com/access/functions/datatype/cdbl.php it doesn't seem to apply.

Not sure what you mean by date constants.  I would think you would handle those on the Access side with the forms and pass the actual values through to the query.
Alexandre TakacsCTOAuthor Commented:
Mans thanks for your input - most usefull !
As for date constants some could (an in my opinion should) be "hard wired" in the Oracle view - what's my best way to do that ?
johnsoneSenior Oracle DBACommented:
Just put the value into the where clause.  Not sure what you are looking for there.  Are you looking for something like SYSDATE which returns current date and time?
awking00Information Technology SpecialistCommented:
To "hard wire" a date, just use the to_date function -
to_date('20150606','YYYYMMDD') will always be June 6th, 2015 (at 00:00:00 time)
to_date('01-JAN-2000','DD-MON-YYYY') will always be January 1st, 2000 (also at 00:00:00 time)

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Alexandre TakacsCTOAuthor Commented:
Thanks for the various inputs - I'm pretty close to have it nailed down !
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.