Re: [firebird-support] URGENT: Invalid request BLR
Thomas, What can you expect if Firebird needs temporary disk space which isn't available to create/activate an index? I expect the statement to fail but but not corrupt the state of the database. Similarly to how a query with a large sort would fail if there is not enough disk space for the temporary sort file and doesn't corrupt the database. Thanks, Alec [Non-text portions of this message have been removed]
Re: [firebird-support] Re: TempDirectories and ALTER INDEX ACTIVE
Thanks Vlad, but this is too complicated to give as a guidance to our customers. However, it sounds like rebuilding an index cannot require more space that the database size itself, right? On Fri, Jun 8, 2012 at 7:22 AM, hvlad hv...@users.sourceforge.net wrote: ** --- In firebird-support@yahoogroups.com, Alec Swan wrote: My main question is how can we calculate the temp sort space required to ALTER INDEX ACTIVE (on all indexes serially) given the size of the database? Is twice the size of the database a good upper bound? Enumerate all fields in index, calculate summa of full size of every field (for [var]char(N) it is N * bytes_per_char, for ex.), add extra 8 bytes (for record number) - this is sort record size. Multiply it by number of records and you'll get a lower estimate of temp space required. Substract value of TempCacheLimit and you'll get estimate of disk space required. Regards, Vlad [Non-text portions of this message have been removed] ++ 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: http://docs.yahoo.com/info/terms/
Re: [firebird-support] URGENT: Invalid request BLR
Thomas, the index used to exist but for some reason it was lost, probably because of the lack of disk space during index rebuild. Firebird should not leave the database in a bad state like this if ALTER INDEX ACTIVATE fails because of lack of disk space! Alec On Thu, Jun 7, 2012 at 12:27 PM, Thomas Steinmaurer t...@iblogmanager.comwrote: ** We are getting the following error during a SELECT-query and gfix with gback do not fix it. org.firebirdsql.jdbc.FBSQLException: GDS Exception. 335544343. invalid request BLR at offset 226 there is no index IDX_p39A2dTscKu7xw43c5lrjQ== for table COPY The customer is really frustrated, what should we do? Are you forcing the optimizer to use an index via an explicit PLAN, but the index doesn't exist? Regards, Thomas [Non-text portions of this message have been removed] ++ 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: http://docs.yahoo.com/info/terms/
Re: [firebird-support] Why PLAN token is not allowed after ORDER BY?
UNION use case makes sense, thanks! On Mon, Jun 4, 2012 at 1:29 AM, Svein Erling Tysvær svein.erling.tysv...@kreftregisteret.no wrote: ** I consider this issue resolved with one side note that having PLAN hint which affects ORDER BY execution before ORDER BY clause is misleading. Don't forget UNION, Alec. I.e. select * from T where a = 1 PLAN (T INDEX (IDX_TA)) union select * from T where a = 2 PLAN (T INDEX (IDX_TA)) order by 1 makes it quite simple to see why the PLAN clause has to be before the ORDER BY. Of course, I have no problems understanding that having the option of specifying ORDER BY before PLAN in simple selects could be easier to read (particularly when the plan involves ORDER as opposed to INDEX). Set [Non-text portions of this message have been removed] ++ 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: http://docs.yahoo.com/info/terms/
Re: [firebird-support] Slow query because an index is not picked up during sort
Actually, it doesn't. The order of joins is determined by the optimizer in the case of an inner join, but outer joins determine the order semantically. Consider this case: so, if FB optimizes INNER JOIN why didn't it use a more selective index on commit_number instead of less selective index on source_id? Especially given that there was an ORDER BY on commit_number? select c.city, s.stateName from cities c inner join states s on c.stateCode = s.stateCode where c.population 100 I am assuming you meant to use LEFT not INNER JOIN, right? Thanks, Alec [Non-text portions of this message have been removed]
Re: [firebird-support] Why PLAN token is not allowed after ORDER BY?
Thanks, Mark. I found FB 2.5 SQL Language reference here http://www.firebirdsql.org/refdocs/langrefupd25-select.html but it does not contain the grammar for the entire SELECT statement, just individual fragments, such as JOIN, ORDER BY, etc. Where can I find a comprehensive syntax definition for the entire SELECT statement? Thanks, Alec On Sun, Jun 3, 2012 at 2:03 AM, Mark Rotteveel m...@lawinegevaar.nl wrote: ** On 2-6-2012 23:17, Dmitry Kuzmenko wrote: Hello, Alec! AS The question is why FB complains about PLAN token after ORDER BY clause? the answer is that PLAN is wrong. ORDER BY in query affects PLAN, it will contain PLAN SORT or TABLE ORDER INDEX specification. Your PLAN specification is crap, sorry for rude answer. As I said before, you need to know how Firebird produces plan specification and how it accepts it. Dmitry, I think this is not a very constructive way to respond. Alec is asking why Firebird does not accept a PLAN after ORDER BY, but it does before ORDER BY. I think the reason is simply that the grammar definition of Firebird expects it before the ORDER BY. There is probably nothing more to it. Telling people they are stupid (my interpretation of your messages) is not helpful. Mark -- Mark Rotteveel [Non-text portions of this message have been removed] ++ 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: http://docs.yahoo.com/info/terms/
Re: [firebird-support] Why PLAN token is not allowed after ORDER BY?
I am sorry for the last post I did not see that Mark already answered it. Is there online docs for SELECT syntax? Thank you, Mark. On Sun, Jun 3, 2012 at 8:59 AM, Alec Swan alecs...@gmail.com wrote: Thanks, Mark. I found FB 2.5 SQL Language reference here http://www.firebirdsql.org/refdocs/langrefupd25-select.html but it does not contain the grammar for the entire SELECT statement, just individual fragments, such as JOIN, ORDER BY, etc. Where can I find a comprehensive syntax definition for the entire SELECT statement? Thanks, Alec On Sun, Jun 3, 2012 at 2:03 AM, Mark Rotteveel m...@lawinegevaar.nlwrote: ** On 2-6-2012 23:17, Dmitry Kuzmenko wrote: Hello, Alec! AS The question is why FB complains about PLAN token after ORDER BY clause? the answer is that PLAN is wrong. ORDER BY in query affects PLAN, it will contain PLAN SORT or TABLE ORDER INDEX specification. Your PLAN specification is crap, sorry for rude answer. As I said before, you need to know how Firebird produces plan specification and how it accepts it. Dmitry, I think this is not a very constructive way to respond. Alec is asking why Firebird does not accept a PLAN after ORDER BY, but it does before ORDER BY. I think the reason is simply that the grammar definition of Firebird expects it before the ORDER BY. There is probably nothing more to it. Telling people they are stupid (my interpretation of your messages) is not helpful. Mark -- Mark Rotteveel [Non-text portions of this message have been removed] ++ 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: http://docs.yahoo.com/info/terms/
Re: [firebird-support] Why PLAN token is not allowed after ORDER BY? - Email found in subject
Sean, you nailed the reason on the head - the FB restriction on index names. In fact, on SQL Server we have pretty index names :) Unfortunately, we didn't have the resources to spend in prettyfying the index names at the time. At this point it's even harder to do this because of upgrade scenarios. Thanks, Alec On Sun, Jun 3, 2012 at 1:06 PM, Leyne, Sean s...@broadviewsoftware.comwrote: ** We create indexes programmatically and we we generate their names based on their definition, such the columns they index. If that is the case, why is the name gibberish? You could create an index which has the name(s) of the source fields (ie. PK_PhysicalCopy.ID or FK_PhysicalCopy.CopyID or PC.ISDIRTY), rather than the GUID-like non-names which you currently use. Yes, there is a limit to the length of an Firebird index name, but you could come up with an algorithm which would be able to shorten the names as appropriate. For example an compound index of COPY_CLASSIFICATION.CLASSIFICATION_CODE and COPY_CLASSIFICATION.LABEL_CLASSIFICATION_CODE could be named COPY_CLASS.CLASSIF_LABELCLASSIF. Sean [Non-text portions of this message have been removed] ++ 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: http://docs.yahoo.com/info/terms/
Re: [firebird-support] Why PLAN token is not allowed after ORDER BY?
Thank you! I consider this issue resolved with one side note that having PLAN hint which affects ORDER BY execution before ORDER BY clause is misleading. Alec On Sun, Jun 3, 2012 at 1:15 PM, Michael Ludwig mil...@gmx.de wrote: ** Alec Swan schrieb am 03.06.2012 um 09:01 (-0600): Is there online docs for SELECT syntax? There's an online HTML version of the SQL reference linked to from the overview page: http://www.ibphoenix.com/files/60sqlref.html On Sun, Jun 3, 2012 at 8:59 AM, Alec Swan alecs...@gmail.com wrote: I found FB 2.5 SQL Language reference here http://www.firebirdsql.org/refdocs/langrefupd25-select.html but it does not contain the grammar for the entire SELECT statement, just individual fragments, such as JOIN, ORDER BY, etc. Where can I find a comprehensive syntax definition for the entire SELECT statement? There's no complete one-stop doc for the whole thing yet, as far as I know. Guess most people are happy enough using the old InterBase 6.0 docs (links at the bottom of the doc overview page) supplemented by the Firebird additions you've found. All here: http://www.firebirdsql.org/en/reference-manuals/ From the InterBase 6.0 Manuals section, download either the Full Set or just the Language Reference. Producing a comprehensive doc would require a lot of work, and there might also be copyright issues for the old InterBase 6.0 docs. Michael [Non-text portions of this message have been removed] ++ 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: http://docs.yahoo.com/info/terms/
Re: [firebird-support] Slow query because an index is not picked up during sort
Sean, I would like to note that the fact that LEFT JOIN can generate an optimal plan where INNER JOIN fails indicates that all our index statistics are up-to-date and that there is some discrepancy between LEFT and INNER JOIN optimization that has already bit us several times. The good thing about LEFT is that it pushes SORT down to the table and uses an index on it whereas INNER does SORT after the join. Now back to your questions. I had to modify your first query a little for it to compile. Here are the stats: Preparing query: SELECT FIRST (1000) PHYSICAL_COPY.ID FROM ( SELECT PHYSICAL_COPY.ID FROM PHYSICAL_COPY INNER JOIN COPY ON PHYSICAL_COPY.COPY_ID = COPY.ID WHERE (PHYSICAL_COPY.IS_DIRTY = 1) AND (COPY.SOURCE_ID = '5d74876f-1b14-466a-aeee-f000b8b16512') AND (PHYSICAL_COPY.COMMIT_NUMBER = 1000) ORDER BY PHYSICAL_COPY.COMMIT_NUMBER ) AbstractTable INNER JOIN PHYSICAL_COPY on PHYSICAL_COPY.ID = AbstractTable.ID INNER JOIN COPY ON PHYSICAL_COPY.COPY_ID = COPY.ID INNER JOIN COPY_CLASSIFICATION ON COPY.ID = COPY_CLASSIFICATION.COPY_ID Prepare time: 0.204s Field #01: PHYSICAL_COPY.ID Alias:ID Type:STRING(38) PLAN JOIN (JOIN (SORT (JOIN (ABSTRACTTABLE COPY INDEX (IDX_Lhi+/ZlWWZ/FDOab6YV2Vg==), ABSTRACTTABLE PHYSICAL_COPY INDEX (IDX_AlJS5EmMT9tODQnFqmid0w==))), PHYSICAL_COPY INDEX (PK_lx3z+dt9CnekKkF+ZE8NIA==)), JOIN (COPY INDEX (PK_ZM6SRonqR8AHSQuCISgvnQ==), COPY_CLASSIFICATION INDEX (IDX_soqMJd+Yux0RNvCbmE9rrg==))) Executing... Done. 6262754 fetches, 0 marks, 808411 reads, 0 writes. 0 inserts, 0 updates, 0 deletes, 1563528 index, 0 seq. Delta memory: 527584 bytes. Total execution time: 0:01:58 (hh:mm:ss) Script execution finished. Here is your second query (has very high reads): Preparing query: SELECT PHYSICAL_COPY.ID FROM COPY INNER JOIN PHYSICAL_COPY ON PHYSICAL_COPY.COPY_ID = COPY.ID INNER JOIN COPY_CLASSIFICATION ON COPY.ID = COPY_CLASSIFICATION.COPY_ID WHERE (PHYSICAL_COPY.IS_DIRTY = 1) AND (COPY.SOURCE_ID = '5d74876f-1b14-466a-aeee-f000b8b16512') AND (PHYSICAL_COPY.COMMIT_NUMBER = 1000) ORDER BY PHYSICAL_COPY.COMMIT_NUMBER ASC Prepare time: 0.015s Field #01: PHYSICAL_COPY.ID Alias:ID Type:STRING(38) PLAN SORT (JOIN (COPY INDEX (IDX_Lhi+/ZlWWZ/FDOab6YV2Vg==), PHYSICAL_COPY INDEX (IDX_AlJS5EmMT9tODQnFqmid0w==), COPY_CLASSIFICATION INDEX (IDX_soqMJd+Yux0RNvCbmE9rrg==))) Executing... Done. 6262689 fetches, 0 marks, 808409 reads, 0 writes. 0 inserts, 0 updates, 0 deletes, 1563505 index, 0 seq. Delta memory: 517200 bytes. Total execution time: 47.437s Script execution finished. On Sun, Jun 3, 2012 at 3:27 PM, Leyne, Sean s...@broadviewsoftware.comwrote: ** Alec, Sean, The query you proposed will not produce the same result because you are sorting and topping the join before applying the filter. You are correct. But a small change to my proposed query would resolve that issue, the new/corrected query would be: SELECT FIRST (1000) PHYSICAL_COPY.ID, ... FROM ( SELECT PHYSICAL_COPY.ID FROM PHYSICAL_COPY INNER JOIN COPY ON PHYSICAL_COPY.COPY_ID = COPY.ID ) AbstractTable WHERE (PHYSICAL_COPY.IS_DIRTY = 1) AND (COPY.SOURCE_ID = '123431234') AND (PHYSICAL_COPY.COMMIT_NUMBER = 1000) ORDER BY PHYSICAL_COPY.COMMIT_NUMBER ) AbstractTable INNER JOIN PHYSICAL_COPY on PHYSICAL_COPY.ID = AbstractTable.ID INNER JOIN COPY ON PHYSICAL_COPY.COPY_ID = COPY.ID INNER JOIN COPY_CLASSIFICATION ON COPY.ID = COPY_CLASSIFICATION.COPY_ID We have a one-to-one relationship between PHYSICAL_COPY and COPY and COPY_CLASSIFICATION. So, the solution I went with was to replace INNER JOINs with LEFT JOINs which changed the execution plan from (I translated index names): This is the second time (see my older post Force query plan to filter before join) where LEFT join saved the day. It would be nice to understand why LEFT join optimization works so much better for us. Is there an explanation of FB optimization techniques available somewhere? What is the performance of your query like if you change the query to be: SELECT PHYSICAL_COPY.ID, ... FROM COPY INNER JOIN PHYSICAL_COPY ON PHYSICAL_COPY.COPY_ID = COPY.ID INNER JOIN COPY_CLASSIFICATION ON COPY.ID = COPY_CLASSIFICATION.COPY_ID WHERE ... (the position of COPY and PHYSICAL_COPY in the query was changed, but the defined relationship is the same) Sean [Non-text portions of this message have been removed] ++ 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:
[firebird-support] Why PLAN token is not allowed after ORDER BY?
Hello, I am using FB 2.5 and execute SQL using Flamerobin. I am trying to specify a plan for a query that has ORDER BY clause. I get a invalid token PLAN error if I specify the plan after ORDER BY, but it works OK if I specify the plan before ORDER BY. But the plan contains instructions for ORDER BY as well. What's the problem? This works: SELECT FIRST (1000) * FROM PHYSICAL_COPY INNER JOIN COPY ON PHYSICAL_COPY.COPY_ID = COPY.ID WHERE (PHYSICAL_COPY.IS_DIRTY = 1) AND (COPY.SOURCE_ID = '5d74876f-1b14-466a-aeee-f000b8b16512') AND (PHYSICAL_COPY.COMMIT_NUMBER = 1000) PLAN JOIN (PHYSICAL_COPY ORDER IDX_214/CmDhH936xtHXcXNQKg== INDEX (IDX_214/CmDhH936xtHXcXNQKg==, IDX_4I5n6Ay0py37rBgakWZS3Q==), COPY INDEX (PK_ZM6SRonqR8AHSQuCISgvnQ==)) ORDER BY PHYSICAL_COPY.COMMIT_NUMBER ASC This DOES NOT WORK!! SELECT FIRST (1000) * FROM PHYSICAL_COPY INNER JOIN COPY ON PHYSICAL_COPY.COPY_ID = COPY.ID WHERE (PHYSICAL_COPY.IS_DIRTY = 1) AND (COPY.SOURCE_ID = '5d74876f-1b14-466a-aeee-f000b8b16512') AND (PHYSICAL_COPY.COMMIT_NUMBER = 1000) ORDER BY PHYSICAL_COPY.COMMIT_NUMBER ASC PLAN JOIN (PHYSICAL_COPY ORDER IDX_214/CmDhH936xtHXcXNQKg== INDEX (IDX_214/CmDhH936xtHXcXNQKg==, IDX_4I5n6Ay0py37rBgakWZS3Q==), COPY INDEX (PK_ZM6SRonqR8AHSQuCISgvnQ==)) Message: isc_dsql_prepare failed SQL Message : -104 Invalid token Engine Code: 335544569 Engine Message : Dynamic SQL Error SQL error code = -104 Token unknown - line 6, column 1 PLAN
Re: Re[4]: [firebird-support] Why PLAN token is not allowed after ORDER BY?
I would have never expected a parser token error if the query syntax is correct but the plan itself is bad. That would have been a run-time, not compile-time error! And I just verified that what I am saying looks like an FB bug. Here is a very simple example that reproduces the bug: create table T (a int); create asc index idx_Ta on T (a); select * from T order by a; -- generates PLAN (T ORDER IDX_TA) select * from T order by a PLAN (T ORDER IDX_TA); ERROR: An Invalid token ... Token unknown - line 4, column 28 PLAN Is there a workaround for specifying PLAN after ORDER BY? Thanks, Alec On Sat, Jun 2, 2012 at 4:27 PM, Dmitry Kuzmenko k...@ibase.ru wrote: ** Hello, Alec! AS 2. Changed the query to A INNER JOIN B ORDER BY X PLAN P, where P is the AS plan generated by FB in #1. Received Unexpected token PLAN error. AS 3. Change the query to A INNER JOIN B PLAN P ORDER BY X - success! AS Why does #3 succeed but #2 fail? because inner join is not equal to left join. -- Dmitry Kuzmenko, www.ib-aid.com [Non-text portions of this message have been removed] ++ 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: http://docs.yahoo.com/info/terms/
[firebird-support] Re: Embedded Firebird leaves a lot of temp files behind
I verified that FIREBIRD_TMP environment variable can be used to control where temporary files are written. But TempDirectories in firebird.conf seems to have no effect with or without FIREBIRD_TMP env var set. Is TempDirectories setting broken in FB 2.5? Does it have no effect on embedded FB version? Also, is it safe to use FIREBIRD_LOCK env var to control where FB writes its lock files? Thanks, Alec On Thu, May 31, 2012 at 1:53 PM, Alec Swan alecs...@gmail.com wrote: I haven't received any responses in 10 days. Maybe the question is not clear, so I will restate it. How can I configure the location of all Firebird temporary, sort and lock files and control their sizes? Thanks, Alec On Mon, May 21, 2012 at 1:10 PM, Alec Swan alecs...@gmail.com wrote: I found the following setting in http://ibexpert.net/ibe/index.php?n=Doc.ConfiguringFirebird#FBConf: TempDirectories = c:\temp 1 I am assuming that 1 is in bytes, correct? What happens when this limit is reached? Thanks, Alec On Mon, May 21, 2012 at 1:00 PM, Alec Swan alecs...@gmail.com wrote: Hello, Our customers are complaining that there are lots of (GBs) temp files written by Firebird in temp directory. Is there a way to have embedded Firebird 2.5 to clean up temp files, e.g. lock files, after it's done with them? Thanks, Alec
Re: [firebird-support] Re: Embedded Firebird leaves a lot of temp files behind
Thanks, Michael. The issue you pointed out is similar to mine and the resolution was to set FIREBIRD_TMP environment variable. But this approach does not allow me to restrict the size of the temp directory. Our customers complain that Firebird creates 6GB temporary sort file and we need to address this asap. Can anyone suggest a solution? Thanks, Alec On Fri, Jun 1, 2012 at 11:28 AM, Michael Ludwig mil...@gmx.de wrote: ** Alec Swan schrieb am 01.06.2012 um 10:16 (-0600): I verified that FIREBIRD_TMP environment variable can be used to control where temporary files are written. But TempDirectories in firebird.conf seems to have no effect with or without FIREBIRD_TMP env var set. Is TempDirectories setting broken in FB 2.5? Does it have no effect on embedded FB version? Doesn't sound exactly relevant for the issue you're facing, but maybe you can make sense of the discussion and so gain some insights: TempDirectories parameter in firebird.conf ignored by global temporary tables - http://tracker.firebirdsql.org/browse/CORE-1241 - 03/May/07 Michael [Non-text portions of this message have been removed] ++ 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: http://docs.yahoo.com/info/terms/
[firebird-support] Slow query because an index is not picked up during sort
Hello, We tracked down the query that generated a 10GB temp file running against a 1.5GB database. Can anybody explain why the query is not using an index on PHYSICAL_COPY.COMMIT_NUMBER? Thanks, Alec Preparing query: SELECT FIRST (1000) PHYSICAL_COPY.ID, PHYSICAL_COPY.COMMIT_NUMBER, PHYSICAL_COPY.COPY_ID, PHYSICAL_COPY.REPOSITORY_TYPE, PHYSICAL_COPY.CANONICAL_LOCATION, PHYSICAL_COPY.CANONICAL_NAME, PHYSICAL_COPY.DISPLAY_LOCATION, PHYSICAL_COPY.DISPLAY_NAME, PHYSICAL_COPY.LOCATION_TYPE, PHYSICAL_COPY.IS_RECYCLED, PHYSICAL_COPY.SIZE, PHYSICAL_COPY.LAST_MODIFIED_TIMESTAMP, PHYSICAL_COPY.CREATED_TIMESTAMP, PHYSICAL_COPY.EXTENSION, PHYSICAL_COPY.IS_MARKED_DELETED, PHYSICAL_COPY.IS_DIRTY, PHYSICAL_COPY.CANONICAL_LOCATION_HASH, PHYSICAL_COPY.CANONICAL_LOCATION_CHECKSUM, COPY.ID, COPY.COMMIT_NUMBER, COPY.VERSION_ID, COPY.EXISTENCE_TIMESTAMP, COPY.REGISTRATION_TIMESTAMP, COPY.PREDECESSOR_ID, COPY.PREDECESSOR_CONFIDENCE_LEVEL, COPY.SOURCE_ID, COPY.THREAD_ID, COPY.ROOT_ID, COPY.IS_DIRTY, COPY_CLASSIFICATION.ID, COPY_CLASSIFICATION.COMMIT_NUMBER, COPY_CLASSIFICATION.COPY_ID, COPY_CLASSIFICATION.CLASSIFICATION_CODE, COPY_CLASSIFICATION.CONFIRMATION_STATUS, COPY_CLASSIFICATION.LABEL_CLASSIFICATION_CODE, COPY_CLASSIFICATION.LABEL_CONFIRMATION_STATUS, COPY_CLASSIFICATION.CUTOFF_TIMESTAMP, COPY_CLASSIFICATION.IS_CUTOFF_TIME_CONFIRMED, COPY_CLASSIFICATION.IS_DIRTY FROM PHYSICAL_COPY INNER JOIN COPY ON PHYSICAL_COPY.COPY_ID = COPY.ID INNER JOIN COPY_CLASSIFICATION ON COPY.ID = COPY_CLASSIFICATION.COPY_ID WHERE (PHYSICAL_COPY.IS_DIRTY = 1) AND (COPY.SOURCE_ID = '123431234') AND (PHYSICAL_COPY.COMMIT_NUMBER = 1000) ORDER BY PHYSICAL_COPY.COMMIT_NUMBER ASC Prepare time: 3.969s PLAN SORT (JOIN (COPY INDEX (IDX_Lhi+/ZlWWZ/FDOab6YV2Vg==), PHYSICAL_COPY INDEX (IDX_AlJS5EmMT9tODQnFqmid0w==), COPY_CLASSIFICATION INDEX (IDX_soqMJd+Yux0RNvCbmE9rrg==))) Executing... Done. 6273556 fetches, 12 marks, 808464 reads, 10 writes. 0 inserts, 0 updates, 0 deletes, 1563789 index, 4789 seq. Delta memory: 607388 bytes. Total execution time: 0:02:04 (hh:mm:ss) Script execution finished.
[firebird-support] Re: Embedded Firebird leaves a lot of temp files behind
I haven't received any responses in 10 days. Maybe the question is not clear, so I will restate it. How can I configure the location of all Firebird temporary, sort and lock files and control their sizes? Thanks, Alec On Mon, May 21, 2012 at 1:10 PM, Alec Swan alecs...@gmail.com wrote: I found the following setting in http://ibexpert.net/ibe/index.php?n=Doc.ConfiguringFirebird#FBConf: TempDirectories = c:\temp 1 I am assuming that 1 is in bytes, correct? What happens when this limit is reached? Thanks, Alec On Mon, May 21, 2012 at 1:00 PM, Alec Swan alecs...@gmail.com wrote: Hello, Our customers are complaining that there are lots of (GBs) temp files written by Firebird in temp directory. Is there a way to have embedded Firebird 2.5 to clean up temp files, e.g. lock files, after it's done with them? Thanks, Alec
[firebird-support] Embedded Firebird leaves a lot of temp files behind
Hello, Our customers are complaining that there are lots of (GBs) temp files written by Firebird in temp directory. Is there a way to have embedded Firebird 2.5 to clean up temp files, e.g. lock files, after it's done with them? Thanks, Alec
[firebird-support] Re: Embedded Firebird leaves a lot of temp files behind
I found the following setting in http://ibexpert.net/ibe/index.php?n=Doc.ConfiguringFirebird#FBConf: TempDirectories = c:\temp 1 I am assuming that 1 is in bytes, correct? What happens when this limit is reached? Thanks, Alec On Mon, May 21, 2012 at 1:00 PM, Alec Swan alecs...@gmail.com wrote: Hello, Our customers are complaining that there are lots of (GBs) temp files written by Firebird in temp directory. Is there a way to have embedded Firebird 2.5 to clean up temp files, e.g. lock files, after it's done with them? Thanks, Alec
Re: [firebird-support] Force query plan to filter before join
Set and Arno, Thank you both of you for your solutions! Arno's solution required swapping the order of PROJECT and PROJECT_CODE_DESCRIPTOR tables in the join and use LEFT JOIN instead of INNER JOIN to join them. This is so simple and the results are amazing. PLAN SORT (JOIN (JOIN (JOIN (PROJECT NATURAL, PROJECT_CODE_DESCRIPTOR INDEX (FK_zDTEgB/EMb14zlRjEdzCZw==)), COPY_CLASSIFICATION INDEX (IDX_epDQN2rI7u0hoaXiWuFXAw==)), JOIN (COPY INDEX (PK_ZM6SRonqR8AHSQuCISgvnQ==), PHYSICAL_COPY INDEX (IDX_AlJS5EmMT9tODQnFqmid0w== 459 fetches, 0 marks, 6 reads, 0 writes. 0 inserts, 0 updates, 0 deletes, 108 index, 12 seq. Delta memory: -104 bytes. Total execution time: 0.025s I think I will stick with this solution, but I appreciate Set educating me on how to fool the query optimizer :) Arno, how did you know that using LEFT JOIN will cause the query optimizer to choose the plan we wanted? Thanks, Alec On Tue, Feb 21, 2012 at 2:15 AM, Arno Brinkman fbsupp...@abvisie.nl wrote: ** What about : SELECT PROJECT.PRIMARY_PROJECT_CODE AS COL0, COUNT(PHYSICAL_COPY.ID) AS COL1 FROM PROJECT LEFT JOIN PROJECT_CODE_DESCRIPTOR ON PROJECT_CODE_DESCRIPTOR.PROJECT_ID = PROJECT.ID JOIN COPY_CLASSIFICATION ON COPY_CLASSIFICATION.CLASSIFICATION_CODE = PROJECT_CODE_DESCRIPTOR.PROJECT_CODE JOIN COPY ON COPY.ID = COPY_CLASSIFICATION.COPY_ID JOIN PHYSICAL_COPY ON PHYSICAL_COPY.COPY_ID = COPY.ID WHERE ( (PROJECT_CODE_DESCRIPTOR.PROJECT_ID IS NOT NULL) and (PROJECT.ASSIGNMENT_STATUS != 'UNASSIGNED') and (PHYSICAL_COPY.IS_MARKED_DELETED = 0) and (PHYSICAL_COPY.IS_RECYCLED = 0) ) GROUP BY PROJECT.PRIMARY_PROJECT_CODE Regards, Arno [Non-text portions of this message have been removed] ++ 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: http://docs.yahoo.com/info/terms/
Re: [firebird-support] Force query plan to filter before join
Hello Set, Your guesswork worked quite well. Your query executes 20 times faster than my original query! Here are the stats for your query: PLAN SORT (JOIN (JOIN (JOIN (SORT (TMP P NATURAL), PCD INDEX (FK_zDTEgB/EMb14zlRjEdzCZw==)), CC INDEX (IDX_epDQN2rI7u0hoaXiWuFXAw==)), JOIN (C INDEX (PK_ZM6SRonqR8AHSQuCISgvnQ==), PC INDEX (IDX_AlJS5EmMT9tODQnFqmid0w== 527 fetches, 0 marks, 60 reads, 0 writes. 0 inserts, 0 updates, 0 deletes, 138 index, 12 seq. Delta memory: 61020 bytes. Total execution time: 0.067s Here is the query result: ACME 6 BANANA 1 CODE A 1 PROJECT A 1 PROJECT B 1 Notice that the sum of counts is 10 - this is what I meant by 'filters out all but 10 joined rows'. If UNASSIGNED was included in the list it would have had a count of almost 14,000 associated with it! So, what I wanted to avoid is for the query optimizer to join all tables first and only then apply the != 'UNASSIGNED' filter. Instead, I wanted the optimizer to apply the filter to the PROJECT table first, then join (and find 10 matching rows) and only after that do the aggregation. And that's what your query is doing I believe. But I understand that the optimizer is not doing what I want it to do because it does not know that 99% of rows are associated with project with 'UNASSIGNED' status and will be filtered after the join. I like using table expressions, but it will be hard to refactor our ORM tool to use them, so I am wondering if there is a way to force the plan of the original query to match your query? Thanks, Alec 2012/2/19 Svein Erling Tysvær svein.erling.tysv...@kreftregisteret.no ** Hello, I need help optimizing the query shown below. PROJECT-related tables contain 12 rows each while COPY-related tables contain 14K rows each. Moreover, PROJECT.ASSIGNMENT_STATUS != 'UNASSIGNED' filters out all but 10 joined rows. My question is how do I change the plan to force PROJECT.ASSIGNMENT_STATUS != 'UNASSIGNED' filter to be applied before the join? Hi Alec! I don't quite understand what you mean by 'filters out all but 10 joined rows', != (not equal) would normally not be able to use any index and trying to force that to be applied before any JOIN would of course find all that are different from 'UNASSIGNED' (note that NULL is neither equal to or different from 'UNASSIGNED') regardless of their connection to any other table, and making an index for a field where all but 10 rows have the same value would only be useful in very special circumstances. You can of course try something like WITH MyTemp AS (SELECT DISTINCT p.ID, p.PRIMARY_PROJECT_CODE FROM PROJECT p WHERE p.ASSIGNMENT_STATUS != 'UNASSIGNED') SELECT tmp.PRIMARY_PROJECT_CODE AS COL0, COUNT(pc.ID) AS COL1 FROM PROJECT_CODE_DESCRIPTOR pcd JOIN MyTemp tmp ON pcd.PROJECT_ID = tmp.ID JOIN COPY_CLASSIFICATION cc ON pcd.PROJECT_CODE = cc.CLASSIFICATION_CODE JOIN COPY c ON cc.COPY_ID = c.ID JOIN PHYSICAL_COPY pc ON c.ID = pc.COPY_ID WHERE pc.IS_MARKED_DELETED = 0 AND pc.IS_RECYCLED = 0 GROUP BY tmp.PRIMARY_PROJECT_CODE but my guess is that this might be slower than your original query (and I'm not certain Firebird would choose the PLAN I'm hoping for, we might have to change things), Note that I'm not saying it is impossible to speed up your query, we just don't know enough about your tables and how selective your indexes are etc. so any suggestion will be more or less guesswork. Set [Non-text portions of this message have been removed] ++ 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: http://docs.yahoo.com/info/terms/
[firebird-support] Force query plan to filter before join
Hello, I need help optimizing the query shown below. PROJECT-related tables contain 12 rows each while COPY-related tables contain 14K rows each. Moreover, PROJECT.ASSIGNMENT_STATUS != 'UNASSIGNED' filters out all but 10 joined rows. My question is how do I change the plan to force PROJECT.ASSIGNMENT_STATUS != 'UNASSIGNED' filter to be applied before the join? Thanks! Preparing query: SELECT PROJECT.PRIMARY_PROJECT_CODE AS COL0, COUNT(PHYSICAL_COPY.ID) AS COL1 FROM PROJECT_CODE_DESCRIPTOR INNER JOIN PROJECT ON PROJECT_CODE_DESCRIPTOR.PROJECT_ID = PROJECT.ID INNER JOIN COPY_CLASSIFICATION ON PROJECT_CODE_DESCRIPTOR.PROJECT_CODE = COPY_CLASSIFICATION.CLASSIFICATION_CODE INNER JOIN COPY ON COPY_CLASSIFICATION.COPY_ID = COPY.ID INNER JOIN PHYSICAL_COPY ON COPY.ID = PHYSICAL_COPY.COPY_ID WHERE ( (PROJECT.ASSIGNMENT_STATUS != 'UNASSIGNED') and (PHYSICAL_COPY.IS_MARKED_DELETED = 0) AND (PHYSICAL_COPY.IS_RECYCLED = 0) ) GROUP BY PROJECT.PRIMARY_PROJECT_CODE Prepare time: 0.004s Field #01: . Alias:COL0 Type:STRING(256) Field #02: . Alias:COL1 Type:INTEGER PLAN SORT (JOIN (PHYSICAL_COPY INDEX (IDX_sKfIZDMkd26ITRo1Da5mzQ==), COPY_CLASSIFICATION INDEX (IDX_soqMJd+Yux0RNvCbmE9rrg==), COPY INDEX (PK_ZM6SRonqR8AHSQuCISgvnQ==), PROJECT_CODE_DESCRIPTOR INDEX (IDX_lEwvSCR+VZpQCfw5Duxo0A==), PROJECT INDEX (PK_f3m9slJ+02gL6hFClhrZvg==))) Executing... Done. 278233 fetches, 0 marks, 16305 reads, 0 writes. 0 inserts, 0 updates, 0 deletes, 69581 index, 0 seq. Delta memory: -3956 bytes. Total execution time: 1.243s Script execution finished.
