Re: Need help with monstrous query migrated from sql server

skallagrimsson_at_gmail.com wrote:
> Hi!
>
> I need help with a query conversion from SQL Server to Oracle.
>
> The SQL Server query runs pretty fast, but when converted to run in
> Oracle it performs poorly. Both versions included below.
>
> Any ideas how to make the Oracle query better?
>
>
> Thanks in advance!
>
>
>
> SQL Server:
>
> SELECT
> permnd, peraar, jurenhet, artskonto, 0 as sumbilagbel,
> akode,rkode,sum(hboksaldo) as sumhboksaldo,
> (select 1 --FROM avstemt a
> WHERE not exists (
> select D.peraar
> from Avstemt D
> where D.permnd = A.permnd
> AND D.peraar = A.peraar
> AND upper(D.jurenhet) = upper(A.jurenhet)
> AND D.artskonto = A.artskonto
> AND (D.avstemtav is null OR D.avstemtav = '')))
> as avstemtav,
> (select ktonavn from Konto K where K.artskonto = A.artskonto) as
> kontonavn,
> COUNT(*) as antall,
> 0 as qaantall,
> COUNT(*) as avstemtantall
> FROM Avstemt A
> WHERE upper(jurenhet) = upper('sy')
> AND permnd = 'jul'
> AND peraar = 2004
> AND (
> NOT EXISTS (
> SELECT 1
> FROM Bilag B
> WHERE A.permnd = B.permnd
> AND A.peraar = B.peraar
> AND upper(A.jurenhet) = upper(B.jurenhet)
> AND A.artskonto = B.artskonto
> AND B.deleted = 'n'
> AND upper(A.rkode) = upper(B.rkode)
> AND upper(A.akode) = upper(B.akode)
> )
> )
> GROUP BY permnd, peraar, jurenhet, artskonto, rkode, akode
> HAVING SUM(hboksaldo) <> 0
>
>
>
>
>
> ORACLE:
>
> /* @(#)C:\AdventNet\SwisSQL\SQLServerToOracle3.0/temp2_PL.sql -
> Generated by AdventNet SwisSQL (SQLServer To oracle) Conversion tool.*/
> ;
> SELECT
> permnd,
> peraar,
> jurenhet,
> artskonto,
> 0 as sumbilagbel,
> akode,
> rkode,
> SUM(hboksaldo) as sumhboksaldo,
> (SELECT 1
> FROM SYS.DUAL
> WHERE NOT exists
> (SELECT D.peraar
> FROM Avstemt D
> WHERE D.permnd = A.permnd
> AND D.peraar = A.peraar
> AND UPPER(D.jurenhet) = UPPER(A.jurenhet)
> AND D.artskonto = A.artskonto
> AND (D.avstemtav is null
> OR D.avstemtav = '')))
> as avstemtav,
> (SELECT ktonavn
> FROM Konto K
> WHERE K.artskonto = A.artskonto)
> as kontonavn,
> COUNT(*) as antall,
> 0 as qaantall,
> COUNT(*) as avstemtantall
> FROM Avstemt A
> WHERE UPPER(jurenhet) = UPPER('sy')
> AND permnd = 'jul'
> AND peraar = 2004
> AND (NOT EXISTS
> (SELECT 1
> FROM Bilag B
> WHERE A.permnd = B.permnd
> AND A.peraar = B.peraar
> AND UPPER(A.jurenhet) = UPPER(B.jurenhet)
> AND A.artskonto = B.artskonto
> AND B.deleted = 'n'
> AND UPPER(A.rkode) = UPPER(B.rkode)
> AND UPPER(A.akode) = UPPER(B.akode) ))
> GROUP BY permnd,
> peraar,
> jurenhet,
> artskonto,
> rkode,
> akode
> HAVING SUM(hboksaldo) <> 0
>
> /

Great that you posted the DML but you didn't post a huge amount of what is really important to help you. Here's a starting list.

1. Version of Oracle
2. Is the data stored in heap tables?
3. Explain Plan
4. Are optimizer statistics (created with DBMS_STATS) current?
5. How many rows in the tables?
6. How many rows correspond to the result set at each stage?

And a few questions:
What is it you are attempting to accomplish with your reference to DUAL? Why are dates not stored as dates?

What is the point of: "UPPER('sy')"
Why not just type 'SY'?

You wrote: "HAVING SUM(hboksaldo) <> 0" Can sums ever be less than zero?

-- 
Daniel A. Morgan
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)