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);
Very slow executing many Insert statement than dbexpint.dll
-
- Posts: 11
- Joined: Tue 30 Nov 2010 04:17
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.
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.
-
- Posts: 11
- Joined: Tue 30 Nov 2010 04:17
Yes! Is working.
Thanks. It's working fast.
Last edited by VadimMescheryakov on Fri 10 Dec 2010 15:55, edited 1 time in total.