This statement,

SELECT
Matricula,
M.Nombre AS Nombre_Marca,
Modelo,
SUBSTRING(T.Nombre,1,15) AS Nombre_Tipo,
NumTarjeta,
Clase,
CaducidadTarjeta,
NOMBRECOMPLETO(C.RAZONSOCIAL,C.APELLIDO2,C.NOMBRE) AS Nombre_Completo,
DOMICILIOCOMPLETO(SV.Sigla,C.ViaPublica2,C.NumeroVia2,C.Escalera2,C.Piso2,C.Puerta2) AS Domicilio_Completo,
MUNICIPIOCOMPLETO(C.CPostal2,C.Municipio2,PR.Nombre) AS Municipio_Completo,
ExpedicionTarjeta,
Ambito,
C.NIF
FROM
MAVEHICU
INNER JOIN MAMARVEH M ON (M.CODIGO = MARCA)
INNER JOIN MATIPVEH T ON (T.CODIGO = TIPOVEHICULO)
INNER JOIN MACLIENT C ON (C.NIF = PROPIETARIO)
INNER JOIN MASIGLAV SV ON (C.SiglaViaCod2 = SV.Codigo)
INNER JOIN MAPROVIN PR ON (C.Provincia2 = PR.Sigla)
WHERE
CADUCIDADTARJETA BETWEEN :DFecha AND :HFecha AND
BLOQUEADO = FALSE AND HISTORICO = FALSE AND
C.NOQUIEREAVISOS = FALSE AND C.BLOQUEADO = FALSE AND C.HISTORICO = FALSE
ORDER BY
C.NIF

Used to execute in a few seconds before 2.03.7

Since 2.03.7 and also 2.03.9 this statement delays for more than 10 minutes (I stopped it)
If you add JOINOPTIMIZECOSTS at the end it fires in 0.9 seconds. I have a bunch of samples, all with same performance difference.
SQL plan shows engine is bad reordering joins, setting a wrong driver table.

Tiago,

<< Used to execute in a few seconds before 2.03.7

Since 2.03.7 and also 2.03.9 this statement delays for more than 10 minutes
(I stopped it)

If you add JOINOPTIMIZECOSTS at the end it fires in 0.9 seconds. I have a
bunch of samples, all with same performance difference. SQL plan shows
engine is bad reordering joins, setting a wrong driver table. >>

There were some join changes in 2.03 B7 and above, so you might need to use
JOINOPTIMIZECOSTS in some cases.   ElevateDB can do a better job of
re-ordering the joins when you have that option set because it can actually
figure out the cheapest joins in terms of I/O.  This is especially important
when all of the joins are optimized.

However, send me the database catalog and tables and I'll double-check just
to make sure that it isn't doing something wrong.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim,

<<However, send me the database catalog and tables and I'll double-check just
to make sure that it isn't doing something wrong.>>

I've send you a mail to support@elevatesoft.com with sample data.

Tiago,

<< I've send you a mail to support@elevatesoft.com with sample data. >>

Thanks, it's fixed now.  As I said in my email response to you, the issue is
with the optimizer being a bit over-aggressive in moving join conditions
before other un-optimized conditions in the execution order.  This would
result in the un-optimized conditions being executed for each join, thus
causing a severe slowdown.

This will be fixed in B10.

--
Tim Young
Elevate Software
www.elevatesoft.com