Yes it's true. Without this subquery process has finished in 25 seconds.
But if i run the query in this way, i have to run other queries for true result. We would like to know if it is possible to end the process with the query that I have sent you first faster than this. Can firebird developpers work on this problem?

The queries that I used to get the accurate result are as followings;


sql1 (finished in 25 seconds):
INSERT INTO STOKRPR (STR_PRS,STR_STK,STR_SDP,STR_KM,STR_DM,
STR_GM,STR_CM,STR_KT,STR_DT,STR_GT,STR_CT,STR_BF,
/*STR_FM,STR_FT,STR_SM,STR_SYMTRH,*/
STR_TARIH1,STR_TARIH2)
SELECT 1 AS PRS_NO,STK_KOD,SDP_ID,
COALESCE(DVR_GIRIS,0.00)+COALESCE(DNM_GIRIS,0.00)-COALESCE(DVR_CIKIS,0.00)-
COALESCE(DNM_CIKIS,0.00) AS
KM,COALESCE(DVR_GIRIS,0.00)-COALESCE(DVR_CIKIS,0.00) AS DM,
COALESCE(DNM_GIRIS,0.00) AS GM,COALESCE(DNM_CIKIS,0.00) AS CM,
(COALESCE(DVR_GIRIS,0.00)+COALESCE(DNM_GIRIS,0.00)-COALESCE(DVR_CIKIS,0.00)-
COALESCE(DNM_CIKIS,0.00))*STK_FIYAT2 AS KT,COALESCE(DVR_GIRIST,0.00)-
COALESCE(DVR_CIKIST,0.00) AS
DT,COALESCE(DNM_GIRIST,0.00) AS GT,
COALESCE(DNM_CIKIST,0.00) AS CT,
CASE WHEN COALESCE(DVR_GIRIS,0.00)+COALESCE(DNM_GIRIS,0.00)=0.00 THEN NULL ELSE
(COALESCE(DVR_GIRIST,0.00)+COALESCE(DNM_GIRIST,0.00))/(COALESCE(DVR_GIRIS,0.00)+
COALESCE(DNM_GIRIS,0.00)) END AS BIRIMFIYAT,
/*
SAYIM-(COALESCE(DVR_GIRIS,0.00)+COALESCE(DNM_GIRIS,0.00)-
COALESCE(DVR_CIKIS,0.00)-COALESCE(DNM_CIKIS,0.00)) AS FARK,
CASE WHEN
COALESCE(DVR_GIRIS,0.00)+COALESCE(DNM_GIRIS,0.00)=0.00 THEN NULL ELSE
(SAYIM-(COALESCE(DVR_GIRIS,0.00)+COALESCE(DNM_GIRIS,0.00)))
*
(
(COALESCE(DVR_GIRIST,0.00)+COALESCE(DNM_GIRIST,0.00))/
(COALESCE(DVR_GIRIS,0.00)+COALESCE(DNM_GIRIS,0.00))
) END AS FARKTUTARI,SAYIM,SYM_TARIH,
*/
'01.01.2009' AS DONEMBASI,
'31.12.2009' AS DONEMSONU FROM
STOKKART
INNER JOIN STOKDEPO ON
(SDP_ID IS NOT NULL)
LEFT OUTER JOIN
(
SELECT SHB_SDP AS DVR_DEPO,SHB_STK AS DVR_STK,
SUM(CASE WHEN SHT_ISLTIPI IN (1,3,4) OR (SHT_ISLTIPI=5 AND
SHB_GCKOD='G') THEN
SHB_ETKIMKTR ELSE 0 END) AS DVR_GIRIS,
SUM(CASE WHEN SHT_ISLTIPI IN (1,3,4) OR (SHT_ISLTIPI=5 AND
SHB_GCKOD='G') THEN
SHB_ETKIMKTR*SHB_FIYAT ELSE 0 END) AS DVR_GIRIST,
SUM(CASE WHEN SHT_ISLTIPI IN (2,6) OR (SHT_ISLTIPI=5 AND
SHB_GCKOD='C') THEN
SHB_ETKIMKTR ELSE 0 END) AS DVR_CIKIS,
SUM(CASE WHEN SHT_ISLTIPI IN (2,6) OR (SHT_ISLTIPI=5 AND
SHB_GCKOD='C') THEN
SHB_ETKIMKTR*SHB_FIYAT ELSE 0 END) AS DVR_CIKIST
FROM SHRKSTR AS DVRSFB,SHRKBSLK AS DVRSFA,SHRKTNM AS DVRSFT
WHERE DVRSFA.SHA_SHT=DVRSFT.SHT_ID AND DVRSFA.SHA_ID=DVRSFB.SHB_SHA AND
DVRSFA.SHA_SEVKTRH<'01.01.2009' GROUP BY SHB_SDP,SHB_STK) AS DVRTBL ON
(SDP_ID=DVR_DEPO AND DVR_STK=STK_KOD)
/*
LEFT OUTER JOIN
(
SELECT SHB_SDP AS SYM_DEPO,SHB_STK AS SYM_STK,SHB_GRLNMKTR AS SAYIM,
SHA_SEVKTRH AS SYM_TARIH FROM SHRKSTR AS SYMSFB,
SHRKBSLK AS SYMSFA
WHERE SYMSFA.SHA_ID=SYMSFB.SHB_SHA AND
SHB_ID=(
SELECT FIRST 1 SHB_ID FROM SHRKSTR AS ST1,SHRKBSLK AS ST2,SHRKTNM AS ST3
WHERE SYMSFB.SHB_SDP=ST1.SHB_SDP AND ST1.SHB_SHA=ST2.SHA_ID AND
ST2.SHA_SHT=ST3.SHT_ID AND ST3.SHT_ISLTIPI=4 AND ST2.SHA_SEVKTRH
BETWEEN '01.01.2009' AND '31.12.2009' AND
SYMSFB.SHB_STK=ST1.SHB_STK ORDER BY ST2.SHA_SEVKTRH DESC)
) AS T2 ON
(SDP_ID=SYM_DEPO AND SYM_STK=STK_KOD)
*/
LEFT OUTER JOIN
(
SELECT SHB_SDP AS DNM_DEPO,SHB_STK AS DNM_STK,
SUM(CASE WHEN SHT_ISLTIPI IN (1,3) OR
(SHT_ISLTIPI=5 AND SHB_GCKOD='G') THEN
SHB_ETKIMKTR ELSE 0 END) AS DNM_GIRIS,
SUM(CASE WHEN SHT_ISLTIPI IN (1,3) OR
(SHT_ISLTIPI=5 AND SHB_GCKOD='G') THEN
SHB_ETKIMKTR*SHB_FIYAT ELSE 0 END) AS DNM_GIRIST,
SUM(CASE WHEN SHT_ISLTIPI IN (2,6) OR
(SHT_ISLTIPI=5 AND SHB_GCKOD='C') THEN SHB_ETKIMKTR ELSE 0 END)
AS DNM_CIKIS,
SUM(CASE WHEN SHT_ISLTIPI IN (2,6) OR
(SHT_ISLTIPI=5 AND SHB_GCKOD='C') THEN
SHB_ETKIMKTR*SHB_FIYAT ELSE 0 END) AS DNM_CIKIST FROM
SHRKSTR AS DNMSFB,SHRKBSLK AS DNMSFA,
SHRKTNM AS DNMSFT WHERE
DNMSFA.SHA_SHT=DNMSFT.SHT_ID AND
DNMSFA.SHA_ID=DNMSFB.SHB_SHA AND
DNMSFA.SHA_SEVKTRH BETWEEN '01.01.2009' AND '31.12.2009'
GROUP BY SHB_SDP,SHB_STK
) AS T3 ON
(SDP_ID=DNM_DEPO AND DNM_STK=STK_KOD)


