Hi!

I've looked at your original SQL as posted on Thursday 18 June and tried
to modify the SQL a bit, but I'm not able to do that. The main reason is
that you do not qualify your fields, I'd like all fields to be preceded
by the alias they refer to, please (E.g. rather than write STK_KOD,
write STK.STK_KOD). The particular place I got in trouble, was with

> 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

Here, you're mixing the implicit SQL-89 style of join and the explicit
SQL-92 style. This is something that you should never do (at least we
were warned a few years ago)! Change to

FROM SHRKSTR ST1
JOIN SHRKBSLK ST2 on ST1.SHB_SHA=ST2.SHA_ID
JOIN SHRKTNM ST3 on ST2.SHA_SHT=ST3.SHT_ID

I suspect it could be written more elegantly with a NOT EXISTS, but will
wait for you to qualify your fields and put the code back on this list
before seriously attempting to do so (it is impossible to do without
knowing which aliases the two SHB_ID refer to).

Also, I'm surprised that you do not get any error at compile time, since
you do not qualify SHB_ID.

There's one further thing that I've requested before and that is - in my
opinion - almost equally important as the actual SQL in diagnosing
performance trouble, and that is the PLAN. Without it, we may need luck
to help you, with the plan and possibly some further information from
you regarding various indexes, it would be much easier to help you and
you would get more reliable answers. I cannot remember any query running
on as small tables as yours (you say your tables are tiny, with only up
to 1000 records) that takes as long as you report. It ought to be
possible to get this down to a few seconds at worst.

I don't think the Firebird developers will work on your problem, but fix
up your SQL a bit and tell us the PLAN and some of us on this list will
do our best to find a query that will give you the desired result a lot
faster!

Set

akdatilla wrote:

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