Re: [firebird-support] Re: epel firebird 2.1.5
On Mon, 29 Oct 2012 09:40:35 +, Nick Upson n...@telensa.com wrote: Hi, it looks like this one was missed after upgrading from 2.1.4 2.1.5.0-2 to I had to manually do this: ln -s /usr/lib/firebird/lib/libfbclient.so.2.1.5 /usr/lib/libgds.so To be honest, I think that is not a bug. Applications that use Firebird should stop using things like libgds.so (or gds32.dll), and only if you really need to should these links or files be created, not by default by the installer. Mark
[firebird-support] Re: Query is running slowly for the first time.
Just to be sure: you did remove the comment symbol (#) before the config option and restart the server after changing it? The default Firebird config only has commented out config options, so it uses defaults. Mark Yes Mark:) I Appreciate your concern tho :)
[firebird-support] documentation of implemented features with Fb 3
Hello, Is there any way to find documentation of specific new features in Firebird 3, so I could test, and even write blog posts on it (to tech others about the features) ? Thanks, Ido
[firebird-support] Raising the scale of a NUMERIC field
I need to change the scale of numeric domain from (15,4) to (15,8). I cannot use the ALTER command, since FB doesn't allow such change. BUT I'm 100% sure that all the existing data would perfectly fit in (15,8) so, the question is: In this case, is it safe to make the change direct in the system table? update RDB$FIELDS set RDB$FIELD_SCALE = -8 where RDB$FIELD_NAME = 'RDB$'; []s Carlos Firebird Performance in Detail - http://videos.firebirddevelopersday.com www.firebirdnews.org - www.FireBase.com.br
[firebird-support] Firebird 2.5.2 RC1 broken links
Hi, The links on the official firebird web page for the Firebird 2.5.2 RC1 Windows x64 are down (http://www.firebirdsql.org/en/firebird-2-5-2-rc1/). Thanks. Jaume [Non-text portions of this message have been removed]
Re: [firebird-support] Raising the scale of a NUMERIC field
On Mon, 29 Oct 2012 13:32:16 -0200, Carlos H. Cantu lis...@warmboot.com.br wrote: I need to change the scale of numeric domain from (15,4) to (15,8). I cannot use the ALTER command, since FB doesn't allow such change. BUT I'm 100% sure that all the existing data would perfectly fit in (15,8) so, the question is: In this case, is it safe to make the change direct in the system table? update RDB$FIELDS set RDB$FIELD_SCALE = -8 where RDB$FIELD_NAME = 'RDB$'; No, a direct system table update like that would rescale all your existing numbers. Eg from (15,4) to (15,8) a value 54321.1234 would become 5.43211234 Firebird stores NUMERIC (and DECIMAL) as an INT (or BIGINT or SMALLINT) and the scale is used to decide the position of the decimal. Mark
Re: [firebird-support] Raising the scale of a NUMERIC field
MR No, a direct system table update like that would rescale all your existing MR numbers. Eg from (15,4) to (15,8) a value 54321.1234 would become MR 5.43211234 Not really. You can test by yourself... seems that stored values are associated with a record in RDB$FORMATS that describes the original format when they were stored, and when FB retrieves the value, it respects the original format (thanks Ivan for the note). My tests showed no problem with my proposed approach. I posted here just to make sure that it would not have any side effects that I didnt see yet. If someone knows any, please post here. []s Carlos Firebird Performance in Detail - http://videos.firebirddevelopersday.com www.firebirdnews.org - www.FireBase.com.br MR On Mon, 29 Oct 2012 13:32:16 -0200, Carlos H. Cantu MR lis...@warmboot.com.br wrote: I need to change the scale of numeric domain from (15,4) to (15,8). I cannot use the ALTER command, since FB doesn't allow such change. BUT I'm 100% sure that all the existing data would perfectly fit in (15,8) so, the question is: In this case, is it safe to make the change direct in the system table? update RDB$FIELDS set RDB$FIELD_SCALE = -8 where RDB$FIELD_NAME = 'RDB$';
[firebird-support] firebird.conf improve for lot of memory
Hello I Recently buied a new server with 48 Gb of memory Currently my system only use 15 Gb of memory I want an opinion with values i can change on firebird.conf to use more memory an chage my performance tanks a lot
Re: [firebird-support] Raising the scale of a NUMERIC field
On Mon, Oct 29, 2012 at 12:20 PM, Mark Rotteveel m...@lawinegevaar.nlwrote: BUT I'm 100% sure that all the existing data would perfectly fit in (15,8) so, the question is: In this case, is it safe to make the change direct in the system table? update RDB$FIELDS set RDB$FIELD_SCALE = -8 where RDB$FIELD_NAME = 'RDB$'; No, a direct system table update like that would rescale all your existing numbers. Eg from (15,4) to (15,8) a value 54321.1234 would become 5.43211234 Firebird stores NUMERIC (and DECIMAL) as an INT (or BIGINT or SMALLINT) and the scale is used to decide the position of the decimal. Ah, you've missed the magic of formats. Every record version is stored with a format version number. Every time a table changes shape - meaning a column is added, dropped, or altered in length or scale, Firebird stores a new format version for the table with the new physical description. A transaction asks for a record in a particular format. Firebird does what is necessary to convert the format it finds to the requested format. On update, a the record is stored in the newest format. So 54321.1234 would become 54321.1234. The new format is created when a change to the system tables is committed. In V3.0, your mileage will change. Cheers, Ann [Non-text portions of this message have been removed]
RE: [firebird-support] firebird.conf improve for lot of memory
I Recently buied a new server with 48 Gb of memory Currently my system only use 15 Gb of memory I want an opinion with values i can change on firebird.conf to use more memory an chage my performance Increasing firebird memory is not necessarily required to improve performance. But let's stay on the topic you posted: What release of Firebird are you running? What OS? What engine version (Classic, SuperClassic or Superserver)? Sean
Re: [firebird-support] Raising the scale of a NUMERIC field
On 29-10-2012 17:48, Carlos H. Cantu wrote: MR No, a direct system table update like that would rescale all your existing MR numbers. Eg from (15,4) to (15,8) a value 54321.1234 would become MR 5.43211234 Not really. You can test by yourself... seems that stored values are associated with a record in RDB$FORMATS that describes the original format when they were stored, and when FB retrieves the value, it respects the original format (thanks Ivan for the note). My tests showed no problem with my proposed approach. I posted here just to make sure that it would not have any side effects that I didnt see yet. If someone knows any, please post here. Ok, good to know that. I didn't know that the formats were also maintained when not using DDL. Mark -- Mark Rotteveel
[firebird-support] Re: firebird.conf improve for lot of memory
--- In firebird-support@yahoogroups.com, Leyne, Sean Sean@... wrote: I Recently buied a new server with 48 Gb of memory Currently my system only use 15 Gb of memory I want an opinion with values i can change on firebird.conf to use more memory an chage my performance Increasing firebird memory is not necessarily required to improve performance. But let's stay on the topic you posted: What release of Firebird are you running? What OS? What engine version (Classic, SuperClassic or Superserver)? Sean Hello Firebird 2.5.1 Super Classic Windows server 2012
RES: [firebird-support] Re: firebird.conf improve for lot of memory
Performance is not only memory, is a combination of network speed, hard drive speed, memory size and speed and finally processor(s) speed. You do not need to do that. Maybe if you do not know exactly what are you doing you can get worst performance. [Non-text portions of this message have been removed]
Re: [firebird-support] Re: firebird.conf improve for lot of memory
How many simultaneous users? What database size? How complex is metadata?
[firebird-support] Firebird 2.5.2.26536
Hi all, I have been looking into my Firebird.log on my Windows 7 64bit running 64bit Firebird 2.5.2.26536 and I can see very often: DEVELOP (Client)Mon Oct 29 20:56:59 2012 C:\Program Files\Firebird\Firebird_2_5\bin\fbserver.exe: terminated abnormally (4294967295) What could be a reason or solution? Thanks. -- Best regards, TRoland http://www.rotursoft.sk http://exekutor.rotursoft.sk
[firebird-support] UNION prevents all records from being pulled to the results
Greetings All, The SQL that follows pulls these results: (These results are missing 5 records from the second select of the union) FEE_CODEAMT_EARNED_AGENCYSHOW_IN_PMT_DIST_PLANFEE_CATEGORY CCO27610 SVC1.1710 SVC3010 This is the SQL statement: SELECT FC.FEE_CODE, COALESCE(PCF.CASE_FEE_AMT_EARNED_AGENCY_1 + PCF.CASE_FEE_AMT_EARNED_AGENCY_2, 0) AS AMT_EARNED_AGENCY, FC.SHOW_IN_PMT_DIST_PLAN, FC.FEE_CATEGORY FROM PMT_CASE_FEE PCF JOIN PMT_CASE PC ON PC.ACCT_ID = PCF.ACCT_ID AND PC.CASE_ID = PCF.CASE_ID AND PC.PMT_ID = PCF.PMT_ID AND PC.PMT_NO = PCF.PMT_NO JOIN CASE_FEE_HISTORY CFH ON CFH.CASE_FEE_ID = PCF.CASE_FEE_ID AND CFH.PMT_ID = PCF.PMT_ID JOIN CASE_FEE CF ON CF.CASE_FEE_ID = PCF.CASE_FEE_ID JOIN FEE_CODE FC ON FC.FEE_ID = CF.FEE_ID WHERE PCF.PMT_ID = :iPmtID AND PCF.PMT_NO = :iPmtNo AND PCF.ACCT_ID = :iAcctID AND PCF.CASE_ID = :iCaseID UNION SELECT FC.FEE_CODE, COALESCE(PDF.DEBT_FEE_AMT_EARNED_AGENCY_1 + PDF.DEBT_FEE_AMT_EARNED_AGENCY_2, 0) AS AMT_EARNED_AGENCY, FC.SHOW_IN_PMT_DIST_PLAN, FC.FEE_CATEGORY FROM PMT_DEBT_FEE PDF JOIN PMT_DEBT PD ON PD.ACCT_ID = PDF.ACCT_ID AND PD.DEBT_NO = PDF.DEBT_NO AND PD.PMT_ID = PDF.PMT_ID AND PD.PMT_NO = PDF.PMT_NO JOIN DEBT_FEE_HISTORY DFH ON DFH.DEBT_FEE_ID = PDF.DEBT_FEE_ID AND DFH.PMT_ID = PDF.PMT_ID JOIN DEBT_FEE DF ON DF.DEBT_FEE_ID = PDF.DEBT_FEE_ID JOIN FEE_CODE FC ON FC.FEE_ID = DF.FEE_ID JOIN DEBT D ON D.ACCT_ID = PDF.ACCT_ID AND D.DEBT_NO = PDF.DEBT_NO WHERE PDF.PMT_ID = :iPmtID AND PDF.PMT_NO = :iPmtNo AND D.ACCT_ID = :iAcctID AND D.CASE_ID = :iCaseID If I execute just the first select in the union it pulls these results FEE_CODEAMT_EARNED_AGENCYSHOW_IN_PMT_DIST_PLANFEE_CATEGORY CCO27610 If I execute the second select in the union it pulls these results FEE_CODEAMT_EARNED_AGENCYSHOW_IN_PMT_DIST_PLANFEE_CATEGORY SVC3010 SVC3010 SVC3010 SVC3010 SVC3010 SVC1.1710 SVC3010 Again, executing the select statements separately results in the correct records being returned, executing both selects with the union omits 5 records. Using Firebird 1.5.3 Any ideas? Thanks, Mike
Re: [firebird-support] UNION prevents all records from being pulled to the results
Em 29/10/2012 18:39, SoftTech escreveu: Greetings All, The SQL that follows pulls these results: (These results are missing 5 records from the second select of the union) FEE_CODEAMT_EARNED_AGENCYSHOW_IN_PMT_DIST_PLANFEE_CATEGORY CCO27610 SVC1.1710 SVC3010 This is the SQL statement: SELECT FC.FEE_CODE, COALESCE(PCF.CASE_FEE_AMT_EARNED_AGENCY_1 + PCF.CASE_FEE_AMT_EARNED_AGENCY_2, 0) AS AMT_EARNED_AGENCY, FC.SHOW_IN_PMT_DIST_PLAN, FC.FEE_CATEGORY FROM PMT_CASE_FEE PCF JOIN PMT_CASE PC ON PC.ACCT_ID = PCF.ACCT_ID AND PC.CASE_ID = PCF.CASE_ID AND PC.PMT_ID = PCF.PMT_ID AND PC.PMT_NO = PCF.PMT_NO JOIN CASE_FEE_HISTORY CFH ON CFH.CASE_FEE_ID = PCF.CASE_FEE_ID AND CFH.PMT_ID = PCF.PMT_ID JOIN CASE_FEE CF ON CF.CASE_FEE_ID = PCF.CASE_FEE_ID JOIN FEE_CODE FC ON FC.FEE_ID = CF.FEE_ID WHERE PCF.PMT_ID = :iPmtID AND PCF.PMT_NO = :iPmtNo AND PCF.ACCT_ID = :iAcctID AND PCF.CASE_ID = :iCaseID UNION SELECT FC.FEE_CODE, COALESCE(PDF.DEBT_FEE_AMT_EARNED_AGENCY_1 + PDF.DEBT_FEE_AMT_EARNED_AGENCY_2, 0) AS AMT_EARNED_AGENCY, FC.SHOW_IN_PMT_DIST_PLAN, FC.FEE_CATEGORY FROM PMT_DEBT_FEE PDF JOIN PMT_DEBT PD ON PD.ACCT_ID = PDF.ACCT_ID AND PD.DEBT_NO = PDF.DEBT_NO AND PD.PMT_ID = PDF.PMT_ID AND PD.PMT_NO = PDF.PMT_NO JOIN DEBT_FEE_HISTORY DFH ON DFH.DEBT_FEE_ID = PDF.DEBT_FEE_ID AND DFH.PMT_ID = PDF.PMT_ID JOIN DEBT_FEE DF ON DF.DEBT_FEE_ID = PDF.DEBT_FEE_ID JOIN FEE_CODE FC ON FC.FEE_ID = DF.FEE_ID JOIN DEBT D ON D.ACCT_ID = PDF.ACCT_ID AND D.DEBT_NO = PDF.DEBT_NO WHERE PDF.PMT_ID = :iPmtID AND PDF.PMT_NO = :iPmtNo AND D.ACCT_ID = :iAcctID AND D.CASE_ID = :iCaseID If I execute just the first select in the union it pulls these results FEE_CODEAMT_EARNED_AGENCYSHOW_IN_PMT_DIST_PLANFEE_CATEGORY CCO27610 If I execute the second select in the union it pulls these results FEE_CODEAMT_EARNED_AGENCYSHOW_IN_PMT_DIST_PLANFEE_CATEGORY SVC3010 SVC3010 SVC3010 SVC3010 SVC3010 SVC1.1710 SVC3010 Again, executing the select statements separately results in the correct records being returned, executing both selects with the union omits 5 records. Using Firebird 1.5.3 Any ideas? Thanks, Mike Use UNION ALL to avoid removing duplicates
Re: [firebird-support] Firebird 2.5.2.26536
DEVELOP (Client) Mon Oct 29 20:56:59 2012 C:\Program Files\Firebird\Firebird_2_5\bin\fbserver.exe: terminated abnormally (4294967295) What could be a reason or solution? Do You use udf? If you usually have this error, configure Windows to create a crash dump and open a ticket in the tracker with that information. http://www.ibphoenix.com/resources/documents/development/doc_35 Regards, Jesús [Non-text portions of this message have been removed]
[firebird-support] Re: UNION prevents all records from being pulled to the results
SoftTech wrote: The SQL that follows pulls these results: (These results are missing 5 records from the second select of the union) FEE_CODE AMT_EARNED_AGENCY SHOW_IN_PMT_DIST_PLAN FEE_CATEGORY CCO2 76 1 0 SVC 1.17 1 0 SVC 30 1 0 ... ... From Helen's Firebird book: If duplicate rows are formed during the creating of the union set, the default behavior is to exclude the duplicate rows from the set. To include the duplicates, use UNION ALL instead of UNION on its own . -- Aage J. [Non-text portions of this message have been removed]
Re: [firebird-support] Re: UNION prevents all records from being pulled to the results
On Mon, Oct 29, 2012 at 5:06 PM, Aage Johansen aagjo...@online.no wrote: SoftTech wrote: The SQL that follows pulls these results: (These results are missing 5 records from the second select of the union) FEE_CODE AMT_EARNED_AGENCY SHOW_IN_PMT_DIST_PLAN FEE_CATEGORY CCO2 76 1 0 SVC 1.17 1 0 SVC 30 1 0 ... ... From Helen's Firebird book: If duplicate rows are formed during the creating of the union set, the default behavior is to exclude the duplicate rows from the set. To include the duplicates, use UNION ALL instead of UNION on its own . And if anybody is curious, yes, that is the behavior described in the ANSI/ISO SQL Standard.One of the few holdovers from set theory in SQL. Good luck, Ann [Non-text portions of this message have been removed]
[firebird-support] Parameterised like query won't use index in the plan
Hello, I have table with an indexed field (Firebird 2.1/2.5). When I use the query: select * from mytable where myfield like 'test' the plan uses the index on myfield and the query returns quickly. When I use the query: select * from mytable where myfield like :myparameter and define myparameter = 'test' the plan doesn't use the index (natural) and my query returns slowly. The big question is: What am I doing wrong? Any help is appreciated. Regards, Roy Damman
Re: RES: [firebird-support] Re: firebird.conf improve for lot of memory
From: Fabiano fabianoas...@gmail.com To: firebird-support@yahoogroups.com Sent: Monday, October 29, 2012 12:52 PM Subject: RES: [firebird-support] Re: firebird.conf improve for lot of memory Performance is not only memory, is a combination of network speed, hard drive speed, memory size and speed and finally processor(s) speed. You do not need to do that. Maybe if you do not know exactly what are you doing you can get worst performance. [Non-text portions of this message have been removed] [Non-text portions of this message have been removed]
Re: [firebird-support] Parameterised like query won't use index in the plan
Em 29/10/2012 20:02, roydamman escreveu: Hello, I have table with an indexed field (Firebird 2.1/2.5). When I use the query: select * from mytable where myfield like 'test' the plan uses the index on myfield and the query returns quickly. When I use the query: select * from mytable where myfield like :myparameter and define myparameter = 'test' the plan doesn't use the index (natural) and my query returns slowly. The big question is: What am I doing wrong? Any help is appreciated. Regards, Roy Damman Once it's a parameter, the parameter could hold any value like: 'ABC' 'ABC%' '%ABC' the first two could use an index during a search the last on could not use any index. The optimizer shoul prepare the path for all the scenarios. You could change your query to something like: select * from mytable where myfield starting with :myparameter This way an index would be used. In fact in a non parameterized query, your original query has a kind of code injection, and become something like: select * from mytable where myfield like 'test%' and myfield starting with 'test' Thus the index could be used. see you !
[firebird-support] Re: Parameterised like query won't use index in the plan
When I use the query: select * from mytable where myfield like :myparameter and define myparameter = 'test' the plan doesn't use the index (natural) and my query returns slowly. Once it's a parameter, the parameter could hold any value like: 'ABC' 'ABC%' '%ABC' the first two could use an index during a search the last on could not use any index. The optimizer shoul prepare the path for all the scenarios. Of course, thank you for your answer. I create my queries dynamically each time and thought the use of parameters would be less problematic (escaping etc.). You could change your query to something like: select * from mytable where myfield starting with :myparameter This way an index would be used. In fact in a non parameterized query, your original query has a kind of code injection, and become something like: select * from mytable where myfield like 'test%' and myfield starting with 'test' Thus the index could be used. Yes, that's a possibility. But I think I still need to create the statement without parameters for string values to give my users the maximum flexibility and performance. Once again, thank you for you answer.
Re: [firebird-support] Parameterised like query won't use index in the plan
At 11:02 AM 30/10/2012, roydamman wrote: Hello, I have table with an indexed field (Firebird 2.1/2.5). When I use the query: select * from mytable where myfield like 'test' the plan uses the index on myfield and the query returns quickly. When I use the query: select * from mytable where myfield like :myparameter and define myparameter = 'test' the plan doesn't use the index (natural) and my query returns slowly. The big question is: What am I doing wrong? Any help is appreciated. Don't use LIKE for an equivalence query. The purpose of LIKE is to pass a string with wildcard characters (_ or % in SQL). Your literal query self-converts to an equivalence query, viz., select * from mytable where myfield = 'test' or to a STARTING WITH query select * from mytable where myfield starting with 'test' Both of these operators use indexes if they are available. Your parameterised query is prepared in anticipation of a string that starts or ends (or both) with a wildcard, e.g., test%, %test or %test%. In preparing this query the engine does not know what it will get in the parameter. It can't speculatively prepare a plan that anticipates the possibility that it will get a literal with no wildcards (like your example) or test% (which it can convert to STARTING WITH) - so the plan is stuck with LIKE's inability to use an index. ./heLen Regards, Roy Damman ++ 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
[firebird-support] Stored Proc vs View, which one has better performance?
Hi all, I have a table, let say Tbl1, which structure something like this id_cst int, id_gd int, qty int. I want to select from that table, all records that has sum(qty) 0 group by id_cst and id_gd. I have 2 options to do that : 1) create a view with a ddl like this : create view tmp (id_cst, id_gd, qty) as select id_cst,id_gd,sum(qty) from Tbl1 group by id_cst,id_gd then select that view with ddl : select * from tmp where qty 0 2) create a stored procedure that returns all records which qty 0 Between those options, with one has a better performance? Thanks and regards, Sugi.
RE: [firebird-support] Stored Proc vs View, which one has better performance?
Sugi, I have a table, let say Tbl1, which structure something like this id_cst int, id_gd int, qty int. I want to select from that table, all records that has sum(qty) 0 group by id_cst and id_gd. I have 2 options to do that : 1) create a view with a ddl like this : create view tmp (id_cst, id_gd, qty) as select id_cst,id_gd,sum(qty) from Tbl1 group by id_cst,id_gd then select that view with ddl : select * from tmp where qty 0 2) create a stored procedure that returns all records which qty 0 Between those options, with one has a better performance? The performance should be almost identical -- not worth worrying about. There would/could be other reasons for choosing one approach versus the other, but performance for the problem you outlined is not one of them. Depending on the number entries in Tbl1, I would argue that neither approach would be best. A temporary table where the current total_qty would be maintained would be a better approach, but there are issues about potential transaction deadlocks which you would need to work through, but they are workable. Sean
