Re: AW: [firebird-support] not EM goal, but question about firebird statement

[Thomas Steinmaurer]

> … How can I commit the DDL-Procedure that the oter find my table?

You can't COMMIT in PSQL. What you could try is to encapsulate your 
stuff in an AUTONOMOUS TRANSACTION (available in Firebird 2.5), but I 
haven't tried if this works in a scenario where you create a table and 
insert into that table in a single PSQL block.

Regards,
Thomas


> Hello,
>
> I created the following stored procedure:
>
> create procedure P_DB_MOVE_RECORDS (
>
> TOID integer)
>
> as
>
> declare variable STR_J char(4);
>
> declare variable STMTXT varchar(300);
>
> declare variable CHKPROZ integer;
>
> begin
>
> for select substring(ts from 1 for 4) as tsz from t_dt where lnr<  :toid
> group by tsz into :str_j
>
> do
>
> begin
>
> if(str_j is not null) then
>
> begin
>
> if (not exists(select 1 from rdb$relations where rdb$relation_name =
> 'B_' || :str_j)) then
>
> begin
>
> select proz_status from p_db_add_new_btable(:str_j) into :chkproz;
>
> end
>
> stmtxt = 'insert into B_' || :str_j ||(…..) ' select * from t_dt;';
>
> execute statement :stmtxt;
>
> end
>
> end
>
> end
>
> The first question: If I call the stored procedure p_db_add_new_btable
> before with the parameter “2012”, it works and the statement knows this
> table. If I call the stored procedure p_db_move_records (calls too this
> procedure), then the statement don’t know this table, it don’t exists. Why
> this?
>
> The second question, I have many table columns. Is there a trick to get all
> columns like: insert into table(*) select * from othertable where condition…
>
> Thank you.
>
> Best regards.
>
> Olaf
>
> Mit freundlichen Grüßen / with best regards
>
> Olaf Kluge
>
> S A T R O N Sachsen
> Steuerungstechnik GmbH
> Johann-Gottlob-Pfaff Straße 7
> D-09405 Zschopau
>
> Tel: +49 (0) 3725 / 3506-31
> Fax: +49 (0) 3725 / 3506-12
> Mobil: +49 (0) 170 / 9292375
> E-Mail:mailto:olaf.kl...@satron.de
>   >  olaf.kl...@satron.de
> 
> Internet:http://www.satron.de/>  http://www.satron.de/
>
> 
> 
> Geschäftsführer: Bernd Grötzschel, Gerd Kaden
> Amtsgericht: Chemnitz HRB1218
> Ust-ID-Nr: DE141294791
>
> 
> 
> Diese E-Mail ist vertraulich. Wenn Sie nicht der beabsichtigte Empfänger
> sind, dürfen Sie die Informationen nicht offen legen oder benutzen. Wenn Sie
> diese E-Mail durch einen Fehler bekommen haben, teilen Sie uns dies bitte
> mit, indem Sie die E-Mail an den Absender zurücksenden. Bitte löschen Sie
> danach diese E-Mail.
> This email is confidential. If you are not the intended recipient, you must
> not disclose or use the information contained in it.
> If you have received this mail in error, please tell us immediately by
> return email and delete the document.
>
> [Non-text portions of this message have been removed]
>
>
>
>
>
> [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
>
>
>




++

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: Firebird hangs for a while blocking all DB operations periodically.

[hvlad]

--- In firebird-support@yahoogroups.com, "anthonyjang2012" wrote:

> The issue occurred again, but, unfortunately, we only obtained a full dump. 
> The full dump is 8GB (1GB zipped). Would this be useful to send to you? 
> Please let me know so that I can put it on an ftp site for you to download. 
> Unfortunately, we did not obtain a mini-dump.

  Are you able to extract stack traces for every thread ? If yes, extract them 
into text file and send to me. Else send me the link
to the ftp with full dump.

Vlad

PS Very sad you have no mini-dump. 

[firebird-support] libgds.so being called from ODBC driver

[Maury Markowitz]

I've been muddling my way through a build of the ODBC driver on OSX. After some 
work I was able to find and build all the required prerequisite files, change 
some paths, move a few things around, and presto, one Mac driver. However, 
attempting to connect using it fails with:

dlopen(libfbclient.so, 2): image not found2012-06-12 09:10:20.845 
ODBCQueryTool[6180:7e17] SQL Error State:08004, Native Error Code: 1FC78, 
ODBC Error: [ODBC Firebird Driver]Unable to connect to data source: library 
'libgds.so' failed to load

Looking around, it seems that this is normally solved by dropping symlinks into 
/usr/iib, so I did this using the libfbclient.dylib.

But this didn't work, same error. Suggestions?

Re: [firebird-support] Statement get stuck and never terminates

[Marc Bettex]

Hi Thomas and thanks for trying to help me.

To answer your questions:


> * What's the read/write ratio in your test?

The ratio is around 1.33 read statement per write statement


> * Any special reason to lock the entire table for write operations? I 
> guess that's PROTECED WRITE supposed to do. Sounds a bit restrictive.

Yes, that's what PROTECTED WRITE does. That's not strictly necessary but it 
makes
my life easier to have Firebird manage the concurrent accesses with locking, 
instead
of me doing it manually with rollback/retries if a write statement fails because
another client has already written the same rows. And since the operations on 
this table
do not happen that often, the performance loss is acceptable in my case.


> Try to use NO WAIT instead of WAIT at transaction level to get 
> immediately notified about lock conflicts.

I'll give it a try to test if the problem still appears. But I would prefer if 
I can avoid this in 
production. It's easier for me if Firebird deals with the concurrent access and 
makes me
wait instead of me doing it manually with retries.


> I would also configure the trace to get some output at transaction level 
> and not only at statement level (prepare/start/finish).

Yes I did it and the log show that the transaction starts normally, then the 
statement is
prepared and everything get stuck at this point, so no other transaction has 
been started
that would lock or access the table. The only other transactions are started 
after the one
that is stuck is the ones I make with ISQL in order to debug the problem. And 
as far as I
can tell (since the log file grows really fast), all previous transactions have 
finished correctly.


Anyway, thanks again for your help and suggestions,

Marc

AW: [firebird-support] not EM goal, but question about firebird statement

[Olaf Kluge]

… How can I commit the DDL-Procedure that the oter find my table?  

Hello,

I created the following stored procedure:

create procedure P_DB_MOVE_RECORDS (

TOID integer)

as

declare variable STR_J char(4);

declare variable STMTXT varchar(300);

declare variable CHKPROZ integer;

begin

for select substring(ts from 1 for 4) as tsz from t_dt where lnr < :toid
group by tsz into :str_j

do

begin

if(str_j is not null) then

begin

if (not exists(select 1 from rdb$relations where rdb$relation_name =
'B_' || :str_j)) then

begin

select proz_status from p_db_add_new_btable(:str_j) into :chkproz;

end

stmtxt = 'insert into B_' || :str_j ||(…..) ' select * from t_dt;';

execute statement :stmtxt;

end

end

end

The first question: If I call the stored procedure p_db_add_new_btable
before with the parameter “2012”, it works and the statement knows this
table. If I call the stored procedure p_db_move_records (calls too this
procedure), then the statement don’t know this table, it don’t exists. Why
this?

The second question, I have many table columns. Is there a trick to get all
columns like: insert into table(*) select * from othertable where condition…

Thank you.

Best regards.

Olaf

Mit freundlichen Grüßen / with best regards

Olaf Kluge

S A T R O N Sachsen 
Steuerungstechnik GmbH
Johann-Gottlob-Pfaff Straße 7
D-09405 Zschopau

Tel: +49 (0) 3725 / 3506-31
Fax: +49 (0) 3725 / 3506-12
Mobil: +49 (0) 170 / 9292375
E-Mail: mailto:olaf.kl...@satron.de
 > olaf.kl...@satron.de
 
Internet: http://www.satron.de/> http://www.satron.de/



Geschäftsführer: Bernd Grötzschel, Gerd Kaden
Amtsgericht: Chemnitz HRB1218
Ust-ID-Nr: DE141294791



Diese E-Mail ist vertraulich. Wenn Sie nicht der beabsichtigte Empfänger
sind, dürfen Sie die Informationen nicht offen legen oder benutzen. Wenn Sie
diese E-Mail durch einen Fehler bekommen haben, teilen Sie uns dies bitte
mit, indem Sie die E-Mail an den Absender zurücksenden. Bitte löschen Sie
danach diese E-Mail.
This email is confidential. If you are not the intended recipient, you must
not disclose or use the information contained in it.
If you have received this mail in error, please tell us immediately by
return email and delete the document.

[Non-text portions of this message have been removed]





[Non-text portions of this message have been removed]

[firebird-support] not EM goal, but question about firebird statement

[Olaf Kluge]

Hello,

 

I created the following stored procedure:

 

create procedure P_DB_MOVE_RECORDS (

TOID integer)

as

declare variable STR_J char(4);

declare variable STMTXT varchar(300);

declare variable CHKPROZ integer;

begin

  for select substring(ts from 1 for 4) as tsz from t_dt where lnr < :toid
group by tsz into :str_j

  do

  begin

if(str_j is not null) then

begin

  if (not exists(select 1 from rdb$relations where rdb$relation_name =
'B_' || :str_j)) then

  begin

select proz_status from p_db_add_new_btable(:str_j) into :chkproz;

  end

  stmtxt = 'insert into B_' || :str_j ||(…..) ' select * from t_dt;';

  execute statement :stmtxt;

end

  end

end

 

The first question: If I call the stored procedure p_db_add_new_btable
before with the parameter “2012”, it works and the statement knows this
table. If I call the stored procedure p_db_move_records (calls too this
procedure), then the statement don’t know this table, it don’t exists. Why
this?

 

The second question, I have many table columns. Is there a trick to get all
columns like: insert into table(*) select * from othertable where condition…

 

Thank you.

 

 

Best regards.

 

Olaf

 

Mit freundlichen Grüßen / with best regards

 

Olaf Kluge

 

S A T R O N  Sachsen 
Steuerungstechnik GmbH
Johann-Gottlob-Pfaff Straße 7
D-09405 Zschopau

 


Tel: +49 (0) 3725 / 3506-31
Fax:+49 (0) 3725 / 3506-12
Mobil:  +49 (0) 170 / 9292375
E-Mail:   mailto:olaf.kl...@satron.de> olaf.kl...@satron.de
Internet:  http://www.satron.de/> http://www.satron.de/




Geschäftsführer: Bernd Grötzschel, Gerd Kaden
Amtsgericht: Chemnitz HRB1218
Ust-ID-Nr: DE141294791

 



Diese E-Mail ist vertraulich. Wenn Sie nicht der beabsichtigte Empfänger
sind, dürfen Sie die Informationen nicht offen legen oder benutzen. Wenn Sie
diese E-Mail durch einen Fehler bekommen haben, teilen Sie uns dies bitte
mit, indem Sie die E-Mail an den Absender zurücksenden. Bitte löschen Sie
danach diese E-Mail.
This email is confidential. If you are not the intended recipient, you must
not disclose or use the information contained in it.
If you have received this mail in error, please tell us immediately by
return email and delete the document.

 



[Non-text portions of this message have been removed]

Re: AW: [firebird-support] create a table every year

[Kjell Rilbe]

Den 2012-06-12 11:18 skrev Olaf Kluge såhär:
>
> Hello Mark,
>
> >If it is just for backup, why not just create a single backup table and
> >move all records into that table at year end. Then there is no need to
> >create a table per year (and it is easier to provide a unified view on
> >’archived' records).
> >
> >Mark
>
> my apprehension is that the queries will slow if I take the many 
> hundred-thousand records every year in a simple table.
>

Depends on your indexing and queries, of course, but I currently have a 
table with some 170 million records, and with indexed queries there are 
no problems. With just ~1 million records per year, I'd say don't worry.

Kjell

-- 
--
Kjell Rilbe
DataDIA AB
E-post: kj...@datadia.se
Telefon: 08-761 06 55
Mobil: 0733-44 24 64





[Non-text portions of this message have been removed]

Re: RES: [firebird-support] How to improve update performance with millions records?

[Thomas Steinmaurer]

> --- In firebird-support@yahoogroups.com, Thomas Steinmaurer  wrote:
>>
>>> --- In firebird-support@yahoogroups.com, Thomas Steinmaurer   wrote:

> --- In firebird-support@yahoogroups.com, "Fabiano"
> wrote:
>>
>> What frequency do you backup/restore this database?
>> This database can not stop, It used to realtime collect data.
>  so no restore only backup, backup it once a week.
>> What firebird version? SS CS?
>> FB2.5 SS, i use FB2.1 SS to test，same result.
>  OS is win XP, change to Win 2008 Server, same result.
>> What your garbage policy?
>> Sweep interval = 200 to 2, when sweeper run, the update speed
>  become more slowly than normal.

 Could be:

 * Background garbage collection (data + index)
 * Automatic sweep kicking in
 * Using AutoCommit/Commit Retaining without doing a hard commit from
 time to time


 - Is this a single client attachment/application doing the work or are
 Yes, now test only. a single client can update.

 multiple attachments connecting to the database?
 when testting, three client attachments, but only connect, none operation.

 - Can you clarify what the update process exactly does?
 i write a app with delphi7 + dbx, for test
>>>for i from 1 to N do
>>>{
>>>  start transaction;
>>>  for j from 1 to 1000 do
>>>  {
>>> execute a update statement;
>>> /*update table1 set  where rid = :rid;
>>>   rid is primary key of table1
>>>   table1 is a single table, not depend on other table or proc...
>>> */
>>>  }
>>>  commit transaction;
>>>}
>>
>> I haven't asked for pseudo-code. I will kick in again, if you show us
>> real Delphi code. ;-)
>>
>>
>> Regards,
>> Thomas
>>
>
> :)
>> of curse, here it is
> procedure TInsertForm.Bt_StartClick(Sender: TObject);
> Var
>i, Measure_Value_ID:Integer;
>Measure_Value_Count: Integer;
>
>TimeStr: String;
>Value: Double;
>
>SumCount: Integer;
>SpendSec: Integer;
>Speed: Integer;
>StartTickCount, TickCount: DWord;
>
>TD: TTransactionDesc;
> Label
>L_Stop;
> begin
>Bt_Start.Enabled := False;
>Bt_Stop.Enabled := True;
>bStop := False;
>
>Measure_Value_Count := SE_Measure_Point_Count.Value * SE_ItemCount.Value;
>
> //  SQL_Insert.SQL.Text := M_InsertSQL.Lines.Text;
>SQL_Insert.Prepared := True;
>
>SumCount := 0;
>StartTickCount := GetTickCount();
>
>For i:=1 To Measure_Value_Count Do//�个测�值, Count is 100 to 
> 500
>Begin
>  If CB_UseTrans.Checked Then// use transaction
>  Begin
>If (SumCount Mod 1000 = 0) Then
>Begin
>  If SQLConnection.InTransaction Then
>SQLConnection.Commit(TD);
>  TD.TransactionID := 1;//not good, :)
>  TD.IsolationLevel := xilREADCOMMITTED;
>  SQLConnection.StartTransaction(TD);
>End;
>  end;
>
>  Measure_Value_ID := Random(Measure_Value_Count);
>  Value := Measure_Value_ID + Random(100) / 100;
>  TimeStr := DateTimeToStr(Now());
> //  SQL_Insert.SQL.Text := Format('Execute Procedure p_InsertRec(%d, 
> %d,''%s'',  %.2f)',[MeterID,T6_ID, TimeStr, Value]);
>  SQL_Insert.ParamByName('Measure_Value_ID').Value := Measure_Value_ID;
>  SQL_Insert.ParamByName('Data_TP').Value := TimeStr;
>  SQL_Insert.ParamByName('Data').Value := Value;
>  SQL_Insert.ExecSQL();
> //  SQL_Insert.Prepared := True;
>
> // the SQL_Insert is Update statement like this:
> // update measure_value_define set data = , ...
> //where measure_value_id = :Measure_value_id;
> //
>  SumCount := SumCount + 1;
>
>  TickCount := GetTickCount() - StartTickCount;
>  SpendSec := TickCount Div 1000;
>
>  // calc update speed
>  If ((SpendSec>  1) and (SumCount mod 200 = 0)) Then
>  Begin
>Speed := SumCount Div SpendSec;
>SE_SumCount.Value := SumCount;
>SE_SpendSec.Value := SpendSec;
>SE_Speed.Value := Speed;
>Application.ProcessMessages();
>If SpendSec>= 10 Then
>Begin
>  SumCount := 0;
>  StartTickCount := GetTickCount();
>End;
>If bStop Then
>  Goto L_Stop;
>  End;
>End;
> L_Stop:
>if SQLConnection.InTransaction Then
>  SQLConnection.Commit(TD);
>Bt_Stop.Click();
> end;

