| SQL Open too slow |
|
| News Group: embarcadero.public.delphi.database.interbase_express |
Hello,
I'm using this code to handle request (3000 records: al record in database), but it took 80 second to execute.
DDL:
{code}
CREATE TABLE CERBER (
RECORD_AUTOINC BIGINT,
RECORD_INDICATOR BIGINT,
ACCOUNT VARCHAR(16),
CYEAR INTEGER,
CMONTH INTEGER,
DATETIME TIMESTAMP,
FILENAME VARCHAR(5),
CODE VARCHAR(5),
DATA01 DOUBLE PRECISION,
DATA02 DOUBLE PRECISION,
DATA03 DOUBLE PRECISION,
DATA04 DOUBLE PRECISION,
DATA05 DOUBLE PRECISION,
DATA06 DOUBLE PRECISION,
DATA07 DOUBLE PRECISION,
DATA08 DOUBLE PRECISION,
DATA09 DOUBLE PRECISION,
DATA10 DOUBLE PRECISION,
DATA11 DOUBLE PRECISION,
DATA12 DOUBLE PRECISION,
DATA13 DOUBLE PRECISION,
DATA14 DOUBLE PRECISION,
DATA15 DOUBLE PRECISION,
DATA16 DOUBLE PRECISION,
DATA17 DOUBLE PRECISION,
DATA18 DOUBLE PRECISION,
DATA19 DOUBLE PRECISION,
DATA20 DOUBLE PRECISION,
DATA21 DOUBLE PRECISION,
DATA22 DOUBLE PRECISION,
DATA23 DOUBLE PRECISION,
DATA24 DOUBLE PRECISION,
DATA25 DOUBLE PRECISION,
LIB01 BLOB SUB_TYPE 1 SEGMENT SIZE 16384,
LIB02 BLOB SUB_TYPE 1 SEGMENT SIZE 16384,
LIB03 BLOB SUB_TYPE 1 SEGMENT SIZE 16384,
LIB04 BLOB SUB_TYPE 1 SEGMENT SIZE 16384,
LIB05 BLOB SUB_TYPE 1 SEGMENT SIZE 16384,
LIB06 BLOB SUB_TYPE 1 SEGMENT SIZE 16384,
LIB07 BLOB SUB_TYPE 1 SEGMENT SIZE 16384,
LIB08 BLOB SUB_TYPE 1 SEGMENT SIZE 16384,
LIB09 BLOB SUB_TYPE 1 SEGMENT SIZE 16384,
LIB10 BLOB SUB_TYPE 1 SEGMENT SIZE 16384,
LIB11 BLOB SUB_TYPE 1 SEGMENT SIZE 16384,
LIB12 BLOB SUB_TYPE 1 SEGMENT SIZE 16384,
LIB13 BLOB SUB_TYPE 1 SEGMENT SIZE 16384,
LIB14 BLOB SUB_TYPE 1 SEGMENT SIZE 16384,
LIB15 BLOB SUB_TYPE 1 SEGMENT SIZE 16384,
LIB16 BLOB SUB_TYPE 1 SEGMENT SIZE 16384,
LIB17 BLOB SUB_TYPE 1 SEGMENT SIZE 16384,
LIB18 BLOB SUB_TYPE 1 SEGMENT SIZE 16384,
LIB19 BLOB SUB_TYPE 1 SEGMENT SIZE 16384,
LIB20 BLOB SUB_TYPE 1 SEGMENT SIZE 16384,
LIB21 BLOB SUB_TYPE 1 SEGMENT SIZE 16384,
LIB22 BLOB SUB_TYPE 1 SEGMENT SIZE 16384,
LIB23 BLOB SUB_TYPE 1 SEGMENT SIZE 16384,
LIB24 BLOB SUB_TYPE 1 SEGMENT SIZE 16384,
LIB25 BLOB SUB_TYPE 1 SEGMENT SIZE 16384
);
/******************************************************************************/
/**** Indices ****/
/******************************************************************************/
CREATE DESCENDING INDEX CERBER_ACCOUNT ON CERBER (ACCOUNT);
CREATE DESCENDING INDEX CERBER_CMONTH ON CERBER (CMONTH);
CREATE DESCENDING INDEX CERBER_CODE ON CERBER (CODE);
CREATE DESCENDING INDEX CERBER_CYEAR ON CERBER (CYEAR);
CREATE DESCENDING INDEX CERBER_FILENAME ON CERBER (FILENAME);
CREATE UNIQUE DESCENDING INDEX CERBER_RECORD_AUTOINC ON CERBER (RECORD_AUTOINC);
{code}
{code}
var
Qry : TIBQuery;
begin
...
Qry.Transaction.StartTransaction;
Qry.SQL.Text := SQLText; // SELECT * FROM CERBER WHERE (Account=:P0000) AND (CYear=:P0001) AND (CMonth=:P0002)
Qry.Open
... Fetch Data ...
Qry.Transaction.Commit;
end;
{code}
Is it necessary to execute the Open statement between StartTransaction and Commit ?
Regards.
|
 | Vote for best question.
