Informix

Expand all | Collapse all

Index in Temp table

  • 1.  Index in Temp table

    Posted 6 days ago
    Dear All,

      I'm running below query to delete data.

    Using Index on Temp Table:

    CREATE TEMP TABLE temp2 (cust_numb INTEGER);
    CREATE INDEX "informix".temp2_n1 ON temp2(cust_numb) using btree;

    INSERT INTO temp2
    select first 10000 cb.cust_numb from optim_cs_csdi cb
    WHERE EXISTS (SELECT op.cust_numb FROM optim_test_2015_11 op
    WHERE op.cust_numb= cb.cust_numb ) ;

    DELETE FROM optim_cs_csdi cb WHERE EXISTS (SELECT t1.cust_numb FROM temp2 t1 WHERE cb.cust_numb = t1.cust_numb );

    This SQL took  total 00.00.44 seconds

    type rows_prod est_rows time est_cost
    -------------------------------------------------
    nljoin 10000 10000 00:00.44 17958

    Using No Index on Temp Table:

    select first 10000 cb.cust_numb from optim_cs_csdi cb
    WHERE EXISTS (SELECT op.cust_numb FROM optim_test_2015_11 op
    WHERE op.cust_numb= cb.cust_numb ) INTO TEMP temp2;

    DELETE FROM optim_cs_csdi cb WHERE EXISTS (SELECT t1.cust_numb FROM temp2 t1 WHERE cb.cust_numb = t1.cust_numb );

    This ran in 00:00:38 seconds

    type rows_prod est_rows time est_cost
    -------------------------------------------------
    nljoin 10000 10000 00:00.38 3038


    please let me know is it good idea to use index on temp tables?

    Will Non Index Temp tables use more CPUs ?

    And how to check how many CPU particular SQL using?

    Thanks in Advance.

    Thanks
    Amit Patel

    ------------------------------
    AMIT PATEL
    ------------------------------


  • 2.  RE: Index in Temp table

    Posted 6 days ago
    Amit:

    It's probably not the index on the temp table that's slowing you down, it's just the differences from one run to another which is normal. Though the index on the temp table in this application might be slowing you down. That's because the engine has to read every page in the temp table to process the delete anyway, so ultimately it doesn't need the index and if it uses the index that adds more IO and more internal overhead to the delete. Most likely though those extra 6/100sec are part from the time to decide not to use the index and, as I said, the difference between one run and another that's normal.

    All that said, you do know that you can use my dbdelete utility to do what you seem to want to do, which I believe is perform a massive delete without risking a long transaction rollback. Like this:

    dbdelete -d mydatabase -t optim_cs_csdi -q '
    select cb.rowid from optim_cs_csdi cb, optim_test_2015_11 op
    WHERE op.cust_numb= cb.cust_numb' 

    Or, if the optim_cs_csdi table is partitioned, then dbdelete can use ifx_row_id instead of rowid (as long as you are using v14.10.FC5 or later - ifx_row_id is too slow in earlier releases):

    dbdelete -d mydatabase -t optim_cs_csdi -I -q '
    select cb.ifx_row_id from optim_cs_csdi cb, optim_test_2015_11 op
    WHERE op.cust_numb= cb.cust_numb' 



    ------------------------------
    Art S. Kagel, President and Principal Consultant
    ASK Database Management Corp.
    www.askdbmgt.com
    ------------------------------



  • 3.  RE: Index in Temp table

    Posted 6 days ago
    There are no issues using indexes on temp tables in particular - they can improve performance just like any index on any table if they are used correctly.  In this case, I would suggest testing the performance of the query below:

    DELETE FROM optim_cs_csdi cb WHERE cust_numb IN (select t1.cust_numb from temp2);

    There's a very good chance that the optimizer is flattening the query for you anyway, and that the correlated subquery (referencing the optim_cs_csdbi table within the subquery) is not hurting, and then the index is immaterial as there will only be a single scan of the temp table.

    Whether one uses more CPU than the other, it depends on the query plan.  I'd be more concerned about choosing the one that performs the least IO.

    If you want to track the CPU usage of a RUNNING query, you can query sysmaster for your session ID:

    SELECT
    s.sid ,
    t.tid,
    t.name,
    t.cpu_time
    FROM syssessions s,
    systcblst t,
    sysrstcb r
    WHERE t.tid = r.tid
    AND s.sid = r.sid;


    ------------------------------
    Mike Walker
    xDB Systems, Inc
    www.xdbsystems.com
    ------------------------------