Doesn't look that bad from a database POV, as you are preparing your SQL 
outside the loop and executing the stored procedure inside the loop with 
a commit interval of 1000.

* I'm not sure about the dbExpress driver (which one are you using?) 
executing a COMMIT or COMMIT RETAINING behind the scene when calling 
SQLConnection.Commit

* Just for a test, I would move the entire GUI progess updating stuff 
from inside the loop to the end, thus outside of the loop. Also, as you 
are setting SumCount := 0 in the GUI updating stuff, this directly 
aff

Re: RES: [firebird-support] How to improve update performance with millions records?

[firebird_jimmy]

--- In firebird-support@yahoogroups.com, Thomas Steinmaurer  wrote:
>
> > --- In firebird-support@yahoogroups.com, Thomas Steinmaurer  wrote:
> >>
> >>> --- In firebird-support@yahoogroups.com, "Fabiano"   wrote:
> 
>  What frequency do you backup/restore this database?
>  This database can not stop, It used to realtime collect data.
> >>> so no restore only backup, backup it once a week.
>  What firebird version? SS CS?
>  FB2.5 SS, i use FB2.1 SS to test，same result.
> >>> OS is win XP, change to Win 2008 Server, same result.
>  What your garbage policy?
>  Sweep interval = 200 to 2, when sweeper run, the update speed
> >>> become more slowly than normal.
> >>
> >> Could be:
> >>
> >> * Background garbage collection (data + index)
> >> * Automatic sweep kicking in
> >> * Using AutoCommit/Commit Retaining without doing a hard commit from
> >> time to time
> >>
> >>
> >> - Is this a single client attachment/application doing the work or are
> >> Yes, now test only. a single client can update.
> >>
> >> multiple attachments connecting to the database?
> >> when testting, three client attachments, but only connect, none operation.
> >>
> >> - Can you clarify what the update process exactly does?
> >> i write a app with delphi7 + dbx, for test
> >   for i from 1 to N do
> >   {
> > start transaction;
> > for j from 1 to 1000 do
> > {
> >execute a update statement;
> >/*update table1 set  where rid = :rid;
> >  rid is primary key of table1
> >  table1 is a single table, not depend on other table or proc...
> >*/
> > }
> > commit transaction;
> >   }
> 
> I haven't asked for pseudo-code. I will kick in again, if you show us 
> real Delphi code. ;-)
> 
> 
> Regards,
> Thomas
>

