Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Speed problems using ODBC queries to Oracle (long message)

Speed problems using ODBC queries to Oracle (long message)

From: Michael B. Abramovitch <michaela_at_img.mhs.compuserve.com>
Date: 1997/02/25
Message-ID: <01bc2325$6ffc0240$329ed0cd@michaela>#1/1

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:

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

SELECT
  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, 

  To_Char(GLAmts.BAL_AMT) BAL_AMT,
  To_Char(GLAmts.BUD_AMT) BUD_AMT,
  '' TMP_FLG,
  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
FROM
  (Select BN,GL_ACCT,FY,PER_NO,SUM(BUD_AMT) as BUD_AMT, SUM(BAL_AMT) as BAL_AMT
  FROM ((SELECT GL_BRANCH_NO AS BN, GL_ACCOUNT_NO AS GL_ACCT, FISCAL_YEAR AS FY, PERIOD_NO AS PER_NO,
                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)
       )

  GROUP BY
    BN,GL_ACCT,FY,PER_NO) GLAmts,
  GLMMST,
  PERMST,
  GLBRMST
Where
   ((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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US