Which Firebird version are you using? Certain things are possible to do in Firebird 2 or 2.1 that isn't available in Firebird 1.5. Knowing which variable belongs to which table may also be of importance, so if you could post the table definitions it would be nice.

The one thing I immediately notice, is that

FROM STOKKART
INNER JOIN STOKDEPO ON SDP_ID IS NOT NULL

means that the number of returned rows may multiply (if there are 1000 rows in STOKKART and 1000 non-null rows in STOKDEPO, you'll get 1 million rows returned).

Set

-----Original Message-----
From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of akdatilla
Sent: 18. juni 2009 15:09
To: firebird-support@yahoogroups.com
Subject: [firebird-support] speed problem

Hi,
We have a problem about firebird database. The following sql text run in the sqlserver database for 2-3 seconds. But in the firebird database this sql run for 25 minutes. How can we run this sql query quickly in the firebird.
Could you help to us for this problem.
Best Regards.

Sql:

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_FM,STR_BF,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,
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
(COALESCE(DVR_GIRIST,0.00)+COALESCE(DNM_GIRIST,0.00))/(COALESCE(DVR_GIRIS,0.00)+
COALESCE(DNM_GIRIS,0.00)) END AS BIRIMFIYAT,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)