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

Report message to a moderator

Re: REPORT BASED ON 2 VIEWS [message #193434 is a reply to message #192682] Sat, 16 September 2006 14:59 Go to previous message
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

Report message to a moderator

Previous Topic: Join Query problem. One using index while another full table scan
Next Topic: redo files are on raid 5 ? why ?
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Sat Nov 23 13:32:32 CST 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.