Hi,

I got a semi complex query that it's used to feed a report, but it takes to long to prepare.

I placed the query in EDB Manager, and found out it takes more than 60 seconds just to prepared. After that the query returns data really quick.

It is my understanding that preparation is required to verify that all tables and fields are valid and check the overall syntax of the expression. If so, how it could take so long?

Here's the query, maybe someone could offer some advice.

Regards,

SELECT
  pe.id_empresa,
  pe.id_producto_empresa,
  exs.id_bodega,
  exs.codigo_bodega,
  exs.bodega,
  pro.id_producto,
  pro.codigo,
  pro.producto,
  uni.id_unidad_medida,
  uni.unidad as unidad_medida,
  pe.costo_unitario,
  exs.existencia_inicial,
  exs.entradas,
  exs.salidas,
  (exs.existencia_inicial + exs.entradas - exs.salidas) as existencia_final   
FROM inv_producto_empresa pe
  INNER JOIN inv_producto pro ON pe.id_producto = pro.id_producto
  INNER JOIN grl_unidad_medida uni ON pe.id_unidad_medida = uni.id_unidad_medida
  INNER JOIN (
           SELECT
              bo.id_bodega,
              bo.codigo as codigo_bodega,
              bo.bodega,
              px.id_producto_empresa,
              fnInvExistenciaBodega(px.id_producto_empresa, bo.id_bodega, CAST('2013-08-01' AS TIMESTAMP)) as existencia_inicial,
              SUM(px.ingresos) as entradas,
              SUM(px.salidas) as salidas,
              SUM(px.ingresos - px.salidas) as saldo
           FROM inv_producto_existencia px
              INNER JOIN inv_bodega_ubicacion bu ON px.id_ubicacion = bu.id_ubicacion
              INNER JOIN vwInvBodegasUsuario bo ON bu.id_bodega = bo.id_bodega
           WHERE
                 (px.fecha >= CAST('2013-01-08' AS TIMESTAMP) AND px.fecha < (CAST('2013-08-31' AS TIMESTAMP)) + INTERVAL '1' DAY)
           AND  ((px.id_producto_empresa = '0') or ('0' = '0'))
           AND  ((bo.id_bodega = '{C6DC29FE-3FB4-4CF3-8672-206550CC0727}') or ('{C6DC29FE-3FB4-4CF3-8672-206550CC0727}' = '0'))
           GROUP BY bo.id_bodega, bo.codigo, bo.bodega, px.id_producto_empresa) exs ON pe.id_producto_empresa = exs.id_producto_empresa
ORDER BY exs.codigo_bodega, pro.codigo


Regards,
Mario

Mario,

Can you also post the Execution Plan please ?

--
Fernando Dias
[Team Elevate]

Mario


My guess, without knowing Tim's source code, is that its the sub select in the join clause. Try without that, I know the result set will be meaningless but if the preparation time goes down that will be the cause.

If my guess is right you'll want to create existencia_inicial externally as a temporary table either in-memory or on disk.

Roy Lambert [Team Elevate]

Mario


I think I misread the extent of the subselect and its much bigger and more complex than I thought - I should have been referring to exs  not existencia_inicial

Roy Lambert [Team Elevate]

Thanks everybody for taking a look!

Here's the execution plan...


================================================================================
SQL Query (Executed by ElevateDB 2.12 Build 2)

Note: The SQL  shown here is generated by ElevateDB and may not be exactly the
same as the SQL that was originally entered.  However, none of the differences
alter the execution results in any way.
================================================================================

SELECT ALL
"pe"."id_empresa" AS "id_empresa",
"pe"."id_producto_empresa" AS "id_producto_empresa",
"exs"."id_bodega" AS "id_bodega",
"exs"."codigo_bodega" AS "codigo_bodega",
"exs"."bodega" AS "bodega",
"pro"."id_producto" AS "id_producto",
"pro"."codigo" AS "codigo",
"pro"."producto" AS "producto",
"uni"."id_unidad_medida" AS "id_unidad_medida",
"uni"."unidad" AS "unidad_medida",
"pe"."costo_unitario" AS "costo_unitario",
"exs"."existencia_inicial" AS "existencia_inicial",
"exs"."entradas" AS "entradas",
"exs"."salidas" AS "salidas",
("exs"."existencia_inicial" + "exs"."entradas" - "exs"."salidas") AS
"existencia_final"
FROM "inv_producto_empresa" AS "pe" INNER JOIN "grl_unidad_medida" AS "uni" ON
"pe"."id_unidad_medida" = "uni"."id_unidad_medida"
INNER JOIN "inv_producto" AS "pro" ON "pe"."id_producto" = "pro"."id_producto"
INNER JOIN (SELECT ALL "bo"."id_bodega" AS "id_bodega", "bo"."codigo" AS
"codigo_bodega", "bo"."bodega" AS "bodega", "px"."id_producto_empresa" AS
"id_producto_empresa", fnInvExistenciaBodega("px"."id_producto_empresa",
"bo"."id_bodega", CAST('2013-08-01', TIMESTAMP)) AS "existencia_inicial",
SUM("px"."ingresos") AS "entradas", SUM("px"."salidas") AS "salidas",
SUM("px"."ingresos" - "px"."salidas") AS "saldo" FROM "vwInvBodegasUsuario" AS
"bo" INNER JOIN "inv_bodega_ubicacion" AS "bu" ON "bu"."id_bodega" =
"bo"."id_bodega" INNER JOIN "inv_producto_existencia" AS "px" ON
"px"."id_ubicacion" = "bu"."id_ubicacion" WHERE (("bo"."id_bodega" =
'{C6DC29FE-3FB4-4CF3-8672-206550CC0727}') OR
('{C6DC29FE-3FB4-4CF3-8672-206550CC0727}' = '0')) AND
(("px"."id_producto_empresa" = '0') OR ('0' = '0')) AND ("px"."fecha" <
(CAST('2013-08-31', TIMESTAMP)) + INTERVAL '1' DAY AND "px"."fecha" >=
CAST('2013-01-08', TIMESTAMP)) GROUP BY "bo"."id_bodega", "bo"."codigo",
"bo"."bodega", "px"."id_producto_empresa") AS "exs" ON
"pe"."id_producto_empresa" = "exs"."id_producto_empresa"
ORDER BY "exs"."codigo_bodega", "pro"."codigo"

