Informix

Informix

Connect with Db2, Informix, Netezza, open source, and other data experts to gain value from your data, share insights, and solve problems.

 View Only

  • 1.  SQL Time

    Posted 08/12/21 08:22 AM
    I'm running below query with set explain on.

     

    QUERY: (OPTIMIZATION TIMESTAMP: 08-12-2021 18:44:05)

    ------

    select count(*) from cs_subr_pcn_00 cb

    WHERE EXISTS (SELECT cl.cust_numb , cl.subr_numb from cs_cust cs, cs_clse_cust cl

    WHERE cs.cust_numb = cl.cust_numb

    AND   cl.cust_numb = cb.cust_numb

    AND   cl.subr_numb = cb.subr_numb

    )

     

    {

    select count(*) from optim_cs_subr_pcn_00;

    select count(*) from cs_subr_pcn_00;

    select count(*) from optim_cs_cust_srch_09;

    select count(*) from cs_cust_srch_09;

    }

     

     

     

     

    Estimated Cost: 14248124

    Estimated # of Rows Returned: 1

     

      1) informix.cb: INDEX PATH

     

        (1) Index Name: informix.neo_csp_subr02_00_01

            Index Keys: cust_numb subr_numb   (Key-Only)  (Serial, fragments: ALL)

     

      2) informix.cl: INDEX PATH

     

        (1) Index Name: informix.pk_cs_clse_cust

            Index Keys: cust_numb subr_numb   (Key-Only)  (Serial, fragments: ALL)

            Lower Index Filter: (informix.cl.cust_numb = informix.cb.cust_numb AND informix.cl.subr_numb = informix.cb.subr_numb )

    NESTED LOOP JOIN

     

      3) informix.cs: INDEX PATH

     

        (1) Index Name: informix.csp_cust_01

            Index Keys: cust_numb   (Key-Only)  (Serial, fragments: ALL)

            Lower Index Filter: informix.cs.cust_numb = informix.cb.cust_numb

    NESTED LOOP JOIN

     

     

    select count(*) from cs_subr_pcn_00;   -- 14965091

    select count(*) from cs_cust;  -- 149553382

    select count(*) from cs_clse_cust; -- 114596229

     But this SQL takes very long time mat be around 40 minutes to execute , sometimes I take cs_cust and cs_clse_cust table data in temp table also but then also it takes long time.

    Please suggest to speed up the SQL.

    UPDATE STATICS run on all tables.

    Thanks
    Amit

     

     

     

     



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

    #Informix


  • 2.  RE: SQL Time

    Posted 08/12/21 09:05 AM
    Amit:
    So, just to be clear, this query:
    select count(*) from cs_subr_pcn_00 cb
    WHERE EXISTS (SELECT cl.cust_numb , cl.subr_numb from c cs, cs_clse_cust cl
    WHERE cs.cust_numb = cl.cust_numb
    AND   cl.cust_numb = cb.cust_numb
    AND   cl.subr_numb = cb.subr_numb
    );

    which joins a nearly 15 million row table to a 114 million row table and a nearly 150 million row table so you can count the number of rows in the result set is slow. Hmm. Understandable. My first thought was to propose to eliminate the subquery and use something like this:
    SELECT count( * )
    FROM cs_subr_pcn_00 AS cb
    JOIN cs_clse_cust AS cl
      ON cb.cust_numb = cl.cust_numb
        AND cb.subr_numb = cl subr_numb
    JOIN cs_subr_pcn_00 AS cs
      ON cs.cust_numb = cl.cust_numb
    ;

    But that will likely have the same query plan as the optimizer has already removed the sub-query and is doing key-only searches of the three indexes. So, I don't think it will be any faster. I'd like to see the statistical details at the end of the SET EXPLAIN output.

    So, then I realized that this question hits my biggest baddest pet peeve. You have basically asked "I tried this solution to my problem and it doesn't work! Help me make it work!" What would be better is if you said "I have this problem that I cannot solve. I tried this one solution, and it runs WAY too long. Can you think of a better way to get what I want?" SO, because I don't think you actually need to know this count() but rather need it for something else, please post the problem and we'll see if we can't figure something out.



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



Related Content