:)
> of curse, here it is
procedure TInsertForm.Bt_StartClick(Sender: TObject);
Var
  i, Measure_Value_ID:Integer;
  Measure_Value_Count: Integer;

  TimeStr: String;
  Value: Double;

  SumCount: Integer;
  SpendSec: Integer;
  Speed: Integer;
  StartTickCount, TickCount: DWord;

  TD: TTransactionDesc;
Label
  L_Stop;
begin
  Bt_Start.Enabled := False;
  Bt_Stop.Enabled := True;
  bStop := False;

  Measure_Value_Count := SE_Measure_Point_Count.Value * SE_ItemCount.Value;

//  SQL_Insert.SQL.Text := M_InsertSQL.Lines.Text;
  SQL_Insert.Prepared := True;

  SumCount := 0;
  StartTickCount := GetTickCount();

  For i:=1 To Measure_Value_Count Do//每个测量值, Count is 100 to 
500
  Begin
If CB_UseTrans.Checked Then// use transaction
Begin
  If (SumCount Mod 1000 = 0) Then
  Begin
If SQLConnection.InTransaction Then
  SQLConnection.Commit(TD);
TD.TransactionID := 1;//not good, :)
TD.IsolationLevel := xilREADCOMMITTED;
SQLConnection.StartTransaction(TD);
  End;
end;

Measure_Value_ID := Random(Measure_Value_Count);
Value := Measure_Value_ID + Random(100) / 100;
TimeStr := DateTimeToStr(Now());
//  SQL_Insert.SQL.Text := Format('Execute Procedure p_InsertRec(%d, %d,''%s'', 
 %.2f)',[MeterID,T6_ID, TimeStr, Value]);
SQL_Insert.ParamByName('Measure_Value_ID').Value := Measure_Value_ID;
SQL_Insert.ParamByName('Data_TP').Value := TimeStr;
SQL_Insert.ParamByName('Data').Value := Value;
SQL_Insert.ExecSQL();
//  SQL_Insert.Prepared := True;

// the SQL_Insert is Update statement like this:
// update measure_value_define set data = , ...
//where measure_value_id = :Measure_value_id;
//
SumCount := SumCount + 1;

TickCount := GetTickCount() - StartTickCount;
SpendSec := TickCount Div 1000;

// calc update speed
If ((SpendSec > 1) and (SumCount mod 200 = 0)) Then
Begin
  Speed := SumCount Div SpendSec;
  SE_SumCount.Value := SumCount;
  SE_SpendSec.Value := SpendSec;
  SE_Speed.Value := Speed;
  Application.ProcessMessages();
  If SpendSec >= 10 Then
  Begin
SumCount := 0;
StartTickCount := GetTickCount();
  End;
  If bStop Then
Goto L_Stop;
End;
  End;
L_Stop:
  if SQLConnection.InTransaction Then
