SV: [firebird-support] Re: Problem slow sorting/distinct in Firebird
Sorry for interfering - but I just wanted to say: Brilliant trick, Fabiano :) I will remember that one! Regards, Poul Fra: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] På vegne af Fabiano 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%') [Non-text portions of this message have been removed]
Re: [firebird-support] Indexed Read Counts / Performance Questions
Sean, Thanks so much for the reply! The "do work with" portion of the processing takes place outside of the database, so I do not believe a For Select ... Do loop will work. The processing of one ticket can create or influence other tickets as well, so it sounds like the climb in indexed read counts is expected to an extent. I'll check on the compound index, that might help me some. Lower weights are first, so heavy things sink to the bottom while lighter things are done at the top and handled quickly. Thanks for the information and advice. Much appreciated, --Daniel On Tue, Oct 2, 2012 at 11:43 AM, Leyne, Sean wrote: > ** > > > > > > As I'm running my operational procedure, it might flow something like > this: > > > > 1. Find first Ticket in 'Pending' Status. > > 2. Work with Ticket. > > 3. Set Ticket status to 'Complete'. > > 4. Repeat. > > Depending on what you mean by "work with ticket", it would seem that you > need to use procedure logic with a FOR SELECT ... DO loop. Where you get > the list of 'Pending' items and then walk over that list. > > In that way the number of reads to build the list of 'Pending' items would > be minimized. > > If you are re-selecting the 'Pending' items within a loop, it would be > expected for the number of indexed read to grow with each iteration. > > You might also want to consider a compound index on weight+status (with > the field with the lowest number of possible value as the first field). The > index would be ascending or descending on how you are managing weight (i.e > is weigth = 1 the highest or lowest priority?) > > Sean > > > [Non-text portions of this message have been removed] ++ Visit http://www.firebirdsql.org and click the Resources item on the main (top) menu. Try Knowledgebase and FAQ links ! Also search the knowledgebases at http://www.ibphoenix.com ++ Yahoo! Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com <*> To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com <*> Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
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]
[firebird-support] Re: Problem slow sorting/distinct in Firebird
Hello Dimitry, Yes, that's right! I use MySQL / InnoDB. With the query: SELECT distinct tr.IdLink, tr.IdTargetLink, tr.IdItem, tr.NodePosition FROM Tree3 tr inner join article3_1 art on art.idarticle = Tr.iditem WHERE tr.ItemType = 2 AND tr.Market1 = 1 AND ((art.IDARTICLE > 0) AND ( (LCASE(art.Artikelnummer) like '%a4 papier%' ) OR ( (LCASE(art.Artikelnummer) like '%a4%' ) AND (LCASE(art.Artikelnummer) like '%papier%')) OR (LCASE(art.Artikelnaam1) like '%a4 papier%' ) OR ( (LCASE(art.Artikelnaam1) like '%a4%' ) AND (LCASE(art.Artikelnaam1) like '%papier%')) OR (LCASE(art.Artikelnaam2) like '%a4 papier%' ) OR ( (LCASE(art.Artikelnaam2) like '%a4%' ) AND (LCASE(art.Artikelnaam2) like '%papier%')) OR (LCASE(art.Artikelnr_leverancier) like '%a4 papier%' ) OR ( (LCASE(art.Artikelnr_leverancier) like '%a4%' ) AND (LCASE(art.Artikelnr_leverancier) like '%papier%')) OR (LCASE(art.Merk) like '%a4 papier%' ) OR ( (LCASE(art.Merk) like '%a4%' ) AND (LCASE(art.Merk) like '%papier%')) OR (LCASE(art.EAN) like '%a4 papier%' ) OR ( (LCASE(art.EAN) like '%a4%' ) AND (LCASE(art.EAN) like '%papier%')) OR (LCASE(art.AltArtnr1) like '%a4 papier%' ) OR ( (LCASE(art.AltArtnr1) like '%a4%' ) AND (LCASE(art.AltArtnr1) like '%papier%')) OR (LCASE(art.AltArtnr2) like '%a4 papier%' ) OR ( (LCASE(art.AltArtnr2) like '%a4%' ) AND (LCASE(art.AltArtnr2) like '%papier%')) )) AND tr.NODELEVEL =5 and tr.LINKTYPE <> 5 ORDER BY tr.NodePosition LIMIT 30; Thanks! --- In firebird-support@yahoogroups.com, Dmitry Yemanov wrote: > > 19.11.2012 14:52, vanderlinde81 wrote: > > > > Thanks for your reply! I've tried your query and it is indeed a bit > > faster. But there is still a big difference between MySQL and Firebird. > > The difference now is 900ms. What could be the cause? > > I hope you're comparing Firebird with MySql/InnoDB and not with > MySql/MyISAM, right? > > > Dmitry >
[firebird-support] Re: Problem slow sorting/distinct in Firebird
19.11.2012 14:52, vanderlinde81 wrote: > > Thanks for your reply! I've tried your query and it is indeed a bit > faster. But there is still a big difference between MySQL and Firebird. > The difference now is 900ms. What could be the cause? I hope you're comparing Firebird with MySql/InnoDB and not with MySql/MyISAM, right? Dmitry
[firebird-support] Re: opening a database created by FB 2.1.5 resulted in SQLSTATE = HY000 error
Maybe it works with: localhost/3050:c:\ etc. VanderLinde --- In firebird-support@yahoogroups.com, "ehaerim" wrote: > > No one can resolve this issue in this forum? > Please help >
[firebird-support] Re: Problem slow sorting/distinct in Firebird
Hello Set, Thanks for your reply! I've tried your query and it is indeed a bit faster. But there is still a big difference between MySQL and Firebird. The difference now is 900ms. What could be the cause? The number of records: Tree3 -> 343223 records Article3_1 -> 42573 records The plan of your query: Prepare time: 0.003s Field #01: TREE3.IDLINK Alias:IDLINK Type:INTEGER Field #02: TREE3.IDTARGETLINK Alias:IDTARGETLINK Type:INTEGER Field #03: TREE3.IDITEM Alias:IDITEM Type:INTEGER Field #04: TREE3.NODEPOSITION Alias:NODEPOSITION Type:INTEGER PLAN SORT (SORT (JOIN (TR INDEX (IDX_TREE3_3), ART INDEX (RDB$PRIMARY2 873306 fetches, 0 marks, 1961 reads, 0 writes. 0 inserts, 0 updates, 0 deletes, 380575 index, 0 seq. Delta memory: 0 bytes. Total execution time: 1.027s Again thank you! --- In firebird-support@yahoogroups.com, Svein Erling Tysvær wrote: > > >PLAN SORT (SORT (JOIN (TR INDEX (IDX_TREE3_2, IDX_TREE3_3), ART INDEX > >(RDB$PRIMARY2 > > Hmm, I don't think you want the index for IDX_TREE3_2 to be used here. Being > the first table in the plan and noticing that Tr.iditem is only used in JOIN > ... ON art.idarticle = tr.iditem, I think Firebird actually "changes" your > query to use tr.iditem > 0 rather than art.IDARTICLE > 0 and my hunch is that > most rows satisfy this criterion. > > >SELECT FIRST 30 SKIP 0 distinct tr.IdLink, tr.IdTargetLink, tr.IdItem, > >tr.NodePosition > >FROM Tree3 tr > >inner join article3_1 art on art.idarticle = Tr.iditem > >WHERE tr.ItemType = 2 AND tr.Market1 = 1 > >AND ((art.IDARTICLE > 0) AND ( (LOWER(art.Artikelnummer) like '%a4 > >papier%' ) OR ( (LOWER(art.Artikelnummer) like 'a4' ) > >AND (LOWER(art.Artikelnummer) like 'papier')) OR (LOWER(art.Artikelnaam1) > >like '%a4 papier%' ) OR ( (LOWER(art.Artikelnaam1) like '%a4%' ) > >AND (LOWER(art.Artikelnaam1) like '%papier%')) OR (LOWER(art.Artikelnaam2) > >like '%a4 papier%' ) OR ( (LOWER(art.Artikelnaam2) like '%a4%' ) > >AND (LOWER(art.Artikelnaam2) like '%papier%')) OR > >(LOWER(art.Artikelnr_leverancier) like '%a4 papier%' ) OR ( > >(LOWER(art.Artikelnr_leverancier) like '%a4%' ) > >AND (LOWER(art.Artikelnr_leverancier) like '%papier%')) OR > >(LOWER(art.Merk) like '%a4 papier%' ) OR ( (LOWER(art.Merk) like '%a4%' ) > >AND (LOWER(art.Merk) like '%papier%')) OR (LOWER(art.EAN) like '%a4 > >papier%' ) OR ( (LOWER(art.EAN) like '%a4%' ) > >AND (LOWER(art.EAN) like '%papier%')) OR (LOWER(art.AltArtnr1) like '%a4 > >papier%' ) OR ( (LOWER(art.AltArtnr1) like '%a4%' ) > >AND (LOWER(art.AltArtnr1) like '%papier%')) OR (LOWER(art.AltArtnr2) like > >'%a4 papier%' ) OR ( (LOWER(art.AltArtnr2) like '%a4%' ) > >AND (LOWER(art.AltArtnr2) like '%papier%')) )) > >AND tr.NODELEVEL =5 and tr.LINKTYPE <> 5 > >ORDER BY tr.NodePosition > > You use so many parenthesis that I had to restructure your query to easier > understand it. Doing that made me notice a minor error. I think the below > query is identical to your original query: > > SELECT distinct tr.IdLink, tr.IdTargetLink, tr.IdItem, tr.NodePosition > FROM Tree3 tr > inner join article3_1 art on art.idarticle = Tr.iditem > WHERE tr.ItemType = 2 > AND tr.Market1 = 1 > AND art.IDARTICLE+0 > 0 > AND (LOWER(art.Artikelnummer) like '%a4 papier%' > OR (LOWER(art.Artikelnummer) like 'a4' > AND LOWER(art.Artikelnummer) like 'papier') > OR (art.Artikelnaam1 containing 'a4' AND art.Artikelnaam1 containing > 'papier') > OR (art.Artikelnaam2 containing 'a4' AND art.Artikelnaam2 containing > 'papier') > OR (art.Artikelnr_leverancier containing 'a4' AND > art.Artikelnr_leverancier containing 'papier') > OR (art.Merk containing 'a4' AND art.Merk containing 'papier') > OR (art.EAN containing 'a4' AND art.EAN containing 'papier') > OR (art.AltArtnr1 containing 'a4' AND art.AltArtnr1 containing 'papier') > OR (art.AltArtnr2 containing 'a4' AND art.AltArtnr2 containing 'papier')) > AND tr.NODELEVEL = 5 > AND tr.LINKTYPE <> 5 > ORDER BY tr.NodePosition > ROWS 1 TO 30 > > I didn't change art.Artikelnummer, to highlight that you don't have wildcards > for a4 and papier here. Change it to be like the others if you intended to > have wildcards, remove the OR statement entirely if not since it cannot be > equal to both a4 and papier. Elsewhere, I've generally ignored "like '%a4 > papier%'" since it is a subset of "like '%a4%' or like '%papier%'". > Furthermore, I find using CONTAINING to be simpler than using LOWER() or > UPPER(). > > Unfortunately, excepting the addition of +0, I don't think the modifications > I've done helps you too much speedwise (but you should of course try it and > report the plan to this list). How many records and how large a proportion of > the records in Tree3 has Market1 = 1? Are there many duplicates if you remove > DISTINCT from your query? > > If papier and a4 are fixed values to be used in this query, you may consider > adding a trigger populated Booleanis
[firebird-support] Re: Is it possible to perform such insert?
Thank you Svein :)
