[firebird-support] Re: FB does table scan as soon as I use left outer join in view - why?
21.08.2015 12:14, 'Louis van Alphen' wrote: Dmitry thanks for the insight. This actual puts FB in quite a different light. I have used FB since IB 5 and is my DB of choice. However this issue may prove quite problematic. It does not cover all cases with views, only the ones when you need to filter the view using another table (e.g. with IDs to be retrieved). I have 2 options: change to another DB or have a major architectural refactor. 3) use the trick I suggested 4) use subselects instead of left joins for lookups inside views 5) sponsor optimizer improvement May be other workarounds are also possible. Dmitry
[firebird-support] Re: FB does table scan as soon as I use left outer join in view - why?
21.08.2015 13:58, 'Louis van Alphen' wrote: Dmitry, re your point 4, do you mean: create view SKIN as select ID, (select NAME from COLOUR_ C where C.ID = S.COLOUR_ID) from SKIN_ S; Right. Not very good option when you need many lookup fields from the same table, but could still be better than a fullscan inside join plans. Dmitry
RE: [firebird-support] Regarding ODBC
32-bit Classic, Superclassic Superserver March 30, 2015 Firebird-2.5.4.26856_0_Win32.exehttp://sourceforge.net/projects/firebird/files/firebird-win32/2.5.4-Release/Firebird-2.5.4.26856_0_Win32.exe/download 7 MB Windows executable installer for full Superclassic/Classic or Superserver, recommended for Nick - I downloaded this where the Powercenter Client is running. Are you saying I need this downloaded even on Powercenter Server side? Anitha Raman Global Data Integration Lead anitha.ra...@am.jll.commailto:anitha.ra...@am.jll.com From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] Sent: Friday, August 21, 2015 9:19 AM To: firebird support Subject: Re: [firebird-support] Regarding ODBC you need to install the firebird client on the PC Nick Upson, Telensa Ltd, Senior Operations Network Engineer direct +44 (0) 1799 533252, support hotline +44 (0) 1799 399200 On 21 August 2015 at 15:14, 'Raman, Anitha' anitha.ra...@am.jll.commailto:anitha.ra...@am.jll.com [firebird-support] firebird-support@yahoogroups.commailto:firebird-support@yahoogroups.com wrote: Thank you. Error Message is : ‘Failed to load PowerMart DLL fbclientM.dll to support this database. Contact Tech Support.’ Anitha Raman Global Data Integration Lead anitha.ra...@am.jll.commailto:anitha.ra...@am.jll.com From: firebird-support@yahoogroups.commailto:firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.commailto:firebird-support@yahoogroups.com] Sent: Friday, August 21, 2015 12:56 AM To: firebird-support@yahoogroups.commailto:firebird-support@yahoogroups.com Subject: Re: [firebird-support] Regarding ODBC The mailinglist doesn't support attachments, please post the error message as text. Mark - Reply message - Van: 'Raman, Anitha' anitha.ra...@am.jll.commailto:anitha.ra...@am.jll.com [firebird-support] firebird-support@yahoogroups.commailto:firebird-support@yahoogroups.com Aan: firebird-support@yahoogroups.commailto:firebird-support@yahoogroups.com Onderwerp: [firebird-support] Regarding ODBC Datum: do, aug. 20, 2015 23:19 Hello group - I have installed Firebird and trying to import structures using an ETL Application called Informatica PowerCenter. ODBC connection seems to be valid as I am able to connect using EXCEL and select from the tables. But when I try using my application to import structures I get the following error. Does anyone have experience configuring Firebird ODBC for Informatica? Thank you! [cid:image001.png@01D0DB64.0D85CEF0] Anitha Raman Global Data Integration Lead anitha.ra...@am.jll.commailto:anitha.ra...@am.jll.commailto:anitha.ra...@am.jll.com%3cmailto:anitha.ra...@am.jll.com This email is for the use of the intended recipient(s) only. If you have received this email in error, please notify the sender immediately and then delete it. If you are not the intended recipient, you must not keep, use, disclose, copy or distribute this email without the author's prior permission. We have taken precautions to minimize the risk of transmitting software viruses, but we advise you to carry out your own virus checks on any attachment to this message. We cannot accept liability for any loss or damage caused by software viruses. The information contained in this communication may be confidential and may be subject to the attorney-client privilege. If you are the intended recipient and you do not wish to receive similar electronic messages from us in the future then please respond to the sender to this effect. [Non-text portions of this message have been removed] Posted by: Raman, Anitha anitha.ra...@am.jll.commailto:anitha.ra...@am.jll.com ++ 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 http://groups.yahoo.com/group/firebird-support/ Individual Email | Traditional http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) firebird-support-dig...@yahoogroups.commailto:firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.commailto:firebird-support-fullfeatu...@yahoogroups.com firebird-support-unsubscr...@yahoogroups.commailto:firebird-support-unsubscr...@yahoogroups.com https://info.yahoo.com/legal/us/yahoo/utos/terms/
[firebird-support] Re: Differences when adding a Primary Key
Nobody knows? Nobody has any idea? Greetings. Walter. On Thu, Aug 20, 2015 at 10:45 AM, Walter R. Ojeda Valiente sistemas2000profesio...@gmail.com wrote: Hello everybody For add a Primary Key to a table we can write: ALTER TABLE MyTable ADD PRIMARY KEY (ID); or we can write: ALTER TABLE MyTable ADD CONSTRAINT MyPK PRIMARY KEY (ID); In the first case, the Firebird puts the name of the Primary Key, in the second case we choose that name, but... Why sometimes the first case fails and the second case always work? Of course that the table has not a Primary Key yet and the ID column has not nulls neither duplicated values. But (just sometimes) the first case fails. Greetings. Walter.
Re: [firebird-support] Join to same table twice - or joins vs subqueries
select r.id, (select a.node from nodes a where r.node_id = a.id) node, route_index, (select b.node from nodes b where r.link_id = b.id) link_node, (select c.quality_label from route_qualities c where r.quality = c.QUALITY) from routes r where exists(select * from nodes a where r.node_id = a.id) and exists(select * from nodes b where r.link_id = b.id) and exists(select * from route_qualities c where r.quality = c.QUALITY) order by 2, 3; Why apparent duplication of the where clauses? Everything from select r.id to from routes r makes sense to me - I don't understand the reason for the whole where exists section. Simple Daniel. When using [INNER] JOIN, you eliminate all rows of ROUTES that does not match a row in NODES or ROUTE_QUALITIES. With subselects without EXISTS, even rows where node_id IS NULL will be returned. Without EXISTS, the subselects would be equal to LEFT JOIN. If subselects don't offer a performance advantage, and since I find them far more confusing than join syntax - I guess I'll forget about them until I have a problem they're designed to solve. Keeping things simple is a good idea. I very rarely use subselects myself, I more often use CTEs (common table expressions) or EXECUTE BLOCK to simplify (or make possible or quicker) complex queries. Set ++ 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] Re: Differences when adding a Primary Key
Hello Ann Well, the world has not falled out, nothing so dangerous, the error message is: Cannot commit transaction: The insert failed because a column definition includes validation constraints. validation error for column ID, value *** null ***. The question is: Why without using CONSTRAINT appears that message but using CONSTRAINT all works fine? And there are not row/s with a NULL value in the column ID. At least, they are not showed with the following query: SELECT * FROM MyTable WHERE ID IS NULL So, it seems very strange to me. The logic for me is: both works or both fails, but why one fails and the other works? Greetings. Walter. On Fri, Aug 21, 2015 at 2:43 PM, Ann Harrison aharri...@ibphoenix.com [firebird-support] firebird-support@yahoogroups.com wrote: On Aug 21, 2015, at 2:13 PM, 'Walter R. Ojeda Valiente' sistemas2000profesio...@gmail.com [firebird-support] firebird-support@yahoogroups.com wrote: Without using CONSTRAINT doesn't work and in such case the name is choosed by Firebird, not for me. For add a Primary Key to a table we can write: ALTER TABLE MyTable ADD PRIMARY KEY (ID); or we can write: ALTER TABLE MyTable ADD CONSTRAINT MyPK PRIMARY KEY (ID); In the first case, the Firebird puts the name of the Primary Key, in the second case we choose that name, but... Why sometimes the first case fails and the second case always work? But (just sometimes) the first case fails. How does it fail? What's the error message? Or does it kill the connection? Crash the server? Freeze the O/S? Set the machine on fire? Cheers, Ann
RE: [firebird-support] RETURNING_VALUES optional variables?
Jorge, I have done a SELECT FROM instead of an EXECUTE PROCEDURE when I do not need all the fields returned in a stored procedure. This will only work I think if the SP has a SUSPEND; in it. SELECT R_PRINC_BAL, R_FEE_BAL FROM SPS_CASE_CUR_BAL(:ACCT_ID, :CASE_ID, NULL, 1) INTO :PRINCIPAL_BAL, :FEE_BAL; Mike From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] Sent: Friday, August 21, 2015 1:08 PM To: firebird-support@yahoogroups.com Subject: [firebird-support] RETURNING_VALUES optional variables? I´m calling a stored procedure (SP1) from inside another SP (SP2). SP2 has three output vars, but when calling it from SP1 I only need one value. It's possible to omit 2nd and 3rd parameters when using EXECUTE PROCEDURE? (Right now I´m using SP1 local variables whose values are never used). Thanks! -- Jorge Andrés Brugger Departamento de Informática DASU - Obra Social del Personal de la Universidad Nacional de la Patagonia Comodoro Rivadavia, Chubut, Argentina Teléfono (0297) 446- int. 103 Correo electrónico: jbrug...@dasu.com.ar Website: www.dasu.com.ar Aquel que tiene una opinión de sí mismo, pero depende de la opinión y los gustos de los demás, es un esclavo (Friedrich Gottlieb Klopstock) -- Antes de imprimir este mensaje, piense si es verdaderamente necesario hacerlo. [Non-text portions of this message have been removed]
Re: [firebird-support] Re: Differences when adding a Primary Key
hi. Maybe you post an ilegal name or this name are not unique for entery database objects name's. every object in firebird database must be unique. no matters if the same name is used on diferent tables. On Fri, Aug 21, 2015 at 12:24 PM, 'Walter R. Ojeda Valiente' sistemas2000profesio...@gmail.com [firebird-support] firebird-support@yahoogroups.com wrote: Nobody knows? Nobody has any idea? Greetings. Walter. On Thu, Aug 20, 2015 at 10:45 AM, Walter R. Ojeda Valiente sistemas2000profesio...@gmail.com wrote: Hello everybody For add a Primary Key to a table we can write: ALTER TABLE MyTable ADD PRIMARY KEY (ID); or we can write: ALTER TABLE MyTable ADD CONSTRAINT MyPK PRIMARY KEY (ID); In the first case, the Firebird puts the name of the Primary Key, in the second case we choose that name, but... Why sometimes the first case fails and the second case always work? Of course that the table has not a Primary Key yet and the ID column has not nulls neither duplicated values. But (just sometimes) the first case fails. Greetings. Walter. -- Ricardo...
Re: [firebird-support] Re: Differences when adding a Primary Key
Hello Ricardo Thank you for your answer, but that is not the problem. Without using CONSTRAINT doesn't work and in such case the name is choosed by Firebird, not for me. Greetings. Walter. On Fri, Aug 21, 2015 at 1:43 PM, Ricardo Uzcategui ricardou...@cantv.net [firebird-support] firebird-support@yahoogroups.com wrote: hi. Maybe you post an ilegal name or this name are not unique for entery database objects name's. every object in firebird database must be unique. no matters if the same name is used on diferent tables. On Fri, Aug 21, 2015 at 12:24 PM, 'Walter R. Ojeda Valiente' sistemas2000profesio...@gmail.com [firebird-support] firebird-support@yahoogroups.com wrote: Nobody knows? Nobody has any idea? Greetings. Walter. On Thu, Aug 20, 2015 at 10:45 AM, Walter R. Ojeda Valiente sistemas2000profesio...@gmail.com wrote: Hello everybody For add a Primary Key to a table we can write: ALTER TABLE MyTable ADD PRIMARY KEY (ID); or we can write: ALTER TABLE MyTable ADD CONSTRAINT MyPK PRIMARY KEY (ID); In the first case, the Firebird puts the name of the Primary Key, in the second case we choose that name, but... Why sometimes the first case fails and the second case always work? Of course that the table has not a Primary Key yet and the ID column has not nulls neither duplicated values. But (just sometimes) the first case fails. Greetings. Walter. -- Ricardo...
[firebird-support] RETURNING_VALUES optional variables?
I´m calling a stored procedure (SP1) from inside another SP (SP2). SP2 has three output vars, but when calling it from SP1 I only need one value. It's possible to omit 2nd and 3rd parameters when using EXECUTE PROCEDURE? (Right now I´m using SP1 local variables whose values are never used). Thanks! -- Jorge Andrés Brugger Departamento de Informática DASU - Obra Social del Personal de la Universidad Nacional de la Patagonia Comodoro Rivadavia, Chubut, Argentina Teléfono (0297) 446- int. 103 Correo electrónico: jbrug...@dasu.com.ar Website: www.dasu.com.ar Aquel que tiene una opinión de sí mismo, pero depende de la opinión y los gustos de los demás, es un esclavo (Friedrich Gottlieb Klopstock) -- Antes de imprimir este mensaje, piense si es verdaderamente necesario hacerlo.
Re: [firebird-support] Re: Differences when adding a Primary Key
On Aug 21, 2015, at 2:13 PM, 'Walter R. Ojeda Valiente' sistemas2000profesio...@gmail.com [firebird-support] firebird-support@yahoogroups.com wrote: Without using CONSTRAINT doesn't work and in such case the name is choosed by Firebird, not for me. For add a Primary Key to a table we can write: ALTER TABLE MyTable ADD PRIMARY KEY (ID); or we can write: ALTER TABLE MyTable ADD CONSTRAINT MyPK PRIMARY KEY (ID); In the first case, the Firebird puts the name of the Primary Key, in the second case we choose that name, but... Why sometimes the first case fails and the second case always work? But (just sometimes) the first case fails. How does it fail? What's the error message? Or does it kill the connection? Crash the server? Freeze the O/S? Set the machine on fire? Cheers, Ann
[firebird-support] Re: FB does table scan as soon as I use left outer join in view - why?
Something like: select S.* from COLLECTION_ COLL left join SKIN S on S.ID = COLL.SKIN_ID where S.ID is not null i.e. fake the left join to get the correct join order (COLLECTION_-SKIN_-COLOUR_). I guess that means that select S.* from COLLECTION_ COLL join SKIN S on S.ID = COLL.SKIN_ID+0 often will be an alternative? I.e. put the view containing the LEFT JOIN at the bottom of your query and make sure that no index can be used for the other side of the JOIN? Though if your query contains two or more such VIEWs, I guess there's no remedy short of sponsoring Firebird and wait a couple of years. Set
RE: [firebird-support] Re: FB does table scan as soon as I use left outer join in view - why?
Dmitry thanks for the insight. This actual puts FB in quite a different light.
I have used FB since IB 5 and is my DB of choice. However this issue may prove
quite problematic. Let me explain:
I have a large ERP class system ( currently ~400+ tables). An architectural
design choice in the beginning was to abstract tables with views. So if I need
to store a Customer object, I will have table CUSTOMER_ and then on top a view
called CUSTOMER. Doing this, I have several benefits:
- This allows me to control access better than granting access to
table level. At least that is my thinking. People accessing the DB cannot
modify data if they don’t have table access, but can access ‘rich’ views to
extract data
- It allows the ‘flattening out’ of an entity when selecting it from
the DB. E.g. the CUSTOMER view will incluce the currency code from the
CURRENCY_ table etc. So when my services layer returns the Customer object to
the client, the CurrencyCode field is populated and is ready for presentation.
I don’t need multiple calls to the DB to fetch the lookup values to populate
the Customer object. One of the big tables have 115 columns with about 50 of
them lookups.
- Views also allow me to easily implement multi-tenancy on a DB level.
Therefore the DB will partition the various tenants’ data.
This problem now causes, in some queries, very inefficient retrieval of data
where I did not expect it to happen. So I have 2 options: change to another DB
or have a major architectural refactor.
Regards
Louis
From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com]
Sent: 21 August 2015 10:19 AM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Re: FB does table scan as soon as I use left outer
join in view - why?
21.08.2015 10:26, 'Louis van Alphen' wrote:
Dmitry, if I then understand you correctly, if a view contains an outer join,
then FB will table scan? Is this documented somewhere so that I can read up?
It depends on a number of factors, but generally your conclusion is
correct. The problem is that the join order always starts with a view
with a joined table coming afterwards.
This is a known issue, but unfortunately hard to fix.
Sure, I look at the plan, but the plan is after the fact. It does not show
you why?
Right, you cannot see why the optimizer does this or that choice, you
see only the resulting plan.
Something like:
select S.*
from COLLECTION_ COLL
left join SKIN S on S.ID = COLL.SKIN_ID
where S.ID is not null
i.e. fake the left join to get the correct join order
(COLLECTION_-SKIN_-COLOUR_).
Not sure what you are doing here and what the where clause does. Are these
tricks documented somewhere?
This is a quite common trick. For inner joins, possible join orders are
either {COLL-SKIN} or {SKIN-COLL}. We want the join order to be
{COLL-SKIN} but the optimizer decides differently and chooses
{SKIN-COLL}. For outer joins, however, the join order is always
predefined and dictated by the join syntax. So we replace inner join
with left join to guarantee the desired join order {COLL-SKIN}. But we
need to exclude the false rows produced by the outer join (records
from COLL having no matches in SKIN), so we add a WHERE clause -- S.ID
is not null -- to remove those unnecessary rows (I assume S.ID is a
primary key and thus it should never be NULL unless produced by the left
join).
Dmitry
[Non-text portions of this message have been removed]
[firebird-support] Re: FB does table scan as soon as I use left outer join in view - why?
21.08.2015 10:26, 'Louis van Alphen' wrote:
Dmitry, if I then understand you correctly, if a view contains an outer join,
then FB will table scan? Is this documented somewhere so that I can read up?
It depends on a number of factors, but generally your conclusion is
correct. The problem is that the join order always starts with a view
with a joined table coming afterwards.
This is a known issue, but unfortunately hard to fix.
Sure, I look at the plan, but the plan is after the fact. It does not show
you why?
Right, you cannot see why the optimizer does this or that choice, you
see only the resulting plan.
Something like:
select S.*
from COLLECTION_ COLL
left join SKIN S on S.ID = COLL.SKIN_ID
where S.ID is not null
i.e. fake the left join to get the correct join order
(COLLECTION_-SKIN_-COLOUR_).
Not sure what you are doing here and what the where clause does. Are these
tricks documented somewhere?
This is a quite common trick. For inner joins, possible join orders are
either {COLL-SKIN} or {SKIN-COLL}. We want the join order to be
{COLL-SKIN} but the optimizer decides differently and chooses
{SKIN-COLL}. For outer joins, however, the join order is always
predefined and dictated by the join syntax. So we replace inner join
with left join to guarantee the desired join order {COLL-SKIN}. But we
need to exclude the false rows produced by the outer join (records
from COLL having no matches in SKIN), so we add a WHERE clause -- S.ID
is not null -- to remove those unnecessary rows (I assume S.ID is a
primary key and thus it should never be NULL unless produced by the left
join).
Dmitry
