Perfomance Problems OracleDataSet vs. FDQuery-FireDac

[mjz1000]

Hey allroundautomations-team,
I have a problem. I use Direct Oracle Access Driver a lot of years and I am very happy with it. But now I have same perfomance problems.
I work with Delphi Rio 10.3.3 Enterprise, Oracle 12, Direct Oracle Access 4.1.3.5 and Windows 10 Prof. The SQL-String is simply easy
select t.*, t.rowid from REV_VPL_STB t.
But now my question, I make several test with the FireDac FDQuery and OracleDateSet and OracleQuery. The FireDac FDQuery works faster then the OracleDataSet and OracleQuery. Do you have a solution for this ?
I didn't want to change my programm-source to get it faster with the FireDac-Driver.
Greetings from Germany and keep safe
J

 

[mjz1000]

I forgot the table-structure:

CREATE TABLE "C##VPL_SB_REV"."REV_VPL_STB"
( "PLAN_ID" NUMBER NOT NULL ENABLE,
"PARENT_ID" NUMBER,
"GROUP_ID" NUMBER,
"START_DATE" TIMESTAMP (6),
"ACTUAL_START" NUMBER,
"FINISH_DATE" TIMESTAMP (6),
"ACTUAL_FINISH" NUMBER,
"CAPTION_SUBJECT" VARCHAR2(255),
"EVENTTYPE" NUMBER,
"LABELCOLOR" NUMBER,
"LOCATION" VARCHAR2(255),
"MESSAGE" VARCHAR2(1024),
"OPTIONS" NUMBER,
"RECURRENCE_INDEX" NUMBER,
"RECURRENCE_INFO" BLOB,
"REMINDER_DATE" TIMESTAMP (6),
"REMINDER_MINUTES_BEFORE" NUMBER,
"REMINDER_RESOURCES_DATA" BLOB,
"RESOURCE_ID" NUMBER,
"STATE" NUMBER,
"TASK_COMPLETE_FIELD" NUMBER,
"TASK_INDEX_FIELD" NUMBER,
"TASK_LINKS_FIELD" BLOB,
"TASK_STATUS_FIELD" NUMBER,
"SYNC_ID_FIELD" NUMBER,
"INPUT_FLAG" VARCHAR2(5),
"KATEGORIE_ID" VARCHAR2(75),
"AIM_ID" VARCHAR2(15),
"STATUS_AB" VARCHAR2(5) DEFAULT 'FALSE',
"STATUS_RESERVE" VARCHAR2(5) DEFAULT 'FALSE',
"STATUS_REP_REV" VARCHAR2(5) DEFAULT 'FALSE',
"STATUS_GWG" VARCHAR2(5) DEFAULT 'FALSE',
"STATUS_ERDEN_KURZSCHL" VARCHAR2(5) DEFAULT 'FALSE',
"STATUS_WANDLER" VARCHAR2(5) DEFAULT 'FALSE',
"STATUS_VE" VARCHAR2(5) DEFAULT 'FALSE',
"BEMERKUNG" CLOB,
"ABGEMELDET" DATE,
"ABGEMELDET_NAME" VARCHAR2(250),
"AV_PLAN" CLOB,
"STATUS_BKLAR_IB" VARCHAR2(15),
"STATUS_BKLAR_IB_DATE" DATE,
"STATUS_BKLAR_IB_NAME" VARCHAR2(250),
"ACTIVE_FLAG_RPL" VARCHAR2(5) DEFAULT 'FALSE',
"ACTIVE_FLAG_VPL" VARCHAR2(5) DEFAULT 'FALSE',
"ACTIVE_FLAG_STB" VARCHAR2(5) DEFAULT 'FALSE',
"DELETE_FLAG" VARCHAR2(5) DEFAULT 'FALSE',
"USER_CHANGE_DATE" VARCHAR2(50) DEFAULT Current_timestamp,
"USER_NAME" VARCHAR2(75) DEFAULT USER,
CONSTRAINT "PLAN_ID_REV_PK" PRIMARY KEY ("PLAN_ID")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "JM_DATEN" ENABLE
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "JM_DATEN"
LOB ("RECURRENCE_INFO") STORE AS SECUREFILE (
TABLESPACE "JM_DATEN" ENABLE STORAGE IN ROW CHUNK 8192
NOCACHE LOGGING NOCOMPRESS KEEP_DUPLICATES
STORAGE(INITIAL 106496 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT))
LOB ("REMINDER_RESOURCES_DATA") STORE AS SECUREFILE (
TABLESPACE "JM_DATEN" ENABLE STORAGE IN ROW CHUNK 8192
NOCACHE LOGGING NOCOMPRESS KEEP_DUPLICATES
STORAGE(INITIAL 106496 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT))
LOB ("TASK_LINKS_FIELD") STORE AS SECUREFILE (
TABLESPACE "JM_DATEN" ENABLE STORAGE IN ROW CHUNK 8192
NOCACHE LOGGING NOCOMPRESS KEEP_DUPLICATES
STORAGE(INITIAL 106496 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT))
LOB ("BEMERKUNG") STORE AS SECUREFILE (
TABLESPACE "JM_DATEN" ENABLE STORAGE IN ROW CHUNK 8192
NOCACHE LOGGING NOCOMPRESS KEEP_DUPLICATES
STORAGE(INITIAL 106496 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT))
LOB ("AV_PLAN") STORE AS SECUREFILE (
TABLESPACE "JM_DATEN" ENABLE STORAGE IN ROW CHUNK 8192
NOCACHE LOGGING NOCOMPRESS KEEP_DUPLICATES
STORAGE(INITIAL 106496 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)) ;
CREATE UNIQUE INDEX "C##VPL_SB_REV"."SYS_IL0000093078C00036$$" ON "C##VPL_SB_REV"."REV_VPL_STB" (
PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "JM_DATEN"
PARALLEL (DEGREE 0 INSTANCES 0) ;
CREATE UNIQUE INDEX "C##VPL_SB_REV"."SYS_IL0000093078C00039$$" ON "C##VPL_SB_REV"."REV_VPL_STB" (
PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "JM_DATEN"
PARALLEL (DEGREE 0 INSTANCES 0) ;
CREATE UNIQUE INDEX "C##VPL_SB_REV"."PLAN_ID_REV_PK" ON "C##VPL_SB_REV"."REV_VPL_STB" ("PLAN_ID")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "JM_DATEN" ;
CREATE UNIQUE INDEX "C##VPL_SB_REV"."SYS_IL0000093078C00015$$" ON "C##VPL_SB_REV"."REV_VPL_STB" (
PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "JM_DATEN"
PARALLEL (DEGREE 0 INSTANCES 0) ;
CREATE UNIQUE INDEX "C##VPL_SB_REV"."SYS_IL0000093078C00018$$" ON "C##VPL_SB_REV"."REV_VPL_STB" (
PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "JM_DATEN"
PARALLEL (DEGREE 0 INSTANCES 0) ;
CREATE UNIQUE INDEX "C##VPL_SB_REV"."SYS_IL0000093078C00023$$" ON "C##VPL_SB_REV"."REV_VPL_STB" (
PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "JM_DATEN"
PARALLEL (DEGREE 0 INSTANCES 0) ;
ALTER TABLE "C##VPL_SB_REV"."REV_VPL_STB" MODIFY ("PLAN_ID" NOT NULL ENABLE);
ALTER TABLE "C##VPL_SB_REV"."REV_VPL_STB" ADD CONSTRAINT "PLAN_ID_REV_PK" PRIMARY KEY ("PLAN_ID")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "JM_DATEN" ENABLE;

The table has 2900 Records.

 

[Marco Kalter]

As a test: does it help if you omit the LOB columns?

 

[mjz1000]

Hey Marco,

I need the CLOB Fields.

Sorry.

 

[Marco Kalter]

Yes, but it's just a test to check if that is the cause.

 

[mjz1000]

I could only omit two LOB Fields. Is it possible that you build a faster load for LOB Fields in a upgrade version.

Thank you for your help.

Greetings

J