SQLConnection.Commit(TD);
  Bt_Stop.Click();
end;

Re: RES: [firebird-support] How to improve update performance with millions records?

[Thomas Steinmaurer]

> --- In firebird-support@yahoogroups.com, Thomas Steinmaurer  wrote:
>>
>>> --- In firebird-support@yahoogroups.com, "Fabiano"   wrote:

 What frequency do you backup/restore this database?
 This database can not stop, It used to realtime collect data.
>>> so no restore only backup, backup it once a week.
 What firebird version? SS CS?
 FB2.5 SS, i use FB2.1 SS to test，same result.
>>> OS is win XP, change to Win 2008 Server, same result.
 What your garbage policy?
 Sweep interval = 200 to 2, when sweeper run, the update speed
>>> become more slowly than normal.
>>
>> Could be:
>>
>> * Background garbage collection (data + index)
>> * Automatic sweep kicking in
>> * Using AutoCommit/Commit Retaining without doing a hard commit from
>> time to time
>>
>>
>> - Is this a single client attachment/application doing the work or are
>> Yes, now test only. a single client can update.
>>
>> multiple attachments connecting to the database?
>> when testting, three client attachments, but only connect, none operation.
>>
>> - Can you clarify what the update process exactly does?
>> i write a app with delphi7 + dbx, for test
>   for i from 1 to N do
>   {
> start transaction;
> for j from 1 to 1000 do
> {
>execute a update statement;
>/*update table1 set  where rid = :rid;
>  rid is primary key of table1
>  table1 is a single table, not depend on other table or proc...
>*/
> }
> commit transaction;
>   }

I haven't asked for pseudo-code. I will kick in again, if you show us 
real Delphi code. ;-)


Regards,
Thomas




++

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: How to improve update performance with millions records?

[firebird_jimmy]

sorry, everybody!
my english is so bad, maybe can not describe clearly.
:)

Re: RES: [firebird-support] How to improve update performance with millions records?

[firebird_jimmy]

--- In firebird-support@yahoogroups.com, Thomas Steinmaurer  wrote:
>
> > --- In firebird-support@yahoogroups.com, "Fabiano"  wrote:
> >>
> >> What frequency do you backup/restore this database?
> >> This database can not stop, It used to realtime collect data.
> >so no restore only backup, backup it once a week.
> >> What firebird version? SS CS?
> >> FB2.5 SS, i use FB2.1 SS to test，same result.
> >OS is win XP, change to Win 2008 Server, same result.
> >> What your garbage policy?
> >> Sweep interval = 200 to 2, when sweeper run, the update speed
> >become more slowly than normal.
> 
> Could be:
> 
> * Background garbage collection (data + index)
> * Automatic sweep kicking in
> * Using AutoCommit/Commit Retaining without doing a hard commit from 
> time to time
> 
> 
> - Is this a single client attachment/application doing the work or are 
> Yes, now test only. a single client can update.
>
> multiple attachments connecting to the database?
> when testting, three client attachments, but only connect, none operation.
>
> - Can you clarify what the update process exactly does?
> i write a app with delphi7 + dbx, for test
 for i from 1 to N do
 {
   start transaction;
   for j from 1 to 1000 do
   {
  execute a update statement;
  /*update table1 set  where rid = :rid;
rid is primary key of table1 
table1 is a single table, not depend on other table or proc...
  */
   }
   commit transaction;
 }
