Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Speed problems using ODBC queries to Oracle (long message)
Hello,
We have serious speed problems with some reports that we wrote. The
underlying SQL is very complicated and uses many functions. The structure
of the way we are using the report writer (Crystal Reports), is the
following:
Crystal ----> MSAccess JET dbengine Driver ---> Main MSAccess type Query
---> Oracle ODBC
To add to the complexity, the ""Main MSAccess type Query" is based on
three Oracle queries (PL/SQL) that are stored in MSAccess, but are
'passed-through' straight into Oracle ODBC.
Crystal creates its own MSAccess SQL by doing a simple SELECT statement to
the main query; it builds the WHERE clause according to some record
selection criteria that are set for that report.
With other reports, we converted all of this SQL to an Oracle View. We did not see speed improvements, but it meant that Crystal could use the view instead of going through MSAccess.
I was told that Oracle disregards Indexes if there are functions in the
WHERE clause.
It seems almost hopeless to speed up the query by fine-tuning the SQL
itself, so I was thinking that there could be a way of storing the results
of the query (the data) on a new table and having Oracle update it
automatically (triggers??). Is this feasible?
Any help would be much appreciated!
Regards,
MichaelA
*mba
P.S. Below I've listed the SQL for two of the queries. The largest of the three queries that the main one is based on is:
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
GLAmts.BN BN, GLAmts.GL_ACCT GL_ACCT, GLMMST.DESCRIPTION DESCRP, GLAmts.FY FY, GLAmts.PER_NO PER_NO, PERMST.PERIOD_FROM_DATE FRM_DATE, PERMST.PERIOD_TO_DATE TO_DATE,
SUBSTR(GLAmts.GL_ACCT,6,4) ACCT_NO, SUBSTR(GLAmts.GL_ACCT,6,1) ACCT_TP, SUBSTR(GLAmts.GL_ACCT,1,4) BUSUNT, SUBSTR(GLAmts.GL_ACCT,11) SUBACCT, SUBSTR(GLMMST.DESCRIPTION,(INSTR(GLMMST.DESCRIPTION,'-',-1,1))) SUBDESC,GLBRMST.CURRENCY_NO CurrNumb
BUDGET_AMOUNT AS BUD_AMT, 0 AS BAL_AMT FROM GLBUDMST WHERE ((TEMPORARY_BUDGET<>'X' Or TEMPORARY_BUDGET Is Null)) ) UNION (SELECT GL_BRANCH_NO AS BN, GL_ACCOUNT_NO AS GL_ACCT, FISCAL_YEAR AS FY, PERIOD_NO AS PER_NO, 0 AS BUD_AMT, BALANCE_AMOUNT AS BAL_AMT FROM GLBALMST) )
((GLAmts.BN = GLMMST.GL_BRANCH_NO) AND (GLAmts.GL_ACCT = GLMMST.GL_ACCOUNT_NO)) AND ((GLAmts.FY = PERMST.FISCAL_YEAR) AND (GLAmts.PER_NO = PERMST.PERIOD_NO)) AND (GLBRMST.GL_BRANCH_NO = GLAmts.BN) <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
The actual main query is (try not to gasp, I do): Note that this is an MSAccess query based on three Oracle SQL queries.
SELECT DISTINCTROW qryGLAccountBalances.BN, qryBusinessUnitDesc.DESCRIPTION AS BUDESC, qryGLAccountBalances.GL_ACCT, Mid([DESCRP],InStr([DESCRP],"-")+2) AS DESCRIPTION,
qryGLAccountBalances.FY, qryGLAccountBalances.PER_NO, qryGLAccountBalances.FRM_DATE, qryGLAccountBalances.TO_DATE, qryGLAccountBalances.BAL_AMT, qryGLAccountBalances.BUD_AMT, qryGLAccountBalances.TMP_FLG, qryGLAccountBalances.ACCT_NO, qryGLAccountBalances.ACCT_TP, qryGLAccountBalances.BUSUNT, qryGLAccountBalances.SUBACCT, qryGLAccountBalances.SUBDESC, qryGLAccountBalances.CURRNUMB, qryExchangeRates.EXCHRATE,IIf([qryGLAccountBalances]![BUSUNT]="7901",9,IIf([qryGLAccountBalances]![BUS UNT]="7401",8,Switch([qryGLAccountBalances]![BUSUNT]="3001" And (Val([qryGLAccountBalances]![ACCT_NO])>=3400 And Val([qryGLAccountBalances]![ACCT_NO])<=3499),1,[qryGLAccountBalances]![BUSUN T]="5701",2,[qryGLAccountBalances]![BUSUNT]="3001" And (Val([qryGLAccountBalances]![ACCT_NO])>=3500 And Val([qryGLAccountBalances]![ACCT_NO])<=3599),3,([qryGLAccountBalances]![BN]= "1" And [qryGLAccountBalances]![BUSUNT]="3001" And [qryGLAccountBalances]![ACCT_NO]="4750"),4,[qryGLAccountBalances]![BUSUNT]=" 7301",7,([qryGLAccountBalances]![BN]="1" And [qryGLAccountBalances]![BUSUNT]="3001" And [qryGLAccountBalances]![ACCT_NO]="3950"),6,True,0))) AS DeptType FROM (qryExchangeRates INNER JOIN qryGLAccountBalances ON (qryExchangeRates.AS_DATE = qryGLAccountBalances.FRM_DATE) AND (qryExchangeRates.CURRTYPE = qryGLAccountBalances.CURRNUMB)) INNER JOIN qryBusinessUnitDesc ON qryGLAccountBalances.BUSUNT = qryBusinessUnitDesc.SEGMENT_CODE;
-- Michael Abramovitch ---------------------------------------------------------------------------- ------------------------------------------- Interwood Marketing Group (IMG): michaela_at_img.mhs.compuserve.com private: michaelba_at_msn.com Note: message content are MY views and NOT my current employer. ---------------------------------------------------------------------------- -------------------------------------------Received on Tue Feb 25 1997 - 00:00:00 CST