Very slow executing many Insert statement than dbexpint.dll

Very slow executing many Insert statement than dbexpint.dll

Postby VadimMescheryakov » Wed 08 Dec 2010 07:55

I testing what dbexpint.dll execute many insert sql very slower than dbexpint.dll.

My test :
36 sec dbexpint.dll.
61 sec dbexpida.dll (I test last and prior version)
Table:
CREATE TABLE CALCSUM_BOR (
IDDOCIN INTEGER NOT NULL,
IDDOCOUT INTEGER NOT NULL,
IDLABEL INTEGER NOT NULL,
KOL NKOL /* NKOL = DOUBLE PRECISION DEFAULT 0 */,
DATEDOCIN DATE,
SUMMA NMONEY /* NMONEY = DOUBLE PRECISION DEFAULT 0 */,
IDSHOP INTEGER,
CENAIN NMONEY /* NMONEY = DOUBLE PRECISION DEFAULT 0 */,
CENAOUT NMONEY /* NMONEY = DOUBLE PRECISION DEFAULT 0 */,
IDIN INTEGER,
DATEDOCOUT DATE,
IDROWIN INTEGER NOT NULL,
IDROWOUT INTEGER NOT NULL,
IDDOCIN_ARCPERIOD INTEGER,
IDDOCOUT_ARCPERIOD INTEGER,
IDROWIN0 INTEGER,
CENAWCOSTING NMONEY /* NMONEY = DOUBLE PRECISION DEFAULT 0 */,
CENAWCOSTINGRUR NMONEY /* NMONEY = DOUBLE PRECISION DEFAULT 0 */,
CENASEBOUTNDS NMONEY /* NMONEY = DOUBLE PRECISION DEFAULT 0 */
);




/******************************************************************************/
/*** Primary Keys ***/
/******************************************************************************/

ALTER TABLE CALCSUM_BOR ADD CONSTRAINT CALCSUM_BOR_102 PRIMARY KEY (IDROWIN, IDROWOUT, IDLABEL);


/******************************************************************************/
/*** Indices ***/
/******************************************************************************/

CREATE INDEX CSBOR_DATEDOCIN ON CALCSUM_BOR (DATEDOCIN);
CREATE INDEX CSBOR_DATEDOCOUT ON CALCSUM_BOR (DATEDOCOUT);
CREATE INDEX CSBOR_IDDOCIN ON CALCSUM_BOR (IDDOCIN);
CREATE INDEX CSBOR_IDDOCOUT ON CALCSUM_BOR (IDDOCOUT);
CREATE INDEX CSBOR_IDIN ON CALCSUM_BOR (IDIN);
CREATE INDEX CSBOR_IDLABEL ON CALCSUM_BOR (IDLABEL);
CREATE INDEX CSBOR_IDROWIN0 ON CALCSUM_BOR (IDROWIN0);
CREATE INDEX CSBOR_IDROWOUT ON CALCSUM_BOR (IDROWOUT);


Code :
var
i: Integer;
tr: TTransactionDesc;

tr.IsolationLevel := xilREADCOMMITTED;
tr.CustomIsolation := ISC_TPB_WRITE + ISC_TPB_READ_COMMITTED + ISC_TPB_REC_VERSION + ISC_TPB_NO_WAIT;

tr.TransactionID := 10;

SQLConnection1.StartTransaction(tr);
SQLQuery1.TransactionLevel := tr.TransactionID;
SQLQuery1.SQL.Clear;
SQLQuery1.SQL.Add('insert into calcsum_bor ');
SQLQuery1.SQL.Add(' (iddocin, iddocout, idlabel, kol, datedocin, idshop, cenain, ');
SQLQuery1.SQL.Add(' cenaout, idin,datedocout,idrowin, idrowout, idrowin0, cenawcosting, ');
SQLQuery1.SQL.Add(' cenawcostingrur, cenaseboutnds) values ( :iddocin, :iddocout, :idlabel, :kol, :datedocin, :idshop, :cenain,');
SQLQuery1.SQL.Add(' :cenaout, :idin, :datedocout,:idrowin, :idrowout, :idrowin0, :cenawcosting,');
SQLQuery1.SQL.Add(' :cenawcostingrur, :cenaseboutnds)');

for i := 50001 to 100000 do
begin
SQLQuery1.ParamByName('iddocin').AsInteger := i;
SQLQuery1.ParamByName('idin').AsInteger := i;
SQLQuery1.ParamByName('idRowIn0').AsInteger := i;
SQLQuery1.ParamByName('iddocout').AsInteger := i;
SQLQuery1.ParamByName('idrowin').AsInteger := i;
SQLQuery1.ParamByName('idrowout').AsInteger := i;
SQLQuery1.ParamByName('idLabel').AsInteger := i;
SQLQuery1.ParamByName('idShop').AsInteger := i;
SQLQuery1.ParamByName('kol').AsFloat := 10;
SQLQuery1.ParamByName('cenawcosting').AsFloat := i + 0.11212;
SQLQuery1.ParamByName('cenawcostingrur').AsFloat := i + 0.11212;
SQLQuery1.ParamByName('cenaseboutnds').AsFloat := i + 0.11212;
SQLQuery1.ParamByName('cenain').AsFloat := i + 0.11212;
SQLQuery1.ParamByName('cenaout').AsFloat := i + 0.11212;
SQLQuery1.ParamByName('DateDocIn').AsDate := Date;
SQLQuery1.ParamByName('DateDocOut').AsDate := Date;
try
SQLQuery1.ExecSQL;
except
end;
end;
SQLConnection1.Commit(tr);
VadimMescheryakov
 
Posts: 9
Joined: Tue 30 Nov 2010 04:17

Postby AlexP » Thu 09 Dec 2010 10:35

Hello,

To increase the performance you should set the Prepared options of TSQLConnection to true, like

const
coPrepared = TSQLConnectionOption(302); // boolean
. . .
SQLConnection1.Connected := True;
SQLConnection1.SQLConnection.SetOption(coPrepared, Integer(True));

For more information please see the .\Devart\DbxIda\Readme.html file.
AlexP
Devart Team
 
Posts: 5525
Joined: Tue 10 Aug 2010 11:35

Yes! Is working.

Postby VadimMescheryakov » Fri 10 Dec 2010 10:46

Thanks. It's working fast.
Last edited by VadimMescheryakov on Fri 10 Dec 2010 15:55, edited 1 time in total.
VadimMescheryakov
 
Posts: 9
Joined: Tue 30 Nov 2010 04:17

Postby AlexP » Fri 10 Dec 2010 11:00

Hello,

It is good to see that this problem was solved. If any other questions come up, please contact us.
AlexP
Devart Team
 
Posts: 5525
Joined: Tue 10 Aug 2010 11:35


Return to dbExpress driver for InterBase & Firebird