Re: [firebird-support] Copy Table to Another Database

2017-11-21 Thread LtColRDSChauhan rdsc1...@gmail.com [firebird-support] 
On Tue, Nov 21, 2017 at 10:33 PM, Dimitry Sibiryakov s...@ibphoenix.com
[firebird-support]  wrote:

> 21.11.2017 15:52, LtColRDSChauhan rdsc1...@gmail.com [firebird-support]
> wrote:
> > Please see if the under mentioned procedure is good enough:
>
>I would do the contrary: select on external source and local insert. It
> is better for
>

I could not frame select on external source and local insert. Can you
please help here.


> performance. But if your SP work for you, it is good.
>

My procedure worked with good performance. I had to amend a bit: the number
of ?s passed as place holders in the values should be equal to the number
of fields.
Just in case some one else needs this, I please put the correct procedure
for records:

SET TERM ^ ;
ALTER PROCEDURE COPY_theTABLE
AS
declare variable FIELD_1 varchar(80);
declare variable FIELD_2 varchar(80);
BEGIN
for select p.FIELD_1, p.FIELD_2 from the_TABLE into :FIELD_1, :FIELD_2
do execute statement
('insert into the_TABLE values(?, ?)') /* the number of ?s passed
as place holders in the values should be equal to the number of fields */
(:FIELD_1, :FIELD_2)
ON EXTERNAL 'ExternalServer:C:\External_Target.FDB'
AS USER 'SYSDBA' PASSWORD 'masterkey';
END^
SET TERM ; ^

>
>
> --
>WBR, SD.
>
>
> 
>
> 
>
> ++
>
> 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
>
>
>
>

[firebird-support] Problem with computed field privileges

2017-11-21 Thread Vígh László vigh.las...@mve.hu [firebird-support] 
Hello,

Our Firebird version: 2.5

We are trying to set up a ROLE in our database so that it only has access to a 
set of views, and can only update tables through these views with triggers.

The problem is that we have a parent table (A) and a child table (B). Both have 
an X named column.
The 'B' table has this X column as a computed field, calculated by a select 
from its parent records X column.
When we try to select from the view that uses the 'B' table, the program throws 
an error that there is no permission for read/select access to TABLE 'A'.

It is certainly caused by the computed field because when we changed the 
calculation to another one without a select,
the program works. We did not find an option to grant select privilage for 'B' 
table to 'A' table.
We tried replacing the computed field definition to a select from the view 
(which the role will have permission to) that has acces to the desired field 
but according to the plan, it will use every table from the view, even when the 
main table of that view is 'A',
and that would impact performance more than we'd like.

Is there a way to give permission for this computed field in table B to read 
from table 'A'?

Thanks!




__ Information from ESET Mail Security, version of virus signature 
database 16446 (20171121) __

The message was checked by ESET Mail Security.
http://www.eset.com

Re: [firebird-support] Copy Table to Another Database

2017-11-21 Thread Dimitry Sibiryakov s...@ibphoenix.com [firebird-support] 
21.11.2017 15:52, LtColRDSChauhan rdsc1...@gmail.com [firebird-support] wrote:
> Please see if the under mentioned procedure is good enough:

   I would do the contrary: select on external source and local insert. It is 
better for 
performance. But if your SP work for you, it is good.


-- 
   WBR, SD.






++

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: Performance problem - input wanted

2017-11-21 Thread michael.vilhelm...@microcom.dk [firebird-support] 
Try changing to Afdeling_ID||'' (or Afdeling_ID+0 if it is a number) in your 
trigger. That should make RDB$FOREIGN105 useless for the query.  I did know 
about this.
 But I can see now, that there are other querys around the program, which has 
the same WHERE clause, which causes the same PLAN.
 And sine the foreign key only references 58 records (and will never reference 
more than a few hundred), and the table contains millions of records, I think 
its better to remove the foreign key.
 

 I think I recall something about to few unique values will render the index / 
foreign key useless. 
 

 

 Though this kind of trigger that finds the MAX value in huge tables (although 
there's only 750 occurences of each VAREPLU_ID on average, I guess there are 
some values that are used a lot more) is a source for inserts being slow as 
well as potentially error prone (in case of concurrent inserts, the same values 
would be returned for both rows, which may or may not be what you want).

 I know that now :)
 But sinse this the code below this trigger is only used by a few customers, I 
will remove the trigger and do this elsewhere, so not all will be affected by 
this.

Re: [firebird-support] Copy Table to Another Database