Source Tables
-------------

inv_producto_empresa (pe): 845 rows
inv_producto (pro): 870 rows
grl_unidad_medida (uni): 132 rows
exs1 (exs): 697 rows

Result Set
----------

The result set was insensitive and read-only
The result set consisted of zero or more rows

Joins
-----

The driver table was the inv_producto_empresa (pe) table

The inv_producto_empresa (pe) table was joined to the grl_unidad_medida (uni)
table with the inner join expression:

"pe"."id_unidad_medida" = "uni"."id_unidad_medida"

The inv_producto_empresa (pe) table was joined to the inv_producto (pro) table
with the inner join expression:

"pe"."id_producto" = "pro"."id_producto"

The inv_producto_empresa (pe) table was joined to the exs1 (exs) table with the
inner join expression:

"pe"."id_producto_empresa" = "exs"."id_producto_empresa"

The optimizer attempted to re-order the joins to a more optimal order

The optimizer successfully re-ordered the joins into this more optimal order:

The driver table was the grl_unidad_medida (uni) table

The grl_unidad_medida (uni) table was joined to the inv_producto_empresa (pe)
table with the inner join expression:

"pe"."id_unidad_medida" = "uni"."id_unidad_medida"

The inv_producto_empresa (pe) table was joined to the inv_producto (pro) table
with the inner join expression:

"pro"."id_producto" = "pe"."id_producto"

The inv_producto_empresa (pe) table was joined to the exs1 (exs) table with the
inner join expression:

"exs"."id_producto_empresa" = "pe"."id_producto_empresa"

The following join condition was applied to the inv_producto_empresa (pe) table:

"pe"."id_unidad_medida" = "uni"."id_unidad_medida"

Index scan (inv_producto_empresa.fk_grl_uni_med_prd_emp)

The following join condition was applied to the inv_producto (pro) table:

"pro"."id_producto" = "pe"."id_producto"

Index scan (inv_producto.pk_inv_producto)

The following join condition was applied to the exs1 (exs) table:

"exs"."id_producto_empresa" = "pe"."id_producto_empresa"

Row scan (exs1)

Hint: Create index(es) (exs1) on column(s) "exs"."id_producto_empresa" for
possible better performance


Result set I/O statistics
-------------------------

Total rows visited: 697

Row buffer manager

Max buffer size: 1022.66KB Buffer size: 952.93KB

Hits: 697   Misses: 0   Hit ratio: 1

Reads: 0   read: 0B

Writes: 0   written: 0B

Index Page buffer manager

Max buffer size: 2MB Buffer size: 112KB

Hits: 1337   Misses: 0   Hit ratio: 1

Reads: 0   read: 0B

Writes: 0   written: 0B

================================================================================
697 row(s) returned in 0.453 secs
================================================================================

Mario


Unfortunately that's not going to help because your problem is preparation not execution, and as it currently works sub selects are not analysed.

The fact that the actual query takes less than 1/2 second reinforces my opinion that its the sub select clauses in the JOIN conditions that are causing the problem.

You can try NOJOINOPTIMISE to see if that reduces the preparation time. If not you need to run each of the sub queries and optimise those first, and probably create temporary tables / views from their result sets to speed things up.

Roy Lambert [Team Elevate]

Mario,

<< I placed the query in EDB Manager, and found out it takes more than 60
seconds just to prepared. After that the query returns data really quick. >>

This is always an indication that a view or derived table is taking a long
time to execute.  Views and derived tables must be materialized before they
can be used in a query since EDB needs them to be materialized in order to
find out which columns are present, etc.

Split out the "exs" derived table as a separate query and execute it - the
resulting execution time should match the preparation time.  Then generate a
query plan for the query and see what it says - it should give an indication
that something needs to be optimized further in order to execute faster.

Tim Young
Elevate Software
www.elevatesoft.com

Thank you guys, I'll take a look.

Regards,
Mario

Tim


<<Views and derived tables must be materialized >>

Beam me up Scotty?

Roy

Roy,

<< Beam me up Scotty? >>

Similar, but a lot less impressive.

Tim Young
Elevate Software
www.elevatesoft.com