IDS Forum

Re: query sqlexplain interpretation

Posted By: Fernando Nunes
Date: Wednesday, 5 November 2014, at 12:29 p.m.

In Response To: query sqlexplain interpretation (Juan Francisco González Navarro)

It's a bit harder for us to explain why it takes that time vs for you to
say with why you think it shouldn't...
We can see from the query plan that the starting point brings a lot of
records... 1.4M... It's possible that if it started from another table it
would bring less... is that your point?
Or do you think it should work faster even considering the number of rows
that match the conditions and the joins?

We could start by obtaining counts of the several tables involved using the
conditions (consider that if you're doing tabA.col1 = tabB.col1 AND
tabB.col1 BETWEEN X and Y we would need the counts for tabA and tabB).
That could give us a clue where to start the query...

We can see that the 1.4 M rows are "transported" acrross the first join but
it is drastically dropped to 40 rows on the last one... so it could be
better to do thequery the other way round... and easy way to test this
could be writing the tables in different order and use the hint ORDERED...

Besides that and as Obnoxio would answer: Have you tried UPDATE STATISTICS?
:)

On Wed, Nov 5, 2014 at 3:45 PM, Juan Francisco González Navarro <
jfrancisco.navarro@gmail.com> wrote:

> Hello:
>
> CAn someone helps me to understand why this query takes 35 seconds?
>
> Thank you.
>
> QUERY: (OPTIMIZATION TIMESTAMP: 11-05-2014 10:32:41)
> ------
> select
>
>
> c.items,b.muestra,b.prueba,b.valor,b.comentario,a.fecha,a.sid,a.pid,a.demo4,a.demo9
> from adatos a,avalor b, pruebas c where a.sid=b.sid and a.fecha=b.fecha and
> a.baja=b.baja and a.baja=c.baja and a.baja='3000-01-01 00:00:00' and
> b.status in ('V','v','I','i') and b.prueba=c.codigo and c.clase='1' and
> a.fecha between '20140530' and '20140930' and pid='1055052'
>
> Estimated Cost: 5
> Estimated # of Rows Returned: 1
>
> 1) informix.b: INDEX PATH
>
> Filters: informix.b.status IN ('V' , 'v' , 'I' , 'i' )
>
> (1) Index Name: root. 104_24
>
> Index Keys: fecha sid baja muestra prueba (Key-First) (Serial,
> fragments: ALL)
>
> Lower Index Filter: informix.b.fecha >= 20140530
>
> Upper Index Filter: informix.b.fecha <= 20140930
>
> Index Key Filters: (informix.b.baja = datetime(3000-01-01
> 00:00:00) year to second )
>
> 2) informix.c: INDEX PATH
>
> Filters: informix.c.clase = '1'
>
> (1) Index Name: informix. 298_990
>
> Index Keys: codigo baja (Serial, fragments: ALL)
>
> Lower Index Filter: (informix.b.prueba = informix.c.codigo AND
> informix.b.baja = informix.c.baja )
> NESTED LOOP JOIN
>
> 3) informix.a: INDEX PATH
>
> Filters: informix.a.pid = '1055052'
>
> (1) Index Name: root. 183_710
>
> Index Keys: fecha sid baja (Serial, fragments: ALL)
>
> Lower Index Filter: ((informix.a.baja = informix.b.baja AND
> informix.a.sid = informix.b.sid ) AND informix.a.fecha = informix.b.fecha )
> NESTED LOOP JOIN
>
> Query statistics:
> -----------------
>
> Table map :
> ----------------------------
> Internal name Table name
> ----------------------------
> t1 b
> t2 c
> t3 a
>
> type table rows_prod est_rows rows_scan time est_cost
> -------------------------------------------------------------------
> scan t1 1422778 1 1640606 00:13.55 1
>
> type table rows_prod est_rows rows_scan time est_cost
> -------------------------------------------------------------------
> scan t2 1422337 1 1422337 00:22.01 0
>
> type rows_prod est_rows time est_cost
> -------------------------------------------------
> nljoin 1422337 1 00:36.10 3
>
> type table rows_prod est_rows rows_scan time est_cost
> -------------------------------------------------------------------
> scan t3 40 1 1422337 00:20.91 0
>
> type rows_prod est_rows time est_cost
> -------------------------------------------------
> nljoin 40 1 00:57.47 5
>
> --001a1140e2c86c5f7005071e7926
>
>
>
> *******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>

--
Fernando Nunes
Portugal

http://informix-technology.blogspot.com
My email works... but I don't check it frequently...

--001a11c2ffb6d9353b05071feab9

Messages In This Thread

• query sqlexplain interpretation
  Juan Francisco González Navarro -- Wednesday, 5 November 2014, at 10:45 a.m.
  • Re: query sqlexplain interpretation
    Art Kagel -- Wednesday, 5 November 2014, at 11:45 a.m.
    • Re: query sqlexplain interpretation
      Juan Francisco González Navarro -- Wednesday, 5 November 2014, at 12:08 p.m.
      • Re: query sqlexplain interpretation
        Art Kagel -- Wednesday, 5 November 2014, at 12:18 p.m.
  • Re: query sqlexplain interpretation
    Fernando Nunes -- Wednesday, 5 November 2014, at 12:29 p.m.
  • Re: query sqlexplain interpretation
    Juan Francisco González Navarro -- Thursday, 6 November 2014, at 9:11 a.m.
    • Re: query sqlexplain interpretation
      Fernando Nunes -- Thursday, 6 November 2014, at 9:39 a.m.
  • Re: query sqlexplain interpretation
    RAFAEL GOMEZ -- Thursday, 6 November 2014, at 10:25 a.m.
    • Re: query sqlexplain interpretation
      Fernando Nunes -- Thursday, 6 November 2014, at 10:52 a.m.
      • Re: query sqlexplain interpretation
        RAFAEL GOMEZ -- Friday, 7 November 2014, at 4:01 a.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.