2017-11-21 Thread LtColRDSChauhan rdsc1...@gmail.com [firebird-support] 
On Tue, Nov 21, 2017 at 4:12 PM, Dimitry Sibiryakov s...@ibphoenix.com
[firebird-support]  wrote:

> 21.11.2017 10:01, LtColRDSChauhan rdsc1...@gmail.com [firebird-support]
> wrote:
> > How can I copy all records of a table to another table in different
> database. The tables
> > having same design.
>
>Use "execute statement on external datasource".
>
>
Thanks SD.
Please see if the under mentioned procedure is good enough:

SET TERM ^ ;
ALTER PROCEDURE COPY_theTABLE
AS
declare variable FIELD_1 varchar(80);
declare variable FIELD_2 varchar(80);
BEGIN
for select p.FIELD_1, p.FIELD_2 from the_TABLE into :FIELD_1, :FIELD_2
do execute statement
('insert into the_TABLE values(?)')
(:FIELD_1, :FIELD_2)
ON EXTERNAL 'ExternalServer:C:\External_Target.FDB'
AS USER 'SYSDBA' PASSWORD 'masterkey';
END^
SET TERM ; ^


> --
>WBR, SD.
>
>
> 
>
> 
>
> ++
>
> 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
>
>
>
>

Re: [firebird-support] Re: Performance problem - input wanted

2017-11-21 Thread Svein Erling Tysvær setys...@gmail.com [firebird-support] 
Try changing to Afdeling_ID||'' (or Afdeling_ID+0 if it is a number) in
your trigger. That should make RDB$FOREIGN105 useless for the query. Though
this kind of trigger that finds the MAX value in huge tables (although
there's only 750 occurences of each VAREPLU_ID on average, I guess there
are some values that are used a lot more) is a source for inserts being
slow as well as potentially error prone (in case of concurrent inserts, the
same values would be returned for both rows, which may or may not be what
you want).

HTH,
Set

Re: [firebird-support] Re: Performance problem - input wanted

2017-11-21 Thread michael.vilhelm...@microcom.dk [firebird-support] 
ALTER TABLE VAREFRVSTR_DETAIL ADD CONSTRAINT VAREFRVSTR_DETAIL_VNR 
   FOREIGN KEY (VAREPLU_ID) REFERENCES VARER
   (PLU_NR) 
   ON DELETE NO ACTION
   ON UPDATE CASCADE
 

 

 RDB$FOREIGN105
 ALTER TABLE VAREFRVSTR_DETAIL ADD  
   FOREIGN KEY (AFDELING_ID) REFERENCES AFDELING
   (AFDELINGSNUMMER) 
   ON DELETE NO ACTION
   ON UPDATE NO ACTION

Re: [firebird-support] Re: Performance problem - input wanted

2017-11-21 Thread Svein Erling Tysvær setys...@gmail.com [firebird-support] 
Please show us the definition of RDB$FOREIGN105 and VAREFRVSTR_DETAIL_VNR.

Set

2017-11-21 12:38 GMT+01:00 michael.vilhelm...@microcom.dk
[firebird-support] :

>
>
> Hi all
>
> Sorry for my late answer and thank you all for your input.  We experienced
> a large performance drop friday and it lasted until monday around 17.
> The place where we host our servers, had an update of some antivirus
> software which gave huge problems.
> At first we suspected Firebird and the new server, so late sunday night I
> moved all back to the old server. Performance still very low.
>
> That said - when we finally folund the reason, i got back to try to locate
> perfomance bottleneck on this one routine.
> I located a BEFORE INSERT trigger, which causes this problem.
>
> This one:
>
>
> *CREATE TRIGGER WEB_UDSALG_SPR FOR VAREFRVSTR_DETAIL INACTIVE BEFORE
> INSERT POSITION 11 AS*
> *begin *
> *  Select *
> *MAX(WEB_Udsalg_Spr), *
> *MAX(WEB_Udsalg_Stk), *
> *MAX(WEB_Udsalg_Type) *
> *from VareFrvStr_Detail Where *
> *  VarePlu_ID=NEW.VarePlu_ID and *
> *  Afdeling_ID=NEW.Afdeling_ID *
> *Into *
> *NEW.WEB_Udsalg_Spr, *
> *NEW.WEB_Udsalg_Stk, *
> *NEW.WEB_Udsalg_Type;*
> *end*
>
> Disabling this one and performance for this one routine increased back to
> normal.
>
>
> If I try runing this query in DBW and look at the plan, its like this:
>
>
> PLAN (VAREFRVSTR_DETAIL INDEX (VAREFRVSTR_DETAIL_VNR, RDB$FOREIGN105))
>
> VAREFRVSTR_DETAIL_VNR: 0,1301287
> (Foreign key to field PLU_NR in table VARER with 76.863 rows)
>
> RDB$FOREIGN105: 0,01724137925
> This is the primary key of the table VAREFRVSTR_DETAIL.
> Primary key is:
>
>  PRIMARY KEY (VAREPLU_ID, FARVE_NAVN, LAENGDE_NAVN, STOERRELSE_NAVN,
> AFDELING_ID)
>
> All fields in the primary key is VARCHAR(30).
> I would never do it this way, but this is before my time, and was setup in
> 1999.
>
> I then did a SET STAT for the above 2 indeices.
> Afterwards its like:
>
> RDB$FOREIGN105: 0,01724137925
> VAREFRVSTR_DETAIL_VNR: 0,1300965
>
> This was the same problem.
>
>
>
> Then I tested the PLAN on an older copy.
>
> It then said:
>
> PLAN (VAREFRVSTR_DETAIL INDEX (VAREFRVSTR_DETAIL_VNR))
>
> So on this server, where it works fast, the plan does not use the primary
> key (RDB$FOREIGN105).
>
> This stat of the two indices on this old server is:
>
> RDB$FOREIGN105:  0,01724137925 (but not used).
> VAREFRVSTR_DETAIL_VNR: 0,1332285
>
> So they are more or less alike.
> On this server (my testserver) im running FB 2.5.3 SS.
>
>
> This means - this must be the reason for the performance drop.
> Right?
>
> And can I by any means changed this trigger to not use primary key.
> Logically it only need VAREFRVSTR_DETAIL_VNR.
>
>
>
>
>
>
> 
>

