Hello Fabiano, I've tried the first index, only the second index indicates which you give twice IdItem on. You mean maybe another field?
But with the first index: SELECT FIRST 30 SKIP 0 tr.NodePosition, tr.IdLink, tr.IdTargetLink, tr.IdItem FROM Tree3 tr inner join article3_1 art on art.idarticle = Tr.iditem WHERE tr.NODELEVEL =5 and tr.LINKTYPE <> 5 and tr.ItemType = 2 AND tr.Market1 = 1 and (lower(coalesce(art.Artikelnummer , '') || ';' || coalesce(art.Artikelnr_leverancier, '') || ';' || coalesce(art.Artikelnaam1, '') || ';' || coalesce(art.Artikelnaam2, '') || ';' || coalesce(art.MERK, '') || ';' || coalesce(art.EAN, '') || ';' || coalesce(art.ALTARTNR1, '') || ';' || coalesce(art.ALTARTNR2, '') ) like '%a4%') AND (lower(coalesce(art.Artikelnummer , '') || ';' || coalesce(art.Artikelnr_leverancier, '') || ';' || coalesce(art.Artikelnaam1, '') || ';' || coalesce(art.Artikelnaam2, '') || ';' || coalesce(art.MERK, '') || ';' || coalesce(art.EAN, '') || ';' || coalesce(art.ALTARTNR1, '') || ';' || coalesce(art.ALTARTNR2, '') ) like '%papier%') group by tr.NodePosition, tr.IdLink, tr.IdTargetLink, tr.IdItem Prepare time: 0.004s Field #01: TREE3.NODEPOSITION Alias:NODEPOSITION Type:INTEGER Field #02: TREE3.IDLINK Alias:IDLINK Type:INTEGER Field #03: TREE3.IDTARGETLINK Alias:IDTARGETLINK Type:INTEGER Field #04: TREE3.IDITEM Alias:IDITEM Type:INTEGER PLAN SORT (JOIN (TR INDEX (IDX_TREE31), ART INDEX (RDB$PRIMARY2))) 592894 fetches, 0 marks, 1846 reads, 0 writes. 0 inserts, 0 updates, 0 deletes, 240385 index, 0 seq. Delta memory: 48 bytes. Total execution time: 1.112s thanks, An VanderLinde --- In [email protected], "Fabiano" <fabianoaspro@...> wrote: > > Try this: > > > > SELECT FIRST 30 SKIP 0 tr.NodePosition, tr.IdLink, tr.IdTargetLink, > tr.IdItem > FROM Tree3 tr > inner join article3_1 art on art.idarticle = Tr.iditem > WHERE > tr.NODELEVEL =5 and tr.LINKTYPE <> 5 and tr.ItemType = 2 AND tr.Market1 = 1 > and > (lower(coalesce(art.Artikelnummer , '') || ';' > || coalesce(art.Artikelnr_leverancier, '') || ';' > || coalesce(art.Artikelnaam1, '') || ';' > || coalesce(art.Artikelnaam2, '') || ';' > || coalesce(art.MERK, '') || ';' > || coalesce(art.EAN, '') || ';' > || coalesce(art.ALTARTNR1, '') || ';' > || coalesce(art.ALTARTNR2, '') > > ) like '%a4%') > > AND (lower(coalesce(art.Artikelnummer , '') || ';' > || coalesce(art.Artikelnr_leverancier, '') || ';' > || coalesce(art.Artikelnaam1, '') || ';' > || coalesce(art.Artikelnaam2, '') || ';' > || coalesce(art.MERK, '') || ';' > || coalesce(art.EAN, '') || ';' > || coalesce(art.ALTARTNR1, '') || ';' > || coalesce(art.ALTARTNR2, '') > > ) like '%papier%') > > group by tr.NodePosition, tr.IdLink, tr.IdTargetLink, tr.IdItem > > > > Finally create na índex on Tree3 table with the fields: NodePosition, > IdLink, IdTargetLink, IdItem > > > > I think you do not need a forced "order by" clause because it is implicitly > formed as a group by. > > > > Measure time, then drop this index and create another on fields: iditem, > NodePosition, IdLink, IdTargetLink, IdItem > > > > One of these Will perform a better performance. > > > > [Non-text portions of this message have been removed] >
