AFRAGE OPTIMIERUNG

31/03/2010 - 10:45 von Phenich Lebao | Report spam
Hallo alle,

can mir jemand helfen by der optiemierung dieser Abfrage

SELECT A.BS_UNT, 'AP', COUNT(DISTINCT A.VOUCHER_ID)
FROM PS_CTG_LNE A
WHERE A.BS_UNT IN (SELECT F.BS_UNT
FROM P_SEC_CLSA F
WHERE F.OPRCLASS = 'PR_ALL')
AND A.GL_DISTRIB_STATUS <> 'D'
GROUP BY A.BS_UNT
UNION
SELECT B.BS_UNT, 'AM', COUNT(DISTINCT B.ASSET_ID)
FROM PS_DIST_LN B
WHERE B.GL_DISTRIB_STATUS <> 'D'
AND B.FISCAL_YEAR >= YEAR({ fn CURDATE() })-1
AND B.BS_UNT IN (SELECT G.BS_UNT
FROM P_SEC_CLSA G
WHERE G.OPRCLASS = 'PR_ALL')
GROUP BY B.BS_UNT
UNION
SELECT C.BS_UNT, 'AR', COUNT(DISTINCT C.ITEM)
FROM PS_ITEM_DST C
WHERE C.BS_UNT IN (SELECT H.BS_UNT
FROM P_SEC_CLSA H
WHERE H.OPRCLASS = 'PR_ALL')
AND C.GL_DISTRIB_STATUS <> 'D'
GROUP BY C.BS_UNT
UNION
SELECT D.BS_UNT, 'PO', COUNT(DISTINCT D.RECEIVER_ID)
FROM PS_RECV_LN_ACCTG D
WHERE D.BS_UNT IN (SELECT I.BS_UNT
FROM P_SEC_CLSA I
WHERE I.OPRCLASS = 'PR_ALL')
AND D.GL_DISTRIB_STATUS <> 'D'
GROUP BY D.BS_UNT
UNION
SELECT E.BS_UNT, 'IN', COUNT(DISTINCT TRANSACTION_GROUP)
FROM PS_CM_ACCTG_LINE E
WHERE E.GL_DISTRIB_STATUS <> 'D'
AND E.BS_UNT IN (SELECT J.BS_UNT
FROM P_SEC_CLSA J
WHERE J.OPRCLASS = 'PR_ALL')
GROUP BY E.BS_UNT
ORDER BY 1

Vielen Dank fur Ihre Hilfe
Phenich
 

Lesen sie die antworten

#1 Dietmar
31/03/2010 - 11:12 | Warnen spam
Hallo Phenich,

das ist ganz einfach, du musst nur...
Nein, so aus der Ferne ist das kaum zu schaffen. Wir kennen deine Datenbank
nicht, wissen nicht welche Schlüssel, Indizies deine Tabellen haben,
wieviele Datensàtze drinstehen usw.
Das einzige was man dir raten kann: Nimm die UNIONs raus und lass jede
Abfrage für sich ablaufen. Dann fàngst du mit der langsamsten Abfrage an.
Indizies zu jedem Schlüssel vorhanden. Zuerst mal den
Datenbankoptimierungsratgeber starten und schauen welches
Verbesserungspotential (wird in % ausgegeben) dieser sieht. Wenn das auch
nur wenig hilft, Ausführungsplan anzeigen lassen und diesen analysieren. Ist
schwer für einen Anfànger zu verstehen aber daran kommste auf die Dauer
nicht vorbei.

Gruß
Dietmar




"Phenich Lebao" schrieb im
Newsbeitrag news:
Hallo alle,

can mir jemand helfen by der optiemierung dieser Abfrage

SELECT A.BS_UNT, 'AP', COUNT(DISTINCT A.VOUCHER_ID)
FROM PS_CTG_LNE A
WHERE A.BS_UNT IN (SELECT F.BS_UNT
FROM P_SEC_CLSA F
WHERE F.OPRCLASS = 'PR_ALL')
AND A.GL_DISTRIB_STATUS <> 'D'
GROUP BY A.BS_UNT
UNION
SELECT B.BS_UNT, 'AM', COUNT(DISTINCT B.ASSET_ID)
FROM PS_DIST_LN B
WHERE B.GL_DISTRIB_STATUS <> 'D'
AND B.FISCAL_YEAR >= YEAR({ fn CURDATE() })-1
AND B.BS_UNT IN (SELECT G.BS_UNT
FROM P_SEC_CLSA G
WHERE G.OPRCLASS = 'PR_ALL')
GROUP BY B.BS_UNT
UNION
SELECT C.BS_UNT, 'AR', COUNT(DISTINCT C.ITEM)
FROM PS_ITEM_DST C
WHERE C.BS_UNT IN (SELECT H.BS_UNT
FROM P_SEC_CLSA H
WHERE H.OPRCLASS = 'PR_ALL')
AND C.GL_DISTRIB_STATUS <> 'D'
GROUP BY C.BS_UNT
UNION
SELECT D.BS_UNT, 'PO', COUNT(DISTINCT D.RECEIVER_ID)
FROM PS_RECV_LN_ACCTG D
WHERE D.BS_UNT IN (SELECT I.BS_UNT
FROM P_SEC_CLSA I
WHERE I.OPRCLASS = 'PR_ALL')
AND D.GL_DISTRIB_STATUS <> 'D'
GROUP BY D.BS_UNT
UNION
SELECT E.BS_UNT, 'IN', COUNT(DISTINCT TRANSACTION_GROUP)
FROM PS_CM_ACCTG_LINE E
WHERE E.GL_DISTRIB_STATUS <> 'D'
AND E.BS_UNT IN (SELECT J.BS_UNT
FROM P_SEC_CLSA J
WHERE J.OPRCLASS = 'PR_ALL')
GROUP BY E.BS_UNT
ORDER BY 1

Vielen Dank fur Ihre Hilfe
Phenich

Ähnliche fragen