Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM SQL » View Thread |
Messages 1 to 5 of 5 total |
Urgent. Please help. How to improve this query? |
Mon, Oct 19 2009 12:19 PM | Permanent Link |
=?iso-8859-1?Q?Santy_Concepci=F3n?= | I have a query that takes about 6 minutes to show results sometimes,
depending on involved tables size. I have post the query statement, query Plan, and involved tables structure. I have tried using 'joins', 'having', simple conditions instead of 'IF', etc... with same result. I have even tried deleting some where conditions to reduce time, with no success. I can post the tables if you need it. Thanks! QUERY and QUERY PLAN: ================================================================================ SQL statement (Executed with 4.28 Build 6) ================================================================================ select FORMAPAGO,COUNT(cpagos.FACTURA),SUM(cpagos.PAGADO) from movim,cpagos,fpago where upper(MOV) = upper('VENTA') and upper(movim.MOV) = upper(cpagos.MOV) and cpagos.PAGADO <> 0 and movim.FACTURA = cpagos.FACTURA and movim.SERIE = cpagos.SERIE and fpago.CODIGO = cpagos.FPAGO and IF(cpagos.FECHA = '2009-10-19' THEN HORA>='12:48:27' ELSE movim.FECHA > '2009-10-19') group by FORMAPAGO order by FORMAPAGO Tables Involved --------------- movim (movim) table opened shared, has 15750 rows cpagos (cpagos) table opened shared, has 50688 rows fpago (fpago) table opened shared, has 20 rows Result Set Generation --------------------- Result set will be canned Result set will consist of one or more grouped rows Result set will be grouped by the following column(s) using a temporary index: FORMAPAGO Result set will be ordered by the following column(s) using a case-sensitive temporary index: FORMAPAGO ASC WHERE Clause Execution ---------------------- The expression: upper(MOV) = upper('VENTA') is OPTIMIZED, covers 14890 rows or index keys, costs 309712 bytes, and will be applied to the movim table (movim) before any joins Join Ordering ------------- The driver table is the movim table (movim) The movim table (movim) is joined to the cpagos table (cpagos) with the INNER JOIN expression: upper(movim.MOV) = upper(cpagos.MOV) AND movim.FACTURA = cpagos.FACTURA AND movim.SERIE = cpagos.SERIE The cpagos table (cpagos) is joined to the fpago table (fpago) with the INNER JOIN expression: cpagos.FPAGO = fpago.CODIGO Optimizer will attempt to re-order the joins to a more optimal order Use the NOJOINOPTIMIZE clause at the end of the SQL statement to force the optimizer to leave the joins in their declared order Optimized Join Ordering ----------------------- The driver table is the fpago table (fpago) The fpago table (fpago) is joined to the cpagos table (cpagos) with the INNER JOIN expression: fpago.CODIGO = cpagos.FPAGO The cpagos table (cpagos) is joined to the movim table (movim) with the INNER JOIN expression: upper(cpagos.MOV) = upper(movim.MOV) AND cpagos.FACTURA = movim.FACTURA AND cpagos.SERIE = movim.SERIE The expression: cpagos.PAGADO <> 0 is UN-OPTIMIZED and will be applied to each candidate row in the cpagos table (cpagos) as the result set is generated Scan Expression Execution ------------------------- The expression: IF(cpagos.FECHA = '2009-10-19',HORA >= '12:48:27',movim.FECHA > '2009-10-19') = TRUE is UN-OPTIMIZED and will be applied to each candidate row in the result set as the result set is generated Join Execution -------------- Costs ARE NOT being taken into account when executing this join Use the JOINOPTIMIZECOSTS clause at the end of the SQL statement to force the optimizer to consider costs when optimizing this join The expression: fpago.CODIGO = cpagos.FPAGO is UN-OPTIMIZED The expression: upper(cpagos.MOV) = upper(movim.MOV) AND cpagos.FACTURA = movim.FACTURA AND cpagos.SERIE = movim.SERIE has been rewritten and is OPTIMIZED ================================================================================ >>>>> 0 rows affected in 83,203 seconds ================================================================================ TABLES INVOLVED: ---------------------------------------------------------------------------------------- MOVIM: ---------------------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS "movim" ( "AUTO" AUTOINC, "FECHA" DATE, "FACTURA" INTEGER, "MOV" VARCHAR(10), "IMPORTE" FLOAT, "CANTIDAD" FLOAT, "CLIENTE" VARCHAR(25), "NOM_C" VARCHAR(100), "IMPORTE_E" FLOAT, "FECHA_VENC" DATE, "TOTAL" FLOAT, "TOTAL_E" FLOAT, "EXPORTADO" SMALLINT, "PAGADO" SMALLINT, "AGENTE" INTEGER, "TOTALAGE" FLOAT, "FPAGO" INTEGER, "COLOR" VARCHAR(10), "SERIE" VARCHAR(1), "ESTADO" MEMO, "IVA_RE" VARCHAR(2), "HORA" TIME, "BLOQUEADO" BOOLEAN, "COBRADO" FLOAT, "PENDIENTE" FLOAT, "CAMPO" VARCHAR(50), "DTO" FLOAT, PRIMARY KEY ("AUTO") COMPRESS FULL LOCALE CODE 1034 ); CREATE NOCASE INDEX IF NOT EXISTS "movven" ON "movim" ("FECHA_VENC") COMPRESS FULL; CREATE NOCASE INDEX IF NOT EXISTS "movfac" ON "movim" ("FACTURA") COMPRESS FULL; CREATE NOCASE INDEX IF NOT EXISTS "movfec" ON "movim" ("FECHA") COMPRESS FULL; CREATE NOCASE INDEX IF NOT EXISTS "movtip" ON "movim" ("MOV") COMPRESS FULL; CREATE NOCASE INDEX IF NOT EXISTS "movname" ON "movim" ("NOM_C") COMPRESS FULL; CREATE INDEX IF NOT EXISTS "FPAGO" ON "movim" ("FPAGO"); CREATE INDEX IF NOT EXISTS "TOTAL_E" ON "movim" ("TOTAL_E"); CREATE INDEX IF NOT EXISTS "SERIE" ON "movim" ("SERIE"); ---------------------------------------------------------------------------------------- CPAGOS: ---------------------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS "cpagos" ( "AUTO" AUTOINC, "FACTURA" INTEGER, "SERIE" VARCHAR(1), "PAGADO" FLOAT, "FECHA" DATE, "MOV" VARCHAR(8), "FPAGO" INTEGER, "HECHO" BOOLEAN, PRIMARY KEY ("AUTO") COMPRESS FULL LOCALE CODE 1034 ); CREATE NOCASE INDEX IF NOT EXISTS "fec" ON "cpagos" ("FECHA") COMPRESS FULL; CREATE INDEX IF NOT EXISTS "FACTURA" ON "cpagos" ("FACTURA"); CREATE INDEX IF NOT EXISTS "MOV" ON "cpagos" ("MOV"); ---------------------------------------------------------------------------------------- FPAGO ---------------------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS "fpago" ( "CODIGO" INTEGER, "FORMAPAGO" VARCHAR(50), "EN_TPV" BOOLEAN, "FORMULA" CHAR(5), "SUMA" BOOLEAN, PRIMARY KEY ("RecordID") COMPRESS NONE LOCALE CODE 1034 ); CREATE INDEX IF NOT EXISTS "CODIGO" ON "fpago" ("CODIGO"); -- Santy |
Tue, Oct 20 2009 6:00 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Santy,
<< I can post the tables if you need it. >> Please email me the tables. Most likely the issue is caused by an un-optimized join. -- Tim Young Elevate Software www.elevatesoft.com |
Tue, Oct 20 2009 8:31 AM | Permanent Link |
"John Hay" | Santy You could try the following to improve the join speeds Create an index on cpagos.FPAGO Create an index on cpagos.SERIE Create a nocase index on cpagos.mov (CREATE NOCASE INDEX IF NOT EXISTS "MOV" ON "cpagos" ("MOV") John |
Tue, Oct 20 2009 1:27 PM | Permanent Link |
Santy Concepción | Hi!
If I add "JOINOPTIMIZECOSTS" to the query, the result takes just 4 seconds! Without "JOINOPTIMIZECOSTS", it takes about 6 minutes. What are the pros and cons of JOINOPTIMIZECOSTS? Is it really necessary? I have tried to create more indexes. Same result. "John Hay" wrote: Santy You could try the following to improve the join speeds Create an index on cpagos.FPAGO Create an index on cpagos.SERIE Create a nocase index on cpagos.mov (CREATE NOCASE INDEX IF NOT EXISTS "MOV" ON "cpagos" ("MOV") John |
Tue, Oct 20 2009 6:28 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Santy,
<< If I add "JOINOPTIMIZECOSTS" to the query, the result takes just 4 seconds! Without "JOINOPTIMIZECOSTS", it takes about 6 minutes. What are the pros and cons of JOINOPTIMIZECOSTS? Is it really necessary? >> What it does is add I/O cost estimations into the set of criteria that the query optimizer uses for determining how to order the joins. Sometimes it is very accurate, and sometimes it isn't, and the reason is that it takes only a sample of each join to determine how many rows each join will produce, and uses that to put the most-selective joins first in the ordering of the joins. If the distribution of the joins is fairly equal across all rows in the driver table for the join (the table on the left), then the JOINOPTIMIZECOSTS clause will be fairly successful and accurate. -- Tim Young Elevate Software www.elevatesoft.com |
This web page was last updated on Wednesday, June 12, 2024 at 01:54 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |