All geek questions in one place

Inserting pandas data in oracle extremely slow

I am working on pandasdataframes. The purpose of my application is to do some analysis in the csv file, and as soon as this is done, insert this csv file into the database oracle.

To insert an oracle into the database, I used the to_sqllibrary command pandas. But to insert 300,000 lines, my code took about 2 hours and ten minutes. However, when I used the database MySQLfor the same analysis and the same input size, it took only 90 seconds.

I do all my actions inside a lubuntu virtual machine. For reference, you can find the corresponding code below.

data_frame.to_sql(name='RSA_DATA', con=get_engine(), if_exists='append',
                           index=False, chunksize=config.CHUNK_SIZE)
input_data_list = get_rsa_object_list(data_frame)

I used the same CHUNK_SIZEfor both cases (which was set to 500).

Can someone tell me what could be causing this and how can I get around this? I cannot change my database system from oracleto any other database. I used MySQLto make sure that the problem is not related to my code.

Thanks in advance.

The following is the command CREATE TABLEfor the database oracle:

  CREATE TABLE "CRISDEV"."RSA_DATA" 
   (    "GENE_SYMBOL" VARCHAR2(50 BYTE), 
    "GENEID" NUMBER, 
    "CRISPR" VARCHAR2(75 BYTE), 
    "RSASCORE" NUMBER, 
    "LOGP" NUMBER, 
    "BEST_ACTIVITY" NUMBER, 
    "RSA_HIT" NUMBER, 
    "HIT_PER_GENE" NUMBER, 
    "TOTAL_CRISPRS" NUMBER, 
    "RSA_RANK" NUMBER, 
    "SAMPLE_NAME" VARCHAR2(10 BYTE), 
    "FOLD_CHANGE" NUMBER, 
    "EXPERIMENT_NUM" NUMBER, 
    "CELL_LINE" VARCHAR2(75 BYTE), 
    "CRISPR_LIB" VARCHAR2(75 BYTE), 
    "CRISPR_LOT" VARCHAR2(25 BYTE), 
    "CAS9_VENDOR" VARCHAR2(50 BYTE), 
    "CAS9_LOT" VARCHAR2(25 BYTE), 
    "CRISPR_INFECT_DATE" DATE, 
    "CAS9_INFECT_DATE" DATE, 
    "CAS9_MOI" NUMBER, 
    "FTE" VARCHAR2(50 BYTE), 
    "SAMPLE_NAME_LONG" VARCHAR2(150 BYTE), 
    "INDICATION" VARCHAR2(100 BYTE), 
    "LOW_CRISPR_COUNT" NUMBER DEFAULT 0, 
    "MODIFICATION" VARCHAR2(20 BYTE), 
    "TIMEPOINT" VARCHAR2(10 BYTE)
   ) 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 "CRISDEV_DATA" ;

  CREATE INDEX "CRISDEV"."C20150312_RSA_CELLLINE" ON "CRISDEV"."RSA_DATA" ("CELL_LINE") 
  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 "CRISDEV_IDX" ;

  CREATE INDEX "CRISDEV"."C20150312_RSA_CRISPRLIB" ON "CRISDEV"."RSA_DATA" ("CRISPR_LIB") 
  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 "CRISDEV_IDX" ;

  CREATE INDEX "CRISDEV"."C20150312_RSA_GENEID" ON "CRISDEV"."RSA_DATA" ("GENEID") 
  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 "CRISDEV_IDX" ;

  CREATE INDEX "CRISDEV"."C20150312_RSA_GENESYMBOL" ON "CRISDEV"."RSA_DATA" ("GENE_SYMBOL") 
  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 "CRISDEV_IDX" ;

  CREATE INDEX "CRISDEV"."C20150312_RSA_SAMPLELONG" ON "CRISDEV"."RSA_DATA" ("SAMPLE_NAME_LONG") 
  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 "CRISDEV_IDX" ;

  CREATE INDEX "CRISDEV"."C20150312_RSA_SAMPLENAME" ON "CRISDEV"."RSA_DATA" ("SAMPLE_NAME") 
  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 "CRISDEV_IDX" ;

CREATE TABLEThe command for is MySQLas follows:

CREATE TABLE `RSA_DATA` (
  `GENE_SYMBOL` varchar(50) DEFAULT NULL,
  `GENEID` int(11) DEFAULT NULL,
  `CRISPR` varchar(75) DEFAULT NULL,
  `RSASCORE` float DEFAULT NULL,
  `LOGP` float DEFAULT NULL,
  `BEST_ACTIVITY` float DEFAULT NULL,
  `RSA_HIT` int(11) DEFAULT NULL,
  `HIT_PER_GENE` int(11) DEFAULT NULL,
  `TOTAL_CRISPRS` int(11) DEFAULT NULL,
  `RSA_RANK` int(11) DEFAULT NULL,
  `SAMPLE_NAME` varchar(10) DEFAULT NULL,
  `FOLD_CHANGE` int(11) DEFAULT NULL,
  `EXPERIMENT_NUM` int(11) DEFAULT NULL,
  `CELL_LINE` varchar(75) DEFAULT NULL,
  `CRISPR_LIB` varchar(75) DEFAULT NULL,
  `CRISPR_LOT` varchar(25) DEFAULT NULL,
  `CAS9_VENDOR` varchar(50) DEFAULT NULL,
  `CAS9_LOT` varchar(25) DEFAULT NULL,
  `CRISPR_INFECT_DATE` date DEFAULT NULL,
  `CAS9_INFECT_DATE` date DEFAULT NULL,
  `CAS9_MOI` int(11) DEFAULT NULL,
  `FTE` varchar(50) DEFAULT NULL,
  `SAMPLE_NAME_LONG` varchar(150) DEFAULT NULL,
  `INDICATION` varchar(100) DEFAULT NULL,
  `LOW_CRISPR_COUNT` int(11) DEFAULT NULL,
  `MODIFICATION` varchar(20) DEFAULT NULL,
  `TIMEPOINT` varchar(10) DEFAULT NULL
);
+4
source share
1 answer

some comments on your question:

  • you can play with different chunksize
  • There are 6 indexes on the Oracle / ExaData side, which, of course, makes it slower
  • Verify that your ExaData is busy / overloaded by doing something else during this load operation.
  • . [ / // ], (nologging): insert /*+APPEND*/ into ...
  • ( Oracle) , ,
+3

Source: https://habr.com/ru/post/1655100/

More articles:


All Articles