RES: [firebird-support] Re: Problem slow sorting/distinct in Firebird
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 firebird-support@yahoogroups.com, "Fabiano" 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] >
RES: [firebird-support] Re: Problem slow sorting/distinct in Firebird
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]
RES: [firebird-support] Re: Problem slow sorting/distinct in Firebird
Hi all,
I've modified the query with the specified points and thereby put the times,
there is still a difference between Firebird and MySQL but the difference is
getting smaller. Thanks for that.
Are there any other options ?
Firebird (Execution time: +/- 0.878s):
SELECT FIRST 30 SKIP 0 distinct 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%')
ORDER BY tr.NodePosition
MySQL (Execution time: +/- 0.6502s):
SELECT distinct tr.NodePosition, tr.IdLink, tr.IdTargetLink, tr.IdItem
FROM Tree3 tr
inner join article3_1 art on art.idarticle = Tr.iditem and tr.NODELEVEL =5 and
tr.LINKTYPE <> 5 and tr.ItemType = 2 AND tr.Market1 = 1
WHERE
concat_ws(';', art.artikelnummer, art.artikelnaam1, art.artikelnaam2, art.ean,
art.merk, art.ALTARTNR1, art.ALTARTNR2) like '%a4%' and
concat_ws(';', art.artikelnummer, art.artikelnaam1, art.artikelnaam2, art.ean,
art.merk, art.ALTARTNR1, art.ALTARTNR2) like '%papier%'
ORDER BY tr.NodePosition
limit 30;
--- In firebird-support@yahoogroups.com, "Fabiano" wrote:
>
> Thanks Poul!
>
>
>
> The unique problem with this approach is when you have a null value in one
> of these columns it will not work correctly.
>
> So, the 'correct' way is use coalesce do convert null value in empty string:
>
> (LCASE(coalesce(art.Artikelnummer , '')||
> coalesce(art.Artikelnr_leverancier, '')) like '%a4 papier%')
>
> Another tip is create a calculated column in the table with the above
> statement - with the name find_name.
>
> So, you can easily write a simple SQL like:
>
> Select * from some_table where find_name like '%a4 papier%'
>
>
>
>
>
>
>
> [Non-text portions of this message have been removed]
>
RES: [firebird-support] Re: Problem slow sorting/distinct in Firebird
Hi, this is not true that FB use 10 full table scans it use only one table scan and check 10 combination of fields try this on emplooye.fdb database SELECT * FROM CUSTOMER r where r.CUSTOMER like '%S%' 15 seq reads and then try SELECT * FROM CUSTOMER r where r.CUSTOMER like '%S%' or r.CUSTOMER like '%B%' or r.CUSTOMER like '%M%' you got the same 15 seq reads if you you notice more reads, then this is an error and you should report it into fbtracker Karol Bieniaszewski --- In firebird-support@yahoogroups.com, "Fabiano" wrote: > > I think the problem is the use of "LCASE(something) like '% something%'" > > When FB encounter a %% statement it will perform a full table scan. If you > have 10 statements like this (and you have much more) you have at least 10 > full table scans to do the job. > > > > First: Rewrite your SQL code and DO NOT USE no one %% statement. Check if > you have the correct indexes (tr.ItemType, tr.Marke1, tr.NODELEVEL, > tr.LINKTYPE) - Try one index for all these fields first. > > > > When you finish you will have a very fast statement. The next step is create > concatenate all columns in a virtual one and do only one %% statement. > Example: > > > > Istead of: > > (LCASE(art.Artikelnummer) like '%a4 papier%') or > (LCASE(art.Artikelnr_leverancier) like '%a4 papier%') > > > > Use: > > (LCASE(art.Artikelnummer|| art.Artikelnr_leverancier) like '%a4 papier%') > > > > In this case FB will do only ONE full table scan instead of 2 from the above > example. > > > > Sorry about my poor English, I hope you can understand my point of view. If > you need more help create a simple database with these tables populated > inside and publish on the internet and answer with the link to download. > > > > Fabiano. > > > > [Non-text portions of this message have been removed] >
RES: [firebird-support] Re: Problem slow sorting/distinct in Firebird
Thanks Poul! The unique problem with this approach is when you have a null value in one of these columns it will not work correctly. So, the 'correct' way is use coalesce do convert null value in empty string: (LCASE(coalesce(art.Artikelnummer , '')|| coalesce(art.Artikelnr_leverancier, '')) like '%a4 papier%') Another tip is create a calculated column in the table with the above statement - with the name find_name. So, you can easily write a simple SQL like: Select * from some_table where find_name like '%a4 papier%' [Non-text portions of this message have been removed]
RES: [firebird-support] Re: Problem slow sorting/distinct in Firebird
I think the problem is the use of "LCASE(something) like '% something%'" When FB encounter a %% statement it will perform a full table scan. If you have 10 statements like this (and you have much more) you have at least 10 full table scans to do the job. First: Rewrite your SQL code and DO NOT USE no one %% statement. Check if you have the correct indexes (tr.ItemType, tr.Marke1, tr.NODELEVEL, tr.LINKTYPE) - Try one index for all these fields first. When you finish you will have a very fast statement. The next step is create concatenate all columns in a virtual one and do only one %% statement. Example: Istead of: (LCASE(art.Artikelnummer) like '%a4 papier%') or (LCASE(art.Artikelnr_leverancier) like '%a4 papier%') Use: (LCASE(art.Artikelnummer|| art.Artikelnr_leverancier) like '%a4 papier%') In this case FB will do only ONE full table scan instead of 2 from the above example. Sorry about my poor English, I hope you can understand my point of view. If you need more help create a simple database with these tables populated inside and publish on the internet and answer with the link to download. Fabiano. [Non-text portions of this message have been removed]