Re: [firebird-support] Performance problem - input wanted

2017-11-21 Thread michael.vilhelm...@microcom.dk [firebird-support] 
Hi all
 

 Sorry for my late answer and thank you all for your input.  We experienced a 
large performance drop friday and it lasted until monday around 17.
 The place where we host our servers, had an update of some antivirus software 
which gave huge problems. 
 At first we suspected Firebird and the new server, so late sunday night I 
moved all back to the old server. Performance still very low. 
 

 And - Sorry to yhou guys how gace me an answer. I deleted them all by 
accident. 
 Sorry!
 

 That said - when we finally folund the reason, i got back to try to locate 
perfomance bottleneck on this one routine. 
 I located a BEFORE INSERT trigger, which causes this problem. 
 

 This one:
 

 CREATE TRIGGER WEB_UDSALG_SPR FOR VAREFRVSTR_DETAIL INACTIVE BEFORE INSERT 
POSITION 11 AS
 begin 
   Select 
 MAX(WEB_Udsalg_Spr), 
 MAX(WEB_Udsalg_Stk), 
 MAX(WEB_Udsalg_Type) 
 from VareFrvStr_Detail Where 
   VarePlu_ID=NEW.VarePlu_ID and 
   Afdeling_ID=NEW.Afdeling_ID 
 Into 
 NEW.WEB_Udsalg_Spr, 
 NEW.WEB_Udsalg_Stk, 
 NEW.WEB_Udsalg_Type;
 end
 

 Disabling this one and performance for this one routine increased back to 
normal. 
 

 

 If I try runing this query in DBW and look at the plan, its like this:
 

 

 PLAN (VAREFRVSTR_DETAIL INDEX (VAREFRVSTR_DETAIL_VNR, RDB$FOREIGN105))
 

 VAREFRVSTR_DETAIL_VNR: 0,1301287 
 (Foreign key to field PLU_NR in table VARER with 76.863 rows)
 

 RDB$FOREIGN105: 0,01724137925
 This is the foreign key to AFDELINGSNUMMER in table AFDELING (containing 58 
records)
 

 I then did a SET STAT for the above 2 indeices. 
 Afterwards its like:
 

 RDB$FOREIGN105: 0,01724137925
 VAREFRVSTR_DETAIL_VNR: 0,1300965
 

 This was the same problem. 
 

 

 

 Then I tested the PLAN on an older copy.
 

 It then said:
 

 PLAN (VAREFRVSTR_DETAIL INDEX (VAREFRVSTR_DETAIL_VNR))
 

 So on this server, where it works fast, the plan does not use the other 
ofreign key (RDB$FOREIGN105).
 

 This stat of the two indices on this old server is:
 

 RDB$FOREIGN105:  0,01724137925 (but not used).
 VAREFRVSTR_DETAIL_VNR: 0,1332285
 

 So they are more or less alike. 
 On this server (my testserver) im running FB 2.5.3 SS.
 

 

 This means - this must be the reason for the performance drop.
 Right?
 

 

 And the solution is to drop the foreign key to table AFDELING, right?

