Re: [firebird-support] Store Procedure for updating data, why it is need to commit?
Yes, I needed it, thanks for your answer. Regards, Anto
[firebird-support] Store Procedure for updating data, why it is need to commit?
Hi guys, I have a simple stored procedure to update data, like this : SET TERM ^ ; CREATE PROCEDURE REG AS BEGIN UPDATE table a set a.field1 = '123'; exit; END^ SET TERM ; ^ When I run that procedure on flamerobin with this command: execute procedure Reg. It runs ok, but without a commit command, the field1 on table won't get update. I rarely use store procedure to do update / insert / delete, but is this a normal behavior? Is there any commands to add so commit is no needed? Thanks in advance.
Re: [firebird-support] Store Procedure for updating data, why it is need to commit?
Hi, Thanks for your replied. Tried to use execute STATEMENT 'COMMIT', raised and error Explicit transaction control is not allowed
[firebird-support] RE: Error Not Enough Disk Space
Dear all, Thanks for your answers. As I know FAT32 is not limited to 4 GB, also manual copy to that disk for a size more than 4 GB is okay. Anyway, I have changed the format to NTFS and run a test again. May be a few days will know if this will solve the problem. Regards, Anto ---In firebird-support@yahoogroups.com, trskopo@... wrote: Dear all, I have a strange error message, tell me that there is not enough disk space while actually it is enough space. Here are the environment : - database size is 4 GB, running on RAM Disk formatted as FAT32, free space still 4 GB - Firebird Super Classic 2.5.2 64 bit - Windows 7 64 bit with 16 GB memory Is that because database running on RAM, but there is still a lot of free space? Testing and running database on physical disk seem ok, but it is very slow since don't have rapid harddisk. Please help me to solve the problem, and thanks in advance. Regards, Anto
[firebird-support] Error Not Enough Disk Space
Dear all, I have a strange error message, tell me that there is not enough disk space while actually it is enough space. Here are the environment : - database size is 4 GB, running on RAM Disk formatted as FAT32, free space still 4 GB - Firebird Super Classic 2.5.2 64 bit - Windows 7 64 bit with 16 GB memory Is that because database running on RAM, but there is still a lot of free space? Testing and running database on physical disk seem ok, but it is very slow since don't have rapid harddisk. Please help me to solve the problem, and thanks in advance. Regards, Anto
[firebird-support] Firebird 3.0 failed to start using flamerobin
Hi all, Anyone here already tried FB 3.0 Alpha? I have installed it, and when when tried to connect via flamerobin, I got error message : GDS::Call() Can't find or load fbclient.dll or gds32.dll Environment : WinXp 64 bit, Superserver FB. Any hints to fix it? Thanks and regards, Anto
[firebird-support] Re: Help, tunning database
Dear all, Today, I faced same problem again, which is Firebird server became not responsive. Here are the latest settings : - win 7 64 bit - Firebirds 2.5.1 super server - page size 8192 - page buffer 8192 - no long transactions - temp cache limit set to 128MB - there are a few numbers errors in insert operations but all already rollback. I was not restarted FB server yet. I think if I do that FB will became normal again. Every times there are errors from execution of SQL statements (insert,delete ,update), although transactions has been rollback, FB became not responsive. Any ideas how to solve this problem without restarting FB server? Thanks, Anto
[firebird-support] Re: How to maximize cpu usage for Firebird?
How do you know that you are CPU and not I/O bound? SuperClassic is capable to utilize SMP environments, but simplified one core per database connection. If you increase the number of database connections with CPU bound stuff, you should see an increase of CPU utilization. -- With regards, Thomas Steinmaurer http://www.upscene.com/ Professional Tools and Services for Firebird FB TraceManager, IB LogManager, Database Health Check, Tuning etc. Thanks for your reply. I run a few queries, all almost stuck at 16% cpu's utilization, so I guess, file I/O is ok. I have only 1 database in this machine. You said SuperClassic use 1 core per database connection. For 1 connection I got cpu's utilization about 16%, so, if there are 8 connection to the same database, running the same task, cpu's utilization will jump to 100%? Regards, Anto
[firebird-support] Re: How to maximize cpu usage for Firebird?
I doubt 100%, but more than 16%, if the other connections/workloads forces the Firebird server process to be CPU bound. Check out my following demo video here, discussing SMP utilization with different Firebird architectures: http://www.iblogmanager.com/download/demos/firebird/fb25_architectures_smp.html -- With regards, Thomas Steinmaurer http://www.upscene.com/ Professional Tools and Services for Firebird FB TraceManager, IB LogManager, Database Health Check, Tuning etc. Thanks for your video, very informative. Regards, Anto
[firebird-support] How to maximize cpu usage for Firebird?
Hi all, Using Firebird Super Classic in Win 7 64 on I7 2600K, I noticed that on a high load, Firebird server never utilize cpu more than 16%. Since the machine is a dedicated Firebird Server, are there any settings so Firebird server can utilize more cpu? Thanks and regrads, Anto
[firebird-support] Re: How to maximize cpu usage for Firebird?
Sorry, It it FB 2.5.2 You didn't mention which FB version you are running. While SuperClassic is the future for FB, I must say that we only use/deploy Classic for all our installs (100+ servers around the world). Sean
[firebird-support] Re: How to maximize cpu usage for Firebird?
--- In firebird-support@yahoogroups.com, Alexandre Benson Smith iblist@... wrote: Perhaps the bottleneck is not the CPU, maybe your disk subsystem can't provide information as fast as the CPU can process it. I think your CPU is sitting idle while waiting the disc provides the data. I/O bound x CPU bound process... see you ! Thanks for your reply. My database is on RAM, as I know it is the most fastest I/O for Firebird. I was watching cpu's utilization via window's task manager, almost constantly at 16%. If there was a bottleneck in I/O, whenever I/O processing finished, I should saw some raising in cpu's utilization. I though this was no bottleneck, it just seems that FB Server couldn't utilized more power from cpu. Anto
[firebird-support] Help, tunning database
Hi all, I have Superserver Firebird 2.5.1 installed on Win7 pro 64 bit on I7 2600K. When data grows, Firebird Server starting to slow down. I shutdown Firebird Server, starting again without changing anything and Firebird Server became smooth again. Any ideas, what is the problem with this? Firebird.conf mostly on its default values except for cpuaffinity set to 15 and DefaultDbCachePages set to 4096. Anto
[firebird-support] Re: Help, tunning database
--- In firebird-support@yahoogroups.com, Jesús GarcÃa jegaza@... wrote: El 15/07/2013, a las 16:31, trskopo trskopo@... escribió: Hi all, I have Superserver Firebird 2.5.1 installed on Win7 pro 64 bit on I7 2600K. When data grows, Firebird Server starting to slow down. I shutdown Firebird Server, starting again without changing anything and Firebird Server became smooth again. Any ideas, what is the problem with this? Firebird.conf mostly on its default values except for cpuaffinity set to 15 and DefaultDbCachePages set to 4096. Anto Probably is long running transactions. I have had this issue in two scenarios. One is long running transactions that makes db slowdown and the other one is when an auto sweep starts, because a long running transaction, there is an issue (that will be solved in 2.5.3) that makes Firebird run sweep several times. I have databases of 30 gb running fine for months without issues except when there is a long running transaction. The last issue was because gbak was out space and the gbak's transaction was opened for days. Our customer called us because the slowdown and we killed the gbak process and then auto sweep started. The sweep runned more than 2 hour (i think because the bug in 2.5.2) and after that all was running fine. (When i run a manual sweep in that database takes 20 minutes and does not slowdown the engine like when auto sweep is started) Jesús [Non-text portions of this message have been removed] Thanks for your reply. How do I check that there is a long transaction running? Anto
[firebird-support] Re: Have a lots memory, but FB could not take advantages
Thanks for all replays. I did a small test, here are the settings : - FB Super Classic 2.5.1 64 bit - Win7 pro 64 bit - Database size about 640mb - Select something like this : select a.field1, b.field2, sum(c.field3) from table1 a, table2 b, table3 c where group by - temp directory set to disk - temp cache set to 64mb Here are the options : 1) Database put on a disk. Page Size 16KB, DefaultDbCache 8192. Execution time is about 1 min. 2) Database put on a RAM. Page Size 4KB, DefaultDbCache 0. (Although DefaultDbCache set to 0, flamerobin indicate the value of Page Buffer = 50) Execution time is about 35 second. During each test, I reboot the comp. Option 1 consume 128 MB memory per connection, while option 2 consume 200KB per connection and 640MB for database file. So, I guess, by having database on RAM (if possible), is the top tweaking possible regarding file I/O.
[firebird-support] Re: Have a lots memory, but FB could not take advantages
Thanks for all replays. I did a small test, here are the settings : - FB Super Classic 2.5.1 64 bit - Win7 pro 64 bit - Database size about 640mb - Select something like this : select a.field1, b.field2, sum(c.field3) from table1 a, table2 b, table3 c where group by - temp directory set to disk - temp cache set to 64mb Here are the options : 1) Database put on a disk. Page Size 16KB, DefaultDbCache 8192. Execution time is about 1 min. 2) Database put on a RAM. Page Size 4KB, DefaultDbCache 0. (Although DefaultDbCache set to 0, flamerobin indicate the value of Page Buffer = 50) Execution time is about 35 second. 3) Database put on a disk. Page Size 16KB, DefaultDbCache 0. Execution time is more than 2 min. During each test, I reboot the comp. Option 1 consumed 128 MB memory per connection, while option 2 consumed 200KB per connection and 640MB for database file. So, I guess, by having database on RAM (if possible), is the top tweaking possible regarding file I/O. Anto
[firebird-support] TempCacheLimit, how it is works?
Hi all, Suppose sql statement issued like this : select * from table where field = 0 If this statement cached by TempCacheLimit, is memory consumption use by TempCacheLimit released after statement finished or remain? Also in Super Classic setting, TempCacheLimit shared or applies for each connection? Thanks and regards, Anto
[firebird-support] DefaultDbCachePages, does it has effects when db placed on RAM?
Hi all, When database file placed on RAM, should I decrease or increase the value of DefaultDbCachePages? Does it has effects on performance, since database already placed on RAM? Thanks and regards, Anto
[firebird-support] Hyper Threading, should it turn on or off?
Hi all, I have windows 7 64bit pro with intel I7 and firebird 2.5.1 Super Classic. Should I turn HT on or off to boost firebird's performance? On some app, when HT is on, performance decrease, what about for firebird? Thanks and regards, Anto
[firebird-support] Question about Temp Directory
Hi all, I set temp directory for firebird 2.5.1 Super Server in firbird.conf like this : TempDirectories =e:\;d:\temp On Win 7 64, also set environment like these : - FIREBIRD_TMP = e:\;d:\temp - TEMP = e:\ - TMP = e:\ How do I know, firebird using that temporary directory? Running a few query via flamerobin, seems that firebird not using that directory. Thanks and regards, Anto
[firebird-support] Re: How to improve Firebird performance on a large table?
Dear Alan, Thanks for your reply. This is my table ddl : CREATE TABLE LOG ( ID Integer NOT NULL, ID_TBL Integer NOT NULL, TBL_NM Varchar(40) NOT NULL, OPR Char(1) NOT NULL, LOG_AT Timestamp DEFAULT CURRENT_TIMESTAMP NOT NULL, SNC Smallint DEFAULT 0, PRIMARY KEY (ID) ); I think, I found the problem. I made a small app that seek for snc's value every second. It seems makes firebird server works heavily. Changing seeking time from 1 second to 10 seconds seems solved the problem. Thanks anyway for your suggestions. Best regards, Anto. --- In firebird-support@yahoogroups.com, Alan J Davies Alan.Davies@... wrote: Without knowing your setup, its difficult to say. You may have 100 fields in each record? In a network environment try to minimise the amount of network traffic. So, you should try to refine your select statement by returning only what you need e.g. instead of Select * from table where field_value = 1. try: select field_value,field_value1,field_value2,field_value100 from table where field_value = 1 (and field_value2=2) order by field_value,field_value2 (aim for more selective index - 2 fields, not just 1) then even better: What do you want to do with the returned data? View it? Can you handle more than 20 records at a time on your screen? select first 20 field_value,field_value1,field_value2,field_value100 from table where field_value = 1 (and field_value2=2) order by field_value,field_value2 Good luck Alan Alan J Davies Aldis On 04/03/2013 06:18, trskopo wrote: Hi all, I have a table with about 5,000,000 records and it's growing. I run slq statement like this : Select * from table where field_value = 1. Table already indexed on field_value (int type). On local hardisk, where there is only 1 user and no update/edit/insert operation, it took about 1-2 seconds to get the result, but on Local Area Network, where there about 20 users attached to that database, lots of update/edit/insert operation on this table,it took almost 20 seconds. Is there any tips / methods to improve performance? Thanks and regards, Anto.
[firebird-support] Re: How to improve Firebird performance on a large table?
Dear Bogdan, I have already add index on snc field. Thanks Best regards, Anto. Hi I would also add a compounded index snc + ID, or just snc Regards Bogdan [Non-text portions of this message have been removed]
[firebird-support] How to improve Firebird performance on a large table?
Hi all, I have a table with about 5,000,000 records and it's growing. I run slq statement like this : Select * from table where field_value = 1. Table already indexed on field_value (int type). On local hardisk, where there is only 1 user and no update/edit/insert operation, it took about 1-2 seconds to get the result, but on Local Area Network, where there about 20 users attached to that database, lots of update/edit/insert operation on this table,it took almost 20 seconds. Is there any tips / methods to improve performance? Thanks and regards, Anto.
[firebird-support] Re: Help, connecting Firebird on CentOs 6.3
I think, I found the problem. Yesterday, I installed again another OS on Virtual Box, then do some network setting on this new OS. And then, suddenly, my previous CentOs 6.3 installation that has problem with firebird's connection became fix by it self. I think, the problem is from Virtual box, may be it still has a bug, though I already use latest version. Sugi --- In firebird-support@yahoogroups.com, Steve Wiser steve@... wrote: For the telnet issue: You need to make sure you are overriding the default telnet port of 23 with the firebird port of 3050 telnet ip address 3050 should let you test to see if that port is accessible and listening. -steve On Mon, Nov 12, 2012 at 8:36 PM, trskopo trskopo@... wrote: ** Hi Baskin, Thanks for your reply. I have disable Windows Firewall, and tried to telnet that port on Win 7, it failed, it seems, unable to connect, but there are few things that I don't understand : 1) I have another OS running as guest via Virtual Box, this time the OS is Windows XP 32bit, firebird 2.5.1 (32bit) superserver 2) I installed again a new CentOS 6.3 (via Virtual Box), firebird 2.5.2(64bit) superserver Both OS (WinXp and new CentOS) failed on telnet, error msg 'Could not open connection to the host on port 23', but firebird on these OS works ok. File system on new CentOs is ext4, while on old CentOS is ext3. I have tested firebird perf on new CentOS with ext4 and is is bad, even with barrier setting set to off. I can install again CentOs with ext3, but before I do that, if possible, I would like to find out what is the problem in my old CentOs with ext3 ? Finding problem might help me / other when face sam problem. Sugi --- In firebird-support@yahoogroups.com, btapkan baskint@ wrote: Sugi, Can you telnet to that port on Windows 7? If not, your Windows-7 firewall can be the culprit. To check, simply disable the port and re-try telnet and connection. If successful, you can add port exceptions to your firewall and turn it back on. Hope this helps, Baskin --- In firebird-support@yahoogroups.com, trskopo trskopo@ wrote: Hi all, I have installed Firebird 2.5.2(64bit) Superserver on CentOS 6.3 (64 bit). This CentOs server running on Virtual Box as a guest system. Host system is Win 7 64 bit. I can ping server's ip address but failed to connect to Firebird via flamerobin installed on Win 7. I got an error message : 'Unable to complete network request to host xxx. Failed to establish a connection' I have check that firebird server is running and listening on port 3050 on CentOs. (using command via terminal- ps aux|grep fbserver and netstat -an | grep 3050). Firewall on CentOS also disabled. Did I miss something here? Thanks and regards, Sugi. This message and any files transmitted with it may contain information that is privileged, confidential, and exempt from disclosure under applicable law. They are intended solely for the use of the intended recipient. If you are not the intended recipient, distributing, copying, disclosing, or reliance on the contents of this communication is strictly prohibited. If this has reached you in error, kindly destroy this message and notify the sender immediately. Thank you for your assistance. We attempt to sweep harmful content (e.g. viruses) from e-mail and attachments, however we cannot guarantee their safety and can accept no liability for any resulting damage. The recipient is responsible to verify the safety of this message and any attachments before accepting them. [Non-text portions of this message have been removed]
[firebird-support] Question about CpuAffinity in firebird.conf
Hi all, Is this setting apply to computer only have 1 cpu with multicore proc or only apply to computer with have more than one physical cpu? Thanks and regards, Sugi
[firebird-support] Question about Firebird disk performance
Hi all, I run a test on a small database, around 128MB using Firebird 2.5 64 bit superserver and the test condition is like these : 1) Put database completely on RAM, set DefaultDbCachePages = 2048 and FileSystemCacheThreshold = 0 2) Put database completely on disk, set DefaultDbCachePages = 2048 and FileSystemCacheThreshold = 0 3) Put database completely on RAM, set DefaultDbCachePages = 2048 and FileSystemCacheThreshold = 65536 4) Put database completely on disk, set DefaultDbCachePages = 2048 and FileSystemCacheThreshold = 65536 I do some extensive select sql on this database, time to finished on each conditions are : 1) +/- 37 sec 2) unknown since it seems took a very long time that I must ended withtask manager 3) +/- 6 sec 4) +/- 6 sec From those result, I don't understand why result from test condition 1 (which is, database completely on RAM) is slower than condition 3 and 4. Isn't test condition 1,3 4, all running on RAM? Thanks and regards, Sugi
[firebird-support] Re: Question about Firebird disk performance
Hi Sean, Thanks for your reply. You were right, after fresh boot, test # 1 shows about same result with test # 3 4. I think, test # 4, where database placed in harddisk, shows same result as database placed in RAM, because database is small enough to cache in memory. When I decreased DefaultDbCachePages or FileSystemCacheThreshold, test # 4 shows a bad performance vs test # 1 / test # 3. Regards, Sugi --- In firebird-support@yahoogroups.com, Leyne, Sean Sean@... wrote: I run a test on a small database, around 128MB using Firebird 2.5 64 bit superserver and the test condition is like these : 1) Put database completely on RAM, set DefaultDbCachePages = 2048 and FileSystemCacheThreshold = 0 2) Put database completely on disk, set DefaultDbCachePages = 2048 and FileSystemCacheThreshold = 0 3) Put database completely on RAM, set DefaultDbCachePages = 2048 and FileSystemCacheThreshold = 65536 4) Put database completely on disk, set DefaultDbCachePages = 2048 and FileSystemCacheThreshold = 65536 I do some extensive select sql on this database, time to finished on each conditions are : 1) +/- 37 sec 2) unknown since it seems took a very long time that I must ended with task manager 3) +/- 6 sec 4) +/- 6 sec From those result, I don't understand why result from test condition 1 (which is, database completely on RAM) is slower than condition 3 and 4. Isn't test condition 1,3 4, all running on RAM? Yes, but RAM disk is not the same as RAM. Further, some RAM disk drivers are much better than others. Sometime ago, I found an analysis/comparison of various RAM Disk drivers. Although the analysis was a couple of years old, it did find that there are substantial performance differences between drivers. I can see if I can find the link, if you'd like. Separately, did you run all the tests from the same state -- ie. a fresh reboot. If not, test #1 would be paying the cost to load all of the pages into the OS cache, whereas the other tests would not. Either reboot between tests, or create a pre-test pass which would force the database to be loaded and then run the tests. Sean
[firebird-support] Help, connecting Firebird on CentOs 6.3
Hi all, I have installed Firebird 2.5.2(64bit) Superserver on CentOS 6.3 (64 bit). This CentOs server running on Virtual Box as a guest system. Host system is Win 7 64 bit. I can ping server's ip address but failed to connect to Firebird via flamerobin installed on Win 7. I got an error message : 'Unable to complete network request to host xxx. Failed to establish a connection' I have check that firebird server is running and listening on port 3050 on CentOs. (using command via terminal- ps aux|grep fbserver and netstat -an | grep 3050). Firewall on CentOS also disabled. Did I miss something here? Thanks and regards, Sugi.
[firebird-support] Re: Help, connecting Firebird on CentOs 6.3
Hi Baskin, Thanks for your reply. I have disable Windows Firewall, and tried to telnet that port on Win 7, it failed, it seems, unable to connect, but there are few things that I don't understand : 1) I have another OS running as guest via Virtual Box, this time the OS is Windows XP 32bit, firebird 2.5.1 (32bit) superserver 2) I installed again a new CentOS 6.3 (via Virtual Box), firebird 2.5.2(64bit) superserver Both OS (WinXp and new CentOS) failed on telnet, error msg 'Could not open connection to the host on port 23', but firebird on these OS works ok. File system on new CentOs is ext4, while on old CentOS is ext3. I have tested firebird perf on new CentOS with ext4 and is is bad, even with barrier setting set to off. I can install again CentOs with ext3, but before I do that, if possible, I would like to find out what is the problem in my old CentOs with ext3 ? Finding problem might help me / other when face sam problem. Sugi --- In firebird-support@yahoogroups.com, btapkan baskint@... wrote: Sugi, Can you telnet to that port on Windows 7? If not, your Windows-7 firewall can be the culprit. To check, simply disable the port and re-try telnet and connection. If successful, you can add port exceptions to your firewall and turn it back on. Hope this helps, Baskin --- In firebird-support@yahoogroups.com, trskopo trskopo@ wrote: Hi all, I have installed Firebird 2.5.2(64bit) Superserver on CentOS 6.3 (64 bit). This CentOs server running on Virtual Box as a guest system. Host system is Win 7 64 bit. I can ping server's ip address but failed to connect to Firebird via flamerobin installed on Win 7. I got an error message : 'Unable to complete network request to host xxx. Failed to establish a connection' I have check that firebird server is running and listening on port 3050 on CentOs. (using command via terminal- ps aux|grep fbserver and netstat -an | grep 3050). Firewall on CentOS also disabled. Did I miss something here? Thanks and regards, Sugi.
[firebird-support] Re: Is windows better than Linux for Firebird 2.5 64 bit?
Hi Lester, Thanks for your reply. So the question should perhaps be ... what are you using with Firebird? I am using firebird in client-server environment for accounting application and mostly only in a LAN. About file system, (if I didn't mistaken) someone here mentions not to use specific file system, because it will decrease firebird's performance. Regards, Sugiarto
[firebird-support] Re: Is windows better than Linux for Firebird 2.5 64 bit?
Hi Dmitry Kuzmenko, Thanks for your reply. 1. Windows can be better or slower than Linux on same hardware, depends on hardware and existing drivers for hardware for Linux and Windows. Difference can be up to 30% of performance. 1) What drivers in that should exist for better performance? My guest is CPU and Harddisk, is this correct? 2) Assuming that Linux and windows has correct drivers, which one is better? 2. You can choose linux distro by your preferrence. If you don't know any Linux well, then, you will have troubles. I am thinking using CentOS or Suse, what distro do you use for yours? Regards, Sugiarto
[firebird-support] Re: Is windows better than Linux for Firebird 2.5 64 bit?
--- In firebird-support@yahoogroups.com, ma_golyo ma_golyo@... wrote: FB has VERY poor performance with EXT4 http://www.firebirdnews.org/?p=6421 Hi ma_golyo, thanks for the tip. Sugiarto
[firebird-support] Re: Is windows better than Linux for Firebird 2.5 64 bit?
--- In firebird-support@yahoogroups.com, ma_golyo ma_golyo@... wrote: FB has VERY poor performance with EXT4 http://www.firebirdnews.org/?p=6421 Hi ma_golyo, Thanks for the tip. After I read that article, if I am not mistaken, Barriers setting in ext4 cause poor performance. Do you know if I set Barriers to off and No Journaling mode in ext4 will improve significant performance? Ask because ext4, do has some advantage such as Multiblock allocation. Regards, Sugiarto
[firebird-support] Re: Stored Proc vs View, which one has better performance?
Ah, that's right, using having clause is much more simple, thanks a lot for that tip. Sugi Have you considered just select id_cst,id_gd,sum(qty) qtySum from Tbl1 group by id_cst,id_gd having qtySum 0 There's a slight benefit to the view or stored procedure because it doesn't have to be recompiled and optimized so often, but it's a pretty simple statement and you'd have to use it a lot to amortize the time you spent creating the view or procedure. As for whether the view or the procedure would be faster - in theory they should be about the same, but only you can demonstrate the difference between theory and practice. This should be easy to test and testing it on your actual data will give the most reliable results. Good luck, Ann
[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.
[firebird-support] Help, how to connect to firebird via Internet
Hi all, I have setup a dynamic ip address via www.no-ip.com and got a domain name. I can ping from windows this domain(by its name or ip address), but when I using flamerobin to connect to this domain, it failed. In flamerobin, i enter this domain name as a hostname in Server registration Info. Error message is 'Unable to complete network request to host...' Trying to connect via ip address also not success. Any suggestions how to solve this problem? Thanks in advance, Anto
[firebird-support] Re: Help, how to connect to firebird via Internet
Thanks for your answer. Problem solved by setup a port forwarding on my router. --- In firebird-support@yahoogroups.com, Yanto asnawi yanto_asnawi@... wrote: 1. Test local conection first 2. conect to internet via modem/router ? 3. setting modem/router DMZ/virtual server to ip server? --- On Thu, 8/16/12, trskopo trskopo@... wrote: From: trskopo trskopo@... Subject: [firebird-support] Help, how to connect to firebird via Internet To: firebird-support@yahoogroups.com Date: Thursday, August 16, 2012, 4:10 AM Â Hi all, I have setup a dynamic ip address via www.no-ip.com and got a domain name. I can ping from windows this domain(by its name or ip address), but when I using flamerobin to connect to this domain, it failed. In flamerobin, i enter this domain name as a hostname in Server registration Info. Error message is 'Unable to complete network request to host...' Trying to connect via ip address also not success. Any suggestions how to solve this problem? Thanks in advance, Anto [Non-text portions of this message have been removed]
[firebird-support] Dynamic Parameter in Stored Procedur
Hi all, Is this possible to create stored proc with dynamic parameter as an input? For example, I have a table like this : id value == = 1 'A' 2 'B' 3 'C' 4 'D' 5 'E' How to create stored proc to returns values from above table but with dynamic id as an input? For ex, how to return values, if with id in (1,2) or id in (1,2,5), etc. In select statement, it will equal to statement like this : select value from table where id in (1,2) or select value from table where id in (1,2,5) Thanks in advance, Best regards, incendio.
[firebird-support] Dynamic Input Parameter in Stored Procedure
Hi all, Is this possible to create stored proc with dynamic parameter as an input? For example, I have a table like this : id value == = 1 'A' 2 'B' 3 'C' 4 'D' 5 'E' How to create stored proc to returns values from above table but with dynamic id as an input? For ex, how to return values, if with id in (1,2) or id in (1,2,5), etc. In select statement, it will equal to statement like this : select value from table where id in (1,2) or select value from table where id in (1,2,5) Thanks in advance, Best regards, incendio.
[firebird-support] Re: database in external USB HDD
2) Pen Drive : more compact. Welcome to our recovery service :) Yes, Pen Drive is really not reliable, prepare to lost you data if you using this media, that why I said USB Hardisk is more reliable compare to Pen Drive :) 3) RAM Disk : highest speed but risky and less space. Do not use it unless you aware of the risk. Runs on a dedicated PC (no other programs run on this pc, only database server and OS), backup it everyday, twice a day and have a reliable UPS. Love the speed when doing maintenance (backup/restore) - superb compare to those two. We have tested RAM Disks - no performance advantages found when comparing to the properly tuned Firebird. Strange, I have a sample database that showing the speed when backup/restore in RAM Disk is much more faster compare to disk, about 20 or 30 times faster perhaps, may be my Disk is too slow, it is SATA II anyway :) About performance, assuming same database (one on the RAM and the other on the disk), I think it has something to do with database cache memory. If most of data can fits in cache memory, of course, no performance gain since both processing took place on RAM. incendio.
[firebird-support] Re: database in external USB HDD
On my experience : 1) USB HDD : more reliable, more speed and more space. 2) Pen Drive : more compact. 3) RAM Disk : highest speed but risky and less space. Do not use it unless you aware of the risk. Runs on a dedicated PC (no other programs run on this pc, only database server and OS), backup it everyday, twice a day and have a reliable UPS. Love the speed when doing maintenance (backup/restore) - superb compare to those two. Incendio. --- In firebird-support@yahoogroups.com, Sergio shg_sistemas@... wrote: Are you sure you are not mixing it with using the database on Pen Drives ? Hi Alexande! thanks for the answer! May be... I'm not sure... But in any case, what would be the difference in having the database file on an USB HDD and a Pen Drive? Or it's just a matter of space or performance? Sergio
[firebird-support] Re: database in external USB HDD
I use FB 2.01 in external USB drive connected to the server for about 3-4 years. No problem at all, I think FB 2.5 won't be a problem too. Currently, using FB 2.5 on a RAM Disk (in server) for a better speed. incendio. --- In firebird-support@yahoogroups.com, Sergio H. Gonzalez shg_sistemas@... wrote: Hello there! I use FB 2.5. Is there any known problem with having the database file in an external USB drive conected to the server? Just in case (don't know if it matters!) I always use superserver mode. thanks!! -sergio [Non-text portions of this message have been removed]
[firebird-support] Re: Firebird 2.5 (64 bit), strange SQL result
Thanks for your replay. If you see null with ibexpert, how come this data can be store in Firebird, since ddl for this column not allow for null value? Could it be a bug in Firebird? Another strange results when issued this statement SELECT * FROM M_INT_FRM a where a.id_div_lc is null It will returns 3 result (via flamerobin). Thanks and regards, Sugiarto I have tested your database and i think is an error of flamerobin, what you see 0 i see null with ibexpert. try SELECT * FROM M_INT_FRM a where a.ID_TRS_TYP = 100 and a.id_div_lc is null May be in sometime you have changed the column from null to not null. Regards, Jesus [Non-text portions of this message have been removed]
[firebird-support] Re: Firebird 2.5 (64 bit), strange SQL result
Thanks for your reply. I think my database is in problem, because when I tried to restored it, I got error Engine Message : validation error for column ID_DIV_LC, value *** null *** warning -- record could not be restored I think, may be there is a leak in Firebird to trap an error when null value allowed to store in a not null value definition. I forgot how to insert this null value to the database. Thanks and regards, Sugiarto What's the execution plan? If it's using an index on ID_DIV_LC and the following returns a result set: select * from m_int_frm a where a.id_trs_typ = 100 and a.id_div_lc + 0 = 0 Then your index on ID_DIV_LC might be problematic/corrupted. -- With regards, Thomas Steinmaurer (^TS^) Firebird Technology Evangelist http://www.upscene.com/ Do you care about the future of Firebird? Join the Firebird Foundation: http://www.firebirdsql.org/en/firebird-foundation/
[firebird-support] Re: Firebird 2.5 (64 bit), strange SQL result
Thanks for your reply.
I did another test, just to make sure.
1) Create a test table
create table test (id int, nm varchar(12));
2) insert some values
insert into test (nm) values('test1');
insert into test values(2, 'test2');
3) change id's column def
UPDATE RDB$RELATION_FIELDS SET RDB$NULL_FLAG = 1
WHERE RDB$FIELD_NAME = 'ID' AND RDB$RELATION_NAME = 'TEST';
4) select a table
select * from test (via flamerobin)
result :
ID NM
[null] test1
2 test2
From those test, I think :
1) Firebird didn't raise an error when set null column into not null column (so
I have to be more careful in the future)
2) return set shows null value when it is null. This is different from my first
case when return set shows 0 for null value. So I guess what happened to my
first case didn't come because I changed column definition from null to not
null, it is still a mystery.
Thanks and regard,
Sugiarto
--- In firebird-support@yahoogroups.com, Helen Borrie helebor@... wrote:
At 02:26 PM 9/05/2012, you wrote:
Thanks for your replay.
If you see null with ibexpert, how come this data can be store in Firebird,
since ddl for this column not allow for null value? Could it be a bug in
Firebird?
It was stored at some point before the column was changed to NOT NULL. When
you do that change, the engine does NOT alter existing data. Thus, you must
always make sure that you correct the data *before* you make that change.
Another strange results when issued this statement
SELECT * FROM M_INT_FRM a
where a.id_div_lc is null
It will returns 3 result (via flamerobin).
Why is it strange? You have already established that you have nulls stored
in this column, from its previous life as nullable.
./heLen
[firebird-support] Firebird 2.5 (64 bit), strange SQL result
Hi all, I run sql statement via flamerobin, the statment is like this : SELECT * FROM M_INT_FRM a where a.ID_TRS_TYP = 100 It will returns : ID ID_DIV_LC SCT_INI ID_TRS_TYP FRM_INI 4 8 MP 100PO 5 9 MN 100PO 6 0 MS 100PO but if I change sql statement to this SELECT * FROM M_INT_FRM a where a.ID_TRS_TYP = 100 and a.id_div_lc = 0 no sql result returns. You can download the database at http://www.mediafire.com/?bx8k29kt7x2ucks Strange, could it be a bug? Thanks and regards, Sugiarto
[firebird-support] Re: How to return only a few rows from select sql statement
Hi Walter, Thanks for your reply. Could you give me an example how to use clauses FIRST, SKIP or ROW TO? Thanks and regards, Sugiarto --- In firebird-support@yahoogroups.com, W O sistemas2000profesional@... wrote: Of course, you can to use clauses FIRST, SKIP or ROW TO Additionally, you can use a WHERE clause. Greetings. Walter. On Thu, Jan 19, 2012 at 11:46 PM, trskopo trskopo@... wrote: ** Hi all, Suppose I have a sql statement : select * from table Is that possible to return only a few rows from that statement (not the whole result), just like MySQL? Thanks and regards, Sugiarto [Non-text portions of this message have been removed]
[firebird-support] Re: How to return only a few rows from select sql statement
Hi Walter, Thanks for your answer. Best regards, Sugiarto --- In firebird-support@yahoogroups.com, W O sistemas2000profesional@... wrote: Of course, you can to use clauses FIRST, SKIP or ROW TO Additionally, you can use a WHERE clause. Greetings. Walter. On Thu, Jan 19, 2012 at 11:46 PM, trskopo trskopo@... wrote: ** Hi all, Suppose I have a sql statement : select * from table Is that possible to return only a few rows from that statement (not the whole result), just like MySQL? Thanks and regards, Sugiarto [Non-text portions of this message have been removed]
[firebird-support] How to return only a few rows from select sql statement
Hi all, Suppose I have a sql statement : select * from table Is that possible to return only a few rows from that statement (not the whole result), just like MySQL? Thanks and regards, Sugiarto
[firebird-support] Re: Firebird 2.5 64bit for Intel Proc
from where and how did you get such message ? what did you did ? under CentOS 6.2 use EPEL repository, you have Firebird packages inside http://fedoraproject.org/wiki/EPEL http://fedoraproject.org/wiki/EPEL/FAQ When I double click this file FirebirdSS-2.5.1.26351-0.amd64.rpm and in the middle of installation, that message appeared. I am newbie in Linux, how do I use this EPEL? Best regards, Sugiarto
[firebird-support] Re: Firebird performance on CentOS 6 (64bit) vs Win 7 (64bit)
I have had a look at your database and it looks like you have been testing on Centos using a page cache of 75 (I suspect you may have classic installed) - this results in the large number of reads on Centos and therefore the slower execution/fetch time. Edit your firebird.conf file (most likely found in /opt/firebird/) and change the value of DefaultDbCachePages to 2048 (the page cache the super server is using on your win 7 machine). Also make sure that DefaultDbCachePages is not commented out (no leading #). Incidentally, if you are using classic on Centos then a much lower page cache should also do the trick - perhaps 128 or thereabouts. Below some results using your database (note that these are for win7 64bit only, however, Centos 5.6 on ext3 yield identical results) Hi David, I have looked test data on CentOS, yes, page cache only 75, it was strange, because I copied test data in Win 7 to CentOS and on Win 7, page cache is 6144 (without any setting). It seem copying database between OS changed the value on page cache. Anyway I have changed page cache on CentOS to 2048 and compare the result with WinXp 64 bit also with page cache set to 2048 and both OS have same RAM (2 GB) and same hardisk, here are the execution time from flamerobin WinXP 64 bit, file system NTFS, RAM 2GB, page cache 2048, SATA2 hardisk : Executing... Done. 8448859 fetches, 6 marks, 1295 reads, 6 writes. 0 inserts, 0 updates, 0 deletes, 4144093 index, 7748 seq. Delta memory: 111272 bytes. Total execution time: 8.758s Script execution finished. CentOS 6 64 bit, file system ext4, RAM 2GB, page cache 2048, SATA2 hardisk : Executing... Done. 8448872 fetches, 6 marks, 1304 reads, 6 writes. 0 inserts, 0 updates, 0 deletes, 4144093 index, 7748 seq. Delta memory: 111240 bytes. Total execution time: 22.027s Script execution finished. Result on CentOS 6 still significantly slower then WinXp64. I will do last test with CentOS but this time with ext3 file system, if it is still slower, then I will use WinXp/Win7 64. Best regards, Sugiarto
[firebird-support] Re: Firebird performance on CentOS 6 (64bit) vs Win 7 (64bit)
Hi David, Thanks for your reply. I am out of town right now. I will check firebird's setting on CentOs when I am at home. Best regards, Sugiarto
[firebird-support] Re: Firebird performance on CentOS 6 (64bit) vs Win 7 (64bit)
Here is the result on WinXp 64 bit, Firebird 2.5 SuperServer, RAM 2GB, Database on Hardisk Executing... Done. 8432701 fetches, 2 marks, 0 reads, 2 writes. 0 inserts, 0 updates, 0 deletes, 4143977 index, 0 seq. Delta memory: 78928 bytes. Total execution time: 7.104s Script execution finished. Looking at the result on Win7 and WinXp, I think CentOS and ext4 is not a good choice. Best regards, Sugiarto
[firebird-support] CPU selection for Firebird Database
Hi all, I am looking for CPU for Firebird database server, which one is better between Intel I7 and AMD Phenom II X6, OS is Windows XP 32 bit or CentOS 6 64 bit. Thanks in advance. Best regards, Sugiarto.
[firebird-support] Re: CPU selection for Firebird Database
--- In firebird-support@yahoogroups.com, yanto_asnawi@... wrote: Hi, I use intel core i7 and centos 6 64bit Harddisk raid level 0, SATA wetern digital raptor performance : excellent. Thanks for your reply. Could you be more specific about your specs for I7 and wd raptor? Also, do you use ext4 and forced write on on CentOS? I heard, there is problem when using ext4 file system and forced write on. best regards, Sugiarto
[firebird-support] Firebird performance on CentOS 6 (64bit) vs Win 7 (64bit)
Hi all, I did a little test about Firebird 2.5 SuperServer 64 bit performance on these 2 OS. Both running on the same cpu, others setting are 1) CentOS 6 : RAM 2GB, file ext4, database on disk 2) Win 7 : RAM 4GB, database on RAM Table structure for this test : CREATE TABLE DT_LOG ( DT Date DEFAULT CURRENT_DATE, CD Char(4) NOT NULL, STR Varchar(80), INP_AT Time DEFAULT CURRENT_TIME ); CREATE INDEX IDX_DT_LOG1 ON DT_LOG (CD); CREATE INDEX IDX_DT_LOG2 ON DT_LOG (DT); CREATE INDEX IDX_DT_LOG3 ON DT_LOG (DT,CD); SQL statement issued : SELECT a.DT, a.CD, a.STR, a.INP_AT FROM DT_LOG a where a.INP_AT = (select max(c.INP_AT) from DT_LOG c where c.CD = a.cd and c.DT = '11/17/11') and dt = '11/17/11' order by cd Result and executing time : 1) CentOS 6 Executing... Done. 8468502 fetches, 2 marks, 4244984 reads, 2 writes. 0 inserts, 0 updates, 0 deletes, 4144063 index, 0 seq. Delta memory: 219264 bytes. Total execution time: 43.510s Script execution finished. 2) Win 7 Executing... Done. 8432907 fetches, 4 marks, 1294 reads, 4 writes. 0 inserts, 0 updates, 0 deletes, 4144031 index, 0 seq. Delta memory: 104600 bytes. Total execution time: 6.854s Script execution finished. I wonder, why CentOS reads so many records (4244984 reads) compare to Win 7 (1294 reads). Maybe someone could give me an explanation? Thanks in advance. Best regards, Sugiarto
[firebird-support] Moving Firebird to Linux Server, advice needed
Hi all, I am planning to use CentOS 6 for Firebird 2.5 server(Super Server mode), but I am really a newbie in Linux. In CentOS, I can only install firebird, setting sysdba password, setting TCP/IP manually, setting file permissions and setting Firewall, that's all. I have tried these steps, and can connect successfully to Firebird server via windows client. Are there anything I should aware before continue? I read about forced write, in windows, the default is on, how about in Linux? And if is not on by default, how to set it on? Thanks in advance and best regards, Sugi.
[firebird-support] Re: Is faster hardisk really a matter for Firebird performance?
Hi Milan, I use Win 7 64, 8GB RAM Incendio --- In firebird-support@yahoogroups.com, Milan Babuskov milanb@... wrote: trskopo wrote: I just doing a little experiment to find out database performance on RAM vs on Disk. Which operating system do you use? -- Milan Babuskov == The easiest way to import XML, CSV and textual files into Firebird: http://www.guacosoft.com/xmlwizard ==
[firebird-support] Ask about Firebird Linux AMD64
Hi all, Is this Firebird for Linux AMD64 only runs on AMD's CPU? Thanks and best regards Incendio
[firebird-support] Re: Is faster hardisk really a matter for Firebird performance?
Hi Brad, Thanks for your reply. Yes, I did the test after reboot. If I run same sql statement twice, I noticed that the second sql gives faster result. Do you know how to turn off file system cache on Win 7 64 bit? Best regards, Incedio --- In firebird-support@yahoogroups.com, Brad Pepers brad@... wrote: On 2011-10-27, at 7:56 PM, trskopo wrote: Hi all, I just doing a little experiment to find out database performance on RAM vs on Disk. I use WD SATA 2 Green, 64MB cache for testing. My RAM speed is about 35x faster than disk. I have 2 databases (size is about 75 MB), one placed on a RAM and the other on disk, then I run select sql for both databases SQL Statement finished about 47 seconds, on RAM and on disk. So there is no significant performance between RAM and disk. Performance on RAM shows when doing backup or restore routine. So I wonder, could it be that there is no need for faster hardisk when using firebird? When you did your test, was it after a reboot? If not, then the operating system might well have been caching that 75 MB database file on disk in RAM so that is why the performance was the same. You have to be *really* careful in testing performance that what you're measuring is what you think you're measuring. Also note that on Windows Vista and 7 the operating system keeps track of the files you use often and will pre-load them into the file system cache in memory even if you haven't even used them yet. So even after a reboot if you give the operating system some time the file may be loaded into RAM even though you haven't even used it yet! You need to turn this feature off if you want to do accurate performance testing on Vista/7. -- Brad
[firebird-support] Is faster hardisk really a matter for Firebird performance?
Hi all, I just doing a little experiment to find out database performance on RAM vs on Disk. I use WD SATA 2 Green, 64MB cache for testing. My RAM speed is about 35x faster than disk. I have 2 databases (size is about 75 MB), one placed on a RAM and the other on disk, then I run select sql for both databases SQL Statement finished about 47 seconds, on RAM and on disk. So there is no significant performance between RAM and disk. Performance on RAM shows when doing backup or restore routine. So I wonder, could it be that there is no need for faster hardisk when using firebird? Thanks and best regards, Incendio
[firebird-support] Advice needed : Using Firebird in a Large Client Server Env
Hi all, Usually, I am using firebird in a small client server environment ( 1 server and up to 5 clients). For Server, I use a desktop pc. It runs ok, though there was performance decrease when reports generated. Database size relatively small, under 300 MB after 3 years. Now, I have a need to build firebird for use by around 60-70 clients. May be someone here could share their experiences about firebird performance on this kind of environment, hardware to use, OS to choose (windows server or linux server), etc. Also, is there a database benchmark tools for firebird? Thanks in advance. Best regards, Incendio.
[firebird-support] Re: Problem in fetch records
Hi Cornie, thanks for your answer. I am using TIBQuery and there is no PacketRecords and FetchOnDemand property for TIBQuery. Best regards, Sugiarto --- In firebird-support@yahoogroups.com, Cornie van Schoor cornievs@... wrote: On 03/09/2011 13:54, trskopo wrote: Hi all, Here is the problem, when I issued Select SQL statement, sometime, not all records fetched, but this was not always happen, sometime, all records fetched ok. Check your dataset in c++, maybe it set to retrieve in only x of amount of records per fetch. PacketRecords = 100 (-1 means fetch all). If FetchOnDemand is True, scrolling a grid for example, will force fetching of more record as required. Regards Cornie
[firebird-support] How to Set CPU Affinity value for multicore processor?
Hi all, I want to set CpuAffinityMask value in firebird.conf for multicore. The description in that file told me that to use only first processor, the value is 1, to use both # CPU 1 and CPU 2, the value is 3. What is the value if I want to use # CPU 1, CPU 2, CPU 3, and CPU 4? Also the value to use # CPU 1, CPU 2, CPU 3, CPU 4, CPU 5, and CPU 6? And how do I test that firebird server running on multicore processors? Note : I am using SuperServer with multiple databases. Thanks in advance. Best regards, Sugiarto
[firebird-support] Re: Problem in fetch records
Hi Alan, thanks for your answer, My SQL statement is like this : select a.*, b.nm from T_IN_OT1 a, m_src b where a.is_cls 'T' and a.id_cst_sppl = b.id order by a.frm_N desc Before run this statement, I call stored procedure to calculate stock calculation, it is quiet a heavy procedure. I am using AMD 6 cores only for testing, because the application is not complex, and I believe Intel Dual Core E5700 is enough. I think, on Win7, there is some delay in when retrieved data, could it be I should change UsePriorityScheduler, PrioritySwitchDelay, and PriorityBoost parameters in firebird.conf? Best regards, Sugiarto --- In firebird-support@yahoogroups.com, Alan J Davies Alan.Davies@... wrote: Don't think its Firebird. I have applications running on lower specification systems with none of these problems, on windows XP, server 2003 and on Linux. Also on Win7. What is your statement? Are there any parameters or where statements? However, if it works on the other syste, why not leave it there? Total records is about 5000 data. That's not a problem - Firebird would handle this in a blink of an eye! Alan J Davies Aldis
[firebird-support] Is Firebird support running on multi core processors?
Hi all, If it can running on multi core, how to set that? It seems, setting CpuAffinityMask in firebird.conf has no effect. Thanks in advance. Best regards, Sugiarto.
[firebird-support] Is Firebird 2.5 support running on multi core processors?
Hi all, If it can running on multi core, how to set that? It seems, setting CpuAffinityMask in firebird.conf has no effect. Thanks in advance. Best regards, Sugiarto.
[firebird-support] Re: Is Firebird 2.5 support running on multi core processors?
In Firebird 2.5 release note, I read this note : The default CpuAffinity setting still binds SuperServer to a single processor only. In order to take advantage of this improvement when working with multiple databases, this setting should be changed in firebird.conf It means, Superserver supported for multicore, right? --- In firebird-support@yahoogroups.com, Alexandre Benson Smith iblist@... wrote: Em 26/8/2011 20:34, trskopo escreveu: Hi all, If it can running on multi core, how to set that? It seems, setting CpuAffinityMask in firebird.conf has no effect. Thanks in advance. Best regards, Sugiarto. use ClassicServer or SuperClassic server... SuperServer continues to be useless for SMP machines...
[firebird-support] Database Trigger - Is is possible to preventing disable db trigger from ISQL?
Hi all, For a security reason, I wrote a database triggers. It can prevent unauthorized users to connect to database. Access from application or database managers (such as flamerobin/others) can be blocked if needed. But the problem is, users can disable / set inactive my db triggers via ISQL. Any suggestions to prevent this? Thanks in advance and best regards, Sugiarto.
