I have few tables with milions of rows. I use inedexes. please look at this
report:
Query
------------------------------------------------
select distinct
articles.art_id,
articles.art_article_nr,
generic_articles.ga_id,
link_art.la_id,
link_la_typ.lat_sort sort,
link_la_typ.lat_sup_id bra_id,
articles.art_replacement
from link_la_typ
inner join generic_articles on lat_ga_id = ga_id
inner join link_art on lat_la_id = la_id
inner join articles on la_art_id = art_id
where lat_typ_id=:TYP_ID and ga_id=:GA_ID
ORDER BY 7 ASC
, 2 ASC
Plan
------------------------------------------------
PLAN SORT (SORT (JOIN (LINK_LA_TYP INDEX (LINK_LA_TYP_IDX1, LINK_LA_TYP_IDX3),
GENERIC_ARTICLES INDEX (GENERIC_ARTICLES_IDX1), LINK_ART INDEX (LINK_ART_IDX1),
ARTICLES INDEX (ARTICLES_IDX1))))
Query Time
------------------------------------------------
Prepare : 31.00 ms
Execute : 4,664.00 ms
Avg fetch time: 222.10 ms
Memory
------------------------------------------------
Current: 10,038,728
Max : 15,804,872
Buffers: 2,048
Operations
------------------------------------------------
Read : 10,368
Writes : 0
Fetches: 21,725
Marks : 0
All for tables are reported that thay had only 674 indexed reads.
So, I get 4 x 674 indexed reads in 4.6 Seconds - to slow for my needs.
I use FB 2.5.3 x64 on Win. I have 8 GB of Ram.
Can someone explain why this is so slow ?
Regards,
Zoran