>
> Btw, SuperServer can't utilize multiple cores/CPUs for a single database.
> No problem, firebird.conf is defualt, use single cpu. 
> 
> Regards,
> Thomas
> 
> 
> 
> 
> >> De: firebird-support@yahoogroups.com
> >> [mailto:firebird-support@yahoogroups.com] Em nome de firebird_jimmy
> >> Enviada em: segunda-feira, 11 de junho de 2012 10:01
> >> Para: firebird-support@yahoogroups.com
> >> Assunto: [firebird-support] How to improve update performance with millions
> >> records?
> >>
> >>
> >>
> >>
> >>
> >> Hi, I'm jimmy
> >> I have a problem, please help me!
> >> I have a table with 3,000,000 rows record, every row update 1 to 5 times in
> >> one day. The speed of update statement become slowly, about 30
> >> records/second, but insert speed above 1000 records/second.
> >> Is my usage be bad or not?
> >> what should i do?
> >>
> >> thanks!
> >>
> >> 20120610
> >>
> >>
> >>
> >>
> >>
> >> [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
> >
> >
> >
>

Re: [firebird-support] Re: How to improve update performance with millions records?

[Thomas Steinmaurer]

> --- In firebird-support@yahoogroups.com, Svein Erling 
> Tysvær  wrote:
>>
 Hi, I'm jimmy
>>>
>>> Hi Jimmy!
>>>
 I have a problem, please help me!
 I have a table with 3,000,000 rows record, every row update 1 to 5 times 
 in one day.
 The speed of update statement become slowly, about 30
 records/second, but insert speed above 1000 records/second.
 Is my usage be bad or not?
 what should i do?
>>>
>>> I don't know whether your usage is bad or not. What kind of indexes do
>>> you have and how do you update? What about transactions, do you have a
>>> noticeable gap between oldest (active) transaction and next
>>> transaction? I don't know whether it is still relevant (it is a very
>>> old article), but in some cases I think rdb$db_key can be useful for
>>> updates: http://ibexpert.net/ibe/index.php?n=Doc.TheMysteryOfRDBDBKEY
>>>
>>> HTH,
>>> Set
>>>
>>
>>> Thanks your help!
>>> My table have a primary key with integer, when update 200 rows then
>>> commit trans. Th
>>
>> Sounds OK, but what's more important is the gap mentioned above - it doesn't 
>> help if the update commit every 200 rows if there is one or more other, 
>> concurrent transactions, that runs for a long time without committing (well, 
>> transactions that are read only AND read committed are OK, but other 
>> combinations are not).
>>
>> And take up Thomas offer, he will notice if there's something wrong with the 
>> output of gstat.
>>
>> Set
>>
>
>> I read http://ibexpert.net/ibe/index.php?n=Doc.TheMysteryOfRDBDBKEY,  use 
>> rdb$db_key as condition of update statement, now update speend improve to 
>> 600 to 2000 rows per second.
>> my database only one user can update data, other only connect and read 
>> record.
>> my table define
> CREATE TABLE MEASURE_VALUE_DEFINE(
>  MEASURE_VALUE_ID  INTEGER NOT NULL,
>  MEASURE_POINT_ID  INTEGER,
>  DATA_ITEM_ID  INTEGER,
>  HIS_TB_NAME   VARCHAR(255),
>  DATA_CLASSINTEGER,
>  PHASE INTEGER,
>  USER_DEFINE   INTEGER,
>  DATA  DOUBLE PRECISION,
>  DATA_TP   TIMESTAMP,
>  COLLECT_TPTIMESTAMP,
>  IS_STORE  INTEGER,
>  STORE_PERIOD  INTEGER,
>  IS_CALC   INTEGER,
>  IS_GRAPHICS   INTEGER,
>  CODON FLOAT,
>  MULTI FLOAT,
>  ZERO_RANGEFLOAT,
>  THRESHOLD FLOAT,
>  UPPER_LIMIT   DOUBLE PRECISION,
>  LOWER_LIMIT   DOUBLE PRECISION,
>  ULTIMATE_UPPER_LIMIT  DOUBLE PRECISION,
>  ULTIMATE_LOWER_LIMIT  DOUBLE PRECISION,
>  STATISTIC_PERIOD  INTEGER,
>  IS_STATISTIC_MIN  INTEGER,
>  IS_STATISTIC_AVG  INTEGER,
>  IS_STATISTIC_MAX  INTEGER,
>  MIN_VALUE DOUBLE PRECISION,
>  MIN_VALUE_TP  TIMESTAMP,
>  MAX_VALUE DOUBLE PRECISION,
>  MAX_VALUE_TP  TIMESTAMP,
>  AVG_VALUE DOUBLE PRECISION,
>  AVG_COLLECT_COUNT INTEGER,
>  WHO_FREEZEINTEGER,
>  UPDATE_TIME   TIMESTAMP,
>  MEASURE_VALUE_GUIDVARCHAR(50) NOT NULL,
>  IS_DELETE INTEGER
> );
>
> Alter table measure_value_define add constraint pk_Measure_Value_Define 
> Primary Key (Measure_value_id);
>
> Create Index idx_Measure_Value_Define on 
> MEASURE_VALUE_DEFINE(Measure_Point_ID, Measure_value_id);
>
>> the table measure_value_define not depend on other tables or proc..., vice 
>> versa
>
>> update statement is:
>update Measure_value_define
>  set data = :data, data_tp = :data_tp, ...
>  where measure_value_id = :id;
>
>> now, i have a question:
>   i use normal update statement with pk to update record, it's performance be 
> so slowly,  beside method use rdb$db_key, have other ways ?

Are running the above statement in context of a prepared statement? It 
seems so due to the parameter names, but want to make sure, that you 
don't reassign the SQL over and over again with new values instead of 
using a prepared statement. Preparing a statement on a large table could 
take some time, although milliseconds, this can sum up at the end.

Care to show us some client code executing the update statement?

Regards,
Thomas



> 
>
> ++
>
> 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
>
>
>

[firebird-support] Re: How to improve update performance with millions records?

[firebird_jimmy]

--- In firebird-support@yahoogroups.com, Svein Erling Tysvær 
 wrote:
>
> > >Hi, I'm jimmy
> > 
> > Hi Jimmy!
> > 
> > > I have a problem, please help me!
> > > I have a table with 3,000,000 rows record, every row update 1 to 5 times 
> > > in one day. 
> > > The speed of update statement become slowly, about 30 
> > > records/second, but insert speed above 1000 records/second.
> > > Is my usage be bad or not? 
> > > what should i do? 
> > 
> > I don't know whether your usage is bad or not. What kind of indexes do 
> > you have and how do you update? What about transactions, do you have a 
> > noticeable gap between oldest (active) transaction and next 
> > transaction? I don't know whether it is still relevant (it is a very 
> > old article), but in some cases I think rdb$db_key can be useful for 
> > updates: http://ibexpert.net/ibe/index.php?n=Doc.TheMysteryOfRDBDBKEY
> > 
> > HTH,
> > Set
> >
> 
> > Thanks your help!
> > My table have a primary key with integer, when update 200 rows then 
> > commit trans. Th
> 
> Sounds OK, but what's more important is the gap mentioned above - it doesn't 
> help if the update commit every 200 rows if there is one or more other, 
> concurrent transactions, that runs for a long time without committing (well, 
> transactions that are read only AND read committed are OK, but other 
> combinations are not).
> 
> And take up Thomas offer, he will notice if there's something wrong with the 
> output of gstat.
> 
> Set
>

>thanks a lot

