select distinct
art_id,
art_article_nr,
COALESCE(art_tex.tex_text, art_tex_uni.tex_text) articledes,
ga_id,
ga_tex.tex_text gades,
ga_tex.tex_text||' ('||ga_assembly_tex.tex_text||')' ga_assembly,
la_id,
lat_sort sort,
lat_sup_id bra_id,
COALESCE(sup_cou.sup_brand, sup_null.sup_brand) bra_brand,
art_replacement,
COALESCE(sup_cou.sup_is_hess, sup_null.sup_is_hess) is_hess
from tof_link_la_typ
join tof_generic_articles on lat_ga_id = ga_id
join tof_designations ga_des on ga_des_id = ga_des.des_id and 25 =
ga_des.des_lng_id
join tof_des_texts ga_tex on ga_des.des_tex_id = ga_tex.tex_id
left outer join tof_designations ga_assembly_des on ga_des_id_assembly =
ga_assembly_des.des_id and 25 = ga_assembly_des.des_lng_id
left outer join tof_des_texts ga_assembly_tex on ga_assembly_des.des_tex_id =
ga_assembly_tex.tex_id
join tof_link_art on lat_la_id = la_id
join tof_articles on la_art_id = art_id
left outer join tof_designations art_des on art_des_id = art_des.des_id and 25
=
art_des.des_lng_id
left outer join tof_des_texts art_tex on art_des.des_tex_id = art_tex.tex_id
left outer join tof_designations art_des_uni on art_des_id = art_des_uni.des_id
and 255 =
art_des_uni.des_lng_id
left outer join tof_des_texts art_tex_uni on art_des_uni.des_tex_id =
art_tex_uni.tex_id
left outer join tof_suppliers sup_cou on lat_sup_id = sup_cou.sup_id and
sup_cou.sup_cou_id = 202
left outer join tof_suppliers sup_null on lat_sup_id = sup_null.sup_id and
sup_null.sup_cou_id is null
where lat_typ_id = :TYP_ID and ga_id=:GA_ID
order by sort
Plan
PLAN SORT (SORT (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN
(JOIN (TOF_LINK_LA_TYP INDEX (TOF_LINK_LA_TYP_INDEX1, TOF_LINK_LA_TYP_INDEX3),
TOF_GENERIC_ARTICLES INDEX (TOF_GENERIC_ARTICLES_INDEX1), GA_DES INDEX
(TOF_DESIGNATIONS_INDEX1), GA_TEX INDEX (TOF_DES_TEXTS_INDEX1)),
GA_ASSEMBLY_DES INDEX (TOF_DESIGNATIONS_INDEX1)), GA_ASSEMBLY_TEX INDEX
(TOF_DES_TEXTS_INDEX1)), TOF_LINK_ART INDEX (TOF_LINK_ART_INDEX1)),
TOF_ARTICLES INDEX (TOF_ARTICLES_INDEX1)), ART_DES INDEX
(TOF_DESIGNATIONS_INDEX1)), ART_TEX INDEX (TOF_DES_TEXTS_INDEX1)), ART_DES_UNI
INDEX (TOF_DESIGNATIONS_INDEX1)), ART_TEX_UNI INDEX (TOF_DES_TEXTS_INDEX1)),
SUP_COU INDEX (TOF_SUPPLIERS_INDEX1)), SUP_NULL INDEX (TOF_SUPPLIERS_INDEX1))))
ARTICLES, GENERIC_ARTICLES, LINK_ART, LINK_LA_TYP - 79 indexed reads
SUPPLIERS - 158 indexed reads
DES_TETXS - 173 indexed reads
DESIGANTIONS - 5484 indexed reads
Prepare time: 15ms
Execute 4 sec 852 msec (second time 141 milisecond)
Database header page information:
Flags 0
Checksum 12345
Generation 15573
Page size 4096
ODS version 11.2
Oldest transaction 1504
Oldest active 14380
Oldest snapshot 14380
Next transaction 14483
Bumped transaction 1
Sequence number 0
Next attachment ID 1191
Implementation ID 26
Shadow count 0
Page buffers 16384
Next header page 0
Database dialect 3
Creation date Oct 14, 2014 10:02:23
Attributes force write
Variable header data:
Sweep interval: 20000
*END*
Seletivities
LINK_LA_TYP_INDEX1 - 0.000017
LINK_LA_TYP_INDEX3 - 0.000405
GENERIC_ARTICLES_INDEX1 - 0.000187
DESIGANTIONS_INDEX1 - 0.000007
DES_TEXTS_INDEX1 - 0.0000008
LINK_ART_INDEX1 - 0.00000006
ARTICLES_INDEX1 - 0.0000002
SUPPLIERS_INDEX1 - 0.001865
---In [email protected], <Sean@...> wrote :
Zoran,
> I am runing WI-V2.5.2.26539 Firebird 2.5, on win 7 64 bit. Databse is more
> than 20 GB big, and I have tables which are really huge - millions of rows.
>
> My query combines 6-7 tables, and all tables are searched with use of
> indexes, so altough tables are huge, query fethes only 10.000 rows, for
> example. So far so good. The issue I have is that if I run query firs time,
> it is
> very slow, even when using queries. Than , second time, cash is doing its,
> and
> query becomes fast.
It sounds like you are running in the problem of the initial query loading the
table and index metadata into cache.
What query are you executing (SQL with PLAN sample please)?
What indexes do have defined?
What does your database header stats show?
What does you index stats shows for the indexes used by the query?
Sean
