Re: [firebird-support] OT - mysql a joke?
Morning Lester, On 07/07/13 21:51, Lester Caine wrote: I did manage to 'hack it' and actually got all the databases working again, but I definitely prefer the firebird methods. Glad you got sorted. ... user login is an utter pain :( Don't I know it. I have to set all my mysql databases up with localhost.* and hubble.* (my server name) in order to get any login to work. Makes no sense at all. Weird. Cheers, Norm. -- Norman Dunbar Dunbar IT Consultants Ltd Registered address: Thorpe House 61 Richardshaw Lane Pudsey West Yorkshire United Kingdom LS28 7EL Company Number: 05132767
Re: [firebird-support] OT - mysql a joke?
From: Norman Dunbar nor...@dunbar-it.co.uk To: firebird-support@yahoogroups.com Sent: Mon, 08 Jul 2013 8:01 Subject: Re: [firebird-support] OT - mysql a joke? Morning Lester, On 07/07/13 21:51, Lester Caine wrote: user login is an utter pain :( Don't I know it. I have to set all my mysql databases up with localhost.* and hubble.* (my server name) in order to get any login to work. Makes no sense at all. Weird I know people complain about Firebird's 'strange' centralised user database, and there are times when a user list per database may seem better, but most of the time the applications ARE better supported by user tables in the target database, and access to the database simply controlled by centralised rolls. Certainly I would not want to manage the hundreds of users on my systems as individual database users. I can then simply backup the target database as a cron job ... Is there a similar backup process available for MySQL? [Non-text portions of this message have been removed]
[firebird-support] Firbird slow performance at windows server 2012
Dear All, I have application with delphi and firebird database, there is no problems until migrate the server to windows server 2012. The query (select, insert, update etc) running to slow. I searching and found the solution example : change filesystemcache,tempbocksize,defaultdbcachepages at firebird.conf but the query still running to slow. There are another way? Please help me Thanks Chandra [Non-text portions of this message have been removed]
RE: [firebird-support] Firbird slow performance at windows server 2012
Dear All, I have application with delphi and firebird database, there is no problems until migrate the server to windows server 2012. The query (select, insert, update etc) running to slow. I searching and found the solution example : change filesystemcache,tempbocksize,defaultdbcachepages at firebird.conf but the query still running to slow. There are another way? Please tell us more, Chandra, even though I know nothing about Windows vs Firebird, I know that a detailed and specific problem description is more likely to result in good answers than general questions. You say that you migrate to Windows server 2012, what do you migrate from? Is everything except the server identical on the fast and slow server - e.g. indexes and plans for all problematic queries? The same number of users and the same content in the databases? What about database statistics? If there are specific queries running slow, you could post them here together with the generated plans and some information about the fields in the indexes being used. Set
Re: [firebird-support] Firbird slow performance at windows server 2012
Em 8/7/2013 05:51, Svein Erling Tysvær escreveu: Dear All, I have application with delphi and firebird database, there is no problems until migrate the server to windows server 2012. The query (select, insert, update etc) running to slow. I searching and found the solution example : change filesystemcache,tempbocksize,defaultdbcachepages at firebird.conf but the query still running to slow. There are another way? Please tell us more, Chandra, even though I know nothing about Windows vs Firebird, I know that a detailed and specific problem description is more likely to result in good answers than general questions. You say that you migrate to Windows server 2012, what do you migrate from? Is everything except the server identical on the fast and slow server - e.g. indexes and plans for all problematic queries? The same number of users and the same content in the databases? What about database statistics? If there are specific queries running slow, you could post them here together with the generated plans and some information about the fields in the indexes being used. Set Besides that. Wich FB version ? Newer windows versions (64bit) has changed th way to manage filesystem cache and it became a problem... Read more here: http://dyemanov.blogspot.com.br/2012/03/firebird-vs-windows-file-system-caching.html see you !
[firebird-support] Re: Using stored procedure results in the WHERE clause
Sorry about the unreadability of the code. The Yahoo RTF editor really mangled it. --- In firebird-support@yahoogroups.com, sboydlns sboydlns@... wrote: I have a set of frequently used search conditions that I find myself repeating over and over in different places in my application. It would be nice to be able to encapsulate these conditions into a stored procedure and just reference the stored procedure in the WHERE clause of my SELECT statement when I need them. Trouble is, I can't figure out how to do it or even if it is possible. Consider the following procedure: CREATE PROCEDURE IS_MANIFESTED(stop_id bigint) RETURNS (result char(1)) ASBEGIN IF EXISTS (SELECT M.OSM_STOP_ID FROM OPS_MANIFEST_STOPS M LEFT JOIN OPS_STOP_REC O ON O.OPS_STOP_ID = M.OSM_STOP_ID LEFT JOIN OPS_HEADER P ON P.PB_ID = O.OPS_ORDER_ID WHERE M.OSM_PKUP_DELV_ID = :stop_id AND COALESCE(P.PB_STATUS, 0) 9)) THEN BEGIN result = 'T'; END ELSE BEGIN result = 'F'; END SUSPEND; EXIT;END It would be nice to be able to use the IS_MANIFEST procedure in a select statement like this: SELECT * FROM OPS_STOP_REC WHERE IS_MANIFESTED(OPS_STOP_ID) 'T'; to get all un-manifested loads. But this doesn't get past the syntax checker. Is there some way I can accomplish what I want? I suppose I could use the procedure in a JOIN clause but it doesn't seem as understandable or elegant. [Non-text portions of this message have been removed]
Re: [firebird-support] OT - mysql a joke?
Evening Lester, On 08/07/13 08:19, les...@lsces.co.uk wrote: I know people complain about Firebird's 'strange' centralised user database, and there are times when a user list per database may seem better, but most of the time the applications ARE better supported by user tables in the target database, and access to the database simply controlled by centralised rolls. Certainly I would not want to manage the hundreds of users on my systems as individual database users. I can then simply backup the target database as a cron job ... Well, speaking as an Oracle DBA, I have to deal with databases where each user has an individual account. Unlike Firebird, each user will have a separate account on each database they are allowed access to. However, as you note, some applications do the user admin stuff manually within the application itself, so I only need to set up and maintain the data owner account.Which makes my like easier - but has serious drawbacks when attempting to audit stuff as everything takes place as the data owner, and not as individuals - so you can't tell who broke things, or attempted actions beyond their security level. Although a lot of applications do their own auditing, that only works within the application and not within the database. Did someone mention security? ;-) Is there a similar backup process available for MySQL? If and when I backup a mySql database, I use PhpMyadmin to do it. I don't know about doing a backup via cron. Does this help? http://www.noupe.com/how-tos/10-ways-to-automatically-manually-backup-mysql-database.html Cheers, Norm. -- Norman Dunbar Dunbar IT Consultants Ltd Registered address: Thorpe House 61 Richardshaw Lane Pudsey West Yorkshire United Kingdom LS28 7EL Company Number: 05132767
[firebird-support] Using stored procedure results in the WHERE clause
I have a set of frequently used search conditions that I find myself repeating over and over in different places in my application. It would be nice to be able to encapsulate these conditions into a stored procedure and just reference the stored procedure in the WHERE clause of my SELECT statement when I need them. Trouble is, I can't figure out how to do it or even if it is possible. Consider the following procedure: CREATE PROCEDURE IS_MANIFESTED(stop_id bigint) RETURNS (result char(1)) ASBEGIN IF EXISTS (SELECT M.OSM_STOP_ID FROM OPS_MANIFEST_STOPS M LEFT JOIN OPS_STOP_REC O ON O.OPS_STOP_ID = M.OSM_STOP_ID LEFT JOIN OPS_HEADER P ON P.PB_ID = O.OPS_ORDER_ID WHERE M.OSM_PKUP_DELV_ID = :stop_id AND COALESCE(P.PB_STATUS, 0) 9)) THEN BEGIN result = 'T'; END ELSE BEGIN result = 'F'; END SUSPEND; EXIT;END It would be nice to be able to use the IS_MANIFEST procedure in a select statement like this: SELECT * FROM OPS_STOP_REC WHERE IS_MANIFESTED(OPS_STOP_ID) 'T'; to get all un-manifested loads. But this doesn't get past the syntax checker. Is there some way I can accomplish what I want? I suppose I could use the procedure in a JOIN clause but it doesn't seem as understandable or elegant. [Non-text portions of this message have been removed]
Re: [firebird-support] OT - mysql a joke?
Alexandre Benson Smith wrote: I can then simply backup the target database as a cron job ... Is there a similar backup process available for MySQL? I use very little MySQL, but when I need to do a back-up I use MySQLDump... http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html From what I see it has the same funcionality as gbak. Dump the Metadata and then the data. But generate a simple SQL text file (DML and DDL) statements.. see you ! But you can't run that while the database is 'live'? Something you never worry about with Firebird :) -- Lester Caine - G8HFL - Contact - http://lsces.co.uk/wiki/?page=contact L.S.Caine Electronic Services - http://lsces.co.uk EnquirySolve - http://enquirysolve.com/ Model Engineers Digital Workshop - http://medw.co.uk Rainbow Digital Media - http://rainbowdigitalmedia.co.uk
Re: [firebird-support] OT - mysql a joke?
Norman Dunbar wrote: Evening Lester, On 08/07/13 08:19,les...@lsces.co.uk wrote: I know people complain about Firebird's 'strange' centralised user database, and there are times when a user list per database may seem better, but most of the time the applications ARE better supported by user tables in the target database, and access to the database simply controlled by centralised rolls. Certainly I would not want to manage the hundreds of users on my systems as individual database users. I can then simply backup the target database as a cron job ... Well, speaking as an Oracle DBA, I have to deal with databases where each user has an individual account. Unlike Firebird, each user will have a separate account on each database they are allowed access to. However, as you note, some applications do the user admin stuff manually within the application itself, so I only need to set up and maintain the data owner account.Which makes my like easier - but has serious drawbacks when attempting to audit stuff as everything takes place as the data owner, and not as individuals - so you can't tell who broke things, or attempted actions beyond their security level. Although a lot of applications do their own auditing, that only works within the application and not within the database. Did someone mention security?;-) As long as the database schema is only editable by a 'superuser' I don't see a problem. All my dynamic websites produce an activity log, and version each page update so we can roll back any mischief. We just have a few 'roles' for users which define their access rights. Is there a similar backup process available for MySQL? If and when I backup a mySql database, I use PhpMyadmin to do it. I don't know about doing a backup via cron. Does this help? http://www.noupe.com/how-tos/10-ways-to-automatically-manually-backup-mysql-database.html I prefer http://www.mysqlperformanceblog.com/2009/03/03/10-things-you-need-to-know-about-backup-solutions-for-mysql/ Particularly point 10 :) In some 20 years I've never lost an Interbase or Firebird database but I've been involved in rebuilding MySQL ones from raw data and other content ... a couple of times into a Firebird replacement :) -- Lester Caine - G8HFL - Contact - http://lsces.co.uk/wiki/?page=contact L.S.Caine Electronic Services - http://lsces.co.uk EnquirySolve - http://enquirysolve.com/ Model Engineers Digital Workshop - http://medw.co.uk Rainbow Digital Media - http://rainbowdigitalmedia.co.uk
RE: [firebird-support] Using stored procedure results in the WHERE clause
Stephen, I have a set of frequently used search conditions that I find myself repeating over and over in different places in my application. It would be nice to be able to encapsulate these conditions into a stored procedure and just reference the stored procedure in the WHERE clause of my SELECT statement when I need them. Trouble is, I can't figure out how to do it or even if it is possible. Do not use a Stored Procedure, use a COMPUTED BY column. Reason == Optimizer should be able to factor the SQL of the field when deciding which indexes/best plan to use. SP is treated with no intelligence. The syntax is pretty straight-forward: ALTER TABLE OPS_STOP_REC ADD Is_Manifested_CM COMPUTED BY ( CASE WHEN (COALESCE( ( SELECT 1 FROM OPS_MANIFEST_STOPS M JOIN OPS_HEADER P ON P.PB_ID = O.OPS_ORDER_ID WHERE M.OSM_PKUP_DELV_ID = OPS_STOP_ID AND COALESCE(P.PB_STATUS, 0) 9) ), 0) = 1 THEN 1 ELSE 0 END ); To use: SELECT * FROM OPS_STOP_REC WHERE Is_Manifested_CM = 1; Sean P.S.The _CM suffix to the column/field name is just my way of indicating that the column is COMPUTED.
Re: [firebird-support] Firbird slow performance at windows server 2012
Before use windows server 2012 (i7, 8GB ram) i used windows xp profesional (dual core, 2GB ram). There is no problem with the application or queries. I just copy the database into windows server 2012, the number of users is same, queries is same to. everything is identical I use FB 2.1 64 bit and try 2.5 64 bit Thanks Chandra From: Alexandre Benson Smith ibl...@thorsoftware.com.br To: firebird-support@yahoogroups.com Sent: Tuesday, July 9, 2013 12:24 AM Subject: Re: [firebird-support] Firbird slow performance at windows server 2012 Em 8/7/2013 05:51, Svein Erling Tysvær escreveu: Dear All, I have application with delphi and firebird database, there is no problems until migrate the server to windows server 2012. The query (select, insert, update etc) running to slow. I searching and found the solution example : change filesystemcache,tempbocksize,defaultdbcachepages at firebird.conf but the query still running to slow. There are another way? Please tell us more, Chandra, even though I know nothing about Windows vs Firebird, I know that a detailed and specific problem description is more likely to result in good answers than general questions. You say that you migrate to Windows server 2012, what do you migrate from? Is everything except the server identical on the fast and slow server - e.g. indexes and plans for all problematic queries? The same number of users and the same content in the databases? What about database statistics? If there are specific queries running slow, you could post them here together with the generated plans and some information about the fields in the indexes being used. Set Besides that. Wich FB version ? Newer windows versions (64bit) has changed th way to manage filesystem cache and it became a problem... Read more here: http://dyemanov.blogspot.com.br/2012/03/firebird-vs-windows-file-system-caching.html see you ! [Non-text portions of this message have been removed]