>gstat result

 
Database "d:\memdb\datastore_memorydb_net" 
Database header page information: 
Flags   0 
Checksum12345 
Generation  12913 
Page size   16384 
ODS version 11.2 
Oldest transaction  10782 
Oldest active   10783 
Oldest snapshot 10783 
Next transaction12889 
Bumped transaction  1 
Sequence number 0 
Next attachment ID  52 
Implementation ID   16 
Shadow count0 
Page buffers4096 
Next header page0 
Database dialect3 
Creation date   Jun 10, 2012 22:36:02 
Attributes  force write 
 
Variable header data: 
Sweep interval: 2000 
*END* 
 
 
Database file sequence: 
File d:\memdb\datastore_memorydb_net is the only file 
 
Analyzing database pages ... 
...  
MEASURE_VALUE_DEFINE (145) 
Primary pointer page: 155, Index root page: 156 
Average record length: 116.76, total records: 1334785 
Average version length: 16.39, total versions: 218126, max versions: 5 
Data pages: 12958, data page slots: 12958, average fill: 88% 
Fill distribution: 
 0 - 19% = 0 
20 - 39% = 1 
40 - 59% = 0 
60 - 79% = 0 
80 - 99% = 12957 
 
Index IDX_MEASURE_VALUE_DEFINE (1) 
Depth: 2, leaf buckets: 960, nodes: 1334915 
Average data length: 1.55, total dup: 0, max dup: 0 
Fill distribution: 
 0 - 19% = 2 
20 - 39% = 0 
40 - 59% = 751 
60 - 79% = 141 
80 - 99% = 66 
 
Index PK_MEASURE_VALUE_DEFINE (0) 
Depth: 2, leaf buckets: 836, nodes: 1334921 
Average data length: 1.24, total dup: 0, max dup: 0 
Fill distribution: 
 0 - 19% = 0 
20 - 39% = 0 
40 - 59% = 627 
60 - 79% = 21 
80 - 99% = 188 
 
...

AW: [firebird-support] create a table every year

[Olaf Kluge]

Hello Mark,

 


>If it is just for backup, why not just create a single backup table and
>move all records into that table at year end. Then there is no need to
>create a table per year (and it is easier to provide a unified view on
>’archived' records).
>
>Mark

my apprehension is that the queries will slow if I take the many 
hundred-thousand records every year in a simple table.

Best regards.

 

Olaf



[Non-text portions of this message have been removed]

[firebird-support] Re: How to improve update performance with millions records?

[firebird_jimmy]

--- In firebird-support@yahoogroups.com, Svein Erling Tysvær 
 wrote:
>
> > >Hi, I'm jimmy
> > 
> > Hi Jimmy!
> > 
> > > I have a problem, please help me!
> > > I have a table with 3,000,000 rows record, every row update 1 to 5 times 
> > > in one day. 
> > > The speed of update statement become slowly, about 30 
> > > records/second, but insert speed above 1000 records/second.
> > > Is my usage be bad or not? 
> > > what should i do? 
> > 
> > I don't know whether your usage is bad or not. What kind of indexes do 
> > you have and how do you update? What about transactions, do you have a 
> > noticeable gap between oldest (active) transaction and next 
> > transaction? I don't know whether it is still relevant (it is a very 
> > old article), but in some cases I think rdb$db_key can be useful for 
> > updates: http://ibexpert.net/ibe/index.php?n=Doc.TheMysteryOfRDBDBKEY
> > 
> > HTH,
> > Set
> >
> 
> > Thanks your help!
> > My table have a primary key with integer, when update 200 rows then 
> > commit trans. Th
> 
> Sounds OK, but what's more important is the gap mentioned above - it doesn't 
> help if the update commit every 200 rows if there is one or more other, 
> concurrent transactions, that runs for a long time without committing (well, 
> transactions that are read only AND read committed are OK, but other 
> combinations are not).
> 
> And take up Thomas offer, he will notice if there's something wrong with the 
> output of gstat.
> 
> Set
>

> I read http://ibexpert.net/ibe/index.php?n=Doc.TheMysteryOfRDBDBKEY,  use 
> rdb$db_key as condition of update statement, now update speend improve to 600 
> to 2000 rows per second. 
> my database only one user can update data, other only connect and read record.
> my table define
CREATE TABLE MEASURE_VALUE_DEFINE(
MEASURE_VALUE_ID  INTEGER NOT NULL,
MEASURE_POINT_ID  INTEGER,
DATA_ITEM_ID  INTEGER,
HIS_TB_NAME   VARCHAR(255),
DATA_CLASSINTEGER,
PHASE INTEGER,
USER_DEFINE   INTEGER,
DATA  DOUBLE PRECISION,
DATA_TP   TIMESTAMP,
COLLECT_TPTIMESTAMP,
IS_STORE  INTEGER,
STORE_PERIOD  INTEGER,
IS_CALC   INTEGER,
IS_GRAPHICS   INTEGER,
CODON FLOAT,
MULTI FLOAT,
ZERO_RANGEFLOAT,
THRESHOLD FLOAT,
UPPER_LIMIT   DOUBLE PRECISION,
LOWER_LIMIT   DOUBLE PRECISION,
ULTIMATE_UPPER_LIMIT  DOUBLE PRECISION,
ULTIMATE_LOWER_LIMIT  DOUBLE PRECISION,
STATISTIC_PERIOD  INTEGER,
IS_STATISTIC_MIN  INTEGER,
IS_STATISTIC_AVG  INTEGER,
IS_STATISTIC_MAX  INTEGER,
MIN_VALUE DOUBLE PRECISION,
MIN_VALUE_TP  TIMESTAMP,
MAX_VALUE DOUBLE PRECISION,
MAX_VALUE_TP  TIMESTAMP,
AVG_VALUE DOUBLE PRECISION,
AVG_COLLECT_COUNT INTEGER,
WHO_FREEZEINTEGER,
UPDATE_TIME   TIMESTAMP,
MEASURE_VALUE_GUIDVARCHAR(50) NOT NULL,
IS_DELETE INTEGER
);

