[firebird-support] LEFT JOIN much faster than JOIN
Hi! For a long time I have been using LEFT JOINs rather than JOINs everywhere, because of some past experience with Firebird 1.5 that LEFT JOIN is much faster than JOIN, even if I have to use WHERE key_of_the_joined_table IS NULL afterwards. Today I encountered a case where this is the case even with both Firebird 2.1 and 2.5. What could possibly be the cause? Should I stick to LEFT JOINs? The query is: SELECT MAX(date_of_operation) FROM table1 [LEFT] JOIN view1 ON view1.foreign_key=table1.primary_key WHERE table1.some_field=1234 with view1 defined as a simple sequence of LEFT JOINs: SELECT ... FROM table2 LEFT JOIN table3 ON table3.primary_key=table2.foreign_key1 LEFT JOIN table4 ON table4.primary_key=table2.foreign_key2 ... LEFT JOIN tableX ON tableX.primary_key=table4.foreign_key The LEFT JOIN version takes some milliseconds to perform, the JOIN version takes over 25 seconds. The difference in plans is, as far as I can tell, that the LEFT JOIN version first selects the relevant records from table1 and then joins the matching records from view1 to it, while the JOIN version first builds the view1 (all records), then joins table1 to it, and then performs the WHERE: LEFT JOIN: PLAN JOIN (table1 INDEX (index_fk1), JOIN(JOIN(...))) JOIN: PLAN JOIN(JOIN(JOIN(...)), table1 INDEX (index_pk)) (Note: all the joins are indexed) In this case I will simply rewrite the query to use the LEFT JOIN and probably keep using LEFT JOINs in all future queries, even if the JOIN might seem more suitable to the query, but I am really curious why is there such a difference in the optimizer. Thanks, Josef ++ 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] Threading using multiple connections
Hi all, Can someone let me know if the following is thread-safe: - a client library from Firebird version 1, 1.5, 2 or 2.1 - using a connection per thread - executing calls via multiple threads 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!
[firebird-support] Re: Threading using multiple connections
--- In firebird-support@yahoogroups.com, Martijn Tonies wrote: Hi all, Can someone let me know if the following is thread-safe: - a client library from Firebird version 1, 1.5, 2 or 2.1 - using a connection per thread - executing calls via multiple threads Add also: protect attach\detach calls with common mutex. Regards, Vlad PS v2.5 client should be free from the limitations above, you surely know it :)
Re: [firebird-support] Re: Threading using multiple connections
Hi Vlad, Can someone let me know if the following is thread-safe: - a client library from Firebird version 1, 1.5, 2 or 2.1 - using a connection per thread - executing calls via multiple threads Add also: protect attach\detach calls with common mutex. You mean that connect/disconnect cannot be done while another thread is already waiting for the API to return, right? Regards, Vlad PS v2.5 client should be free from the limitations above, you surely know it :) Yes. 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!
[firebird-support] firebird computed field
whether the computed field affect the performance and speed of the table? simple example : table x value1 integer value2 integer total computed(value1+value2) table y value1 integer value2 integer query1 select * from x select total from x query2 select * from y select value1+value2 as total from y Which is better performance and speed? table x or table y ? [Non-text portions of this message have been removed]
Re: [firebird-support] LEFT JOIN much faster than JOIN
Hello Josef, I've never had that issue using Firebird, but some time ago I had a similar behavior with SQL Server and the responsible was a fragmented index. May be you should take a look to your indexes design or rebuild them via backup/restore. Regards, De: Josef Kokeš j.ko...@apatykaservis.cz Para: Firebird Support Firebird-Support@yahoogroups.com Enviado: Viernes, 2 de agosto, 2013 1:43:35 Asunto: [firebird-support] LEFT JOIN much faster than JOIN Hi! For a long time I have been using LEFT JOINs rather than JOINs everywhere, because of some past experience with Firebird 1.5 that LEFT JOIN is much faster than JOIN, even if I have to use WHERE key_of_the_joined_table IS NULL afterwards. Today I encountered a case where this is the case even with both Firebird 2.1 and 2.5. What could possibly be the cause? Should I stick to LEFT JOINs? The query is: SELECT MAX(date_of_operation) FROM table1 [LEFT] JOIN view1 ON view1.foreign_key=table1.primary_key WHERE table1.some_field=1234 with view1 defined as a simple sequence of LEFT JOINs: SELECT ... FROM table2 LEFT JOIN table3 ON table3.primary_key=table2.foreign_key1 LEFT JOIN table4 ON table4.primary_key=table2.foreign_key2 ... LEFT JOIN tableX ON tableX.primary_key=table4.foreign_key The LEFT JOIN version takes some milliseconds to perform, the JOIN version takes over 25 seconds. The difference in plans is, as far as I can tell, that the LEFT JOIN version first selects the relevant records from table1 and then joins the matching records from view1 to it, while the JOIN version first builds the view1 (all records), then joins table1 to it, and then performs the WHERE: LEFT JOIN: PLAN JOIN (table1 INDEX (index_fk1), JOIN(JOIN(...))) JOIN: PLAN JOIN(JOIN(JOIN(...)), table1 INDEX (index_pk)) (Note: all the joins are indexed) In this case I will simply rewrite the query to use the LEFT JOIN and probably keep using LEFT JOINs in all future queries, even if the JOIN might seem more suitable to the query, but I am really curious why is there such a difference in the optimizer. Thanks, Josef ++ 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 [Non-text portions of this message have been removed]
Re: [firebird-support] LEFT JOIN much faster than JOIN
Hello Josef, I've never had that issue using Firebird, but some time ago I had a similar behavior with SQL Server and the responsible was a fragmented index. May be you should take a look to your indexes design or rebuild them via backup/restore. Hi! That should not be the case here, as all my tests were performed right after a backup/restore cycle. Josef
Re: [firebird-support] LEFT JOIN much faster than JOIN
but LEFT JOIN is diferente of JOIN... - LEFT JOIN brings all the data filtered from the FROM table, and - JOIN brings only the data that have the ON condition in the JOINED table. Att, Alexis Diel On Fri, Aug 2, 2013 at 3:43 AM, Josef Kokeš j.ko...@apatykaservis.czwrote: Hi! For a long time I have been using LEFT JOINs rather than JOINs everywhere, because of some past experience with Firebird 1.5 that LEFT JOIN is much faster than JOIN, even if I have to use WHERE key_of_the_joined_table IS NULL afterwards. Today I encountered a case where this is the case even with both Firebird 2.1 and 2.5. What could possibly be the cause? Should I stick to LEFT JOINs? The query is: SELECT MAX(date_of_operation) FROM table1 [LEFT] JOIN view1 ON view1.foreign_key=table1.primary_key WHERE table1.some_field=1234 with view1 defined as a simple sequence of LEFT JOINs: SELECT ... FROM table2 LEFT JOIN table3 ON table3.primary_key=table2.foreign_key1 LEFT JOIN table4 ON table4.primary_key=table2.foreign_key2 ... LEFT JOIN tableX ON tableX.primary_key=table4.foreign_key The LEFT JOIN version takes some milliseconds to perform, the JOIN version takes over 25 seconds. The difference in plans is, as far as I can tell, that the LEFT JOIN version first selects the relevant records from table1 and then joins the matching records from view1 to it, while the JOIN version first builds the view1 (all records), then joins table1 to it, and then performs the WHERE: LEFT JOIN: PLAN JOIN (table1 INDEX (index_fk1), JOIN(JOIN(...))) JOIN: PLAN JOIN(JOIN(JOIN(...)), table1 INDEX (index_pk)) (Note: all the joins are indexed) In this case I will simply rewrite the query to use the LEFT JOIN and probably keep using LEFT JOINs in all future queries, even if the JOIN might seem more suitable to the query, but I am really curious why is there such a difference in the optimizer. Thanks, Josef ++ 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 [Non-text portions of this message have been removed]
Re: [firebird-support] LEFT JOIN much faster than JOIN
On 2.8.2013 13:59, Alexis Diel wrote: but LEFT JOIN is diferente of JOIN... - LEFT JOIN brings all the data filtered from the FROM table, and - JOIN brings only the data that have the ON condition in the JOINED table. Which should make JOIN faster than LEFT JOIN, if anything, because it can (potentionally) filter out some records. Josef
Re: [firebird-support] LEFT JOIN much faster than JOIN
But LEFT OUTER JOIN may cause the optimizer to scan through the table for which all rows are being included in physical order while the INNER JOIN might cause a plan where rows are read based on a scan through an index. If rows aren't being filtered out, I can see how this might speed up the query execution. Louis Kleiman On Fri, Aug 2, 2013 at 8:04 AM, Josef Kokeš j.ko...@apatykaservis.czwrote: ** On 2.8.2013 13:59, Alexis Diel wrote: but LEFT JOIN is diferente of JOIN... - LEFT JOIN brings all the data filtered from the FROM table, and - JOIN brings only the data that have the ON condition in the JOINED table. Which should make JOIN faster than LEFT JOIN, if anything, because it can (potentionally) filter out some records. Josef [Non-text portions of this message have been removed]
Re: [firebird-support] LEFT JOIN much faster than JOIN
On Aug 2, 2013, at 11:25 AM, Louis Kleiman (SSTMS, Inc.) lklei...@sstms.com wrote: But LEFT OUTER JOIN may cause the optimizer to scan through the table for which all rows are being included in physical order while the INNER JOIN might cause a plan where rows are read based on a scan through an index. If rows aren't being filtered out, I can see how this might speed up the query execution. but LEFT JOIN is diferente of JOIN... - LEFT JOIN brings all the data filtered from the FROM table, and - JOIN brings only the data that have the ON condition in the JOINED table. Which should make JOIN faster than LEFT JOIN, if anything, because it can (potentionally) filter out some records. To understand what's happening, you must look at the plan for the join. An outer join forces the order of the operation - outer table first, then inner table. Depending on the indexes and indexed conjuncts, the order you set in the outer join may lead to a fast join, or not. If outer joins work well for you, I'd guess that you know your data and that (maybe) the statistics on your indexes are bad. In theory, the optimizer should choose the best order for inner joins, but if you have better information than it does, outer joins will be faster. Outer joins do not imply full table scans in physical order. Conditions in the WHERE clause that apply to the outer table and involve indexed terms are applied first. In this query select custName from Customers C left outer join Orders O on O.custID = C.custID where C.state = 'CT' and C.lastName = 'Smith' the optimizer will use the index on C.state and the index on C.lastName to prequalify Customers. Good luck, Ann commercial note(if you need more than luck, try IBPhoenix)/commercial note
[firebird-support] Re: LEFT JOIN much faster than JOIN
I can be wrong, but should make (JOIN) faster than (LEFT JOIN) only if the joined condition is using index of the joined table. I think that if u don't have a index for the joined condition it need to seek in the whole table to filter. In theory should not be difference between (JOIN) and (LEFT JOIN), if is used the same condition, the response brought the (FROM) table is the same, only filtering when the JOIN info is NULL or NOT. sorry for the my awful english... --- In firebird-support@yahoogroups.com, Josef Kokeš j.kokes@... wrote: On 2.8.2013 13:59, Alexis Diel wrote: but LEFT JOIN is diferente of JOIN... - LEFT JOIN brings all the data filtered from the FROM table, and - JOIN brings only the data that have the ON condition in the JOINED table. Which should make JOIN faster than LEFT JOIN, if anything, because it can (potentionally) filter out some records. Josef
Re: [firebird-support] firebird computed field
-- Original Message --- From: Wewe sullen...@yahoo.com select * from y select value1+value2 as total from y Which is better performance and speed? table x or table y ? --- End of Original Message --- I've never noticed a difference in performance at select time. I think the main reason for picking computed-by columns is that the same formula gets used everywhere, is accessible even inside triggers, and can be changed in a single place, leading to fewer coding mistakes. I *think* you can grant column-level select privileges such that the underlying (value1, value2) fields are hidden, but the computation (total) is not, depending on the user. That could be useful for last-four-digits, etc. situations. Using computed-by columns is easier than creating a separate view, for which you have to manage permissions and possibly triggers (to make it updateable, depending on the exact view definition) -- that's the solution used in Oracle. While I was an Oracle dev, I missed being able to just add computed columns. I would suggest that it also makes it really easy to use computed-by indices, but CORE-1212 and CORE-1173 are still open. You can however create a separate index on the computation (repeat the expression), and FB should use the index when you filter by the computed-by column, as if you had typed it out. At least you still have the advantage that by using the computed-by column, if its expression exactly matches that of the index you create, anyone using the table and filtering/sorting by that computed column should get the expected indexing automatically; if done by hand, there's always a chance someone will write the expression out differently in their SQL and the optimizer won't see the index as being useful (on top of them possibly writing it out wrong.) -Philip
[firebird-support] Re: Threading using multiple connections
--- In firebird-support@yahoogroups.com, Martijn Tonies wrote: Hi Vlad, Can someone let me know if the following is thread-safe: - a client library from Firebird version 1, 1.5, 2 or 2.1 - using a connection per thread - executing calls via multiple threads Add also: protect attach\detach calls with common mutex. You mean that connect/disconnect cannot be done while another thread is already waiting for the API to return, right? It can be done of course, but there were reports about deadlocks in fbclient when few threads executed attach\detach concurrently. Note, you need to serialize attach\detach API's only, all other calls is safe (as long as you follow thread per attachment rule). Hope it is clear now, Vlad
