7 Replies Latest reply: Nov 2, 2012 9:08 AM by Chrisjenkins-Oracle RSS

    Row Insert in Timesten takes more time than Oracle

    856103
      Hi,

      I have a Timesten IMDB (11.2.1.8.0 (64 bit Linux/x86_64) with an underlying Oracle Database 11Gr2.

      Sys.odbc.ini entry is :

      [DSN_NAME]
      Driver=/application/TimesTen/matrix/lib/libtten.so
      DataStore=/application/TimesTen/DSN_NAME_datastore/DSN_NAME_DS_DIR
      LogDir=/logs_timeten/DSN_NAME_logdir
      PermSize=8000
      TempSize=250
      PLSQL=1
      DatabaseCharacterSet=WE8MSWIN1252
      OracleNetServiceName=DBNAME
      Connections=500
      PassThrough=0
      SQLQueryTimeout=250
      LogBufMB=512
      LogFileSize=512
      LogPurge=1


      When I try to insert a simple row in a table in an asyc cache group in Timesten it takes 3 ms (it has 6 indexes on it). On removing 4 indexes the performance improves to 1 ms. However inserting the same row on Oracle (with 6 indexes) takes 1.2 ms.

      How can we improve the insert row performance in Timesten ? Kindly assist.

      Regards,
      Karan

      PS: During the test run, we monitored deadlocks and log buffer waits with the following query and both values never changed from zero

      select PERM_ALLOCATED_SIZE,PERM_IN_USE_SIZE,TEMP_ALLOCATED_SIZE,TEMP_IN_USE_SIZE,DEADLOCKS,LOG_FS_READS,LOG_FS_WRITES,LOG_BUFFER_WAITS from sys.monitor;

      Edited by: 853100 on Nov 2, 2012 4:19 AM
        • 1. Re: Row Insert in Timesten takes more time than Oracle
          Chrisjenkins-Oracle
          Even 1 ms is a very long time to insert one row in TimesTen. How exactly are you measuring this and what exactly are you measuring? Are you running in direct mode or client/server mode (to TimesTen)? Are you just measuring a single INSERT (extremely inaccurate) or are you actually measuring the time required for many inserts and then dividing the time by the number of inserts?

          Chris
          • 2. Re: Row Insert in Timesten takes more time than Oracle
            Gennady Sigalaev
            Hi 853100,
            When I try to insert a simple row in a table in an asyc cache group in Timesten it takes 3 ms (it has 6 indexes on it)
            My VmWare virtual machine with 1,5Gb memory and 1 core.
            Command> create table t1 (id number primary key, id2 number, id3 number, id4 number, id5 number, id6 number, id7 number, id8 number, id9 number, id10 number);
            Command> create unique index t1_indx1 ON t1 ( id2);
            Command> create unique index t1_indx2 ON t1 ( id3);
            Command> create unique index t1_indx3 ON t1 ( id4);
            Command> create unique index t1_indx4 ON t1 ( id5);
            Command> create unique index t1_indx5 ON t1 ( id6);
            Command> create unique index t1_indx6 ON t1 ( id7);
            Command> create unique index t1_indx7 ON t1 ( id8);
            Command> create unique index t1_indx8 ON t1 ( id9);
            Command> create unique index t1_indx9 ON t1 ( id10);
            Command>
            Command> desc t1;
            
            Table ORATT.T1:
              Columns:
               *ID                              NUMBER NOT NULL
                ID2                             NUMBER
                ID3                             NUMBER
                ID4                             NUMBER
                ID5                             NUMBER
                ID6                             NUMBER
                ID7                             NUMBER
                ID8                             NUMBER
                ID9                             NUMBER
                ID10                            NUMBER
            
            1 table found.
            (primary key columns are indicated with *)
            Command> indexes;
            
            Indexes on table ORATT.T1:
              T1: unique range index on columns:
                ID
              T1_INDX1: unique range index on columns:
                ID2
              T1_INDX2: unique range index on columns:
                ID3
              T1_INDX3: unique range index on columns:
                ID4
              T1_INDX4: unique range index on columns:
                ID5
              T1_INDX5: unique range index on columns:
                ID6
              T1_INDX6: unique range index on columns:
                ID7
              T1_INDX7: unique range index on columns:
                ID8
              T1_INDX8: unique range index on columns:
                ID9
              T1_INDX9: unique range index on columns:
                ID10
              10 indexes found.
            
            10 indexes found on 1 table.
            Command>
            Command> set timing 1;
            Command> insert into t1 (id,id2,id3,id4,id5,id6,id7,id8,id9,id10) values (1,1,1,1,1,1,1,1,1,1);
            1 row inserted.
            Execution time (SQLExecute) = 0.000353 seconds.
            Command> insert into t1 (id,id2,id3,id4,id5,id6,id7,id8,id9,id10) values (2,2,2,2,2,2,2,2,2,2);
            1 row inserted.
            Execution time (SQLExecute) = 0.000164 seconds.
            Command> insert into t1 (id,id2,id3,id4,id5,id6,id7,id8,id9,id10) values (3,3,3,3,3,3,3,3,3,3);
            1 row inserted.
            Execution time (SQLExecute) = 0.000115 seconds.
            Command> insert into t1 (id,id2,id3,id4,id5,id6,id7,id8,id9,id10) values (4,4,4,4,4,4,4,4,4,4);
            1 row inserted.
            Execution time (SQLExecute) = 0.000614 seconds.
            Command> insert into t1 (id,id2,id3,id4,id5,id6,id7,id8,id9,id10) values (5,5,5,5,5,5,5,5,5,5);
            1 row inserted.
            Execution time (SQLExecute) = 0.000087 seconds.
            Basically, the first insert should be executed longer, because in this case a new page will be allocated.

            Best regards,
            Gennady
            • 3. Re: Row Insert in Timesten takes more time than Oracle
              856103
              Hi Chris/Gennady,

              When we carried out a test case like Gennady, each insert took 0.05 ms on an average for each insert. However when we time it through our pro*c code and time is put before and after the individual query, we see that the query takes 1 ms.

              We are using direct mode.

              What can we do next ??

              Thanks,
              karan
              • 4. Re: Row Insert in Timesten takes more time than Oracle
                Gennady Sigalaev
                Hi Karan,

                Could you please post the pro*c code you use?

                Best regards,
                Gennady
                • 5. Re: Row Insert in Timesten takes more time than Oracle
                  Chrisjenkins-Oracle
                  And please be sure to include the timing code as well...

                  Chris
                  • 6. Re: Row Insert in Timesten takes more time than Oracle
                    856103
                    Hi Chris/Gennady,

                    Pro*c code using for insert in table
                    logTimestamp("BEFORE inserting in orders table");
                    if ( strncmp(lvar_ord_exch_id.arr,"NSE",3) ==0 )
                    { 
                    if(tmpExchOrderNo == -1)
                    insertExchOrderNo = NULL;
                    else 
                    insertExchOrderNo = tmpExchOrderNo;
                    }
                    else if ( strncmp(lvar_ord_exch_id.arr,"BSE",3) ==0 )
                    {
                    if(tmpExchOrderNo == -1)
                    insertExchOrderNo = NULL;
                    else 
                    insertExchOrderNo = tmpExchOrderNo;
                    }
                    
                    lvar_ord_acc_code.len = strlen (lvar_ord_acc_code.arr);
                    sprintf (lv_UserIdOrLogPktId.arr,"%d",UserIdOrLogPktId );
                    lv_UserIdOrLogPktId.len = strlen (lv_UserIdOrLogPktId.arr) ;
                    
                    lEquBseUserCode.len = fTrim(lEquBseUserCode.arr,16);
                    lvar_ord_buyback_flg.len = fTrim(lvar_ord_buyback_flg.arr,1);
                    
                    lvar_ord_exch_id.len = fTrim(lvar_ord_exch_id.arr,3);
                    EXEC SQL AT :db_conn
                    INSERT INTO ORDERS(
                    ORD_ORDER_NO ,
                    ORD_SERIAL_NO ,
                    ORD_SEM_SMST_SECURITY_ID,
                    ORD_BTM_EMM_MKT_TYPE ,
                    ORD_BTM_BOOK_TYPE ,
                    ORD_EXCH_ID ,
                    ORD_EPM_EM_ENTITY_ID ,
                    ORD_EXCH_ORDER_NO , 
                    ORD_CLIENT_ID ,
                    ORD_BUY_SELL_IND ,
                    ORD_TRANS_CODE ,
                    ORD_STATUS ,
                    ORD_ENTRY_DATE ,
                    ORD_ORDER_TIME ,     
                    ORD_QTY_ORIGINAL ,
                    ORD_QTY_REMAINING ,
                    ORD_QTY_DISC ,
                    ORD_QTY_DISC_REMAINING ,
                    ORD_QTY_FILLED_TODAY , 
                    ORD_ORDER_PRICE ,
                    ORD_TRIGGER_PRICE , 
                    ORD_DISC_QTY_FLG ,
                    ORD_GTC_FLG ,
                    ORD_DAY_FLG ,
                    ORD_IOC_FLG ,
                    ORD_MIN_FILL_FLG ,
                    ORD_MKT_FLG ,
                    ORD_STOP_LOSS_FLG ,
                    ORD_AON_FLG ,
                    ORD_GOOD_TILL_DAYS ,
                    ORD_GOOD_TILL_DATE     ,     
                    ORD_AUCTION_NO ,
                    ORD_ACC_CODE ,
                    ORD_UM_USER_ID ,
                    ORD_MIN_FILL_QTY ,
                    ORD_SETTLEMENT_DAYS ,
                    ORD_COMPETITOR_PERIOD ,
                    ORD_SOLICITOR_PERIOD , 
                    ORD_PRO_CLIENT ,
                    ORD_PARTICIPANT_TYPE ,
                    ORD_PARTICIPANT_CODE ,
                    ORD_COUNTER_BROKER_CODE ,
                    ORD_CUSTODIAN_CODE ,
                    ORD_SETTLER ,
                    ORD_REMARKS ,
                    ORD_BSE_DELV_FLAG ,
                    ORD_BSE_NOTICE_NUM ,
                    ORD_ERROR_CODE ,
                    ORD_EXT_CLIENT_ID , 
                    ORD_SOURCE_FLG ,
                    ORD_BUY_BACK_FLG ,
                    ORD_RESERVE_FLG ,
                    ORD_BSE_REMARK ,
                    ORD_CARRY_FORWARD_FLAG ,
                    ORD_ORDER_OFFON ,
                    ORD_D2C1_FLAG ,
                    ORD_FI_RETAIL_FLG ,
                    ORD_OIB_INT_REF_ID ,
                    ORD_BOB_BASKET_ORD_NO ,
                    ORD_PRODUCT_ID ,
                    ORD_OIB_EXEC_REPORT_ID ,
                    ORD_BANK_DP_TXN_ID ,
                    ORD_USERINFO_PROG ,
                    ORD_BANK_CODE ,
                    ORD_BANK_ACC_NUM ,
                    ORD_DP_CODE ,
                    ORD_DP_ACC_NUM ,
                    ORD_SESSION_ORDER_TYPE ,
                    ORD_ORDER_CC_SEQ ,
                    ORD_RMS_DAEMON_STATUS , 
                    ORD_GROUP_ID ,
                    ORD_REASON_CODE ,
                    ORD_REASON_DESCRIPTION ,
                    ORD_SERIES_IND , 
                    ORD_BOB_BASKET_TYPE ,
                    ORD_ORIGINAL_TIME ,
                    ORD_TRD_EXCH_TRADE_NO, 
                    ORD_MKT_PROT ,
                    ORD_SETTLEMENT_TYPE ,
                    ORD_SUB_CLIENT,
                    ORD_ALGO_OI_NUM,
                    ORD_FROM_ALGO_CLORDID,
                    ORD_FROM_ALGO_ORG_CLORDID
                    )
                    
                    VALUES(
                    :lvar_ord_order_no ,
                    :lvar_ord_serial_no ,
                    ltrim(rtrim(:lvar_ord_sem_smst_security_id)),
                    ltrim(rtrim(:lvar_ord_btm_emm_mkt_type)),
                    ltrim(rtrim(:lvar_ord_btm_book_type)),
                    ltrim(rtrim(:lvar_ord_exch_id)) ,
                    decode(:lD2C1Flag,'N',ltrim(rtrim(:lvar_ord_epm_em_entity_id)),ltrim(rtrim(:sD2C1ControllerId))) ,
                    :insertExchOrderNo, 
                    ltrim(rtrim(:lvar_ord_client_id)) ,
                    ltrim(rtrim(:lvar_ord_buy_sell_ind)),
                    :lvar_ord_trans_code,
                    :cTransitStatus ,
                    sysdate,
                    sysdate,
                    :lvar_ord_qty_original,
                    decode(:lvar_ord_qty_remaining ,-1,to_number(null),:lvar_ord_qty_remaining) ,
                    decode(:lvar_ord_qty_disc ,-1,to_number(null),:lvar_ord_qty_disc),
                    decode(:lvar_ord_qty_disc_remaining,-1,to_number(null),:lvar_ord_qty_disc_remaining),
                    :lvar_ord_qty_filled_today ,
                    :lvar_ord_order_price,
                    decode(:lvar_ord_trigger_price ,-1,to_number(null),:lvar_ord_trigger_price) ,
                    decode(:lvar_ord_disc_qty_flg ,-1,null,:lvar_ord_disc_qty_flg) ,
                    decode(:lvar_ord_gtc_flg ,-1,null,:lvar_ord_gtc_flg) ,
                    decode(:lvar_ord_day_flg ,-1,null,:lvar_ord_day_flg) ,
                    decode(:lvar_ord_ioc_flg ,-1,null,:lvar_ord_ioc_flg) ,
                    decode(:lvar_ord_min_fill_flg ,-1,null,:lvar_ord_min_fill_flg) ,
                    decode(:lvar_ord_mkt_flg ,-1,null,:lvar_ord_mkt_flg) ,
                    decode(:lvar_ord_stop_loss_flg ,-1,null,:lvar_ord_stop_loss_flg) ,
                    decode(:lvar_ord_aon_flg ,-1,null,:lvar_ord_aon_flg) ,
                    decode(:lvar_ord_good_till_days ,-1,to_number(null),:lvar_ord_good_till_days),
                    to_date(ltrim(rtrim(:lvar_ord_good_till_date)) ,'dd-mm-yyyy'),
                    :lvar_ord_auction_no,
                    ltrim(rtrim(:lvar_ord_acc_code)),
                    ltrim(rtrim(:lv_UserIdOrLogPktId)),
                    decode(:lvar_ord_min_fill_qty,-1,to_number(null),:lvar_ord_min_fill_qty),
                    :lvar_ord_settlement_days,
                    :lvar_ord_competitor_period,
                    :lvar_ord_solicitor_period,
                    :lvar_ord_pro_client ,
                    ltrim(rtrim(:lvar_ord_participant_type)),
                    ltrim(rtrim(:lvar_ord_participant_code)),
                    ltrim(rtrim(:lvar_ord_counter_broker_code)),
                    trim(:lvar_ord_custodian_code) ,
                    ltrim(rtrim(:lvar_ord_settler)),
                    ltrim(rtrim(:lvar_ord_remarks)),
                    ltrim(rtrim(:lvar_ord_bse_delv_flag)) ,
                    ltrim(rtrim(:lvar_ord_bse_notice_num)) ,
                    :lvar_ord_error_code ,
                    trim(:lvar_ord_ext_client_id) ,
                    ltrim(rtrim(:lvar_ord_source_flg)),
                    ltrim(rtrim(:lvar_ord_buyback_flg)),
                    :lvar_ord_reserve_flag ,
                    trim(:lvar_ord_bse_remark) ,
                    ltrim(rtrim(:lvar_ord_carryfwd_flg)),
                    :cOnStatus,
                    :lD2C1Flag,
                    :lSendToRemoteUser,
                    :lInternalRefId,
                    :lvar_bob_basket_ord_no,
                    ltrim(rtrim(:lvar_ord_product_id)),
                    trim(:lvar_ord_oib_exec_report_id) ,
                    :lvar_BankDpTxnId ,
                    ltrim(rtrim(:lEquBseUserCode )),
                    ltrim(rtrim(:lvar_BankCode)) ,
                    ltrim(rtrim(:lvar_BankAccNo)),
                    ltrim(rtrim(:lvar_DPCode)),
                    ltrim(rtrim(:lvar_DPAccNo)) ,
                    ltrim(rtrim(:lvar_OrderSessionType)) ,
                    :lvar_ord_order_cc_seq,
                    :lvar_ord_rms_daemon_status ,
                    :lvarGrpId,
                    :lvar_ord_reason_code ,
                    trim(:lvar_ord_reason_description) ,
                    :lSecSeriesInd,
                    ltrim(rtrim(:lBasketType)),
                    sysdate,
                    (-1 * :lvar_ord_serial_no),
                    :MktProt ,     
                    :lvar_ord_sett_type,
                    ltrim(rtrim(:lvar_ca_cli_type)) ,
                    :ComplianceID,
                    ltrim(rtrim(:lvar_ClOrd)),
                    ltrim(rtrim(:lvar_OrgClOrd))
                    
                    );
                    
                    logTimestamp("AFTER inserting in orders table");
                    Log timestamp code
                    void
                    logTimestamp (const char *fmt, ...)
                    {
                    va_list ap;
                    
                    va_start(ap, fmt);
                    CHAR buffer[LOG_TIMESTAMP_LENGTH];
                    CHAR chktime[DATE_LEN];
                    static CHAR message[LOG_BUFFER_LENGTH];
                    struct timeval tv;
                    time_t curtime;
                    gettimeofday(&tv, NULL);
                    curtime=tv.tv_sec;
                    
                    GetLogLevel();
                    
                    strftime(chktime, DATE_LEN, "%d:%m:%Y", localtime(&curtime));
                    if ( strcmp(chktime,logTime))
                    {
                    logLevelSet = 0;
                    GetLogLevel();
                    }
                    strftime(buffer, LOG_TIMESTAMP_LENGTH, "%d:%m:%Y %T", localtime(&curtime));
                    vsprintf(message, fmt, ap);
                    fprintf(stdout, "%s.%06ld: %s\n", buffer, tv.tv_usec, message);
                    va_end(ap);
                    return;
                    }
                    Thanks,
                    Karan

                    Edited by: 853100 on Nov 2, 2012 7:00 AM
                    • 7. Re: Row Insert in Timesten takes more time than Oracle
                      Chrisjenkins-Oracle
                      This is not very efficient as the statement will require likely need to be parsed for each INSERT. Even a soft parse is very expensive compared to the cost of the actual INSERT.

                      Can you try changing your code to something like the following just to evaluate the difference in performance. The object is to prepare the INSERT just once, outside of the INSERT loop and then execute the prepared INSERT many times passing the required input parameters. I'm not a Pro*C expert but an outline of the code looks something like this:

                      char * ins1 = "               INSERT INTO ORDERS(
                                          ORD_ORDER_NO             ,
                                          ORD_SERIAL_NO            ,
                                          ORD_SEM_SMST_SECURITY_ID,
                                          ORD_BTM_EMM_MKT_TYPE    ,
                                          ORD_BTM_BOOK_TYPE        ,
                                          ORD_EXCH_ID              ,
                                          ORD_EPM_EM_ENTITY_ID     ,
                                          ORD_EXCH_ORDER_NO        ,
                                          ORD_CLIENT_ID            ,
                                          ORD_BUY_SELL_IND         ,
                                          ORD_TRANS_CODE           ,
                                          ORD_STATUS               ,
                                          ORD_ENTRY_DATE           ,
                                          ORD_ORDER_TIME           ,     
                                          ORD_QTY_ORIGINAL         ,
                                          ORD_QTY_REMAINING        ,
                                          ORD_QTY_DISC             ,
                                          ORD_QTY_DISC_REMAINING   ,
                                          ORD_QTY_FILLED_TODAY     ,
                                          ORD_ORDER_PRICE          ,
                                          ORD_TRIGGER_PRICE        ,  
                                          ORD_DISC_QTY_FLG         ,
                                          ORD_GTC_FLG              ,
                                          ORD_DAY_FLG              ,
                                          ORD_IOC_FLG             ,
                                          ORD_MIN_FILL_FLG        ,
                                          ORD_MKT_FLG             ,
                                          ORD_STOP_LOSS_FLG       ,
                                          ORD_AON_FLG             ,
                                          ORD_GOOD_TILL_DAYS      ,
                                          ORD_GOOD_TILL_DATE     ,     
                                          ORD_AUCTION_NO          ,
                                          ORD_ACC_CODE            ,
                                          ORD_UM_USER_ID          ,
                                          ORD_MIN_FILL_QTY        ,
                                          ORD_SETTLEMENT_DAYS     ,
                                          ORD_COMPETITOR_PERIOD   ,
                                          ORD_SOLICITOR_PERIOD    ,
                                          ORD_PRO_CLIENT          ,
                                          ORD_PARTICIPANT_TYPE    ,
                                          ORD_PARTICIPANT_CODE    ,
                                          ORD_COUNTER_BROKER_CODE ,
                                          ORD_CUSTODIAN_CODE      ,
                                          ORD_SETTLER             ,
                                          ORD_REMARKS             ,
                                          ORD_BSE_DELV_FLAG       ,
                                          ORD_BSE_NOTICE_NUM      ,
                                          ORD_ERROR_CODE          ,
                                          ORD_EXT_CLIENT_ID       ,
                                          ORD_SOURCE_FLG          ,
                                          ORD_BUY_BACK_FLG        ,
                                          ORD_RESERVE_FLG         ,
                                          ORD_BSE_REMARK          ,
                                          ORD_CARRY_FORWARD_FLAG  ,
                                          ORD_ORDER_OFFON         ,
                                          ORD_D2C1_FLAG           ,
                                          ORD_FI_RETAIL_FLG       ,
                                          ORD_OIB_INT_REF_ID      ,
                                          ORD_BOB_BASKET_ORD_NO   ,
                                          ORD_PRODUCT_ID          ,
                                          ORD_OIB_EXEC_REPORT_ID   ,
                                          ORD_BANK_DP_TXN_ID       ,
                                          ORD_USERINFO_PROG        ,
                                          ORD_BANK_CODE            ,
                                          ORD_BANK_ACC_NUM         ,
                                          ORD_DP_CODE              ,
                                          ORD_DP_ACC_NUM           ,
                                          ORD_SESSION_ORDER_TYPE   ,
                                          ORD_ORDER_CC_SEQ         ,
                                          ORD_RMS_DAEMON_STATUS    ,
                                          ORD_GROUP_ID             ,
                                          ORD_REASON_CODE          ,
                                          ORD_REASON_DESCRIPTION   ,
                                          ORD_SERIES_IND           ,
                                          ORD_BOB_BASKET_TYPE  ,
                                          ORD_ORIGINAL_TIME    ,
                                          ORD_TRD_EXCH_TRADE_NO,     
                                          ORD_MKT_PROT   ,
                                          ORD_SETTLEMENT_TYPE      ,
                                          ORD_SUB_CLIENT,
                                               ORD_ALGO_OI_NUM,
                                               ORD_FROM_ALGO_CLORDID,
                                               ORD_FROM_ALGO_ORG_CLORDID
                                     )

                                     VALUES(
                                          :lvar_ord_order_no       ,
                                          :lvar_ord_serial_no     ,
                                          ltrim(rtrim(:lvar_ord_sem_smst_security_id)),
                                          ltrim(rtrim(:lvar_ord_btm_emm_mkt_type)),
                                          ltrim(rtrim(:lvar_ord_btm_book_type)),
                                          ltrim(rtrim(:lvar_ord_exch_id))  ,
                                          decode(:lD2C1Flag,'N',ltrim(rtrim(:lvar_ord_epm_em_entity_id)),ltrim(rtrim(:sD2C1ControllerId)))  ,
                                                     :insertExchOrderNo,
                                          ltrim(rtrim(:lvar_ord_client_id))  ,
                                          ltrim(rtrim(:lvar_ord_buy_sell_ind)),
                                          :lvar_ord_trans_code,
                                          :cTransitStatus      ,
                                          sysdate,
                                          sysdate,
                                          :lvar_ord_qty_original,
                                          decode(:lvar_ord_qty_remaining  ,-1,to_number(null),:lvar_ord_qty_remaining)    ,
                                          decode(:lvar_ord_qty_disc       ,-1,to_number(null),:lvar_ord_qty_disc),
                                          decode(:lvar_ord_qty_disc_remaining,-1,to_number(null),:lvar_ord_qty_disc_remaining),
                                          :lvar_ord_qty_filled_today    ,
                                          :lvar_ord_order_price,
                                          decode(:lvar_ord_trigger_price  ,-1,to_number(null),:lvar_ord_trigger_price)     ,
                                                     decode(:lvar_ord_disc_qty_flg ,-1,null,:lvar_ord_disc_qty_flg)  ,
                                                     decode(:lvar_ord_gtc_flg ,-1,null,:lvar_ord_gtc_flg)  ,
                                                     decode(:lvar_ord_day_flg ,-1,null,:lvar_ord_day_flg)  ,
                                                     decode(:lvar_ord_ioc_flg ,-1,null,:lvar_ord_ioc_flg)  ,
                                                     decode(:lvar_ord_min_fill_flg ,-1,null,:lvar_ord_min_fill_flg)  ,
                                                     decode(:lvar_ord_mkt_flg ,-1,null,:lvar_ord_mkt_flg)  ,
                                                     decode(:lvar_ord_stop_loss_flg ,-1,null,:lvar_ord_stop_loss_flg)  ,
                                                     decode(:lvar_ord_aon_flg ,-1,null,:lvar_ord_aon_flg)  ,
                                          decode(:lvar_ord_good_till_days ,-1,to_number(null),:lvar_ord_good_till_days),
                                          to_date(ltrim(rtrim(:lvar_ord_good_till_date))  ,'dd-mm-yyyy'),
                                          :lvar_ord_auction_no,
                                          ltrim(rtrim(:lvar_ord_acc_code)),
                                          ltrim(rtrim(:lv_UserIdOrLogPktId)),
                                          decode(:lvar_ord_min_fill_qty,-1,to_number(null),:lvar_ord_min_fill_qty),
                                          :lvar_ord_settlement_days,
                                          :lvar_ord_competitor_period,
                                          :lvar_ord_solicitor_period,
                                          :lvar_ord_pro_client         ,
                                          ltrim(rtrim(:lvar_ord_participant_type)),
                                          ltrim(rtrim(:lvar_ord_participant_code)),
                                          ltrim(rtrim(:lvar_ord_counter_broker_code)),
                                          trim(:lvar_ord_custodian_code)     ,
                                          ltrim(rtrim(:lvar_ord_settler)),
                                          ltrim(rtrim(:lvar_ord_remarks)),
                                          ltrim(rtrim(:lvar_ord_bse_delv_flag))      ,
                                          ltrim(rtrim(:lvar_ord_bse_notice_num))     ,
                                          :lvar_ord_error_code         ,
                                          trim(:lvar_ord_ext_client_id)      ,
                                          ltrim(rtrim(:lvar_ord_source_flg)),
                                          ltrim(rtrim(:lvar_ord_buyback_flg)),
                                          :lvar_ord_reserve_flag        ,
                                          trim(:lvar_ord_bse_remark)         ,
                                          ltrim(rtrim(:lvar_ord_carryfwd_flg)),
                                          :cOnStatus,
                                          :lD2C1Flag,
                                          :lSendToRemoteUser,
                                          :lInternalRefId,
                                          :lvar_bob_basket_ord_no,
                                          ltrim(rtrim(:lvar_ord_product_id)),
                                          trim(:lvar_ord_oib_exec_report_id)   ,
                                          :lvar_BankDpTxnId  ,
                                          ltrim(rtrim(:lEquBseUserCode )),
                                          ltrim(rtrim(:lvar_BankCode))  ,
                                          ltrim(rtrim(:lvar_BankAccNo)),
                                          ltrim(rtrim(:lvar_DPCode)),
                                          ltrim(rtrim(:lvar_DPAccNo))  ,
                                          ltrim(rtrim(:lvar_OrderSessionType))   ,
                                          :lvar_ord_order_cc_seq,
                                          :lvar_ord_rms_daemon_status    ,
                                          :lvarGrpId,
                                          :lvar_ord_reason_code          ,
                                          trim(:lvar_ord_reason_description)   ,
                                          :lSecSeriesInd,
                                          ltrim(rtrim(:lBasketType)),
                                          sysdate,
                                          (-1 * :lvar_ord_serial_no),
                                          :MktProt ,          
                                          :lvar_ord_sett_type,
                                          ltrim(rtrim(:lvar_ca_cli_type)) ,
                                                     :ComplianceID,
                                                     ltrim(rtrim(:lvar_ClOrd)),
                                                     ltrim(rtrim(:lvar_OrgClOrd))

                                          )" ;



                      EXEC SQL AT :db_conn PREPARE i1 FROM :ins1;



                           logTimestamp("BEFORE inserting in  orders table");


                      for (i=0; i<NUM_INSERTS; i++)
                      {

                                if ( strncmp(lvar_ord_exch_id.arr,"NSE",3) ==0 )
                                {     
                                     if(tmpExchOrderNo == -1)
                                          insertExchOrderNo = NULL;
                                     else
                                          insertExchOrderNo = tmpExchOrderNo;
                                }
                                else if ( strncmp(lvar_ord_exch_id.arr,"BSE",3) ==0 )
                                {
                                     if(tmpExchOrderNo == -1)
                                          insertExchOrderNo = NULL;
                                     else
                                          insertExchOrderNo = tmpExchOrderNo;
                                }

                                  lvar_ord_acc_code.len = strlen (lvar_ord_acc_code.arr);
                                sprintf (lv_UserIdOrLogPktId.arr,"%d",UserIdOrLogPktId );
                                lv_UserIdOrLogPktId.len = strlen (lv_UserIdOrLogPktId.arr) ;
                                

                                lEquBseUserCode.len = fTrim(lEquBseUserCode.arr,16);
                                lvar_ord_buyback_flg.len = fTrim(lvar_ord_buyback_flg.arr,1);

                                lvar_ord_exch_id.len = fTrim(lvar_ord_exch_id.arr,3);

                                
                                

                                     EXEC SQL AT :db_conn EXECUTE i1 USING

                                          :lvar_ord_order_no       ,
                                          :lvar_ord_serial_no     ,
                                          :lvar_ord_sem_smst_security_id,
                                          :lvar_ord_btm_emm_mkt_type,

                      etc. ;

                      }

                                logTimestamp("AFTER inserting in  orders table");

                      /* Divide reported time by NUM_INSERTS to get average time for one insert */


                      Chris