Hi!

I would like to migrate my apps from DBISAM 4 to EDB 2, and I am making some
tests to check EDB improvements, speed, etc... and compare them.

I made a simple project which connects to a remote DBISAM server and to a
remote EDB server.

EDB is faster opening tables and locating records (with Locate), BUT
executing a simple query takes about 11 seconds with EDB, and just 1 second
with DBISAM!

Tables are the same (migrated from dbisam using EDBManager).

The query is slow using both the EDBManager and my app.
The query is the following:

This is the EDB query plan:

================================================================================
SQL Query (Executed by ElevateDB 2.03 Build 6)

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
"movim"."AUTO" AS "AUTO",
"movim"."FECHA" AS "FECHA",
"movim"."FACTURA" AS "FACTURA",
"movim"."MOV" AS "MOV",
"movim"."IMPORTE" AS "IMPORTE",
"movim"."CANTIDAD" AS "CANTIDAD",
"movim"."CLIENTE" AS "CLIENTE",
"movim"."NOM_C" AS "NOM_C",
"movim"."IMPORTE_E" AS "IMPORTE_E",
"movim"."FECHA_VENC" AS "FECHA_VENC",
"movim"."TOTAL" AS "TOTAL",
"movim"."TOTAL_E" AS "TOTAL_E",
"movim"."EXPORTADO" AS "EXPORTADO",
"movim"."PAGADO" AS "PAGADO",
"movim"."AGENTE" AS "AGENTE",
"movim"."TOTALAGE" AS "TOTALAGE",
"movim"."FPAGO" AS "FPAGO",
"movim"."COLOR" AS "COLOR",
"movim"."SERIE" AS "SERIE",
"movim"."ESTADO" AS "ESTADO",
"movim"."IVA_RE" AS "IVA_RE",
"movim"."HORA" AS "HORA",
"movim"."BLOQUEADO" AS "BLOQUEADO",
"movim"."COBRADO" AS "COBRADO",
"movim"."PENDIENTE" AS "PENDIENTE",
"movim"."CAMPO" AS "CAMPO",
"movim"."DTO" AS "DTO",
"clientes"."CODIGO" AS "CODIGO",
"clientes"."NOMBRE" AS "NOMBRE",
"clientes"."DIRECCION" AS "DIRECCION",
"clientes"."POBLACION" AS "POBLACION",
"clientes"."NIF" AS "NIF",
"clientes"."CODIPOS" AS "CODIPOS",
"clientes"."TELEFONO" AS "TELEFONO",
"clientes"."TELEFONO_2" AS "TELEFONO_2",
"clientes"."E_MAIL" AS "E_MAIL",
"clientes"."NOTAS" AS "NOTAS",
"clientes"."PROVINCIA" AS "PROVINCIA",
"clientes"."FPAGO" AS "FPAGO1",
"clientes"."CUENTA" AS "CUENTA",
"clientes"."IMP" AS "IMP",
"clientes"."RE" AS "RE",
"clientes"."DTO" AS "DTO1",
"clientes"."AVISAR" AS "AVISAR",
"clientes"."CONTACTO" AS "CONTACTO",
"clientes"."TARIFA" AS "TARIFA",
"clientes"."SERIE" AS "SERIE1",
"clientes"."ABONO" AS "ABONO",
"clientes"."COLOR" AS "COLOR1",
"clientes"."N_FISCAL" AS "N_FISCAL",
"clientes"."PAIS" AS "PAIS",
"clientes"."FAX" AS "FAX",
"clientes"."ALTA" AS "ALTA",
"clientes"."NACIMIENTO" AS "NACIMIENTO",
"clientes"."TIPO" AS "TIPO",
"clientes"."BLOQUEADO" AS "BLOQUEADO1",
"clientes"."ARCHIVO" AS "ARCHIVO",
"clientes"."AGENTE" AS "AGENTE1"
FROM "movim", "clientes"
WHERE "movim"."CLIENTE" = "clientes"."CODIGO"
ORDER BY "clientes"."NOMBRE"

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

movim: 783 rows
clientes: 1983 rows

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

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

Filtering
---------

The following filter condition was applied to the result set rows as they
were
generated:

"movim"."CLIENTE" = "clientes"."CODIGO"

Joins
-----

The driver table was the movim table

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

The joins were already in the most optimal order


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

Total rows visited: 1552689

Row buffer manager

Max buffer size: 376832 Buffer size: 849344

Hits: 577   Misses: 0   Hit ratio: 1

Reads: 0   Bytes read: 0

Writes: 0   Bytes written: 0

Index page buffer manager

Max buffer size: 65536 Buffer size: 77824

Hits: 1146   Misses: 0   Hit ratio: 1

Reads: 0   Bytes read: 0

Writes: 0   Bytes written: 0

BLOB block buffer manager

Max buffer size: 32768 Buffer size: 8704

Hits: 17   Misses: 0   Hit ratio: 1

Reads: 0   Bytes read: 0

Writes: 0   Bytes written: 0

================================================================================
577 row(s) returned in 11,906 secs
================================================================================



--
Santy C

Santy


I'm still getting up to speed on the ElevateDB query plans but I think

FROM "movim", "clientes"
WHERE "movim"."CLIENTE" = "clientes"."CODIGO"

as show by

Filtering
---------
The following filter condition was applied to the result set rows as they
were
generated:

"movim"."CLIENTE" = "clientes"."CODIGO"

is your problem.

Try rewriting it to use a join eg

FROM "movim"
JOIN Clientes ON"movim"."CLIENTE" = "clientes"."CODIGO"


A bit of experimentation shows that the execution plan gives no indication if there was an index used as part of the ORDER BY clause or not so make sure its there.

Roy Lambert [Team Elevate]

Hi, Roy...

Both tables have their indexes, both on Dbisam and EDB.

If I change the SQL query and use JOIN, the query executes fast, so... May I
have to change every two-tables queries and use JOINS? Qhy DBISAM doesn't
need to use joins?

Thanks!

--
Santy C

"Roy Lambert" <roy.lambert@skynet.co.uk> escribió en el mensaje de
noticias:647CF358-D505-4826-8837-1FBD23C1157E@news.elevatesoft.com...
> Santy
>
>
> I'm still getting up to speed on the ElevateDB query plans but I think
>
> FROM "movim", "clientes"
> WHERE "movim"."CLIENTE" = "clientes"."CODIGO"
>
> as show by
>
> Filtering
> ---------
> The following filter condition was applied to the result set rows as they
> were
> generated:
>
> "movim"."CLIENTE" = "clientes"."CODIGO"
>
> is your problem.
>
> Try rewriting it to use a join eg
>
> FROM "movim"
> JOIN Clientes ON"movim"."CLIENTE" = "clientes"."CODIGO"
>
>
> A bit of experimentation shows that the execution plan gives no indication
> if there was an index used as part of the ORDER BY clause or not so make
> sure its there.
>
> Roy Lambert [Team Elevate]
>
>
>
>

Santy

>Both tables have their indexes, both on Dbisam and EDB.

Good, missing indices are always a good guess for a slow query though

>If I change the SQL query and use JOIN, the query executes fast, so... May I
>have to change every two-tables queries and use JOINS?

I don't really know but my best guess would be "probably, or in some cases subselects"

>Qhy DBISAM doesn't
>need to use joins?

Different version of SQL. I think DBISAM was 89/92 and ElevateDB is 2003. There are chunks of the 2003 standard I don't like (if you search these ngs you find a number of vociferous comments from me) and chunks I don't understand but it works.

Roy Lambert [Team Elevate]

Santy,

<< If I change the SQL query and use JOIN, the query executes fast, so...
May I have to change every two-tables
queries and use JOINS? >>

Yes.  You need to use SQL-92 joins in order to have the EDB query optimizer
optimize them.

<< Qhy DBISAM doesn't need to use joins? >>

It supports the older SQL-89 joins, and EDB does not.

BTW, this is mentioned in the manual here:

http://www.elevatesoft.com/manual?action=mantopic&id=edb2&product=d&version=7&category=2&topic=21

under "SELECT":

"ElevateDB does not optimize join expressions in the WHERE clause, otherwise
known as SQL-89 style joins. You must use the JOIN clause in order to have
ElevateDB optimize the joins."

--
Tim Young
Elevate Software
www.elevatesoft.com