Alter table measure_value_define add constraint pk_Measure_Value_Define Primary 
Key (Measure_value_id);

Create Index idx_Measure_Value_Define on MEASURE_VALUE_DEFINE(Measure_Point_ID, 
Measure_value_id);

>the table measure_value_define not depend on other tables or proc..., vice 
>versa

>update statement is:
  update Measure_value_define
set data = :data, data_tp = :data_tp, ...
where measure_value_id = :id;

>now, i have a question:
 i use normal update statement with pk to update record, it's performance be so 
slowly,  beside method use rdb$db_key, have other ways ? 

AW: AW: [firebird-support] create a table every year

[Olaf Kluge]

Hi Thomas,

>>> Hello,
>>>
>>> I would like to save the old records in a separate table every year.
>>> On the
>>> end of the year I would Copy the empty main-table to an new table
>>> named like "Backup" and the old year for example. The new backup table
>>> needs no triggers.
>>>
>>> How can I realize this?
>>>
>> What's the problem?
>>
>> Kjell
>>
> I would not use a text-based ddl-string, the main-table has many columns.
Is
> there a simple solution available? For example ""copy"" table etc.

>No direct DDL statement, but if this can involve user interaction via a 
>tool, use "Duplicate Table" in Database Workbench, but you know, I'm 
>biased. ;-)

>Regards,
>Thomas

At the time I'm using a simple solution. There are two generators, one with
the id (PK) and one counts the inserted date. If the second generator
reached a count value, I move the old records in a backup-table like the
year of creating. After this I reset the second generator.

 

For creating the table I use at the time a simple statement-text
(varchar(5000) with DDL) and execute this, without reading the system
tables. It works but may not be the best solution.

 

Thanks.



[Non-text portions of this message have been removed]

Re: [firebird-support] create a table every year

[Mark Rotteveel]

On Tue, 12 Jun 2012 10:36:57 +0200, "Olaf Kluge" 
wrote:
> Hello,
> 
> I would like to save the old records in a separate table every year. On
the
> end of the year I would Copy the empty main-table to an new table named
> like
> "Backup" and the old year for example. The new backup table needs no
> triggers.
> 
> How can I realize this?

If it is just for backup, why not just create a single backup table and
move all records into that table at year end. Then there is no need to
create a table per year (and it is easier to provide a unified view on
'archived' records).

Mark

Re: AW: [firebird-support] create a table every year

[Thomas Steinmaurer]

>>> Hello,
>>>
>>> I would like to save the old records in a separate table every year.
>>> On the
>>> end of the year I would Copy the empty main-table to an new table
>>> named like "Backup" and the old year for example. The new backup table
>>> needs no triggers.
>>>
>>> How can I realize this?
>>>
>> What's the problem?
>>
>> Kjell
>>
> I would not use a text-based ddl-string, the main-table has many columns. Is
> there a simple solution available? For example ""copy"" table etc.

No direct DDL statement, but if this can involve user interaction via a 
tool, use "Duplicate Table" in Database Workbench, but you know, I'm 
biased. ;-)


Regards,
Thomas

Re: [firebird-support] create a table every year

[Thomas Steinmaurer]

> I would like to save the old records in a separate table every year. On the
> end of the year I would Copy the empty main-table to an new table named like
> "Backup" and the old year for example. The new backup table needs no
> triggers.
>
>
>
> How can I realize this?

Are you curious about the:

1) "every year" part, or
2) creating a structural identical table from an existing one

ad 1): The Firebird engine doesn't have a task scheduler / job engine 
component included, thus you can use e.g. the OS task scheduling facility

ad 2): Firebird doesn't support something like CREATE TABLE ... AS 
SELECT ... like Oracle (feel free to vote on: 
http://tracker.firebirdsql.org/browse/CORE-796), so you have to query 
the RDB$ system tables for the field names, data types etc.


Regards,
Thomas

AW: [firebird-support] create a table every year

[Olaf Kluge]

Hello,
> > Hello,
> >
> > I would like to save the old records in a separate table every year.
> > On the
> > end of the year I would Copy the empty main-table to an new table
> > named like "Backup" and the old year for example. The new backup table
> > needs no triggers.
> >
> > How can I realize this?
> >
> What's the problem?
> 
> Kjell
> 
I would not use a text-based ddl-string, the main-table has many columns. Is
there a simple solution available? For example ""copy"" table etc.

Thanks Olaf

Re: [firebird-support] create a table every year

[Kjell Rilbe]

Den 2012-06-12 10:36 skrev Olaf Kluge såhär:
>
> Hello,
>
> I would like to save the old records in a separate table every year. 
> On the
> end of the year I would Copy the empty main-table to an new table 
> named like
> "Backup" and the old year for example. The new backup table needs no
> triggers.
>
> How can I realize this?
>
What's the problem?

Kjell

-- 
--
Kjell Rilbe
DataDIA AB
E-post: kj...@datadia.se
Telefon: 08-761 06 55
Mobil: 0733-44 24 64





[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] create a table every year

[Olaf Kluge]

Hello,

 

I would like to save the old records in a separate table every year. On the
end of the year I would Copy the empty main-table to an new table named like
"Backup" and the old year for example. The new backup table needs no
triggers.

 

How can I realize this?

 

Thanks.

 

Best regards.

 

Olaf

 

 



[Non-text portions of this message have been removed]