-----Original Message-----
From: Gary Benade [mailto:hobbit@...]
Sent: Monday, November 22, 2004 6:10 PM
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] Parallel querying of FB

> Hi,
> We have a server with 2 xeon cpu's, 1GB Ram, Raid, FB 1.5.1.
> If we start a Query with sub-queries like follow,
> we have 50% cpu usage and unusable for other users.
> Is there any way to optimize FB Server?
> Kadri.
>
> -----------------------------------------------
> STOK has ~ 100000 records
> STOK_ISLEM has ~ 246000 records
> the query
> -----------------------------------------------
> select
> s.kod,
> s.kod2,
> s.kod3,
> s.model,
> s.renk,
> s.ozel1,
> s.beden,
> s.grupadi,
> s.saticiadi,
> s.alisfiyati,
> (select sum(miktar)from stok_islem
> where depokod =:Depo and tarih<=:SonTarih and stoksirano = s.sirano)as
> kalan,
> ((select sum(miktar) from stok_islem
> where depokod =:Depo and tarih<=:SonTarih and stoksirano = s.sirano) *
> s.alisfiyati) as tutar,
> s.alisdoviz as doviz
> from stok s
> where s.departman = 'Deri'
> order by s.kod

Do you have an index on stok_islem( stoksirano)? If not add one and let us
know.
You may, and I stress may, also need one on either stok_islem( tarih) or
tarih( depokod) if there are lots of rows returned by filtering on
stoksirano alone. However, I suspect that there would be about 3 rows in
stok_islem for every row in stok based on a thumbsuck and your data counts,
so probably not.

Posting the plan generated by this query would be helpful.

Gary

-------------------------------------------------------------------------
this is the plan

Plan
PLAN (S ORDER UNQ_STOK_1)
PLAN (STOK_ISLEM INDEX (STOK_ISLEM_STOKSIRANO,STOK_ISLEM_IDX7))
PLAN (STOK_ISLEM INDEX (STOK_ISLEM_STOKSIRANO,STOK_ISLEM_IDX7))

Adapted Plan
PLAN (S ORDER UNQ_STOK_1) PLAN (STOK_ISLEM INDEX
(STOK_ISLEM_STOKSIRANO,STOK_ISLEM_IDX7)) PLAN (STOK_ISLEM INDEX
(STOK_ISLEM_STOKSIRANO,STOK_ISLEM_IDX7))