> --- In firebird-support@yahoogroups.com, Thomas Steinmaurer<ts@...> wrote:
>>
>>> --- In firebird-support@yahoogroups.com, Thomas Steinmaurer<ts@> wrote:
>>>>
>>>>> --- In firebird-support@yahoogroups.com, "Fabiano"<fabianoaspro@> 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 20000, 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 1000000 to 5000000
> 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
affects your commit interval.

* If you are using Firebird 2.5, try to use the Trace API to exactly see
what's happening at database level.


Regards,
Thomas