sql2 (finished in 257miliseconds):
SELECT SHB_SDP AS SYM_DEPO,SHB_STK AS SYM_STK,SHB_GRLNMKTR AS SAYIM,
SHA_SEVKTRH AS SYM_TARIH FROM SHRKSTR AS SYMSFB,
SHRKBSLK AS SYMSFA
WHERE SYMSFA.SHA_ID=SYMSFB.SHB_SHA AND
SHB_ID=(
SELECT FIRST 1 SHB_ID FROM SHRKSTR AS ST1,SHRKBSLK AS ST2,SHRKTNM AS ST3
WHERE SYMSFB.SHB_SDP=ST1.SHB_SDP AND ST1.SHB_SHA=ST2.SHA_ID AND
ST2.SHA_SHT=ST3.SHT_ID AND ST3.SHT_ISLTIPI=4 AND ST2.SHA_SEVKTRH
BETWEEN '01.01.2009' AND '31.12.2009' AND
SYMSFB.SHB_STK=ST1.SHB_STK ORDER BY ST2.SHA_SEVKTRH DESC)

at the end of this query I have to some updates.


Thanks for your precious help.

> Hi there,
> don't know if I missunderstand your reply or Zlatko question but this subselect may be the problem...
> to test this you can do this subselect and then put it result in your original query to see diff in performance...
> try this
>
> SELECT FIRST 1 SHB_ID FROM SHRKSTR AS ST1,SHRKBSLK AS ST2,SHRKTNM AS ST3
> WHERE SYMSFB.SHB_SDP=ST1.SHB_SDP AND ST1.SHB_SHA=ST2.SHA_ID AND
> ST2.SHA_SHT=ST3.SHT_ID AND ST3.SHT_ISLTIPI=4 AND ST2.SHA_SEVKTRH
> BETWEEN '01.01.2009' AND '31.12.2009' AND
> SYMSFB.SHB_STK=ST1.SHB_STK ORDER BY ST2.SHA_SEVKTRH DESC
>
> then replace the result in your original sql. ex:
> if it return '10001', then replace
>
> SHB_ID=(
> SELECT FIRST 1 SHB_ID FROM SHRKSTR AS ST1,SHRKBSLK AS ST2,SHRKTNM AS ST3
> WHERE SYMSFB.SHB_SDP=ST1.SHB_SDP AND ST1.SHB_SHA=ST2.SHA_ID AND
> ST2.SHA_SHT=ST3.SHT_ID AND ST3.SHT_ISLTIPI=4 AND ST2.SHA_SEVKTRH
> BETWEEN '01.01.2009' AND '31.12.2009' AND
> SYMSFB.SHB_STK=ST1.SHB_STK ORDER BY ST2.SHA_SEVKTRH DESC)
>
> with
> SHB_ID= '10001'
>
> also give the plan for the original sql like sven said, it can help.
> (may help the plan of this subselect and the altered original query too)
>
> Regards,
>