[firebird-support] Re: Performance problem - input wanted

2017-11-21 Thread michael.vilhelm...@microcom.dk [firebird-support] 
Hi all 

 Sorry for my late answer and thank you all for your input.  We experienced a 
large performance drop friday and it lasted until monday around 17.
 The place where we host our servers, had an update of some antivirus software 
which gave huge problems. 
 At first we suspected Firebird and the new server, so late sunday night I 
moved all back to the old server. Performance still very low. 
 

 That said - when we finally folund the reason, i got back to try to locate 
perfomance bottleneck on this one routine. 
 I located a BEFORE INSERT trigger, which causes this problem. 
 

 This one:
 

 CREATE TRIGGER WEB_UDSALG_SPR FOR VAREFRVSTR_DETAIL INACTIVE BEFORE INSERT 
POSITION 11 AS

 begin 
   Select 
 MAX(WEB_Udsalg_Spr), 
 MAX(WEB_Udsalg_Stk), 
 MAX(WEB_Udsalg_Type) 
 from VareFrvStr_Detail Where 
   VarePlu_ID=NEW.VarePlu_ID and 
   Afdeling_ID=NEW.Afdeling_ID 
 Into 
 NEW.WEB_Udsalg_Spr, 
 NEW.WEB_Udsalg_Stk, 
 NEW.WEB_Udsalg_Type;
 end
 

 Disabling this one and performance for this one routine increased back to 
normal. 

 

 

 If I try runing this query in DBW and look at the plan, its like this:
 

 

 PLAN (VAREFRVSTR_DETAIL INDEX (VAREFRVSTR_DETAIL_VNR, RDB$FOREIGN105))

 

 VAREFRVSTR_DETAIL_VNR: 0,1301287 

 (Foreign key to field PLU_NR in table VARER with 76.863 rows)
 

 RDB$FOREIGN105: 0,01724137925

 This is the primary key of the table VAREFRVSTR_DETAIL.
 Primary key is:
 

  PRIMARY KEY (VAREPLU_ID, FARVE_NAVN, LAENGDE_NAVN, STOERRELSE_NAVN, 
AFDELING_ID)
 

 All fields in the primary key is VARCHAR(30). 
 I would never do it this way, but this is before my time, and was setup in 
1999.
 

 I then did a SET STAT for the above 2 indeices. 
 Afterwards its like:
 

 RDB$FOREIGN105: 0,01724137925

 VAREFRVSTR_DETAIL_VNR: 0,1300965

 


 This was the same problem. 
 

 

 

 Then I tested the PLAN on an older copy.
 

 It then said:
 

 PLAN (VAREFRVSTR_DETAIL INDEX (VAREFRVSTR_DETAIL_VNR))

 

 So on this server, where it works fast, the plan does not use the primary key 
(RDB$FOREIGN105).
 

 This stat of the two indices on this old server is:
 

 RDB$FOREIGN105:  0,01724137925 (but not used).

 VAREFRVSTR_DETAIL_VNR: 0,1332285

 

 So they are more or less alike. 

 On this server (my testserver) im running FB 2.5.3 SS.
 

 

 This means - this must be the reason for the performance drop.
 Right?
 

 And can I by any means changed this trigger to not use primary key.
 Logically it only need VAREFRVSTR_DETAIL_VNR.

Re: [firebird-support] Copy Table to Another Database

2017-11-21 Thread Dimitry Sibiryakov s...@ibphoenix.com [firebird-support] 
21.11.2017 10:01, LtColRDSChauhan rdsc1...@gmail.com [firebird-support] wrote:
> How can I copy all records of a table to another table in different database. 
> The tables 
> having same design.

   Use "execute statement on external datasource".


-- 
   WBR, SD.






++

Visit http://www.firebirdsql.org and click the Documentation item
on the main (top) menu.  Try FAQ and other links from the left-side menu there.

Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ 

++


Yahoo Groups Links

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/firebird-support/join
(Yahoo! ID required)

<*> To change settings via email:
firebird-support-dig...@yahoogroups.com 
firebird-support-fullfeatu...@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
firebird-support-unsubscr...@yahoogroups.com

<*> Your use of Yahoo Groups is subject to:
https://info.yahoo.com/legal/us/yahoo/utos/terms/

[firebird-support] Copy Table to Another Database

2017-11-21 Thread LtColRDSChauhan rdsc1...@gmail.com [firebird-support] 
Hello,

How can I copy all records of a table to another table in different
database. The tables having same design.

Regards,
Rajiv