Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 10 of 10 total |
Query taking to much to prepare, suggestions? |
Mon, Sep 9 2013 4:53 PM | Permanent Link |
Mario Enríquez Open Consult | 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 |
Mon, Sep 9 2013 5:33 PM | Permanent Link |
Fernando Dias Team Elevate | Mario,
Can you also post the Execution Plan please ? -- Fernando Dias [Team Elevate] |
Tue, Sep 10 2013 3:24 AM | Permanent Link |
Roy Lambert NLH Associates 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] |
Tue, Sep 10 2013 3:29 AM | Permanent Link |
Roy Lambert NLH Associates 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] |
Tue, Sep 10 2013 4:13 PM | Permanent Link |
Mario Enríquez Open Consult | 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 ================================================================================ |
Wed, Sep 11 2013 4:41 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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] |
Wed, Sep 11 2013 1:27 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
Wed, Sep 11 2013 1:43 PM | Permanent Link |
Mario Enríquez Open Consult | Thank you guys, I'll take a look.
Regards, Mario |
Thu, Sep 12 2013 3:10 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Tim
<<Views and derived tables must be materialized >> Beam me up Scotty? Roy |
Tue, Sep 17 2013 1:20 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Roy,
<< Beam me up Scotty? >> Similar, but a lot less impressive. Tim Young Elevate Software www.elevatesoft.com |
This web page was last updated on Wednesday, May 15, 2024 at 08:40 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |