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

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

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

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

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