[firebird-support] Connected users from MON$ATTACHMENT
Hello! May I use the system table MON$ATTACHMENT in order to get the connected users in moment? I already try to do it, but when a connection from a client (another machine) is ended abnormally (for example, turn off the machine or disconnect the cable of network), this connection still stay on system table MON$ATTACHMENT. There are a way to refresh this system table? Thank you! *Jeferson Sisto* jsis...@gmail.com jsis...@yahoo.com.br
[firebird-support] Composite Primary Key in JOIN - performance
Goodmorning, I have to continue to work on a existing ERP program with an existing FB database. I don't like the design very much, looks messy and the original designer didn't think twice a lot from what I see. Example, when I take a look at the small lookup tables used to store payment conditions, currency codes, titles, etc, the primary key is always VARCHAR(4) to either store a generator number of a user defined CODE. But most of the time the value is just coming from a generator and with '0' as prefixes, like '0001', '0002'. It doesn't make much sense to me as the code is not shown anywhere. I guess the design would be better if a SMALLINT was used, am I right ? 2 Bytes instead of 8, correct ? The PROJECT table has a primary key VARCHAR(7) and more worse the SUBPROJECT table has composite primary key made up of the PROJECTNUMBER VARCHAR(7) AND A UNIQUE SUBPROJECTNUMBER for each project, VARCHAR(4). So JOINs look like this : JOIN PROJECT PR ON PR.PR_NR = AColumn JOIN SUBPROJECT SU ON SU.SU_NR = PR.PR_NR AND SU.SU_SUB = AColumn The quotation table has only 30.000 records and is not performing too good. That SUBPROJECT JOIN has to JOIN on 2 fields because of the composite primary key. VARCHAR(7) + (4) = 11, sounds like 22 bytes to me, instead of 4 bytes of an Integer. I think a better design would be to just define a INTEGER field as primary key for both tables with a meaningless value from a generator. That way the join would look like this : JOIN PROJECT PR ON PR.PR_ID = AColumnID JOIN SUBPROJECT SU ON SU.SU_ID = AColumnID Am I right in my thinking that this is slowing down the whole query ? Thanks in advance ! Best regards, Steve
Re: [firebird-support] Trying to decide legacy tables.
On Mon, 10 Aug 2015 19:23:56 +, Maureen Bertocci mberto...@bblfleet.com [firebird-support] firebird-support@yahoogroups.com wrote: Hello, and thank you I am excite about my new endeavor! I would like to join the support group. I am new to firebird. There are two things I am trying to figure out. 1. The first one is How can I print a relationship diagram of all tables in the database. There is nothing in Firebird itself to do this; Firebird is a database server. You need a tool like Database Workbench or IB Expert to do this. 2. How can I tell the last time a table was accessed? You can't, no such information is tracked. For inserts, updated, and deletes you can track this using triggers, but you need to set it up yourself. For selects it's not possible. Mark
Re: [firebird-support] error firebird v2.5.4 using trigger and hash function and update or delete record same content
On 10 Aug 2015 14:23:44 -0700, email...@yahoo.com.br [firebird-support] firebird-support@yahoogroups.com wrote: - I add new records mannually, add one new ID field value and the other fields have your values by TRIGGER BEFORE INSERT - Then, I try add 2 or more record with ID = 1 - Later, I try delete any one record and I have this message: *-*-*-*-*-*-*-*-*-*-* Firebird error message when trying delete or update any record with same values in your fields *-*-*-*-*-*-*-*-*-*-* .Cannot delete record! .There is at least one record with same fields value! These don't look like Firebird errors, is this coming from your application? If so, show exactly what you're doing. .Error Message: . .Invalid token. .Dynamic SQL Error. .SQL error code = -104. .Unexpected end of command - line 2, column 66416683. *-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-* This indicates you have a syntax error somewhere Any idea for this error? The Firebird dont use Internal RDB$DB_KEY (or just DB_KEY) to control records (internal) in table when dont exist PrimaryKey or Index? I am not sure what you mean with this, or how it would be relevant in the context of this question. Mark
RE: [firebird-support] error firebird v2.5.4 using trigger and hash function and update or delete record same content
I believe there is no PK in the table, so engine doesn't know what record to delete Regards, Bogdan From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] Sent: Tuesday, August 11, 2015 12:54 PM To: firebird-support@yahoogroups.com Subject: Re: [firebird-support] error firebird v2.5.4 using trigger and hash function and update or delete record same content On 10 Aug 2015 14:23:44 -0700, email...@yahoo.com.br [firebird-support] mailto:email...@yahoo.com.br%20[firebird-support] firebird-support@yahoogroups.com wrote: - I add new records mannually, add one new ID field value and the other fields have your values by TRIGGER BEFORE INSERT - Then, I try add 2 or more record with ID = 1 - Later, I try delete any one record and I have this message: *-*-*-*-*-*-*-*-*-*-* Firebird error message when trying delete or update any record with same values in your fields *-*-*-*-*-*-*-*-*-*-* .Cannot delete record! .There is at least one record with same fields value! These don't look like Firebird errors, is this coming from your application? If so, show exactly what you're doing. .Error Message: . .Invalid token. .Dynamic SQL Error. .SQL error code = -104. .Unexpected end of command - line 2, column 66416683. *-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-* This indicates you have a syntax error somewhere Any idea for this error? The Firebird dont use Internal RDB$DB_KEY (or just DB_KEY) to control records (internal) in table when dont exist PrimaryKey or Index? I am not sure what you mean with this, or how it would be relevant in the context of this question. Mark --- Ta e-pošta je bila pregledana z Avast protivirusnim programom. https://www.avast.com/antivirus
RE: [firebird-support] error firebird v2.5.4 using trigger and hash function and update or delete record same content
On Tue, 11 Aug 2015 13:09:27 +0200, 'Bogdan' bog...@mordicom.si [firebird-support] firebird-support@yahoogroups.com wrote: I believe there is no PK in the table, so engine doesn't know what record to delete A delete statement doesn't need an unique identification of the row (the exception is delete ... returning ... as it only returns a single row), a delete statement will simply delete everything that matches the where clause. The error Unexpected end of command indicates that something goes wrong before execution, during parsing the statement. That is why I asked you to show exactly what you are doing: what statement you execute, or if you don't execute a delete statement directly: how you are deleting the record. Mark
Re: [firebird-support] Composite Primary Key in JOIN - performance
On Aug 11, 2015, at 4:59 AM, steve.decle...@yahoo.com [firebird-support] firebird-support@yahoogroups.com wrote: ...when I take a look at the small lookup tables used to store payment conditions, currency codes, titles, etc, the primary key is always VARCHAR(4) to either store a generator number of a user defined CODE. But most of the time the value is just coming from a generator and with '0' as prefixes, like '0001', '0002'. It doesn't make much sense to me as the code is not shown anywhere. I guess the design would be better if a SMALLINT was used, am I right ? 2 Bytes instead of 8, correct ? In general, fixed size small textual fields should be CHAR rather than VARCHAR. Since the value stored is always the same length, the two bytes that describe the actual vs. declared length are wasted. And yes, an integer type would be better if the content will always be numeric. I have a personal problem with small int based on unpleasant experiences when they overflow, wrap around, and are generally a nuisance. But as long as you're certain you'll never have more than 32,767 of them... The PROJECT table has a primary key VARCHAR(7) and more worse the SUBPROJECT table has composite primary key made up of the PROJECTNUMBER VARCHAR(7) AND A UNIQUE SUBPROJECTNUMBER for each project, VARCHAR(4). So JOINs look like this : JOIN PROJECT PR ON PR.PR_NR = AColumn JOIN SUBPROJECT SU ON SU.SU_NR = PR.PR_NR AND SU.SU_SUB = AColumn The quotation table has only 30.000 records and is not performing too good. That SUBPROJECT JOIN has to JOIN on 2 fields because of the composite primary key. VARCHAR(7) + (4) = 11, sounds like 22 bytes to me, instead of 4 bytes of an Integer. Firebird's index key generator is sensitive to major datatypes and tries to minimize the key size by eliminating trailing blanks in strings - which won't exist in your case because the actual data is zero filled on the left, not blank filled on the right. Numeric keys are represented as a mangled double precision float. The mangling causes the number to compare correctly bytewise (moving and inverting the sign) and eliminates trailing zeros after the decimal point. Compound keys are handled so they also compare bytewise and should be reasonably quick. So, yes, I do think you'd be better off with integer keys (skip the small int) but no, I don't think that's the problem with your queries. Things that would help figure out why looking up stuff in a 30K record table is slow might include: query plan, indexes on non-primary keys, query stats specifically reads vs. fetches, and probably other things I'm not thinking of now. Generally, you're right that lying to Firebird by calling a number a variable length character string will lead to unhappiness... just not this particular unhappiness. Good luck, Ann
Re: [firebird-support] Reads inconsistency between FB TraceManager and IB Planalyser
On Aug 11, 2015, at 12:22 PM, 'Louis van Alphen' lo...@nucleo.co.za [firebird-support] firebird-support@yahoogroups.com wrote: Sure I understand that, but the reads/fetches are 3000 to return 1 row. Which involves a four way join, on top of having to find and use the system tables to identify the user tables, columns, and indexes, then find the appropriate index pages, pointer pages, data pages, etc. for it's internal queries and your query. The second time, most of the heavily used stuff is in cache. Good luck, Ann From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] Sent: 11 August 2015 06:15 PM To: firebird-support@yahoogroups.com Subject: Re: [firebird-support] Reads inconsistency between FB TraceManager and IB Planalyser Hi Lois, Reads value means actual reads from the disk. If you run query twice, necessary database pages are cached, and you will see in stats more fetches - i.e., calls to cache, not record fetches. Actually, always is better to run query twice, to get heated cache. Regards, Alexey Kovyazin IBSurgeon I am trying to find a performance issue in a system and I am using 2 tools just for comparison and verification. One being FB TraceManager (FBTM) and the other is an old util called Interbase Planalyzer (IP) It seems that the particular query from the view TRACKED_ITEM_LOCATION_DETAIL is slow. From FBTM,while monitoring the app, I get the following raw output: -- 2015-08-11T16:35:52.4990 (6180:00F9A948) EXECUTE_STATEMENT_FINISH D:\PROJECTS\KKI\TECH\DATABASE\PRODUCTION DATABASES\2015-07-14\DIGITAN.KKI.FDB (ATT_34, SYSDBA:NONE, NONE, TCPv4:127.0.0.1) (TRA_20898, READ_COMMITTED | NO_REC_VERSION | WAIT | READ_WRITE) Statement 3407: --- select * from TRACKED_ITEM_LOCATION_DETAIL where ITEM_ID = ? order by ID ^^^ PLAN JOIN (JOIN (JOIN (TRACKED_ITEM_LOCATION_DETAIL TILD ORDER TILD_PK_IDX INDEX (TILD_ITEM_IDX), TRACKED_ITEM_LOCATION_DETAIL TL INDEX (TL_PK_IDX)), TRACKED_ITEM_LOCATION_DETAIL FTL INDEX (TL_PK_IDX)), TRACKED_ITEM_LOCATION_DETAIL USR INDEX (USR_PK_IDX)) param0 = bigint, 2147191655 1 records fetched 112 ms, 3316 read(s), 3325 fetch(es) Table Natural IndexUpdateInsert Delete Backout Purge Expunge *** USER_ 1 TRACKING_LOCATION_1 TRACKED_ITEM_LOCATION_DETAIL_ 1 -- 3325 fetches and 112ms seem way over the top. The cache hit ratio is 0%. However, when I use IP and manually enter the same SQL, I get the following: Prepare time 1ms Execution time 148ms Fetch time 4ms With a total of 7 fetches. Not sure where FBTM gets 3K fetches? Also, if I remove the order by, IP reports a drastic reduction is execution time i.e. down to 6msec. Does the ORDER BY on the PK make such a difference? I am not understanding what is going on. Either way, here are the table view info stats Thanks Louis van Alphen -- CREATE TABLE TRACKED_ITEM_LOCATION_DETAIL_ ( UID DOM_UID /* DOM_UID = VARCHAR(36) */, IDDOM_ID /* DOM_ID = BIGINT NOT NULL */, IS_DELETEDDOM_BINARY /* DOM_BINARY = SMALLINT DEFAULT 0 NOT NULL CHECK (( value in ( 0,1) )) */, CREATED_DTM DOM_DTM default current_timestamp /* DOM_DTM = TIMESTAMP */, CREATED_USER_ID DOM_FK /* DOM_FK = BIGINT */, ROW_ORIGINDOM_FK /* DOM_FK = BIGINT */, ITEM_ID DOM_FK NOT NULL /* DOM_FK = BIGINT */, LOCATION_ID DOM_FK NOT NULL /* DOM_FK = BIGINT */, FROM_LOCATION_ID DOM_FK /* DOM_FK = BIGINT */, START_DTM DOM_DTM NOT NULL /* DOM_DTM = TIMESTAMP */, END_DTM DOM_DTM /* DOM_DTM = TIMESTAMP */, START_DAT DOM_DAT NOT NULL /* DOM_DAT = DATE */, END_DAT DOM_DAT /* DOM_DAT = DATE */ ); ALTER TABLE
RE: [firebird-support] Reads inconsistency between FB TraceManager and IB Planalyser
Sure I understand that, but the reads/fetches are 3000 to return 1 row. From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] Sent: 11 August 2015 06:15 PM To: firebird-support@yahoogroups.com Subject: Re: [firebird-support] Reads inconsistency between FB TraceManager and IB Planalyser Hi Lois, Reads value means actual reads from the disk. If you run query twice, necessary database pages are cached, and you will see in stats more fetches - i.e., calls to cache, not record fetches. Actually, always is better to run query twice, to get heated cache. Regards, Alexey Kovyazin IBSurgeon I am trying to find a performance issue in a system and I am using 2 tools just for comparison and verification. One being FB TraceManager (FBTM) and the other is an old util called Interbase Planalyzer (IP) It seems that the particular query from the view TRACKED_ITEM_LOCATION_DETAIL is slow. From FBTM,while monitoring the app, I get the following raw output: -- 2015-08-11T16:35:52.4990 (6180:00F9A948) EXECUTE_STATEMENT_FINISH D:\PROJECTS\KKI\TECH\DATABASE\PRODUCTION DATABASES\2015-07-14\DIGITAN.KKI.FDB (ATT_34, SYSDBA:NONE, NONE, TCPv4:127.0.0.1) (TRA_20898, READ_COMMITTED | NO_REC_VERSION | WAIT | READ_WRITE) Statement 3407: --- select * from TRACKED_ITEM_LOCATION_DETAIL where ITEM_ID = ? order by ID ^^^ PLAN JOIN (JOIN (JOIN (TRACKED_ITEM_LOCATION_DETAIL TILD ORDER TILD_PK_IDX INDEX (TILD_ITEM_IDX), TRACKED_ITEM_LOCATION_DETAIL TL INDEX (TL_PK_IDX)), TRACKED_ITEM_LOCATION_DETAIL FTL INDEX (TL_PK_IDX)), TRACKED_ITEM_LOCATION_DETAIL USR INDEX (USR_PK_IDX)) param0 = bigint, 2147191655 1 records fetched 112 ms, 3316 read(s), 3325 fetch(es) Table Natural IndexUpdateInsert Delete Backout Purge Expunge *** USER_ 1 TRACKING_LOCATION_1 TRACKED_ITEM_LOCATION_DETAIL_ 1 -- 3325 fetches and 112ms seem way over the top. The cache hit ratio is 0%. However, when I use IP and manually enter the same SQL, I get the following: Prepare time 1ms Execution time 148ms Fetch time 4ms With a total of 7 fetches. Not sure where FBTM gets 3K fetches? Also, if I remove the order by, IP reports a drastic reduction is execution time i.e. down to 6msec. Does the ORDER BY on the PK make such a difference? I am not understanding what is going on. Either way, here are the table view info stats Thanks Louis van Alphen -- CREATE TABLE TRACKED_ITEM_LOCATION_DETAIL_ ( UID DOM_UID /* DOM_UID = VARCHAR(36) */, IDDOM_ID /* DOM_ID = BIGINT NOT NULL */, IS_DELETEDDOM_BINARY /* DOM_BINARY = SMALLINT DEFAULT 0 NOT NULL CHECK (( value in ( 0,1) )) */, CREATED_DTM DOM_DTM default current_timestamp /* DOM_DTM = TIMESTAMP */, CREATED_USER_ID DOM_FK /* DOM_FK = BIGINT */, ROW_ORIGINDOM_FK /* DOM_FK = BIGINT */, ITEM_ID DOM_FK NOT NULL /* DOM_FK = BIGINT */, LOCATION_ID DOM_FK NOT NULL /* DOM_FK = BIGINT */, FROM_LOCATION_ID DOM_FK /* DOM_FK = BIGINT */, START_DTM DOM_DTM NOT NULL /* DOM_DTM = TIMESTAMP */, END_DTM DOM_DTM /* DOM_DTM = TIMESTAMP */, START_DAT DOM_DAT NOT NULL /* DOM_DAT = DATE */, END_DAT DOM_DAT /* DOM_DAT = DATE */ ); ALTER TABLE TRACKED_ITEM_LOCATION_DETAIL_ ADD CONSTRAINT TILD_PK PRIMARY KEY (ID) USING INDEX TILD_PK_IDX; CREATE INDEX TILD_END_DAT_IDX ON TRACKED_ITEM_LOCATION_DETAIL_ (END_DAT); CREATE INDEX TILD_ITEM_IDX ON TRACKED_ITEM_LOCATION_DETAIL_ (ITEM_ID); CREATE INDEX TILD_LOCATION_IDX ON TRACKED_ITEM_LOCATION_DETAIL_ (LOCATION_ID); CREATE INDEX TILD_START_DAT_IDX ON TRACKED_ITEM_LOCATION_DETAIL_ (START_DAT); CREATE OR ALTER VIEW TRACKED_ITEM_LOCATION_DETAIL( UID, ID, IS_DELETED, CREATED_DTM, CREATED_USER_ID, ROW_ORIGIN, ITEM_ID, LOCATION_ID, FROM_LOCATION_ID,
RE: [firebird-support] Reads inconsistency between FB TraceManager and IB Planalyser
Thanks Ann, so maybe the 2 tools' metric are different. So maybe FBTM includes all (low level) reads and IP only user table reads. My other question is why is there 'WARNING' against some of the indexes? Are the stats out of spec? From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] Sent: 11 August 2015 06:49 PM To: firebird-support@yahoogroups.com Subject: Re: [firebird-support] Reads inconsistency between FB TraceManager and IB Planalyser On Aug 11, 2015, at 12:22 PM, 'Louis van Alphen' lo...@nucleo.co.za [firebird-support] firebird-support@yahoogroups.com wrote: Sure I understand that, but the reads/fetches are 3000 to return 1 row. Which involves a four way join, on top of having to find and use the system tables to identify the user tables, columns, and indexes, then find the appropriate index pages, pointer pages, data pages, etc. for it's internal queries and your query. The second time, most of the heavily used stuff is in cache. Good luck, Ann From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] Sent: 11 August 2015 06:15 PM To: firebird-support@yahoogroups.com Subject: Re: [firebird-support] Reads inconsistency between FB TraceManager and IB Planalyser Hi Lois, Reads value means actual reads from the disk. If you run query twice, necessary database pages are cached, and you will see in stats more fetches - i.e., calls to cache, not record fetches. Actually, always is better to run query twice, to get heated cache. Regards, Alexey Kovyazin IBSurgeon I am trying to find a performance issue in a system and I am using 2 tools just for comparison and verification. One being FB TraceManager (FBTM) and the other is an old util called Interbase Planalyzer (IP) It seems that the particular query from the view TRACKED_ITEM_LOCATION_DETAIL is slow. From FBTM,while monitoring the app, I get the following raw output: -- 2015-08-11T16:35:52.4990 (6180:00F9A948) EXECUTE_STATEMENT_FINISH D:\PROJECTS\KKI\TECH\DATABASE\PRODUCTION DATABASES\2015-07-14\DIGITAN.KKI.FDB (ATT_34, SYSDBA:NONE, NONE, TCPv4:127.0.0.1) (TRA_20898, READ_COMMITTED | NO_REC_VERSION | WAIT | READ_WRITE) Statement 3407: -- select * from TRACKED_ITEM_LOCATION_DETAIL where ITEM_ID = ? order by ID ^^^ PLAN JOIN (JOIN (JOIN (TRACKED_ITEM_LOCATION_DETAIL TILD ORDER TILD_PK_IDX INDEX (TILD_ITEM_IDX), TRACKED_ITEM_LOCATION_DETAIL TL INDEX (TL_PK_IDX)), TRACKED_ITEM_LOCATION_DETAIL FTL INDEX (TL_PK_IDX)), TRACKED_ITEM_LOCATION_DETAIL USR INDEX (USR_PK_IDX)) param0 = bigint, 2147191655 1 records fetched 112 ms, 3316 read(s), 3325 fetch(es) Table Natural Index Update Insert Delete Backout Purge Expunge *** USER_ 1 TRACKING_LOCATION_ 1 TRACKED_ITEM_LOCATION_DETAIL_ 1 -- 3325 fetches and 112ms seem way over the top. The cache hit ratio is 0%. However, when I use IP and manually enter the same SQL, I get the following: Prepare time 1ms Execution time 148ms Fetch time 4ms With a total of 7 fetches. Not sure where FBTM gets 3K fetches? Also, if I remove the order by, IP reports a drastic reduction is execution time i.e. down to 6msec. Does the ORDER BY on the PK make such a difference? I am not understanding what is going on. Either way, here are the table view info stats Thanks Louis van Alphen -- CREATE TABLE TRACKED_ITEM_LOCATION_DETAIL_ ( UID DOM_UID /* DOM_UID = VARCHAR(36) */, ID DOM_ID /* DOM_ID = BIGINT NOT NULL */, IS_DELETED DOM_BINARY /* DOM_BINARY = SMALLINT DEFAULT 0 NOT NULL CHECK (( value in ( 0,1) )) */, CREATED_DTM DOM_DTM default current_timestamp /* DOM_DTM = TIMESTAMP */, CREATED_USER_ID DOM_FK /* DOM_FK = BIGINT */, ROW_ORIGIN DOM_FK /* DOM_FK = BIGINT */, ITEM_ID DOM_FK NOT NULL /* DOM_FK = BIGINT */, LOCATION_ID DOM_FK NOT NULL /* DOM_FK = BIGINT */, FROM_LOCATION_ID DOM_FK /* DOM_FK = BIGINT */, START_DTM DOM_DTM NOT NULL /* DOM_DTM = TIMESTAMP */, END_DTM DOM_DTM /* DOM_DTM = TIMESTAMP */, START_DAT DOM_DAT NOT NULL /* DOM_DAT = DATE */, END_DAT DOM_DAT /* DOM_DAT = DATE */ ); ALTER TABLE TRACKED_ITEM_LOCATION_DETAIL_ ADD CONSTRAINT TILD_PK PRIMARY KEY (ID) USING INDEX TILD_PK_IDX; CREATE INDEX TILD_END_DAT_IDX ON TRACKED_ITEM_LOCATION_DETAIL_ (END_DAT); CREATE INDEX TILD_ITEM_IDX ON TRACKED_ITEM_LOCATION_DETAIL_ (ITEM_ID); CREATE INDEX TILD_LOCATION_IDX ON
Re: [firebird-support] Reads inconsistency between FB TraceManager and IB Planalyser
On Aug 11, 2015, at 1:30 PM, 'Louis van Alphen' lo...@nucleo.co.za [firebird-support] firebird-support@yahoogroups.com wrote: Thanks Ann, so maybe the 2 tools' metric are different. So maybe FBTM includes all (low level) reads and IP only user table reads. Or maybe one runs some queries before the one it measures so all the system table queries are compiled, optimized, and executed, filling the cache with good stuff. Also, if I remove the order by, IP reports a drastic reduction is execution time i.e. down to 6msec. Does the ORDER BY on the PK make such a difference? Unh, maybe. Between the list, my mail system and yours, I've lost most of the formatting and can't look at the original while typing (grrr) You seem to use left outer joins and that can be a problem because (in general) the order in which outer joins are declared in the query is the order in which they have to be accessed in the query plan. For example, a three way join of students, registrations, and courses with inner joins can be run starting with any of the three tables and get the same results. Select s.student_name, c.course_name from students s join registrations r on r.student_id = s.student_id join courses c on c.course_id = r.course_id The optimizer can choose to start with registrations and lookup students and courses by primary key, or students and look up a registration by student_id and a course by the course_id in registrations, or courses - registrations - students. Or sort the students and registrations and merge them, looking up courses from the matching pairs. Or other things, probably. However, this query can be executed in only one way: students - registrations - courses Select s.student_name, c.course_name from students s left outer join registrations r on r.student_id = s.student_id left outer join courses c on c.course_id = r.course_id If that way isn't optimal, too bad. The query must be executed that way or you'll miss all the students who aren't registered for courses and all the registrations that don't correspond to any courses. It may be that adding the sort, you've convinced Firebird that it has to read and sort all the records in that table, then start from the sorted result. Try using one of the various syntaxes that tells it to return only the first record found that matches the criteria. That may cause Firebird to walk the index ... read records in index order ... rather than reading the table and sorting it. Now that probably sounds really dumb, but in fact, walking a table in index order is generally a lot slower than reading it in physical order and sorting the result. Yes, sort is n(log n), but there's a big K applied that is the cost of a random record read. So, if you expect a lot of results, sort first and ask for matches later If not, use 'Select FIRST n' or 'LIMIT n' or whatever the standards committee chose to bless ... maybe 'FETCH FIRST n'. Any of those hints will convince Firebird to walk the table in index order. This query is likely to read and sort the students first, then start looking for the rest of the data unless there's a good index on student_name. Select s.student_name, c.course_name from students s left outer join registrations r on r.student_id = s.student_id left outer join courses c on c.course_id = r.course_id where s.student_name = 'Dinah MacQuarie' order by s.student_id This may give a faster result in the absence of an student_name index. It may not, but do consider trying it when playing with query forms... Select first 30 s.student_name, c.course_name from students s left outer join registrations r on r.student_id = s.student_id left outer join courses c on c.course_id = r.course_id where s.student_name = 'Dinah MacQuarie' order by s.student_id It's worth noting that Firebird does not store records in primay key order unless the records happen to arrive in primary key order. Even when records are created in primary key order - as with a generated key - deleted records may be replaced with records created much later. Lots of databases cluster records on primary key, making it unnecessary to sort when reading the primary key. That has other costs, particularly with natural keys... This post is already way to long to take on that issue. Now, just to complicate things, it's possible to turn left outer joins to inner joins - at least if you're a person. Maybe the Firebird optimizer knows about this, and maybe it doesn't. Certainly in the previous century one database developer - initials JAS - thought that if you were dumb enough to use outer joins when you shouldn't, the optimizer shouldn't reward your stupidity. He's become more benevolent in his old age. Anyway, here's the case: Select s.student_name, c.course_name from students s join
[firebird-support] Fwd: Need help in Firebird
Dear All, I have a question. I need to store some data in Firebird database. One of the data is a running serial number, 0 to 99. I try the Count SQL function, but sometimes after INSERT function I get same number 2-3 times. After I try to make a tabel which contain an INTEGER field, and I update it, after I run a QUERRY, but the situation is same. Could you give me a good sollution how can the DB make a running serial number (not recurring)? After each UPDATE I run a COMMIT. The operating system is: Windows 7. Please give me an answer ASAP. Thank you very much! Best Regards: Richard Bakos -- Üdvözlettel: / Best regards, / Mit freundlichen Grüssen: Richárd Bakos IMC Soft Kft. Tel: +36-30-381-9004 Email: bakos.rich...@imc-soft.com -- Üdvözlettel: / Best regards, / Mit freundlichen Grüssen: Richárd Bakos IMC Soft Kft. Tel: +36-30-381-9004 Email: bakos.rich...@imc-soft.com
Re: [firebird-support] Need help in Firebird
Dear All, I have a question. I need to store some data in Firebird database. One of the data is a running serial number, 0 to 99. I try the Count SQL function, but sometimes after INSERT function I get same number 2-3 times. After I try to make a tabel which contain an INTEGER field, and I update it, after I run a QUERRY, but the situation is same. Could you give me a good sollution how can the DB make a running serial number (not recurring)? After each UPDATE I run a COMMIT. The operating system is: Windows 7. Please give me an answer ASAP. Thank you very much! Hi Richard! The reason you get the same number is most likely because of simultaneous updates, i.e. that one transaction starts before the previous transaction commits. COUNT have never been a reliable way of getting a unique number. Moreover, COUNT will make things slow as the table grows. Rather, try: CREATE SEQUENCE MyPreferredSequenceName and then in your insert statement include NEXT VALUE FOR MyPreferredSequenceName If for some reason a transaction rolls back, that particular sequence number is lost. Normally, this doesn't matter, but sometimes it may for financial applications where laws say that there should be no unexplained gaps. HTH, Set PS! Your title Need help in Firebird is quite useless here, since almost 50% of the emails fit that description (the other 50% are answers). It would have been far better if it had been something like My numbers duplicate, I need them to be unique
Re: [firebird-support] Reads inconsistency between FB TraceManager and IB Planalyser
Also, if I remove the order by, IP reports a drastic reduction is execution time i.e. down to 6msec. Does the ORDER BY on the PK make such a difference? Unh, maybe. Between the list, my mail system and yours, I've lost most of the formatting and can't look at the original while typing (grrr) My guess would rather be that the ORDER BY forces Fb to fetch all rows, whereas without ORDER BY only the first few (random) rows are fetched, and that the time for fetching all rows rather than the first row are similar. You seem to use left outer joins and that can be a problem because (in general) the order in which outer joins are declared in the query is the order in which they have to be accessed in the query plan. Sure, LEFT JOIN limits the optimizer. However, the WHERE clause only refers to the TILD (main) table, and the three LEFT JOINs seem to have a function similar to subselects used to find lookup values (the last three fields in the view). Hence, I'm pretty certain the optimizer would have chosen a very similar plan if LEFT had been deleted (it could of course have reordered the three lookup tuples, but they're not related anyway (they each join to the TILD table, not eachother), so the same indexes should be used. Set
