--- In firebird-support@yahoogroups.com, "akdatilla" <akdatilla@...> wrote:

>
> Thank you for informing us,
> I will display a stock report by this query, One of the necessary data is counted inventory amount. Counted amount of inventory can be updated at different times. Therefore, to reach the updated counting results of different time periods I use below mentioned subquery.
>
> 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)
>
>
> ----------------------------------------------------------------------
> Re: speed problem
>
> --- In firebird-support@yahoogroups.com, "akdatilla" <akdatilla@> wrote:
> >
> > 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:
> >
>
> If you 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=0 (or SHB_ID = '0')
>
> what do you get ?
>
>
> Regards,
> Zlatko
>

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,