Re: [firebird-support] Is this a bug of Firebird?
Interesting. (I haven't used Delphi for decades, certainly not this century.) On 09/02/2017 13:17, DougC d...@moosemail.net [firebird-support] wrote: Delphi's latest compiler provides a hint in these cases: var x: Int32; begin try x := 0; x := 1; writeln(x); end. [dcc32 Hint] Project1.dpr(14): H2077 Value assigned to 'x' never used (Line 14 is the one assigning zero.) On Thu, 09 Feb 2017 04:30:06 -0500 *Tim Ward t...@telensa.com [firebird-support] <firebird-support@yahoogroups.com>* wrote It' the equivalent in a conventional programming language of saying: x = a; x = b; where the compiler is expected to know that neither a not the first assignment have any side effects other than the assignment (and where the expression b doesn't depend on the value of x)(and where x isn't volatile, ect ect). A compiler *could* detect and warn about such things (ie it's not forbidden by the laws of mathematics) but I don't think I know of any that do. And as there are good reasons for deliberately wanting to do the above it could only be a warning, not an error. -- Tim Ward
Re: [firebird-support] Is this a bug of Firebird?
It' the equivalent in a conventional programming language of saying:
x = a;
x = b;
where the compiler is expected to know that neither a not the first
assignment have any side effects other than the assignment (and where
the expression b doesn't depend on the value of x)(and where x isn't
volatile, ect ect).
A compiler *could* detect and warn about such things (ie it's not
forbidden by the laws of mathematics) but I don't think I know of any
that do. And as there are good reasons for deliberately wanting to do
the above it could only be a warning, not an error.
On 08/02/2017 23:36, 'Walter R. Ojeda Valiente'
sistemas2000profesio...@gmail.com [firebird-support] wrote:
The error is of the programmer, I agree with you, but to repeat the
name of a variable without the Firebird showing any message of error
is, at least for me, a bug.
To have 2 or more variables with the same name after the INTO clause
is useless. The compiler can be smart enough to detect such thing.
Or not?
Greetings.
Walter.
On Wed, Feb 8, 2017 at 2:36 PM, 'Leyne, Sean'
s...@broadviewsoftware.com [firebird-support]
<firebird-support@yahoogroups.com
<mailto:firebird-support@yahoogroups.com>> wrote:
> Yes, but I can not know the value of the column X.ALU_NOMBRE
>
> And the idea, of course, is know that value, that's why it
appears in the FOR
> SELECT. If not, I can do nothing with X.ALU_NOMBRE
You are asking for the system to evaluate the *intent* of logic.
That is completely outside the purview of any application
environment that I know.
The only thing that a system can check/enforce is the correctness
of the code, not to check whether the developer has 2 brain cells.
Sean
--
Tim Ward
[firebird-support] UDF parameter size mismatch
So, if I've got a UDF which is declared as DECLARE EXTERNAL FUNCTION xxx INTEGER, INTEGER, INTEGER, INTEGER, INTEGER, INTEGER, INTEGER, INTEGER, INTEGER, INTEGER RETURNS INTEGER BY VALUE ENTRY_POINT 'xxx' MODULE_NAME 'xxx_udf'; these INTEGERs are all 32 bits, right, both on 32 bit and 64 bit versions of Firebird? And if it turns out that the function is declared in C as long xxx(long *elem1, long *elem2, long *elem3, long *elem4, long *elem5, long *elem6, long *elem7, long *elem8, long *elem9, long *elem10) we might expect long to be 32 bits in a 32 bit build and 64 bits in a 64 bit build, yes? Which means that on a 64 bit build there's a mismatch between the parameter and result sizes, yes? Does anyone have the remotest clue what will happen? - this is crypto stuff (it would be), so therefore (as always with crypto stuff) the function churns away and returns some number which you can't tell by looking at it whether it's right or not. -- Tim Ward
Re: [firebird-support] firebird schema changes during backup
On 24/01/2017 01:03, 'Leyne, Sean' s...@broadviewsoftware.com [firebird-support] wrote: Firebird does support dynamic changes, you can change the database schema while users are connected. Dmitry was referring more to the inherent risks of using a production database as a development platform -- schema changes need to be tested and carefully applied. In our case, we use strict SQL script numbering process along with a CI process that ensures that all scripts are checked/tested against exemplar database*0*s before a master script is created/applied. And even then you can have fun when one of your production databases has patterns of data that weren't included in your test data sets. -- Tim Ward
Re: [firebird-support] Re: Time zones
On 13/01/2017 14:04, Dimitry Sibiryakov s...@ibphoenix.com [firebird-support] wrote: > 13.01.2017 13:09, Tim Ward t...@telensa.com [firebird-support] wrote: >> I just don't believe this. Can anyone make any other suggestion as to >> what is going on? > Is timezone on your Linux set globally or for each current user > environment independently? One thing I've now noticed is that running the trigger by updating a record via isql gives the correct, Brazilian, answer. I was getting the wrong answer when doing the update using Database Workbench. Whether this is the tool or the result of using an old connection or what I don't know, next week I'll reboot everything and retry all the experiments. -- Tim Ward ++ Visit http://www.firebirdsql.org and click the Documentation item on the main (top) menu. Try FAQ and other links from the left-side menu there. Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ ++ 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: https://info.yahoo.com/legal/us/yahoo/utos/terms/
[firebird-support] Re: Time zones
However what I'm actually seeing is as follows. (1) Set a Linux machine to "Brazil/East". Check this using the "date" command line command. (2) Confirm that select CURRENT_TIMESTAMP from rdb$database does report the Brazilian local time as expected. (3) Next, I've got a table with a TIMESTAMP column called TIMEDATESAVED. The BEFORE UPDATE trigger includes new.TIMEDATESAVED = CURRENT_TIMESTAMP; When I modify a record in that table I find that TIMEDATESAVED gets set to the the current UK wall clock time, *not* the Brazilian time as at (3) above. That's looking at the field both using a SELECT from isql and using Database Workbench, both giving the same result - I haven't made any attempt to see what the actual binary on the disk is. -- So it seems from these experiments that CURRENT_TIMESTAMP sometimes reports the Brazilian time and sometimes the UK time. I just don't believe this. Can anyone make any other suggestion as to what is going on? On 13/01/2017 11:21, Tim Ward wrote: > On 12/01/2017 14:09, Tim Ward wrote: >> Sorry if this is a really basic question, but some time spent >> searching has failed to find for me the definitive detailed >> documentation on exactly how Firebird handles time zones (I'm >> particularly interested, to start with, in using CURRENT_TIMESTAMP to >> create a value in a TIMESTAMP field, and later reading it out and >> using it to display in a UI, but that's only to start with). >> >> Can someone point me in the right direction please? > > So to summarize the replies (thanks all): > > (1) Firebird does not attempt to do anything at all with time zones, > and has no concept of UTC. > > (2) CURRENT_TIMESTAMP and its relatives ask the operating system for > current wall clock time. > > (3) This is what gets stored and read back out of tables, with no time > zone manipulation. > > (4) So if for example CURRENT_TIMESTAMP is called when it's 0700 UTC > but 0500 local wall clock time as configured in the operating system, > then the value will be 0500 at all times and places in Firebird > (unless arithmetic is done in it by explicit application code). > > (I'm trying to understand the behaviour of an existing system here, > not design anything new.) > -- Tim Ward
[firebird-support] Re: Time zones
On 12/01/2017 14:09, Tim Ward wrote: > Sorry if this is a really basic question, but some time spent > searching has failed to find for me the definitive detailed > documentation on exactly how Firebird handles time zones (I'm > particularly interested, to start with, in using CURRENT_TIMESTAMP to > create a value in a TIMESTAMP field, and later reading it out and > using it to display in a UI, but that's only to start with). > > Can someone point me in the right direction please? So to summarize the replies (thanks all): (1) Firebird does not attempt to do anything at all with time zones, and has no concept of UTC. (2) CURRENT_TIMESTAMP and its relatives ask the operating system for current wall clock time. (3) This is what gets stored and read back out of tables, with no time zone manipulation. (4) So if for example CURRENT_TIMESTAMP is called when it's 0700 UTC but 0500 local wall clock time as configured in the operating system, then the value will be 0500 at all times and places in Firebird (unless arithmetic is done in it by explicit application code). (I'm trying to understand the behaviour of an existing system here, not design anything new.) -- Tim Ward
[firebird-support] Time zones
Sorry if this is a really basic question, but some time spent searching has failed to find for me the definitive detailed documentation on exactly how Firebird handles time zones (I'm particularly interested, to start with, in using CURRENT_TIMESTAMP to create a value in a TIMESTAMP field, and later reading it out and using it to display in a UI, but that's only to start with). Can someone point me in the right direction please? -- Tim Ward
Re: [firebird-support] slow connection time to the db
Could be worse, at least you haven't run into the infamous Java spurious reverse DNS lookup timeout ... which is 30 seconds. On 06/01/2017 13:31, Marianne Castel - Titelive caste...@titelive.be [firebird-support] wrote: Thanks Dimitry, I'll ask my server's administrators to have a look in that direction. Marianne Castel Development team -Message d'origine- De : firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] Envoyé : jeudi 5 janvier 2017 15:03 À : firebird-support@yahoogroups.com Objet : Re: [firebird-support] slow connection time to the db 05.01.2017 14:40, Marianne Castel - Titelive caste...@titelive.be [firebird-support] wrote: > Has somebody an idea, where is the sytem loosing time ? Most often (x)inetd waste time trying to identify client by ident protocol or reverse DNS to get host name. -- WBR, SD. -- Tim Ward
Re: [firebird-support] Safe Thread - ODBC
On 22/11/2016 12:32, fabia...@itbizolutions.com.au [firebird-support] wrote: The application does not use "begin transaction and commit", so we let the database commit automatically after a write process, we read the table from another thread inside the app or from another app inside the same OS session, and the data is not there. Of course after a few seconds (or milliseconds) the data is available. Our concern is that we have a sequence of code that assume whatever was written 5 lines above is already available for reading, but it is failing every so often. When did I last see that one? - ah yes, it was using an Access database to communicate from one process on one machine to another on another. You'd receive the sync event (via another channel), poll the database, and find that the new data wasn't actually there yet. Turned out that there was a half-second delay before data got flushed to disk, as an optimisation in case you were just about to write something else. (Which could, once you'd discovered what was going on, which might take a few days or weeks, be turned off.) I don't however expect to see exactly the same problem with Firebird 3 ... -- Tim Ward
Re: [firebird-support] php transactions
On 12/07/2016 21:28, 'Daniel Miller' dmil...@amfes.com [firebird-support] wrote: I have an application I'm developing with PHP & Firebird. While it mostly works fine - I've been having what I believe to be transaction issues. One problem was visibility - committed changes were not visible to selects. I know they were committed - after executing the commit within the scripts, I could see the changes within FlameRobin. Yet the script didn't see them. I also saw the "oldest active transaction" getting old...and it didn't seem to update until I killed some of the longer-running php processes. I use explicit transactions for every insert or update followed by explicit commits. Could my problem be that I need to use explicit transactions for the selects as well? Quite likely. If you have a long running PHP process which does lots of selects and never does a commit it'll quite likely all be in one transaction which can produce the symptoms you describe. -- Tim Ward
Re: [firebird-support] Failure to create primary key - 2.5, 64 bit, Linux
... but having restored the database from backup the problem appears to have gone away. On 12/07/2016 12:52, Tim Ward wrote: There was plenty of space on the disk partition containing the database. But there had at some point been a disk full on some other partition. So, having run out of ideas, I've deleted the database and am recreating it from backup, in the hope that just maybe it got corrupted when the other partition filled up. On 12/07/2016 12:50, liviuslivius liviusliv...@poczta.onet.pl [firebird-support] wrote: W dniu 2016-07-12 11:54:52 użytkownik Tim Ward t...@telensa.com [firebird-support] <firebird-support@yahoogroups.com> napisał: == > CREATE TABLE TBLCSTAGASSETS > ( > ELEMENTID BIGINT NOT NULL, > USERID INTEGER NOT NULL, > CONSTRAINT PK_TBLCSTAGASSETS PRIMARY KEY (ELEMENTID, USERID) > ) > commit < ERROR: unsuccessful metadata update cannot create index PK_TBLCSTAGASSETS > script processed in 0.055 sec == This works when run against a 32 bit 2.1 database but fails when run against a 64 bit 2.5 database. What's going on please? -- Tim Ward What exact version of FB2.5 i tested this on WI-V2.5.3.26738 Firebird 2.5 but on Windows not Linux and no problems. Do you have free disc space? May be db try to expand? regards, Karol Bieniaszewski -- Tim Ward -- Tim Ward
Re: [firebird-support] Failure to create primary key - 2.5, 64 bit, Linux
There was plenty of space on the disk partition containing the database. But there had at some point been a disk full on some other partition. So, having run out of ideas, I've deleted the database and am recreating it from backup, in the hope that just maybe it got corrupted when the other partition filled up. On 12/07/2016 12:50, liviuslivius liviusliv...@poczta.onet.pl [firebird-support] wrote: W dniu 2016-07-12 11:54:52 użytkownik Tim Ward t...@telensa.com [firebird-support] <firebird-support@yahoogroups.com> napisał: == > CREATE TABLE TBLCSTAGASSETS > ( > ELEMENTID BIGINT NOT NULL, > USERID INTEGER NOT NULL, > CONSTRAINT PK_TBLCSTAGASSETS PRIMARY KEY (ELEMENTID, USERID) > ) > commit < ERROR: unsuccessful metadata update cannot create index PK_TBLCSTAGASSETS > script processed in 0.055 sec == This works when run against a 32 bit 2.1 database but fails when run against a 64 bit 2.5 database. What's going on please? -- Tim Ward What exact version of FB2.5 i tested this on WI-V2.5.3.26738 Firebird 2.5 but on Windows not Linux and no problems. Do you have free disc space? May be db try to expand? regards, Karol Bieniaszewski -- Tim Ward
[firebird-support] Failure to create primary key - 2.5, 64 bit, Linux
== > CREATE TABLE TBLCSTAGASSETS > ( > ELEMENTID BIGINT NOT NULL, > USERID INTEGER NOT NULL, > CONSTRAINT PK_TBLCSTAGASSETS PRIMARY KEY (ELEMENTID, USERID) > ) > commit < ERROR: unsuccessful metadata update cannot create index PK_TBLCSTAGASSETS > script processed in 0.055 sec == This works when run against a 32 bit 2.1 database but fails when run against a 64 bit 2.5 database. What's going on please? -- Tim Ward
Re: [firebird-support] Users for application
On 23/06/2016 12:46, Gabriel Frones grfro...@gmail.com [firebird-support] wrote: You can still see which process is doing what using a single user, though, since the system tables provide you with process name (foo.exe) and ip address for each transaction. Depends on your particular circumstances - that's not terribly helpful if all the process names are "python" and all the IP addresses are "127.0.0.1", for example. -- Tim Ward
Re: [firebird-support] Users for application
On 23/06/2016 03:17, 'Daniel Miller' dmil...@amfes.com [firebird-support] wrote: Separate from security theories and considerations of "good practice", what, if any, benefits accrue from using multiple users when accessing a Firebird database? We have different processes using different users. This means that poking around in the database to see what's going on (performance, long-lived transactions, etc) is a bit easier - we can instantly see which process is doing what, as the users are named after the processes. -- Tim Ward
[firebird-support] When do triggers take effect?
Using Database Workbench I added a trigger to a table, but it didn't seen to do anything (in particular didn't throw an exception which it should have done when I deliberately created an invalid record). After restarting DBWB the trigger behaved as expected. Do triggers not take effect on existing connections, or something? -- Tim Ward
Re: [firebird-support] Connecting to Firebird database from two or more pc with same username
On 14/04/2016 08:26, 'Thomas Steinmaurer' t...@iblogmanager.com [firebird-support] wrote: With a single user you basically can't distinguish people on the server side through CURRENT_USER etc. We use one Firebird user for the web server, and then one each for each of several batch processes, so if we poke around in the database we can see at least which part of the system any problematic (eg very long running) user sessions or transactions or queries belong to. -- Tim Ward
Re: [firebird-support] Update big table and nbackup benefit or not?
If the update makes the records longer (after run-length encoding) it's even more fun, as you might get fragmentation (of records across pages) and access times can then increase by a very large factor indeed, even to the extent of completely crippling the performance of an entire application. On 05/04/2016 09:21, liviuslivius liviusliv...@poczta.onet.pl [firebird-support] wrote: Hi, i must update big table 100 GB and as we know when we do update then new record version will be created. scenarion 1: 1. Table size 100GB (db size 200GB) 2. Update field in all records generate 100GB new record versions 3. table size after is 200GB (db size 300GB) 4. sweep remove 100GB and mark pages as free (table size 100 GB but database still 300GB) 5. backup and restre bring db to it previous size (db size 200GB) but what happen when i do this? scenarion 2: 1. Table size 100GB (db size 200GB) 2. i lock database with nbackup -L 3. Update field in all records generate 100GB delta file (db size 200GB) 4. table size in db is 100GB and delta is 100GB (db size 200GB) 5. i unlock database nbackup -U A. table will be 100GB and no free pages? (db size will be 200GB and no need to bacup and restore process) B. table will be 100GB and in db will be 100GB free pages? (db size will be 300GB and i need to bakup and restore?) what is the answer for this A or B? regards, Karol Bieniaszewski -- Tim Ward
[firebird-support] COALESCE
So I'm getting errors when there's a call to COALESCE with only a single
parameter. (Why would we write code like that? - because nobody thought
to spot it as a special case in our query generation code, perhaps.)
So what does the Firebird book say? - well, my copy appears to say
COALESCE (value 1> { , value 2 [, ...value n })
where {} indicates "mandatory", so it looks like there must be at least
two parameters, so our query generation code is wrong.
Jolly good. So why am I pointing this out? (Other than that if the
designer of the syntax for COALESCE had considered the possibility of
machine-generated code they might have reached a different decision
about the second parameter, or even the first, being mandatory?)
I'm just pointing out the typo in the book - there's no closing square
bracket in that definition.
--
Tim Ward
++
Visit http://www.firebirdsql.org and click the Documentation item
on the main (top) menu. Try FAQ and other links from the left-side menu there.
Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/
++
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:
https://info.yahoo.com/legal/us/yahoo/utos/terms/
[firebird-support] violation of PRIMARY or UNIQUE KEY constraint
So I've got this transaction which tries to create records in a table and gets "violation of PRIMARY or UNIQUE KEY constrain" There are, I believe, no records in the table /visible to the same transaction/ with the same primary key, as the insertion code checks first, and doesn't attempt to insert a record that's already there. Now, I seem to recall seeing somewhere that actually a primary key (and any other unique index?) is independent of transactions, so the following: (1) Transaction A inserts record X (2) Transaction B attempts to insert record X (having first checked that it doesn't exist, which it doesn't as far as transaction B is concerned, because transaction A hasn't committed yet) results in the error. Have I remembered this behaviour of primary keys correctly? Please could someone remind me where the documentation is if so? -- Tim Ward
Re: [firebird-support] firebird and transaction behaviour questions
On 23/02/2016 15:38, 'Zoran' zoran...@gmail.com [firebird-support] wrote: 23.02.2016 16:03, 'Zoran' zoran...@gmail.com [firebird-support] wrote: >> Why would you use transaction for select statements? > For data consistency. People like consistent data in reports. If other clients are using transactions properly (when updating multiple rows), database should be in a stable state. In the following sequence: - you do a SELECT - some other transaction commits - you do another SELECT - you build a report with the results from the two SELECTs you can get inconsistent results in your report unless your two SELECTs are in a transaction. Using transactions when not needed (in my opinion - and I am no expert in Firebird) just add an overhead to the server. If the database designers have chosen that everything is always done in a transaction then that's how it works - there's no overhead because there's no concept of doing anything without a transaction. -- Tim Ward
Re: [firebird-support] cannot transliterate Firebird 1.5
On 27/01/2016 13:15, blumy2...@yahoo.com [firebird-support] wrote: in my Builder c++ 6 application i have a SQL query for a table, in Firebird 1.5.6 fdb database " where STRADA " + = QuotedStr(edText->Text). Field STRADA in table is define WIN1251 in edText, the are Romanian characters, like ş and ţ. As far as I can see, those characters do not exist in the Windows 1251 character set, so isn't the error message simply telling the truth? -- Tim Ward
[firebird-support] The PHP default transaction
I've got a long running (hours) PHP script which does large numbers (tens of thousands) of database operations along the lines of start explicit transaction do something in that transaction commit that transaction the idea being not to hold any individual transaction open for a long time and, thereby, not to open up a transaction number gap and hold up garbage collection. But we see from the MON$ tables what looks like a transaction that starts at the beginning of the script and remains in existence throughout the lifetime of the script. This transaction is marked "inactive". (1) Is this likely to be the "default transaction" that PHP creates for a connection if you don't do an explicit start transaction? Ie, does PHP create the default transaction for a connection even if you never use it (rather than create it on first use, as one might have hoped)? (2) What, if anything, is the import of a transaction being marked "inactive"? Does an "inactive" transaction still freeze the various transaction counters and hold up garbage collection? (3) If the answer to (2) is that the existence of this transaction *is* a potential performance problem, I imagine I can get rid of it simply by doing "ibase_commit();" at the start of the PHP script? -- Tim Ward
[firebird-support] Restore failed
gbak:adjusting an invalid decompression length from -33 to -19 gbak: ERROR:value exceeds the range for valid timestamps gbak: ERROR:gds_$send failed gbak:Exiting before completion due to errors Anyone any idea what any of that means? -- Tim Ward
[firebird-support] 32 bit vs 64 bit arthmetic
So I've got this stored procedure which takes DECIMAL(18,6) parameters and returns DECIMAL(18,12) results, and internally copies stuff into and out of DOUBLE PRECISION variables which it uses to call a UDF to do the actual calculations. And the results are different (in the 8th decimal place or so) between running on 32 bit and 64 bit systems. I would expect that Firebird is going to do exactly the same thing on both, down to the last bit, as the data types are defined to be what they are and nothing to do with the native word length of the machine? So I've got to look at the UDF for the difference? (Which I'd not expect to find there either, actually, *if* the code is the same, as IEEE arithmetic also shouldn't change just because the lengths of integers changes. So I suspect that what I'm actually going to be looking for is a difference in the code in the two different versions of the library called by the UDF.) -- Tim Ward
Re: [firebird-support] 32 bit vs 64 bit arthmetic
On 18/01/2016 17:34, 'Leyne, Sean' s...@broadviewsoftware.com [firebird-support] wrote: Tim, > So I've got this stored procedure which takes DECIMAL(18,6) parameters and > returns DECIMAL(18,12) results, and internally copies stuff into and out of > DOUBLE PRECISION variables which it uses to call a UDF to do the actual > calculations. > > And the results are different (in the 8th decimal place or so) between > running on 32 bit and 64 bit systems. Please post the logic for the SP and a reproducible example, this may be worthy of a bug tracker ticket. I have confirmed that the versions of the underlying library (GDAL) are different on the two systems, and for that and other reasons that the best use of my time at the moment is doing other things, so I will be assuming that it's the change to the GDAL code that resulted in the slightly different output and will not be researching it any further. -- Tim Ward
[firebird-support] Forced writes on Linux?
I know the folklore around forced writes
- turning forced writes on is safer
- turning forced writes on is slower
- the safety bit is much more of an issue with Windows than with Linux,
to the extent that it used not to work on Linux and nobody noticed for
years.
But I haven't found any actual, y'know, like, data, evidence, so on.
Things like (with reference to Linux, I'm not interested in Windows):
(1) Is there any data about how much slower it is, eg has anyone done
any benchmark runs on their systems? - I've found just one blog entry
somewhere with a number ("up to three times slower") but without any
(published) data behind it.
(2) Is there any evidence about how much safer it is? Statistics on
corruptions with and without? Analysis of individual database corruption
events showing whether turning on forced writes would or would not have
prevented the corruption event?
Thanks
--
Tim Ward
Re: [firebird-support] Small Database with very very bad performance on LAN
On 08/01/2016 11:50, Luigi Siciliano luigi...@tiscalinet.it [firebird-support] wrote: > Hallo, > I have a small database (less 100MB) that works on Firebird 2.5.5 > SS64bit over a machine with Win7 64bit with 4GB Ram, CPU Pentium G620. > > I seems to work very well in local but with the 3 clients on LAN works > very bad. It is very very slow to open and navigate a simple table with > less of 5.000 rows! > > What i can do? What are your queries, query plans, query statistics? -- Tim Ward ++ Visit http://www.firebirdsql.org and click the Documentation item on the main (top) menu. Try FAQ and other links from the left-side menu there. Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ ++ 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: https://info.yahoo.com/legal/us/yahoo/utos/terms/
[firebird-support] Can "forced writes" change application behaviour?
Hi, We've got a system which does different things depending on whether "forced writes" is switched on or not (and, possibly, on /when/ it's switched on or off). It seems to me that this is wrong - turning forced writes on or off should only affect performance, it shouldn't make an application follow different paths through the code? So my first assumption is that in fact forced write state is not actually causing Firebird to behave in different ways, and that we've got (a) some of our application code that is timing dependent, or (b) something else wrong, and the repeatable changes in behaviour when switching forced writes on and off are just an amazing coincidence. But just to eliminate the most unlikely explanation first, and get that out of the way, please can someone confirm to me very strenuously indeed that there is no possibility that (c) switching forced writes on or off changes the logical behaviour of a Firebird application whether through changing the outcome of multiple concurrent transactions or otherwise? This is Firebird 2.5 running on 64 bit Linux. The application consists of a bunch of processes written in C/C++ accessing the database, partly direct queries and partly stored procedure calls, as well as user interface accesses via PHP. (Oh, and possibly some Python processes and other stuff, but it's the interaction between a couple of the C/C++ processes that is currently confusing us.) -- Tim Ward
Re: [firebird-support] Is it save to append some data at end of the binary firebird database file?
On 04/12/2015 00:07, Christian Gütter n...@guetter.org
[firebird-support] wrote:
> In Windows this is not an abuse, executable files have
> provision for version metadata ("resource") and you're supposed
> to use it properly. Pity this doesn't apply portably to all file
types, innit.
True, but if you just append the info to the exe file, it is an abuse ;-)
And there are no restrictions with regard to the information you want
to store.
There are no restrictions doing it properly with resources - you don't
have to just use the VERSIONINFO resource (although you should, as vast
numbers of other tools understand it), you can add whatever custom
resources you like.
--
Tim Ward
Re: [firebird-support] Is it save to append some data at end of the binary firebird database file?
On 03/12/2015 14:48, Christian Gütter n...@guetter.org
[firebird-support] wrote:
Or, if you are a big fan of abusing file formats, you might want to
try to append the information to your executable file.
In Windows this is /not /an abuse, executable files have provision for
version metadata ("resource") and you're /supposed/ to use it properly.
Pity this doesn't apply portably to all file types, innit.
--
Tim Ward
Re: [firebird-support] Deleting records and deadlocks
Thanks for the replies. Don't worry folks, I'm not going to try this, I was just curious. Yes I do know it's not a real deadlock, I was using the word because I knew it would be understood and because, I'm pretty sure?, I've seen it in one of the relevant error messages. -- Tim Ward ++ Visit http://www.firebirdsql.org and click the Documentation item on the main (top) menu. Try FAQ and other links from the left-side menu there. Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ ++ 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: https://info.yahoo.com/legal/us/yahoo/utos/terms/
[firebird-support] Deleting records and deadlocks
I know that if two concurrent transactions try to make changes to the same record at the same time one of them gets a deadlock. What about if two concurrent transactions are both trying to *delete* the *same* record at once? - from the point of the view of the user's objectives there's no reason why this shouldn't work, as either way the record is going to end up deleted, which is what the user wants, but would I be right in guessing that Firebird isn't that clever, and that deadlocks are possible in this scenario? -- Tim Ward
[firebird-support] Error 335544721
Any idea how to start diagnosing this? It's not actually a network error, as client and server are running on the same machine. We have multiple processes running for hours doing the same sorts of things to the database, and every once in a while one of the client processes crashes with this error. Restart it, and it runs fine. Until the next time. -- Tim Ward
Re: [firebird-support] Why can't I have a SUSPEND or UPDATE in the same Stored Procedure?
There are a number of areas where something slightly unusual works in Firebird but Database Workbench can't cope with it. Have you tried running it by typing EXECUTE PROCEDURE into isql? On 03/11/2015 16:09, 'stwizard' stwiz...@att.net [firebird-support] wrote: I’m sorry I should have been a little bit more concise on what I’m doing. I use Database Workbench v5 for all of my development needs. Running the stored procedure setting the V_REPORT = 1 in Database Workbench, I do get a results set. So far, so good. However, if I set V_REPORT = 0 which should cause the UPDATE to be processed instead, I’m not able to commit as the “Commit” and “Rollback” buttons are not enabled. However, If I comment out the first portion (as shone below) leaving just the UPDATE clause it works fine and the “Commit” and “Rollback” buttons are not enabled. /* IF (V_REPORT = 1) THEN SUSPEND; ELSE */ UPDATE ACCT_CASE SET LEGAL_CASE_DATE = :ACCH_LEGAL_CASE_DATE WHERE ACCT_ID = :ACCT_ID AND CASE_ID = :CASE_ID; Any ideas why? Thanks, Mike From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] Sent: Tuesday, November 03, 2015 9:29 AM To: firebird-support@yahoogroups.com Subject: ODP: [firebird-support] Why can't I have a SUSPEND or UPDATE in the same Stored Procedure? hi, you got an error or what? regards, Karol Bieniaszewski Oryginalna wiadomość Od: "'stwizard' stwiz...@att.net [firebird-support]" <firebird-support@yahoogroups.com> Data: 03.11.2015 14:59 (GMT+01:00) Do: firebird-support@yahoogroups.com Temat: [firebird-support] Why can't I have a SUSPEND or UPDATE in the same Stored Procedure? Greetings All, Firebird v 2.5.4 Many times I would like to run a report before I do an update. Why can’t I allow for both in one stored procedure? Look at the end of this stored procedure where I use V_REPORT. Thanks, Mike SET TERM ^^ ; CREATE PROCEDURE X_CHK_LEGAL_CASE_DATE ( V_REPORT SmallInt) returns ( ACCT_ID Integer, CASE_ID SmallInt, LEGAL_CASE_DATE Date, CASE_LEGAL_CASE_DATE Date, ACCH_LEGAL_CASE_DATE Date, ACCH_NOTE VarChar(200)) AS DECLARE VARIABLE iAcctCaseCourtID Integer; begin FOR SELECT ACCT_CASE_COURT_ID, CAST(CREATE_DATE AS DATE), ACCT_ID, CASE_ID FROM ACCT_CASE_COURT WHERE STATUS_CODE = 'A' ORDER BY ACCT_ID, CASE_ID INTO :iAcctCaseCourtID, :LEGAL_CASE_DATE, :ACCT_ID, :CASE_ID DO BEGIN SELECT LEGAL_CASE_DATE FROM ACCT_CASE WHERE ACCT_ID = :ACCT_ID AND CASE_ID = :CASE_ID INTO :CASE_LEGAL_CASE_DATE; IF (CASE_LEGAL_CASE_DATE IS NULL) THEN BEGIN SELECT FIRST 1 CAST(CREATE_DATE AS DATE), NOTE FROM ACCT_CASE_COURT_HIST WHERE ACCT_CASE_COURT_ID = :iAcctCaseCourtID ORDER BY ACCT_CASE_COURT_HIST_ID INTO :ACCH_LEGAL_CASE_DATE, ACCH_NOTE; IF (V_REPORT = 1) THEN SUSPEND; ELSE UPDATE ACCT_CASE SET LEGAL_CASE_DATE = :ACCH_LEGAL_CASE_DATE WHERE ACCT_ID = :ACCT_ID AND CASE_ID = :CASE_ID; END END end ^^ SET TERM ; ^^ [Non-text portions of this message have been removed] -- Tim Ward
[firebird-support] Holding a connection open forever
What are the implications of holding a connection to the database open forever (and running millions and millions of tiny transactions flat out through that connection)? We have a system whose performance gradually gets worse and worse, and we have a feeling that one of the things that gets it back to normal is restarting one particular process which does all its business through a single permanent connection. We're wondering whether dropping and re-making the connection from time to time would help, but doing a decent set of before-and-after experiments would take months and would cause significant disruption to a live system. I think we know about stored procedures not being re-optimised if the connection is held open, but we're not worried about that in this instance as the system is mature and the nature of the data (and hence the statistics) isn't changing much. So: What other information, resources, etc, does the database hold internally linked to an open connection? How might these affect performance? What difference would it make if we dropped the connection periodically? This is with 2.1 - are there any changes in 2.5 in this area? (I have asked a similar question some time ago but didn't end up with any actual diagnosis of our problem.) -- Tim Ward ++ Visit http://www.firebirdsql.org and click the Documentation item on the main (top) menu. Try FAQ and other links from the left-side menu there. Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ ++ 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: https://info.yahoo.com/legal/us/yahoo/utos/terms/
Re: [firebird-support] How can I see which query within a stored procedure execution takes the longest time?
I lost the logging code some time ago so can't quote it I'm afraid. I defined an external table, mapped to a file, with columns for timestamp, a couple of numeric values, and a text message. Then a procedure which wrote a record to this table. Then called the procedure at points in my code where I suspected there were bottlenecks. I haven't used this approach for a while because I found the profiling output easier to use (no need to instrument the code) and just as useful. This involves running the query select * from mon$call_stack order by mon$call_id desc from a PHP script once a second and (if you can be bothered) doing various post-processing on the result. The query appears to only work sometimes, and I don't know why, but it works often enough to be useful. This is Firebird 2.1. On 13/10/2015 19:30, 'stwizard' stwiz...@att.net [firebird-support] wrote: Hi Tim, Thanks for the reply. You mentioned that you did it with a logging procedure. Can you share what you exactly what you did to accomplish this? Mike -- Tim Ward
Re: [firebird-support] How can I see which query within a stored procedure execution takes the longest time?
I did it by writing a logging procedure which wrote timestamps to an external file mapped to a text file, then calling it where I was interested. An alternative is Monte Carlo profiling - I've also got a script which polls the call stack from the RDB$ tables. The procedure or statement it hits most often is the one taking the most time! (This turns out not to be terribly reliable in terms of actually getting call stack data every time it asks, but it does seem to be pretty reliable in pinpointing the performance bottlenecks.) On 13/10/2015 16:30, 'stwizard' stwiz...@att.net [firebird-support] wrote: Greetings All, Firebird 2.5.4 I would like to know if there is any way that I can retrieve the execution time of each SQL SELECT or EXECUTE PROCEDURE within a stored procedure? Any tool to accomplish this? Thanks, Mike -- Tim Ward
Re: [firebird-support] Find grid page containing record
Thanks, hadn't thought of the COUNT(*). This still means visiting every record of course, but at least on a good day most of them are being done entirely within the database engine. On a bad day however this might not gain anything if the user chooses to sort by something useless and unselective (you might say they then deserve the delay they get, but we're trying not to build too many more of these into the system). Plus autogenerating the code for the comparison could be "interesting" particularly in cases where some of the fields in the ORDER BY (under the control of the user, remember) are ASC and some DESC? I think we'll have to continue to think about it! On 06/10/2015 21:50, setysvar setys...@gmail.com [firebird-support] wrote: Den 06.10.2015 17:38, skrev Tim Ward t...@telensa.com [firebird-support]: > Given that a query needed to return data for a page of a grid is of the form > > SELECT FIRST 25 SKIP > .ID (and some other fields of human-readable data) > FROM < plus tables as needed for other fields in the > SELECT, WHERE and ORDER BY clauses> > WHERE <something, may be complex expressions involving several fields> > ORDER BY > > how can I find out what is for a given table>.ID? > > (Without using any features that are only in Firebird 3, which are > needed for the solutions I've found so far. Surely this is not a rare > thing to want to be able to do?) > > Scenario: Data is displayed in a grid in the user interface, with 25 > records per page. The user gets to specify the filters (WHERE) and > sorting (ORDER BY) in the user interface, ie these change outside my > control (and in consequence appropriate tables get pulled into the FROM > clause as necessary by the query generation code). > > The wanted operation is that the user can say "show me the record with > ID such-and-such" (by doing something in some other part of the UI), and > the grid will display the correct page of data (and then scroll as > necessary and highlight the wanted record, which obviously we'll have to > do in the UI). In, that is, a sane amount of time - fetching hundreds of > pages of data to the grid sequentially until the right record appears is > not a reasonable solution! ID may or may not be the primary key, but we > can use an indexed unique field if that helps. I think this requires several steps. 1) Fetch the ORDER BY fields of the record of interest 2) SELECT COUNT(*) where fields < ORDER BY fields of the record of interest (or > if ORDER BY ... DESC) (this gets you the number of records you can safely skip) 3) Fetch one or more pages of data until the right record appears This somewhat tedious process is required since your ORDER BY may not start with your ID field. 3 is still required since you have limited control of the ORDER BY chosen by the user and cannot guarantee how many duplicates there may be (ORDER BY SURNAME may not find Tim Ward on the first page of the Ward's). Sorry for not knowing of any quicker and simpler solution. Unfortunately, the desire of a flexible and powerful interface often means that the developer has to do a fair bit of coding. HTH, Set -- Tim Ward
Re: [firebird-support] Find grid page containing record
There is I'm afraid something of a difference between "the UX we might want" and "the UI we've got to which we're trying to add particular features". On 07/10/2015 10:40, 'Louis van Alphen' lo...@nucleo.co.za [firebird-support] wrote: I have taken the approach that it is undesirable to present users with pages and pages of data and having the user have to page until he finds what he wants. It’s not a great UX. My view is to rather give the user powerful search facility to very quickly get to the data he wants. So I have adopted a google-like search function where, as the users types in a search box, the resultset is filtered according to the search specification. The search specification includes the following attributes: - Search text (the text the user enters) - MatchType (anywhere, exact, startswith, endswith) Exact will generate a where clause in the form Column=@parameter, and anywhere will generate a where clause like Column=%param% etc - Case Sensitive - MaxRows to return. This defines the max nuber of matching rows to return - EmptySearchAction (Empty or MaxRows) this defines what is returned when the search field is empty. Empty means an empty resultset is returned like google. MaxRows means the first MaxRows are returned - List of columns to search on This search is done server-side by dynamically generated SQL and the result set is returned for display. Some searches are quite resources intensive of the user chooses columns and sort orders on columns without proper indexes. Most front-end grids incl HTML grids do support ordering client side, but if you want to order server side i.e. last 50 rows, then you will pay the price But it depends on the type of UX you want for your users and the use case at hand. The traditional paging may be appropriate but maybe some new way as well. From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] Sent: 07 October 2015 10:37 AM To: firebird-support@yahoogroups.com Subject: Re: [firebird-support] Find grid page containing record Thanks, hadn't thought of the COUNT(*). This still means visiting every record of course, but at least on a good day most of them are being done entirely within the database engine. On a bad day however this might not gain anything if the user chooses to sort by something useless and unselective (you might say they then deserve the delay they get, but we're trying not to build too many more of these into the system). Plus autogenerating the code for the comparison could be "interesting" particularly in cases where some of the fields in the ORDER BY (under the control of the user, remember) are ASC and some DESC? I think we'll have to continue to think about it! On 06/10/2015 21:50, setysvar setys...@gmail.com <mailto:setys...@gmail.com> [firebird-support] wrote: Den 06.10.2015 17:38, skrev Tim Ward t...@telensa.com <mailto:t...@telensa.com> [firebird-support]: > Given that a query needed to return data for a page of a grid is of the form > > SELECT FIRST 25 SKIP > .ID (and some other fields of human-readable data) > FROM < plus tables as needed for other fields in the > SELECT, WHERE and ORDER BY clauses> > WHERE <something, may be complex expressions involving several fields> > ORDER BY > > how can I find out what is for a given table>.ID? > > (Without using any features that are only in Firebird 3, which are > needed for the solutions I've found so far. Surely this is not a rare > thing to want to be able to do?) > > Scenario: Data is displayed in a grid in the user interface, with 25 > records per page. The user gets to specify the filters (WHERE) and > sorting (ORDER BY) in the user interface, ie these change outside my > control (and in consequence appropriate tables get pulled into the FROM > clause as necessary by the query generation code). > > The wanted operation is that the user can say "show me the record with > ID such-and-such" (by doing something in some other part of the UI), and > the grid will display the correct page of data (and then scroll as > necessary and highlight the wanted record, which obviously we'll have to > do in the UI). In, that is, a sane amount of time - fetching hundreds of > pages of data to the grid sequentially until the right record appears is > not a reasonable solution! ID may or may not be the primary key, but we > can use an indexed unique field if that helps. I think this requires several steps. 1) Fetch the ORDER BY fields of the record of interest 2) SELECT COUNT(*) where fields < ORDER BY fields of the record of interest (or > if ORDER BY ... DESC) (this gets you the number of records you can safely skip) 3) Fetch one or more pages of data until the right record appears This somewhat tedious process is required since your ORDER BY may
[firebird-support] Find grid page containing record
Given that a query needed to return data for a page of a grid is of the form SELECT FIRST 25 SKIP .ID (and some other fields of human-readable data) FROM < plus tables as needed for other fields in the SELECT, WHERE and ORDER BY clauses> WHERE <something, may be complex expressions involving several fields> ORDER BY how can I find out what is for a given .ID? (Without using any features that are only in Firebird 3, which are needed for the solutions I've found so far. Surely this is not a rare thing to want to be able to do?) Scenario: Data is displayed in a grid in the user interface, with 25 records per page. The user gets to specify the filters (WHERE) and sorting (ORDER BY) in the user interface, ie these change outside my control (and in consequence appropriate tables get pulled into the FROM clause as necessary by the query generation code). The wanted operation is that the user can say "show me the record with ID such-and-such" (by doing something in some other part of the UI), and the grid will display the correct page of data (and then scroll as necessary and highlight the wanted record, which obviously we'll have to do in the UI). In, that is, a sane amount of time - fetching hundreds of pages of data to the grid sequentially until the right record appears is not a reasonable solution! ID may or may not be the primary key, but we can use an indexed unique field if that helps. -- Tim Ward
Re: [firebird-support] What is better: UPDATE or DELETE + INSERT?
On 16/09/2015 07:51, brucedickin...@wp.pl [firebird-support] wrote: I need to rebuild some records in my database. I have to choices: 1. Firstly I delete set of records and then insert new ones. 2. I update the existing ones. What solution is preferable? Will scenerio number 1. cause more garbage in database and decrease its performance? Depends. If you're updating the records in such a way as to make them longer (after run length encoding) enough that each record is then fragmented across more than one database page then resulting performance can become a complete catastrophe (millions of times slower if you're really lucky). If that's your situation you may be better off deleting and recreating. -- Tim Ward
[firebird-support] Another ordering question?
SELECT FIRST 1 FROM MYTAB ORDER BY MYKEY1, MYKEY2; PLAN (MYTAB ORDER PK_MYTAB) Current memory = 3437420 Delta memory = 152 Max memory = 3586952 Elapsed time= 0.04 sec Buffers = 150 Reads = 232 Writes 0 Fetches = 132082 There may be several thousand records in the table, but the primary key is defined as (MYKEY1, MYKEY2) and the plan says it's using it, so why the 232 disk reads? - one might have guessed 2 would be enough, one for the index and one for the record. (The key fields are both 32 bit integers. Yes, I do have plans to replace them by a generator-generated key, but I can't see why that would make a difference to this particular performance issue.) This table is being used as a FIFO queue. Would the most likely explanation be lack of garbage collection, so it's having to scan lots of deleted records before finding the first real one, or are there other possibilities? I must say that Oldest transaction390433636 Oldest active390433637 Oldest snapshot390433637 Next transaction391092651 doesn't look too encouraging, so I'd better investigate that. -- Tim Ward
Re: [firebird-support] Another ordering question?
Well, with all transaction numbers within a few hundred of each other
Oldest transaction391428664
Oldest active391428665
Oldest snapshot391428665
Next transaction391429145
it isn't noticeably better
Elapsed time= 0.01 sec
Buffers = 150
Reads = 212
Writes 0
Fetches = 546
so that doesn't look like the explanation ...
On 09/09/2015 13:13, Nick Upson n...@telensa.com [firebird-support] wrote:
without a "where" clause firebird has to read the entire table (to
determine if each row is valid for the current transaction) and sort
the results
Nick Upson, Telensa Ltd, Senior Operations Network Engineer
direct +44 (0) 1799 533252, support hotline +44 (0) 1799 399200
On 9 September 2015 at 13:00, Tim Ward t...@telensa.com
<mailto:t...@telensa.com> [firebird-support]
<firebird-support@yahoogroups.com
<mailto:firebird-support@yahoogroups.com>> wrote:
SELECT FIRST 1 FROM MYTAB ORDER BY MYKEY1, MYKEY2;
PLAN (MYTAB ORDER PK_MYTAB)
Current memory = 3437420
Delta memory = 152
Max memory = 3586952
Elapsed time= 0.04 sec
Buffers = 150
Reads = 232
Writes 0
Fetches = 132082
There may be several thousand records in the table, but the
primary key
is defined as (MYKEY1, MYKEY2) and the plan says it's using it, so
why
the 232 disk reads? - one might have guessed 2 would be enough,
one for
the index and one for the record. (The key fields are both 32 bit
integers. Yes, I do have plans to replace them by a
generator-generated
key, but I can't see why that would make a difference to this
particular
performance issue.)
This table is being used as a FIFO queue. Would the most likely
explanation be lack of garbage collection, so it's having to scan
lots
of deleted records before finding the first real one, or are there
other
possibilities? I must say that
Oldest transaction 390433636
Oldest active 390433637
Oldest snapshot 390433637
Next transaction 391092651
doesn't look too encouraging, so I'd better investigate that.
--
Tim Ward
--
Tim Ward
[firebird-support] MAX() and index
Please can someone explain to me, again, why select Max(MYCOL) from MYTAB doesn't use the primary key PLAN (MYTAB NATURAL) Current memory = 3074072 Delta memory = -20 Max memory = 3217516 Elapsed time= 0.27 sec Buffers = 150 Reads = 1811 Writes 0 Fetches = 894779 of which it is the first field CONSTRAINT PK_MYTABPRIMARY KEY (MYCOL, OTHERCOL) and instead reads thousands of pages from disk and takes over a quarter of a second? (I'm just curious and wanting to understand, really. I'm going to fix the actual problem with a change of approach, that's needed for other reasons anyway, which eliminates the problem query.) -- Tim Ward
Re: Odp: [firebird-support] Firebird 2.52 gbak fails to do a restore - error trigger (3)
On 11/06/2015 16:05, Jack Mason jackma...@mindspring.com [firebird-support] wrote: Thanks, However, that won't help us. Our concern is that we have been backing up our databases for years and it has been a fruitless exercise. We cannot restore them. *Any* backup procedure for *any* system using *any* tools is pointless if you don't do regular test restores. -- Tim Ward
Re: [firebird-support] Why index is not used in this query?
How about something along the lines of: A table that small is going to fit into a single disk page. So a table scan involves reading one disk page. Using the index would involve reading the index as well, which is a second disk page, so twice as slow. ? (Other RDBMS which have a covering index concept can, for appropriate queries, use the index without reading the table at all, and might come to a different conclusion in such cases.) On 15/05/2015 14:02, brucedickin...@wp.pl [firebird-support] wrote: Hello, here is full example: CREATE TABLE TABLE_1 ( ID INTEGER NOT NULL, NAME VARCHAR(32), CONSTRAINT PK_TABLE_1 PRIMARY KEY (ID) ); GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE ON TABLE_1 TO SYSDBA WITH GRANT OPTION; CREATE TABLE TABLE_2 ( ID INTEGER NOT NULL, TABLE_1_ID INTEGER ); ALTER TABLE TABLE_2 ADD CONSTRAINT FK_TABLE_2 FOREIGN KEY (TABLE_1_ID) REFERENCES TABLE_1 (ID); GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE ON TABLE_2 TO SYSDBA WITH GRANT OPTION; INSERT INTO TABLE_1 VALUES(1, 'V_1'); INSERT INTO TABLE_1 VALUES(2, 'V_2'); INSERT INTO TABLE_1 VALUES(3, 'V_3'); INSERT INTO TABLE_1 VALUES(4, 'V_4'); INSERT INTO TABLE_1 VALUES(5, 'V_5'); INSERT INTO TABLE_2 VALUES(1, 1); INSERT INTO TABLE_2 VALUES(2, 1); INSERT INTO TABLE_2 VALUES(3, 2); INSERT INTO TABLE_2 VALUES(4, 2); INSERT INTO TABLE_2 VALUES(5, 3); SELECT * FROM TABLE_2 T2 INNER JOIN TABLE_1 T1 ON T2.TABLE_1_ID = T1.ID After executing this query I am getting such plan: PLAN JOIN (T1 NATURAL, T2 INDEX (FK_TABLE_2)) Why in case of TABLE_1 optimizer did not chose index PK_TABLE_1? And why I am getting error when i try to enforce it with: PLAN JOIN (T1 INDEX (PK_TABLE_1), T2 INDEX (FK_TABLE_2)) Thanks for any clues. -- Tim Ward
Re: [firebird-support] How To Change Column DataType From Varchar To Integer ?
On 22/04/2015 09:26, Mark Rotteveel m...@lawinegevaar.nl [firebird-support] wrote: Create a new column, assign/convert the values from the old column, drop the old column, rename the new column (optional). ... and sort out, manually, however many hundreds of dependencies you have (triggers, foreign keys, procedures, ...). -- Tim Ward
Re: [firebird-support] Re: Plan question, what is a stream and what isn't?
On 15/04/2015 18:50, hv...@users.sourceforge.net [firebird-support] wrote: ---In firebird-support@yahoogroups.com, listas@... wrote : Re: [firebird-support] Re: Plan question, what is a stream and what isn't? Note, stored procedures and triggers are prepared\optimized once when loaded into metadata cache. Regards, Vlad Just by curiosity, when are SPs/Triggers loaded into Metadata Cache? Right before first execution? Exactly. What does right before first execution mean? What is the lifecycle of metadata cache? So what's the answer to my question, re a process which keeps a connection open permanently and repeatedly re-runs the same procedures in different transactions? Does it need to drop and recreate the connection in order to take account of any SET STATISTICS done by other people, or not? -- Tim Ward
[firebird-support] Re: When does SET STATISTICS take effect?
On 14/04/2015 11:39, Tim Ward wrote: If SET STATISTICS is done on one connection, when does it take effect? What about a transaction that was already running at the time someone else did SET STATISTICS, does that use the new statistics for any new statements it runs (where all the code running is in stored procedures)? What about a connection that was already in existence at the time someone else did SET STATISTICS, does that use the new statistics for any new transactions it runs (where all the code running is in stored procedures)? Nobody? The scenario was (1) Newly set up system, bits of data copied from elsewhere. (2) System started up. One process in particular holds its connection open forever (but the transactions it uses across that connection are of finite length). (3) Observed that one operation this process performed was taking about 80 seconds. (4) After a few days of operation (millions of things happening to various records in various tables) SET STATISTICS was run. (5) Observed that this one operation was still taking about 80 seconds, despite the fact that if its queries were run manually in another, new, connection they took milliseconds. (6) Stopped and restarted the process in question, so that it closed its connection, opened a new one, and carried on doing exactly the same thing. (7) Observed that the operation that used to take 80 seconds now took 4 seconds. What I don't know is /why/ restarting that process instantly made that particular procedure call 20 times faster, and I'm trying to get some clues as to whether starting a new connection after the SET STATISTICS had run could have had any effect. -- Tim Ward
[firebird-support] When does SET STATISTICS take effect?
If SET STATISTICS is done on one connection, when does it take effect? What about a transaction that was already running at the time someone else did SET STATISTICS, does that use the new statistics for any new statements it runs (where all the code running is in stored procedures)? What about a connection that was already in existence at the time someone else did SET STATISTICS, does that use the new statistics for any new transactions it runs (where all the code running is in stored procedures)? -- Tim Ward ++ Visit http://www.firebirdsql.org and click the Documentation item on the main (top) menu. Try FAQ and other links from the left-side menu there. Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ ++ 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: https://info.yahoo.com/legal/us/yahoo/utos/terms/
Re: [firebird-support] Profiler for 2.1?
Yes, we are planning a migration to 2.5, but we have to do more than one thing at a time, we can't afford to serialise all our projects! Not least because it'll be quite a while before we get all installations upgraded and we'll need to maintain the 2.1 installations in the meantime. So does the sort of profiler I describe exist for 2.5, or is it a question of rolling one's own using the TraceAPI ... in which case I might as well roll my own using MON$CALL_STACK? On 12/04/2015 10:17, Alexey Kovyazin a...@ib-aid.com [firebird-support] wrote: Hi Tim, 2.1 series was discontinued, so nobody will invest in tools for it. Instead of this, consider migration - 2.1 is pretty close to 2.5, where TraceAPI is available. Regards, Alexey Kovyazin IBSurgeon I've just discovered MON$CALL_STACK, and it seems possible that one could use this to produce a Monte Carlo type profiler - query it once every so many seconds, and built up a tree of how often each path through nested procedures has been hit, thus pointing at which paths through the code are taking the time. Do any of the available tools have this feature? - the output I'm after is a graphical drill-down calling tree like the ones you get from profilers for conventional languages. -- Tim Ward -- Tim Ward
Re: [firebird-support] Re: V1.56 query killing my V2.54 app
On 08/04/2015 19:44, andrew_s_...@yahoo.com [firebird-support] wrote: After more than a decade of Firebird I'm more than used to using the +0 to get the results desired from the engine I'm used to writing compilers, in which world it is inconceivable that a + 0 wouldn't get optimised out by constant folding, so it's not something I'd have thought of trying for myself! -- Tim Ward
Re: [firebird-support] Character sets. (Again.)
On 31/03/2015 20:00, Ann Harrison aharri...@ibphoenix.com [firebird-support] wrote: On Mar 30, 2015, at 7:01 AM, Tim Ward t...@telensa.com [firebird-support] firebird-support@yahoogroups.com wrote: Sorry about this, but I really can't work out what's going on here, and could do with some clues. Not so much a clue as a question. If you declare the columns as varchar, do you see the same behavior? Yes. With those two particular columns. But a third (VARCHAR) column in the same table produces the expected (correct length) result. -- Tim Ward
Re: [firebird-support] Character sets. (Again.)
On 31/03/2015 20:24, Mark Rotteveel m...@lawinegevaar.nl [firebird-support] wrote: The problem is mainly that when the connection character set is UTF8, and the field character set is anything other than NONE or OCTETS, the server will return the field as UTF8. The length of a field in UTF8 is 4 * the declared length (which is the maximum number of bytes required). It is up to the client (isql, or a driver like the ibase PHP driver, Jaybird, etc), to correctly interpret a field of length 32 bytes in utf8 to a string of 8 characters; unfortunately not all clients/drivers do this, so they will act as if it is string of 32 characters. Mark So my temporary fudge to trim() the result is actually the best that can be done, and it's a good thing I'm not expecting the presence or absence of trailing spaces in VARCHAR fields to be significant, it looks like. -- Tim Ward ++ Visit http://www.firebirdsql.org and click the Documentation item on the main (top) menu. Try FAQ and other links from the left-side menu there. Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ ++ 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: https://info.yahoo.com/legal/us/yahoo/utos/terms/
[firebird-support] Character sets. (Again.)
Sorry about this, but I really can't work out what's going on here, and could do with some clues. The basic problem is that I'm getting string values of columns into PHP padded on the right to four times their correct length. I've tried various combinations of things and got various weird results. Here's one example: (1) I believe the database default character set is UTF8 (although I don't know in detail what that means or what it's used for) SQL select rdb$character_set_name from rdb$database; RDB$CHARACTER_SET_NAME UTF8 (2) I've got a column in a table defined as ASCII (not that that makes an awful lot of difference, I get very similar results if it's UTF8): METERID CHAR( 8) CHARACTER SET ASCII COLLATE ASCII, (3) I connect ISQL without a -ch command line parameter and get the field arriving in ISQL as eight characters long: SQL select meterid from tblmeterchange; METERID 0001A6BN 0001A6BN (4) I connect ISQL with -ch UTF8 and get the field arriving in ISQL as 32 characters long: SQL select meterid from tblmeterchange; METERID 0001A6BN 0001A6BN (5) I connect via PHP using ibase_connect with UTF8 as the character set parameter and get this eight character field returned as the eight wanted characters padded on the right with 24 spaces. (6) If I change the definition of the METERID column to UTF8 I similarly get 32 characters in PHP. So what should I be doing to get an eight character value of an eight character field turning up as eight characters in PHP? (I don't think I have the option of setting the connection charset to anything other than UTF8 because I don't know what else it might affect and there are plenty of other columns in the database which, unlike this one, can't be declared as ASCII ... but then I don't know exactly what the concept of connection character set means or does anyway.) I have read http://www.firebirdsql.org/file/community/ppts/fbcon11/FbCon2011-Charsets-Heymann.pdf, but that doesn't help - it suggests that Firebird will transliterate as necessary, but padding an eight character string with 24 spaces to make a 32 character string isn't any sort of transliteration I'm familiar with. -- Tim Ward ++ Visit http://www.firebirdsql.org and click the Documentation item on the main (top) menu. Try FAQ and other links from the left-side menu there. Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ ++ 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: https://info.yahoo.com/legal/us/yahoo/utos/terms/
Re: [firebird-support] Cast as Numeric without parenthesis
On 26/03/2015 12:35, Svein Erling Tysvær svein.erling.tysv...@kreftregisteret.no [firebird-support] wrote: Here, I find that SELECT CAST(123456789.12345678 as decimal) FROM RDB$DATABASE succeeds, whereas SELECT CAST(123456789.123456789 as decimal) FROM RDB$DATABASE fails. I don't know why. Once Upon A Time there was a language called Algol68 whose designers appeared to be keen to get all that sort of thing absolutely right, according to a careful reading of the transput specs (I/O was considered an old-fashioned term). The conclusion one had to come to was that the only way to get the conversion of numbers between binary and decimal digit representation right was to do all the work (not terribly quickly, to be sure) as arbitrary length string manipulation, with only a very /very/ carefully designed conversion to binary form, from a carefully designed canonical string representation, as the last stage of a potentially long and complex sequence of operations. I've never seen anyone go to remotely as much trouble to get this stuff right since then. I can't, however, see such an approach having a problem with the example above. -- Tim Ward
Re: [firebird-support] Re: How do I get my database back?
On 24/03/2015 18:27, Dmitry Yemanov dim...@users.sourceforge.net [firebird-support] wrote: 24.03.2015 18:42, Tim Ward wrote: [#] Yes, well, there's then the question about how come you're allowed to get a database into such an illegal state in the first place, isn't there. I'd say this is what should be asked first. And it's no longer possible starting with FB3.0. The fix in 3.0 sounds good! - but it'll be a while for us, we're currently looking at upgrading to 2.5. Yes, that is the first question to ask, that's the first slice of cheese: as a pilot I'm used to the Swiss cheese model for analysing cock-ups - in this case there were several holes that lined up, ie several missed opportunities to avoid the end result, and this was indeed the first. (Our production upgrade scripts, by the way, do include deleting any pre-existing records in the table causing the problem. So assuming all the scripts are run successfully we won't have a problem with production databases, but on my dev system things had got patched here and there along the way.) -- Tim Ward
[firebird-support] How do I get my database back?
gbak: ERROR:validation error for column BOXNUMBER, value *** null *** gbak: ERROR: warning -- record could not be restored gbak:Exiting before completion due to errors So, at some point prior to the backup someone had added this NOT NULL column, and hadn't gone round setting the values in the column. So yes, I do understand what initiated the problem. *** BUT *** this is a you had one job issue, isn't it? The one and only job of a backup utility is to create a backup that can be restored. If it doesn't do that it's failed. At its one and only job. S many other approaches could have been taken, that wouldn't have lost my database for me, including but probably not limited to: (1) During backup, fail if the backup file being created is one that it won't be able to restore. (2) During restore ... er, just restore it anyway? - in its previous state the database was, strictly speaking, illegal[#], but it was, in real life, working fine. If gbak took this option, with a warning, then I'd be able to fix the data ... but as it is, I can't, because I can't restore the database. Yes I did discover no_validity, with which the restore did create *a* database, but it was a completely ing useless database as the NOT NULL constraints appeared to have been dropped everywhere, and who knows what other manglings had taken place (the documentation doesn't list them explicitly, it just says deletes validity constraints from restored metadata). Which means that gbak had a third option to get it right: (3) Provide a restore option that warns about validity checking errors, rather than failing them, but doesn't actually delete the checks from the database. So, please, how am I expected to get my database back? OK OK, so I don't actually need the data in the one table causing the problem (there aren't millions of foreign keys pointing at it from all over the shop), so -o -v worked, only losing the data in the one table that I didn't care about anyway, but that's only because I happen to be lucky with the data structures, it won't be a solution in general. [#] Yes, well, there's then the question about how come you're allowed to get a database into such an illegal state in the first place, isn't there. -- Tim Ward
Re: [firebird-support] Re: ON EXTERNAL DATA SOURCE
Re (1), I somehow managed to miss that sentence - thanks. Re (2), let me put the question another way. Given that it's going to close the connection every time, rather than re-use it, if I'm wanting to do this hundreds or thousands of times from within a single stored procedure call in database A, will the overhead of reconnecting to database B hundreds or thousands of times mean that using WITH AUTONOMOUS TRANSACTION is likely to be an impractical design in performance terms? How many hundreds or thousands of times per second can a connection be set up and torn down? Sure I could set up a pair of servers, write some code, and measure the performance. But I was hoping there might be someone who had used this feature who might be able to give general advice along the lines of either no chance, you don't want to do it like that, it'll run like a fast-asleep snail or we do that all the time, there's no performance issue in practice, setting up the connection is a fraction of the cost of running a statement that actually does anything. -- Tim Ward
Re: [firebird-support] Re: ON EXTERNAL DATA SOURCE
Yes, I did, of course, you will see that not only do I quote the documentation but also that my questions are on points that the documentation doesn't answer, which is why I asked them here. On 27/02/2015 18:57, hv...@users.sourceforge.net [firebird-support] wrote: Read the documentation first http://www.firebirdsql.org/file/documentation/reference_manuals/reference_material/html/langrefupd25-psql-execstat.html Regards, Vlad -- Tim Ward
[firebird-support] ON EXTERNAL DATA SOURCE
Trying to work out whether this feature might form part of some new design we're thinking of doing., and I can find very little discussion of this feature so I'm wondering whether anyone much is using it and what experience of it there is. My main concern is performance. Reading various sources of information (but being a bit confused by page 646 of the book): (1) ON EXTERNAL DATA SOURCE ... WITH COMMON TRANSACTION Scenario: (a) Running a stored procedure in database A (b) EXECUTE STATEMENT ... ON EXTERNAL DATA SOURCE ... WITH COMMON TRANSACTION ... on database B (c) repeat (b) several times (d) Complete the stored procedure which is running in database A and commit the transaction in database A. Suppose the actual statement executed in (b) is updating one field in one record in a properly indexed table, ie is about the simplest UPDATE statement possible. My reading of the material I've found is that (i) The first time (b) is run a new connection will be opened to database B and a new transaction will be started (ii) On subsequent executions of (b) then (provided the connection string etc matches) both the connection and the transaction will be re-used (iii) I can't find any documentation of when the connection on database B get closed, and when and under what circumstances the transaction on database B gets committed and when and under what circumstances it gets rolled back (2) ON EXTERNAL DATA SOURCE ... WITH AUTONOMOUS TRANSACTION Scenario as above, except AUTONOMOUS instead of COMMON at (b). My reading of the material I've found is that (i) Every time (b) is run a new connection will be opened to database B and a new transaction will be started (ii) Each time the statement run in (b) completes the transaction in database B will be committed, unless there was an error in which case it will be rolled back (ii) Each time the statement run in (b) completes the connection to database B will be closed Have I got that right so far? - in which case please (A) In case (1), what's the answer to point (iii)? (B) In case (2), isn't it rather expensive to reopen the connection to database B every time? Or is the cost of reconnection so little compared to the cost of starting a new transaction that it doesn't matter? (! - opening a connection must be several packets across the network and starting a new server process, so it can't be *that* cheap?) -- Tim Ward
Re: [firebird-support] Know nothing - please help
On 18/02/2015 11:05, Svein Erling Tysvær svein.erling.tysv...@kreftregisteret.no [firebird-support] wrote: Note that I removed your right join since your where clause logically and practically makes it an inner join. It might nonetheless have been deliberate - sometimes swapping between inner and outer joins with identical meaning makes a significant difference to performance. (A perfect query optimiser would be aware that the two forms produced the same results and create the same plan for either, which would remove this tuning opportunity from the developer, but there is of course no such thing as a theoretically perfect query optimiser.) -- Tim Ward
[firebird-support] Yet another which architecture question
Sorry, you must all be really bored with these, but I just can't get my head round it. We're looking to upgrade our (Linux) operating system, and with that is likely to come an upgrade from 32 bit Firebird 2.1 Superserver to 64 bit Firebird 2.5, but what architecture? We need more performance than we're getting at the moment, a factor of 10 would be a nice start. We've got two types of workload (a) Web accesses - short-lived connections that run large queries, which in our experience really do benefit from having thousands of pages in the Firebird cache. There are typically multiple queries in the same connection that use the same data. Concurrent queries from multiple users, or subsequent queries from the same users, are also very likely to use some thousands of the same pages that were used by immediately preceding queries. There are a small number of simultaneous users, say from two to a dozen. (b) Persistent links to other systems. I believe that these need considerably less in the cache, but share some of their cache requirements with the web accesses. These typically perform large numbers of small queries, as opposed to the small number of large queries that the web accesses involve. There are single figures of these connections. We observe that when we've got performance problems one core is saturated, for seconds or even minutes at a time, and that seems to be the bottleneck. (Yes we keep nibbling away, and sometimes taking large bites, at schema design and query optimisation etc, and we will continue to do so, but we don't think that can be the complete solution.) So for 64 bit 2.5 the choices seem to be - Superserver, which keeps us our large shared cache, but will (still) only use one core (of the many available on the hardware)? - one of the other two architectures, which will use more cores, but may simply occupy them re-reading the same thousands of pages time and time again from the database into the cache? Have I got that right so far? I've read stuff about keeping persistent connections from PHP to the database to maintain the state of the private cache in a non-Superserver setup, and how that causes all sorts of problems if one web access falls over, resulting in a failure to rollback the transaction automatically on script termination. So, not recommended. I've also read stuff about how not having a large shared cache doesn't actually matter, because you're not reading from the disk into cache anyway, you're only reading from the filing system's cache into the database server's cache. This latter point does make sense cf some testing and measuring we've done, but surely it still takes lots of CPU cycles to copy thousands of pages from one cache into the other over and over again? Yes I know we could just try the various architectures and measure the results, but it's not as simple as that. We want to do some restructuring of our system anyway, and options include putting bits of it onto different boxes, and splitting some tables into separate databases, and possibly putting different database servers onto different boxes, and stuff like that, which gives rise to an impractical number of combinations of systems to design, code and test - having to only design and code one system would go down best with management. So ... ... ... ??? Oh, and if we do use one of the private-non-persistent-cache architectures, can we set the cache size differently to suit different connections with radically different types of workload? Or have I completely misunderstood the whole business? -- Tim Ward
[firebird-support] Dynamic SQL Error SQL error code = -104 CTE CTE_CP is not used in query
Why?? Why does that have to be a failure?? Why can't Firebird just ignore it?? Is there any switch to turn off this behaviour?? (I've got some immensely complicated query generation code which, it now appears, can occasionally include a common table expression in the query that isn't actually used anywhere. I've now got to fix that code to be extremely careful about *ex*cluding unneeded CTEs - it's currently aimed at being extremely careful to *in*clude CTEs that *are* needed.) -- Tim Ward ++ Visit http://www.firebirdsql.org and click the Documentation item on the main (top) menu. Try FAQ and other links from the left-side menu there. Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ ++ 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: https://info.yahoo.com/legal/us/yahoo/utos/terms/
Re: [firebird-support] Dynamic SQL Error SQL error code = -104 CTE CTE_CP is not used in query
On 17/02/2015 09:44, Mark Rotteveel m...@lawinegevaar.nl [firebird-support] wrote: On Tue, 17 Feb 2015 09:41:56 +, Tim Ward t...@telensa.com [firebird-support] firebird-support@yahoogroups.com wrote: Why?? Why does that have to be a failure?? Why can't Firebird just ignore it?? Is there any switch to turn off this behaviour?? (I've got some immensely complicated query generation code which, it now appears, can occasionally include a common table expression in the query that isn't actually used anywhere. I've now got to fix that code to be extremely careful about *ex*cluding unneeded CTEs - it's currently aimed at being extremely careful to *in*clude CTEs that *are* needed.) It sounds like this should be a warning, and not an error. Mark __._, That would be my view, yes! - I've got a mind-bending day (or three) ahead of me trying to tweak the query generation code. -- Tim Ward
Re: [firebird-support] Problem with sub-optimal query plan
You could try an outer join ... mucky in the extreme but it sometimes forces the optimiser to look at the sane table first ... (yes I know one shouldn't have to do that sort of thing). On 03/02/2015 12:17, Fulvio Senore mail...@fsoft.it [firebird-support] wrote: I have a database containing(simplifyinga little) a CUSTOMERS table andan INVOICES table. INVOICES contains a foreign key to CUSTOMERS, as you can expect. I want to retrieve rows showing INVOICES data and some CUSTOMERS data so I use an inner join, and I want to see newer invoices first so I add an ORDER BY clause. I only need a few rowssince I will show only the latest invoices in a grid. The query is something like SELECT INVOICES.*, CUSTOMERS.NAME FROM INVOICES INNER JOIN CUSTOMERS ON INVOICES.CUSTOMER_ID = CUSTOMERS.CUSTOMER_ID ORDER BY INVOICES.YEAR DESC, INVOICES.NUMBER DESC I have created a descending index on INVOICES.YEAR, INVOICES.NUMBER The problem is that if the tables are large the query is rather slow. Looking at the plan I see: PLAN SORT (JOIN (CUSTOMERS NATURAL, INVOICES INDEX (FK_INVOICES_CUSTOMERS))) so the database loads all rows and then it sorts them.Of course it is slow. If I test a query like SELECT * FROM INVOICES ORDER BY INVOICES.YEAR DESC, INVOICES.NUMBER DESC the plan is PLAN (INVOICESORDER IDX_INVOICES_DESC) and the query is much faster since it uses the index and it only reads a few rows. So it looks like if there is an inner join the optimizer does not use the descending index on INVOICES to speed up things. Is there a way to force the optimizer to use the descending index instead of sorting all the resulting rows? I am using Firebird 2.1 on Windows. Thanks in advance. Fulvio Senore -- Tim Ward ++ Visit http://www.firebirdsql.org and click the Documentation item on the main (top) menu. Try FAQ and other links from the left-side menu there. Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ ++ 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: https://info.yahoo.com/legal/us/yahoo/utos/terms/
Re: [firebird-support] nesting - weight calculation
On 29/01/2015 12:29, 'checkmail' check_m...@satron.de [firebird-support] wrote: Is there a simpler statement possible than my stored procedure (recursive) Oooh, it's the transitive closure of sparse connectivity matrix problem again. (With, in this case, the arcs of the directed graph being weighted.) Same problem as given this table of people, find all the ancestors/descendants of such-and-such. Or, in other words, SQL isn't that good at tree structures, you have to get a bit clever. (The other main thing I've found, over the decades, that SQL is not very good at being time series.) -- Tim Ward
Re: [firebird-support] Re: Common Table Expressions ( “WITH ... AS ... SELECT”)
On 26/01/2015 09:53, masb...@za-management.com [firebird-support] wrote: Hi Dimitry, thank you for your response. I think in my case I can replace the outer join with an inner join, because it is the same sp with the same query parameters I am calling twice. Are there any future plans to change this re-evaluation structure in outter joins? With a lot of complex queries typically involving several joins, both inner and outer, several CTEs, and sometimes some procedures as well, I find that sometimes changing an inner join to an outer join improves performance by orders of magnitude, and sometimes changing an outer join to an inner join improves performance by orders of magnitude. I have not been able to deduce any rhyme or reason to what's going on - when I find something that works I'm afraid I just have to leave it and move on to the next problem rather than take time to investigate in any more detail. In some cases the logic of the query is such that inner and outer joins *can only* deliver the same result, so one might hope that an optimiser would spot this and pick the faster execution path (manual tweaks to the syntax that don't affect the meaning of the query should not affect the output of the optimiser), but this doesn't (always?) seem to be what's happening. -- Tim Ward
Re: [firebird-support] I don´t receive my messages
On 20/01/2015 19:49, Eduardo guse...@gmail.com [firebird-support] wrote: Hello: Yesterday I sent an e-mail to the list but I didn´t receive it. I received other mails from the list but didn´t receive my own message. The list works like that. Really clever - not! - as it means your auto-filed folder for the list only contains parts of the conversations that interest you most - the ones you took part in! It's difficult to imagine what was going through the mind of the person who thought up this feature. Other mailing list providers are available, and I've never seen any other behave like this one. -- Tim Ward
Re: [firebird-support] Re: Firebird Android
On 18/01/2015 17:43, Craig Cox craig_...@coxcolvin.com [firebird-support] wrote: Thanks to everyone for your thoughts. I understand that this is really not the forum for such a discussion and I appreciate everyone’s advice. I failed to note an important detail in my original discussion. That is, my mobile device (tablet) will likely not have a connection to the internet until it reaches its home base. Because of this, I need to sync prior to leaving home base, collect data, then resync at a later time. Yes, that's exactly what we're doing with our fetching and sending XML data to PHP scripts, it's not assuming an internet connection more than once a day. -- Tim Ward
Re: [firebird-support] Firebird Android
On 15/01/2015 19:19, Hugo Eyng hugoe...@msn.com [firebird-support] wrote: Hello. I will develop an App for Android and I want to conect FB running on windows. Did somebody tried doing this? Some hint, article or information about how can I implement that? Driver? Agree with the other responses. We're doing this by having the Android app access a URL on our server and send or receive an XML file which are processed into and out of the database by server side code. -- Tim Ward ++ Visit http://www.firebirdsql.org and click the Documentation item on the main (top) menu. Try FAQ and other links from the left-side menu there. Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ ++ 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: https://info.yahoo.com/legal/us/yahoo/utos/terms/
Re: [firebird-support] Three trailing spaces?
On 13/12/2014 09:14, Mark Rotteveel m...@lawinegevaar.nl
[firebird-support] wrote:
On 12-12-2014 15:38, Tim Ward t...@telensa.com [firebird-support] wrote:
On 12/12/2014 14:15, Mark Rotteveel m...@lawinegevaar.nl
[firebird-support] wrote:
What is the default character set of your database (if I had to
guess it
is UTF8), and what is your connection character set (if I had to guess
it's
NONE or not specified)?
Database charset is UTF8. Connection charset appears also to be UTF8.
If the connection character set is UTF8 (this needs to be specified
explicitly!) then it looks like the driver is misbehaving.
The call is
ibase_connect(TSL_DATABASE, TSL_FB_USERNAME, TSL_FB_PASSWORD,
TSL_FB_CHARSET, TSL_FB_BUFFERS, TSL_FB_DIALECT);
where
define('TSL_FB_CHARSET', 'UTF8'); //NB: no dash, not 'UTF-8'
The 'problem' is that a CHAR(1) in UTF8 is sent as 4 bytes. If the
character in question is a one-byte UTF8 character, then the remaining
three bytes are 0x20 (a space). A driver should truncate the string to
the declared number of characters, but doesn't do that here.
A potential workaround might be to use VARCHAR(1) instead.
Mark
--
Mark Rotteveel
--
Tim Ward
[firebird-support] Three trailing spaces?
I've got a column defined as MODACTION D_MODACTION , where the domain is defined as CREATE DOMAIN D_MODACTION AS CHAR(1) CHARACTER SET ASCII NOT NULL CHECK (VALUE IN ( 'A', 'U', 'D', 'M', 'S' )) COLLATE ASCII; I've got a record which appears to have 'A' in this field. But when I do a SELECT * FROM ... (from PHP using ibase_fetch_assoc) I don't get the string 'A' I get the string 'A ' (three spaces after the A). Yes this may be a PHP question, but just in case it isn't, any ideas? -- Tim Ward
Re: [firebird-support] Three trailing spaces?
On 12/12/2014 14:15, Mark Rotteveel m...@lawinegevaar.nl [firebird-support] wrote: What is the default character set of your database (if I had to guess it is UTF8), and what is your connection character set (if I had to guess it's NONE or not specified)? Database charset is UTF8. Connection charset appears also to be UTF8. -- Tim Ward
[firebird-support] Unit testing for stored procedures?
Anyone done any of this? Testing a stored procedure involves - set up test data - run the procedure - check and report on results - any clean up needed (remove or restore output data, reset generators, ect ect) and setting this all up is somewhat non-trivial, but then so is manually checking the results of a procedure which can do quite complicated things to a dozen or more tables. So it would be nice to have a framework into which we could just drop a few scripts describing the input data, the expected output data, and the procedure(/query/script/whatever) to run. Does such a thing exist? -- Tim Ward
Re: [firebird-support] Why this similar to is wrong?
On 25/11/2014 13:29, sirhamac...@gmail.com [firebird-support] wrote:
select
iif('em...@testdomain.com' similar to
'([_a-zA-Z\d\-\.]+@[_a-zA-Z\d\-]+(\.[_a-zA-Z\d\-]+)+)','ok','fail')
from rdb$database
Says 'invalid string' and 'invalid pattern', but the pattern it´s the
same in other languages for validate email address.
Well, just a couple of general observations:
- there is no such thing as a regular expression that can validate an
email address, at best you can get an approximation that works quite
often but still gets it wrong sometimes
- there are dozens of languages all with slightly different rules for
regular expressions, it's not a surprise if any non-trivial regular
expression needs changing between languages, you just have to read the
rules for the new language.
--
Tim Ward
Re: [firebird-support] How do I count the number of duplicate rows in a table?
SELECT CASE_NUMBER, COUNT(*) FROM ACCT_CASE_COURT GROUP BY CASE_NUMBER That what you want? (The sorting comes for free.) On 28/10/2014 12:13, 'Softtech Support' stwiz...@att.net [firebird-support] wrote: Greetings All, Firebird 1.5.3 Should be elementary but, I'm drawing a blank on how to accomplish this. I have a table (ACCT_CASE_COURT) that contains these fields (among others): ACCT_CASE_COURT_ID INTEGERNOT NULLPK ACCT_IDINTEGERNOT NULL CASE_IDSMALLINTNOT NULL CASE_NUMBER VARCHAR(20)NOT NULL ... How do I form a SQL Select statement that would contain CASE_NUMBER in the first column (sorted) and number of times that the case number is found in the table in the second column (CNT)? Any help truely appreciated. Thanks, Mike http://www.avast.com/ This email is free from viruses and malware because avast! Antivirus http://www.avast.com/ protection is active. -- Tim Ward
Re: [firebird-support] Advice with the following sql command
Means exactly what it says. You don't have TRANS_DATE and TRANSTIME in either the GROUP BY or in an aggregate function. As the query can process many records with different values for these columns what do you expect the query to return? On 25/09/2014 14:38, 'Andrew Gable' andrew.ga...@tesco.net [firebird-support] wrote: Hi all, I need someone please to advice me of the following sql code Select TRANS_DATE,TRANSTIME, POSNUMBER, TRANSNUMBER, Count(ITEM_QTY) as ItemsSold, SUM(LINE_TOTAL) as LineTotal from accounthistory WHERE TRANS_DATE BETWEEN ' StartDate ' AND ' EndDate AND AccountID='1001' GROUP BY POSNUMBER, TRANSNUMBER Order By TRANS_DATE, TRANSTIME DESC; When I run that within my application I get this error message Run Time Error -2147467259 (80004005) [ODBC Firebird Driver][Firebird] SQL Error SQL error code =-104 Invalid expression in the select list (not contained in either an aggregate function or the GROUP BY clause) Any advice is welcomed as this has me stumped as well as my SQL expert mate Thanks Andy -- Tim Ward
[firebird-support] Coalesce can't be optimised?
I've got a query like: SELECT ... FROM TBL WHERE COALESCE( FIELD1, FIELD2 ) = 12345 where FIELD2 is the primary key and FIELD1 also has an index (in fact it's a foreign key back to FIELD2). This results in a table scan. However expanding the COALESCE by hand: SELECT ... FROM TBL WHERE ( FIELD1 = 12345 ) OR ( FIELD1 IS NULL AND FIELD2 = 12345) results in use of both indices and no table scan. (In the vast majority of cases FIELD1 is null so I just want a lookup of a single record by primary key; just occasionally FIELD1 is not null and I might want two or three records in the result set.) So, my questions: (1) Have I got this right? Is the second query a correct expansion of COALESCE that produces the same results as the first query? (2) If so, is it indeed the case that the optimiser doesn't understand how to expand COALESCE? -- Tim Ward
Re: [firebird-support] Export data to CSV file from stored procedure?
You want to be looking around your external CSV file being an external table - that's the only way to write to files from stored procedures AFAIK, so, no matter how convoluted the code ends up, that's probably the approach you'll have to take. (Unless there's new stuff in 2.5 I don't know about, I've only done this with 2.1.) On 20/08/2014 17:19, conver...@gmail.com [firebird-support] wrote: Hi there, As subject, is it possible to export a query result set to an external csv file from a Firebird stored procedure? I know this can be done with IBExpert but we are looking for a pure firebird sql solution. We are using Firebrd 2.5. Any pointers on how to do this will be much appreciated. Regards, -Ed -- Tim Ward
Re: [firebird-support] Very long transaction start after deleting a big number of records.
On 25/07/2014 06:46, brucedickin...@wp.pl [firebird-support] wrote: Hi, 1. I've deleted 50 records from TABLE A. I've commited transaction. 2. I made SELECT * FROM TABLE A. Transaction was starting for a very long time. I just wanted to make sure that there is no way to prevent this behaviour? I mean, I can not delay somehow clearing of this garbage? As far as I understand this article: http://www.firebirdsql.org/manual/gfix-housekeeping.html there is no such possibility becasue: Whenever a subsequent transaction reaches garbage from a /committed/ transaction, that garbage is automatically cleared out. So it appears. Essentially part of the cost of doing the deletion is palmed off onto some later user, who is faced with unpredictable delays in consequence. For example an overnight housekeeping job can result in the first end user transaction of the day taking orders of magnitude longer than usual (and timing out and crashing, if its timeouts are set to something reasonable). You can avoid this cost being passed onto the poor user who through no fault of his own is the first to follow the housekeeping by including the SELECT * FROM TABLEA as part of the cleanup job, thus putting the cost back with its owner. In other words you can't delay the garbage collection, but you can make it happen earlier so that it's predictable. -- Tim Ward
Re: [firebird-support] Firebird 2.5 connection issue
On 18/07/2014 13:31, Mark Rotteveel m...@lawinegevaar.nl [firebird-support] wrote: That would give an entirely different error. The database file either doesn't exist, or the user account running the Firebird service does not have access to this folder. Yes, I've noticed that on Linux - you get the file not found message when actually what it should say is access denied. -- Tim Ward
Re: [firebird-support] Invalid argument in EXECUTE STATEMENT - cannot convert to string
In fact it was elementid. My problem was how long it took me to guess what the error message meant (I didn't find it in the FAQ of what error messages mean?). Wording like You can't EXECUTE STATEMENT a NULL, it has to be a string would have been immediately clear. On 26/06/2014 12:58, Thomas Beckmann thomas.beckm...@assfinet.de [firebird-support] wrote: userClause is probably null. Then the entire string will be null. Am 25.06.2014 16:08, schrieb Tim Ward t...@telensa.com [firebird-support]: Anyone know what this error message actually means? Yes I am trying to run an EXECUTE STATEMENT, but it's not clear to me what an argument means in this context, or what it is that something is trying to convert to a string, or why it's trying to do so, or why it can't be done. execute statement 'select first 1 MODULEID, ELEMENTID from TBLMODULE' || :tableType || ' ' || 'where ELEMENTID = ' ||:ELEMENTID || :userClause ||' order by 1 ASC' into :nModuleId, :nElementId; tableType is a varchar elementid is a bigint userclause is a varchar nmoduleid is a bigint nelementid is a bigint The only thing I can think of, after struggling for some time to understand the message, is that the thing between statement and into is the argument, and the only reason I can think of that it might not be a string - despite obviously looking like one!! - is that elementId is null (neither of the other variables can ever be null). Does that make sense? -- Tim Ward -- Tim Ward ++ Visit http://www.firebirdsql.org and click the Documentation item on the main (top) menu. Try FAQ and other links from the left-side menu there. Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ ++ 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: https://info.yahoo.com/legal/us/yahoo/utos/terms/
[firebird-support] Invalid argument in EXECUTE STATEMENT - cannot convert to string
Anyone know what this error message actually means? Yes I am trying to run an EXECUTE STATEMENT, but it's not clear to me what an argument means in this context, or what it is that something is trying to convert to a string, or why it's trying to do so, or why it can't be done. execute statement 'select first 1 MODULEID, ELEMENTID from TBLMODULE' || :tableType || ' ' || 'where ELEMENTID = ' ||:ELEMENTID || :userClause || ' order by 1 ASC' into :nModuleId, :nElementId; tableType is a varchar elementid is a bigint userclause is a varchar nmoduleid is a bigint nelementid is a bigint The only thing I can think of, after struggling for some time to understand the message, is that the thing between statement and into is the argument, and the only reason I can think of that it might not be a string - despite obviously looking like one!! - is that elementId is null (neither of the other variables can ever be null). Does that make sense? -- Tim Ward
Re: [firebird-support] Re: INSERT INTO ... RETURNING with EXECUTE STATEMENT
On 23/06/2014 13:12, Dmitry Yemanov dim...@users.sourceforge.net [firebird-support] wrote: 23.06.2014 14:48, Tim Ward t...@telensa.com [firebird-support] wrote: I've got an INSERT statement like: INSERT INTO table( list of columns ) SELECT list of columns FROM table whose name is not known until run time WHERE something RETURNING generated_primary_key INTO :variable the point being that the name of the table used in the FROM isn't known until run time. This needs to be inside a stored procedure for Firebird 2.1. Elsewhere (simple SELECT, FOR SELECT) I've worked out how to do this using EXECUTE STATEMENT. But what, please, is the correct syntax for using EXECUTE STATEMENT for an INSERT INTO ... SELECT ... RETURNING? - this isn't an example usage which I've been able to find in any of the documentation. I believe it should be: EXECUTE STATEMENT 'INSERT INTO ... SELECT ... RETURNING generated_primary_key' INTO :variable; Dmitry The procedure does compile, but when I run it I get request synchronization error at that statement. -- Tim Ward
[firebird-support] INSERT INTO ... RETURNING with EXECUTE STATEMENT
Hi, I see that this has been discussed before, but the only discussions I could find were in Russian and I was not able to understand them. I've got an INSERT statement like: INSERT INTO table( list of columns ) SELECT list of columns FROM table whose name is not known until run time WHERE something RETURNING generated_primary_key INTO :variable the point being that the name of the table used in the FROM isn't known until run time. This needs to be inside a stored procedure for Firebird 2.1. Elsewhere (simple SELECT, FOR SELECT) I've worked out how to do this using EXECUTE STATEMENT. But what, please, is the correct syntax for using EXECUTE STATEMENT for an INSERT INTO ... SELECT ... RETURNING? - this isn't an example usage which I've been able to find in any of the documentation. Thanks. -- Tim Ward
Re: [firebird-support] Re: INSERT INTO ... RETURNING with EXECUTE STATEMENT
Thanks - the procedure does actually compile if I do that, which is a good indication! (I can't actually try running it just yet.) On 23/06/2014 13:12, Dmitry Yemanov dim...@users.sourceforge.net [firebird-support] wrote: 23.06.2014 14:48, Tim Ward t...@telensa.com [firebird-support] wrote: I've got an INSERT statement like: INSERT INTO table( list of columns ) SELECT list of columns FROM table whose name is not known until run time WHERE something RETURNING generated_primary_key INTO :variable the point being that the name of the table used in the FROM isn't known until run time. This needs to be inside a stored procedure for Firebird 2.1. Elsewhere (simple SELECT, FOR SELECT) I've worked out how to do this using EXECUTE STATEMENT. But what, please, is the correct syntax for using EXECUTE STATEMENT for an INSERT INTO ... SELECT ... RETURNING? - this isn't an example usage which I've been able to find in any of the documentation. I believe it should be: EXECUTE STATEMENT 'INSERT INTO ... SELECT ... RETURNING generated_primary_key' INTO :variable; Dmitry -- Tim Ward
Re: [firebird-support] filter conditions: WHERE vs. JOIN clause
On 27/05/2014 16:17, jakef...@yahoo.com [firebird-support] wrote: In general, does it make a difference whether the filter conditions are in the JOIN or WHERE clause? In particular, if there are outer joins you can get different results. -- Tim Ward
Re: [firebird-support] non us characters in comments fail
Yes, I had one of these yesterday - part of a comment had been cut-and-pasted from Word, and contained something that looked like an ASCII minus but was in fact an em-rule. Whether you get a failure or not, and at which point of the process, depends, apparently, on all sorts of things, including in particular the connection character set. In our case the procedure had been stored in the database OK, and the application ran OK, but developers couldn't extract metadata from the database using Database Workbench or (to a more limited extent) FlameRobin. I decided that the best way out of it for me was to replace the em-rule with a minus. -- Tim Ward
Re: [firebird-support] non us characters in comments fail
On 25/04/2014 09:14, Pekka Paunio wrote: There seems to be all sorts of annoyances that have to do with character sets. For me it is not necessary to use non us characters but these things take time unnecessarily. There is a Cambridge Computer Science exam question: Explain why even experienced programmers have difficulties with character sets. Originally you were expected to write an essay about escape sequences on flexowriter tapes (five bit wide paper tapes); in my day we were expected to write about conversions between ASCII and EBCDIC. I don't know whether the question is still asked with unchanged wording but can see no reason why it shouldn't be! - today's essay might be about something like how to represent Chinese in the from line of an email. -- Tim Ward
Re: [firebird-support] non us characters in comments fail
I'm using v4.4.5.0. I can't check what others are using as they aren't in the office just right now, but I would expect them to be on the same version. On 25/04/2014 09:23, Martijn Tonies (Upscene Productions) wrote: Hello Tim, Yes, I had one of these yesterday - part of a comment had been cut-and-pasted from Word, and contained something that looked like an ASCII minus but was in fact an em-rule. Whether you get a failure or not, and at which point of the process, depends, apparently, on all sorts of things, including in particular the connection character set. In our case the procedure had been stored in the database OK, and the application ran OK, but developers couldn't extract metadata from the database using Database Workbench or (to a more limited extent) FlameRobin. Are you still experiencing this issue with recent versions of Database Workbench? With regards, Martijn Tonies Upscene Productions http://www.upscene.com -- Tim Ward
Re: [firebird-support] non us characters in comments fail
This seems to be repeatable: (1) Store the procedure in the database from Database Workbench with connection character set none. (2) You can now read it no problem using the same instance of Database Workbench, but someone else who has Database Workbench's character set UTF8 then gets the transliteration error on trying to read the procedures. It seems to me that (1) is most likely to be the problem - it appears that Database Workbench allowed me to store illegitimate garbage into the database: something should have told me not to or at least warned me that I was being silly? Might not sound like a big deal, but with hundreds of procedures, and the transliteration error message giving not the remotest clue as to what the problem was, it took several of us many hours over several days to find it. On 25/04/2014 09:39, Martijn Tonies (Upscene Productions) wrote: Hello Tim, Hmm, if Firebird allows you to store such characters, I would expect Database Workbench to support it. ;) If you can find a reproducible case, let me know. With regards, Martijn Tonies Upscene Productions http://www.upscene.com Download Database Workbench for Oracle, MS SQL Server, Sybase SQL Anywhere, MySQL, InterBase, NexusDB and Firebird! From: Tim Ward Sent: Friday, April 25, 2014 10:27 AM To: firebird-support@yahoogroups.com Subject: Re: [firebird-support] non us characters in comments fail I'm using v4.4.5.0. I can't check what others are using as they aren't in the office just right now, but I would expect them to be on the same version. On 25/04/2014 09:23, Martijn Tonies (Upscene Productions) wrote: Hello Tim, Yes, I had one of these yesterday - part of a comment had been cut-and-pasted from Word, and contained something that looked like an ASCII minus but was in fact an em-rule. Whether you get a failure or not, and at which point of the process, depends, apparently, on all sorts of things, including in particular the connection character set. In our case the procedure had been stored in the database OK, and the application ran OK, but developers couldn't extract metadata from the database using Database Workbench or (to a more limited extent) FlameRobin. Are you still experiencing this issue with recent versions of Database Workbench? -- Tim Ward
Re: [firebird-support] Efficient subselects
On 18/04/2014 10:29, Josef Kokeš wrote: On 18.4.2014 11:10, Thomas Beckmann wrote: Hi Josef, what I figured out to be quite handy, is to write something like: select MASTER.*, cast(left(S, 10) as bigint), cast(substring(S from 10 for 10) as bigint), cast(right(S, 10) as bigint), from (SELECT MASTER.ID, (SELECT lpad(SUM(VALUE1), 10) || lpad(MAX(VALUE2), 10) || lpad(MIN(VALUE1), 10) FROM DETAIL WHERE DETAIL_ID=MASTER.ID) as S FROM MASTER WHERE NAME STARTING WITH 'A') s join MASTER m on m.ID = s.ID Thomas Interesting approach. I will consider it, maybe it would be sufficient for my needs. Yes, I'll note this idea, I've got a similar query I'll probably have to speed up one day! -- Tim Ward ++ 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: https://info.yahoo.com/legal/us/yahoo/utos/terms/
Re: [firebird-support] Reading unnecessary records
On 04/04/2014 23:36, Alexandre Benson Smith wrote: If you don't need a particular table information, having it on the view is useless... So if you have the need of a distinct rescult set only one view would not handle this. So the on the fly generated query is not better than the view because it's better optimzed than a view would be, but because it's a diferent query. All of which is true, but it is also true that the optimiser doesn't always work out that some of the stuff returned by the view is never used by a particular query that calls the view, and that therefore it doesn't need to be calculated. (To put it very very crudely.) -- Tim Ward
Re: [firebird-support] order by
On 07/04/2014 12:40, Andrea Raimondi wrote: And anyway, you should never be using an order by number - unless there is really no way around it, in my opinion. When people make statements like that it would be really helpful if they were to say *why* they are of this opinion, otherwise the reader doesn't gain anything. -- Tim Ward
Re: [firebird-support] order by
Thanks. Knowing this reasoning, one can then use one's judgement in comparing this reason against a desire to avoid repeating a long complex expression which could also lead to a maintenance risk. On 07/04/2014 15:02, Andrea Raimondi wrote: I am of this opinion because SQL scripts change and what is today column number 2 may become column number 14 two days later. This, in turn, leads to potential problems because you think it's ordering by something whereas instead it's ordering by something else. A On Mon, Apr 7, 2014 at 2:58 PM, Tim Ward t...@telensa.com mailto:t...@telensa.com wrote: On 07/04/2014 12:40, Andrea Raimondi wrote: And anyway, you should never be using an order by number - unless there is really no way around it, in my opinion. When people make statements like that it would be really helpful if they were to say *why* they are of this opinion, otherwise the reader doesn't gain anything. -- Tim Ward -- Tim Ward
Re: [firebird-support] Reading unnecessary records
We generate the queries on the fly rather than trying to use a view, precisely because of these problems. So if CityName is not required in the output, there's no JOIN to City. And if there's something in the WHERE clause such as HobbyCode 27 then we know that HobbyID can't be null, which means we can use a JOIN instead of a LEFT JOIN, and quite often that results in a better plan. But yes, it does involve hundreds of lines of quite complex code to analyse what information is required in a particular case and generate the appropriate query, and in many cases the query optimiser could, theoretically, have worked this out for itself, but it doesn't. On 04/04/2014 12:36, kokok_ko...@yahoo.es wrote: I use the latest version of FB 2.5. There is a view for called PERSON. Each row represents a person and it shows information as address, name, hobbies, etc. There are 20 joined codification tables using LEFT JOIN. For example all cities are codified, hobbies, etc. The structure of the view is something like CREATE VIEW PersonView .. SELECT * FROM PersonTable P LEFT JOIN City ON City.ID = P.CityID LEFT JOIN Hobby ON Hobby.ID = P.HobbyID ... and so on for the 20 codified tables. City.ID is a primary key, like all IDs for remaining codifications. How can I optimize this view? My problem is that Firebird uses a really slow plan because it reads ALL codification records. For example, supposing PersonTable has 10 records. SELECT COUNT(*) FROM PersonView I would expect that Firebird only read 10 record, however it reads 10 + 10x20 = 210 records. In theory the optimizer could know that City.ID and Hobby.ID are both primary keys (or unique) and therefore only scan PersonTable. Another example: SELECT CityName FROM PersonView I woud expect that Firebird read 10 records for PersonTable and 10 for City table, but it reads 210. The real problem is that I have millions of records in the database, and a simple consult can take minutes when it could take few seconds with an optimized plan. What options do I have? Thank you -- Tim Ward
[firebird-support] EXECUTE STATEMENT with long string
Would I be right in thinking that if I've got an EXECUTE STATEMENT in a stored procedure, and the statement to be executed (which is put together from various parts using ||) now needs to be (potentially) longer than the maximum length of a VARCHAR, then I've got to find some other way of doing it? (Eg rewriting the stored procedure in PHP.) -- Tim Ward