|
| | Score: 0 |
# Vote: 0 |
|
|
| Re: SQL Open too slow |
|
| News Group: embarcadero.public.delphi.database.interbase_express |
Hafedh TRIMECHE wrote:
> Hello,
>
> I'm using this code to handle request (3000 records: al record in database), but it took 80 second to execute.
>
> DDL:
> {code}
> CREATE TABLE CERBER (
> RECORD_AUTOINC BIGINT,
> RECORD_INDICATOR BIGINT,
> ACCOUNT VARCHAR(16),
> CYEAR INTEGER,
> CMONTH INTEGER,
> DATETIME TIMESTAMP,
> FILENAME VARCHAR(5),
> CODE VARCHAR(5),
> DATA01 DOUBLE PRECISION,
> DATA02 DOUBLE PRECISION,
> DATA03 DOUBLE PRECISION,
> DATA04 DOUBLE PRECISION,
> DATA05 DOUBLE PRECISION,
> DATA06 DOUBLE PRECISION,
> DATA07 DOUBLE PRECISION,
> DATA08 DOUBLE PRECISION,
> DATA09 DOUBLE PRECISION,
> DATA10 DOUBLE PRECISION,
> DATA11 DOUBLE PRECISION,
> DATA12 DOUBLE PRECISION,
> DATA13 DOUBLE PRECISION,
> DATA14 DOUBLE PRECISION,
> DATA15 DOUBLE PRECISION,
> DATA16 DOUBLE PRECISION,
> DATA17 DOUBLE PRECISION,
> DATA18 DOUBLE PRECISION,
> DATA19 DOUBLE PRECISION,
> DATA20 DOUBLE PRECISION,
> DATA21 DOUBLE PRECISION,
> DATA22 DOUBLE PRECISION,
> DATA23 DOUBLE PRECISION,
> DATA24 DOUBLE PRECISION,
> DATA25 DOUBLE PRECISION,
> LIB01 BLOB SUB_TYPE 1 SEGMENT SIZE 16384,
> LIB02 BLOB SUB_TYPE 1 SEGMENT SIZE 16384,
> LIB03 BLOB SUB_TYPE 1 SEGMENT SIZE 16384,
> LIB04 BLOB SUB_TYPE 1 SEGMENT SIZE 16384,
> LIB05 BLOB SUB_TYPE 1 SEGMENT SIZE 16384,
> LIB06 BLOB SUB_TYPE 1 SEGMENT SIZE 16384,
> LIB07 BLOB SUB_TYPE 1 SEGMENT SIZE 16384,
> LIB08 BLOB SUB_TYPE 1 SEGMENT SIZE 16384,
> LIB09 BLOB SUB_TYPE 1 SEGMENT SIZE 16384,
> LIB10 BLOB SUB_TYPE 1 SEGMENT SIZE 16384,
> LIB11 BLOB SUB_TYPE 1 SEGMENT SIZE 16384,
> LIB12 BLOB SUB_TYPE 1 SEGMENT SIZE 16384,
> LIB13 BLOB SUB_TYPE 1 SEGMENT SIZE 16384,
> LIB14 BLOB SUB_TYPE 1 SEGMENT SIZE 16384,
> LIB15 BLOB SUB_TYPE 1 SEGMENT SIZE 16384,
> LIB16 BLOB SUB_TYPE 1 SEGMENT SIZE 16384,
> LIB17 BLOB SUB_TYPE 1 SEGMENT SIZE 16384,
> LIB18 BLOB SUB_TYPE 1 SEGMENT SIZE 16384,
> LIB19 BLOB SUB_TYPE 1 SEGMENT SIZE 16384,
> LIB20 BLOB SUB_TYPE 1 SEGMENT SIZE 16384,
> LIB21 BLOB SUB_TYPE 1 SEGMENT SIZE 16384,
> LIB22 BLOB SUB_TYPE 1 SEGMENT SIZE 16384,
> LIB23 BLOB SUB_TYPE 1 SEGMENT SIZE 16384,
> LIB24 BLOB SUB_TYPE 1 SEGMENT SIZE 16384,
> LIB25 BLOB SUB_TYPE 1 SEGMENT SIZE 16384
> );
>
> /******************************************************************************/
> /**** Indices ****/
> /******************************************************************************/
>
> CREATE DESCENDING INDEX CERBER_ACCOUNT ON CERBER (ACCOUNT);
> CREATE DESCENDING INDEX CERBER_CMONTH ON CERBER (CMONTH);
> CREATE DESCENDING INDEX CERBER_CODE ON CERBER (CODE);
> CREATE DESCENDING INDEX CERBER_CYEAR ON CERBER (CYEAR);
> CREATE DESCENDING INDEX CERBER_FILENAME ON CERBER (FILENAME);
> CREATE UNIQUE DESCENDING INDEX CERBER_RECORD_AUTOINC ON CERBER (RECORD_AUTOINC);
> {code}
>
> {code}
> var
> Qry : TIBQuery;
> begin
> ...
> Qry.Transaction.StartTransaction;
> Qry.SQL.Text := SQLText; // SELECT * FROM CERBER WHERE (Account=:P0000) AND (CYear=:P0001) AND (CMonth=:P0002)
> Qry.Open
> ... Fetch Data ...
> Qry.Transaction.Commit;
> end;
> {code}
>
> Is it necessary to execute the Open statement between StartTransaction and Commit ?
>
> Regards.
Yes you must open the dataset, that is what actually executes the SQL. What is
the plan? Also why descending indexes. They are only needed if you are trying
to solve a max problem, in all other cases ascending is more efficient.
I suspect the year and month indexes are highly unselective. I would drop the
account index and create a new index on account, year, month.
--
Jeff Overcash (TeamB)
(Please do not email me directly unless asked. Thank You)
And so I patrol in the valley of the shadow of the tricolor
I must fear evil. For I am but mortal and mortals can only die.
Asking questions, pleading answers from the nameless
faceless watchers that stalk the carpeted corridors of Whitehall.
(Fish)
|
| Vote for best answer.
|
| | Score: 0 |
# Vote: 0 |
|
|
| |
Mega Search
Sign Up 
