Home » RDBMS Server » Performance Tuning » REPORT BASED ON 2 VIEWS
REPORT BASED ON 2 VIEWS [message #192682] |
Wed, 13 September 2006 04:34 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
gore8980
Messages: 1 Registered: September 2006 Location: Athens
|
Junior Member |
|
|
HELLO TO EVERYONE
I am a MS SQL developer and I want to find a fast way to execute a crystal report
I have one report based on 2 views. One it is the primary (x)view where we collect the data and the other one (y) where we get info.
the X view is as follows:
-----------------------------------------------------------
SELECT SLDF.DOCID AS FDOCID, SLDF.DOSCODE AS FDOSCODE, SDTF.DOTCODE AS FDOTCODE,
SLDF.DOCNUMBER AS FDOCNUMMER, SLDF.TDOOTHERNUMBER AS FTDOOTHERNUMBER,
SLDF.TDOOTHERDATE AS FTDOOTHERDATE, SSDF.LINENO AS FLINENO, SSDF.MCIID,
SSDF.CODCODE AS CODCODE, SSDF.STDQTYA AS FIQ, SSDF.SSDTRANSFQTYA AS FTQ,
SSDF.SSDNETUNITPRICE AS FSSDNETUNITPRICE, SSDF.SSDNETVALUE AS FSSDNETVALUE,
DECODE(SSDF.SSDEXECDATE,NULL,SLDF.TDOEKTELESISDATE,SSDF.SSDEXECDATE) AS EXECDATE,
DECODE(SLDF.DOCENIMEROSISDATE, null,0,1) FDOCISISSUED ,
SSDT.DOCID AS TDOCID, SLDT.DOSCODE AS TDOSCODE, SDTT.DOTCODE AS TDOTCODE,
SLDT.DOCNUMBER AS TDOCNUMBER, SSDT.LINENO AS TLINENO, SSDT.STDQTYA AS TQ,
SSDT.SSDNETUNITPRICE AS TSSDNETUNITPRICE, SSDT.SSDNETVALUE AS TSSDNETVALUE,
CUS.TRAID, CUS.TRACODE, LEE.LEENAME,CCD.CTGID AS YPOID,
CPMF.PMTCODE AS FPMTCODE, -- ΚΩΔΙΚΟΣ ΤΡΟΠΟΥ ΠΛΗΡΩΜΗΣ ΠΑΡΑΣΤΑΤΙΚΟΥ
CPMF.PMTTITLE AS FPMTTITLE,
SLDF.TDOADDRESS AS FTDOADDRESS,
SLDF.SYS_LUPD AS UPDDATE
FROM SSD SSDF, --HEADER ΠΑΡΑΣΤΑΤΙΚΟΥ FROM
SLD SLDF, --ΓΡΑΜΜΕΣ ΠΑΡΑΣΤΑΤΙΚΟΥ FROM
SDT SDTF, --ΤΥΠΟΣ ΠΑΡΑΣΤΑΤΙΚΟΥ FROM
SFI, --ΜΕΤΑΣΧΗΜΑΤΙΣΜΟΙ ΓΡΑΜΜΩΝ
SSD SSDT, --HEADER ΠΑΡΑΣΤΑΤΙΚΟΥ ΤΟ
SLD SLDT, --ΓΡΑΜΜΕΣ ΠΑΡΑΣΤΑΤΙΚΟΥ ΤΟ
SDT SDTT, --ΤΥΠΟΣ ΠΑΡΑΣΤΑΤΙΚΟΥ TO
CUS, --ΠΕΛΑΤΕΣ
LEE, --ΣΥΝΑΛΑΣΣΟΜΕΝΟΙ
CCD, --ΚΑΤΗΓΟΡΙΕΣ ΠΕΛΑΤΩΝ
CPM CPMF -- ΤΡΟΠΟΣ ΠΛΗΡΩΜΗΣ
WHERE SSDF.DOCID = SLDF.DOCID
AND SDTF.DOTID = SLDF.DOTID
AND SLDF.TRAID = CUS.TRAID(+)
AND CUS.LEEID = LEE.LEEID(+)
AND SSDF.DOCID = SFI.DOCIDFROM(+)
AND SSDF.LINENO = SFI.LINENOFROM(+)
AND SFI.DOCID = SSDT.DOCID(+)
AND SFI.LINENO = SSDT.LINENO(+)
AND SSDT.DOCID = SLDT.DOCID(+)
AND SLDT.DOTID = SDTT.DOTID(+)
AND CUS.TRAID = CCD.TRAID(+)
AND 8 = CCD.CTGIDROOT(+)
AND SLDF.PMTIDPAYMENT = CPMF.PMTID (+)
-----------------------------------------------
the Y Views is as follows:
SELECT FDOSCODE, FDOTCODE, FDOCNUMMER, MCIID, CODCODE, TRAID, TRACODE, YPOID,
FDOCISISSUED, FTDOADDRESS , FPMTCODE, FPMTTITLE, UPDDATE ,
NVL(ZT87_ZSLD.APPROVED,1) AS APPROVED, --ΕΓΚΡΙΣΗ
MAX(FTDOOTHERNUMBER) AS TDOOTHERNUMBER, -- ΑΡΙΘΜΟΣ ΑΝΑΦΟΡΑΣ
MAX(FTDOOTHERDATE) AS TDOOTHERDATE, -- ΗΜΕΡΟΜΗΝΙΑ ΑΝΑΦΟΡΑΣ
MAX(LEENAME) AS LEENAME, --ΕΠΩΝΥΜΙΑ ΣΥΝΑΛΛΑΣΟΜΕΝΟΥ
MAX(EXECDATE) AS EXECDATE, --ΗΜΕΡΟΜΗΝΙΑ ΕΚΤΕΛΕΣΗΣ
MAX(FSSDNETUNITPRICE) AS FSSDNETUNITPRICE, --ΤΙΜΗ ΜΟΝΑΔΟΣ
MAX(FSSDNETVALUE/FIQ) AS FSSDTUP, --ΤΙΜΗ ΜΟΝΑΔΟΣ ΜΕΤΑ ΤΗΝ ΕΚΠΤΩΣΗ
FIQ, --ΠΟΣΟΤΗΤΑ ΠΑΡΑΓΓΕΛΙΑ
FTQ, --ΜΕΤΑΣΧΗΜΑΤΙΣΜΕΝΗ
--ΠΟΣΟΤΗΤΑ ΠΟΥ ΜΕΤΑΣΧΗΜΑΤΙΘΗΚΕ ΣΕ ΑΛΛΗ ΠΑΡΑΓΓΕΛΙΑ
SUM(DECODE(SUBSTR(TDOTCODE,1,1),'Π',DECODE(SUBSTR(TDOTCODE,1,2),'ΠΧ',0,TQ),0)) AS ORDERS_Q,
--ΠΟΣΟΤΗΤΑ ΠΟΥ ΜΕΤΑΣΧΗΜΑΤΙΘΗΚΕ ΣΕ ΤΙΜΟΛΟΓΙΟ ΔΕΛΤΙΟ ΑΠΟΣΤΟΛΗΣ
SUM(DECODE(SUBSTR(TDOTCODE,1,1),'Π',0,TQ)) AS SALES_Q,
--ΠΟΣΟΤΗΤΑ ΠΟΥ ΜΕΤΑΣΧΗΜΑΤΙΘΗΚΕ ΣΕ ΑΚΥΡΩΤΙΚΗ ΠΑΡΑΓΓΕΛΙΑ
SUM(DECODE(SUBSTR(TDOTCODE,1,2),'ΠΧ',TQ,0)) AS CANCEL_Q,
(SELECT CTGOUTLINE FROM CCD, CTG
WHERE CCD.CTGIDROOT = 7 AND
CCD.TRAID = ZZ_INTEK_SLDHIST_TEST.TRAID
AND CTG.CTGID = CCD.CTGID) AS CUSTNET ,
(SELECT HCOUTLINE FROM ZZ_INTEK_STICTG
WHERE ZZ_INTEK_STICTG.MCIID = ZZ_INTEK_SLDHIST_TEST.MCIID ) AS HCOUTLINE
FROM ZZ_INTEK_SLDHIST_TEST, ZT87_ZSLD
WHERE FDOTCODE LIKE 'Π%'
AND FDOTCODE NOT LIKE 'ΠΧ%'
AND FDOTCODE <> 'ΠΘ'
AND ZZ_INTEK_SLDHIST_TEST.FDOCID = ZT87_ZSLD.DOCID(+)
GROUP BY FDOSCODE, FDOTCODE, FDOCNUMMER, MCIID, CODCODE, TRAID, TRACODE, YPOID,FDOCISISSUED,FIQ,FTQ,
NVL(ZT87_ZSLD.APPROVED,1), FTDOADDRESS , FPMTCODE, FPMTTITLE , UPDDATE
HAVING MAX(FIQ) > MAX(FTQ)
-------------------------------------------------------------
Is there any idea how to make them faster ?
Thanks in advance
|
|
|
Re: REPORT BASED ON 2 VIEWS [message #193434 is a reply to message #192682] |
Sat, 16 September 2006 14:59 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
kimant
Messages: 201 Registered: August 2006 Location: Denmark
|
Senior Member |
|
|
We have no chance with the info You have provided.
You need to provide some more details, like execution time, execution plans, indexes, size of tables and indexes etc.
br
Kim
|
|
|
Goto Forum:
Current Time: Thu Sep 12 11:48:24 CDT 2024
|