Optimizando Consulta

[lbuelvas]

Cordial saludo. Puedes regalarnos los scripts SQL para generar esa parte de la base de datos, con algunos datos. Esto para poder reproducir la consulta.

Personalmente al trabajar un proyecto de una base de datos, hago scripts para llenar las tablas con el estimado de información de 10 años. Allí me doy cuenta de que optimizaciones se deben hacer.

[lbuelvas]

Bueno, de lo que alcanzo a observar, la parte

Código SQL [-]
WHERE CAST(FACTURAS_VENTAS.FECHA AS DATE) BETWEEN :FECHAINI AND :FECHAFIN

hace que la operación cast tenga que hacerse en todos los registros convirtiendo el valor a fecha y luego ver si se encuentre en el rango fechaini y fechafin, por tanto hará un recorrido natural de la tabla.

Si un campo es para almacenar una fecha lo mejor es que sea del tipo correspondiente, es decir, Date; ahora, si es un timestamp no es necesario hacer el cast.

[lbuelvas]

Sobre el código

Código SQL [-]
FROM FAC_CLIENTE
INNER JOIN CLIENTES ON FAC_CLIENTE.ID_CLIENTE = CLIENTES.ID_CLIENTE
INNER JOIN FACTURAS_VENTAS ON FAC_CLIENTE.ID_FACTURA = FACTURAS_VENTAS.ID_FACTURA
INNER JOIN FAC_CAJA ON FACTURAS_VENTAS.ID_FACTURA = FAC_CAJA.ID_FACTURA
INNER JOIN CAJAS ON CAJAS.ID_CAJA = FAC_CAJA.ID_CAJA

Decir que de si las llaves foráneas son "not null" (es decir que debe ir un valor para esos campos) utilizar LEFT JOIN en lugar de INNER JOIN puede hacer que el plan de la consulta utilice mejor los índices. Con Firebird llevo años usando LEFT JOIN sobre INNER JOIN.

[lbuelvas]

Si envías el script de esa parte del proyecto con algunos datos personalmente te podría colaborar un poco más.

[Casimiro Notevi]

Empezado por lbuelvas

Si envías el script de esa parte del proyecto con algunos datos personalmente te podría colaborar un poco más.

Totalmente necesario, sin eso no podemos hacer gran cosa.

Y además debe verificar que ambas bases de datos son realmente iguales.

[novato_erick]

Empezado por lbuelvas

Si envías el script de esa parte del proyecto con algunos datos personalmente te podría colaborar un poco más.

Código SQL [-]
/* SQL Manager for InterBase and Firebird 5.5.4.52620    */
/* ----------------------------------------------------- */
/* Host     : localhost                                  */
/* Database : C:\MIDB\BDASCII.FDB */


CREATE DATABASE 'localhost/3050:C:\MIDB\BDASCII.FDB'
  USER 'SYSDBA'
  PASSWORD 'masterkey'
  PAGE_SIZE = 4096
  DEFAULT CHARACTER SET ASCII
  COLLATION ASCII;

SET AUTODDL ON;

/* Structure for the `FAC_CLIENTE` table :  */

CREATE TABLE FAC_CLIENTE (
  ID_FACCLIENTE INTEGER NOT NULL,
  ID_CLIENTE INTEGER NOT NULL,
  ID_FACTURA INTEGER NOT NULL);


ALTER TABLE FAC_CLIENTE ADD PRIMARY KEY (ID_FACCLIENTE);

CREATE INDEX IDX_FAC_CLIENTE ON FAC_CLIENTE(ID_FACTURA);

CREATE INDEX IDX_FAC_CLIENTE1 ON FAC_CLIENTE(ID_CLIENTE);

/* Definition for the `FAC_CLIENTE_ID_FACCLIENTE_GEN` generator :  */

CREATE GENERATOR FAC_CLIENTE_ID_FACCLIENTE_GEN;

/* Definition for the `BI_FAC_CLIENTE_ID_FACCLIENTE` trigger :  */

SET TERM ^ ;

CREATE TRIGGER BI_FAC_CLIENTE_ID_FACCLIENTE FOR FAC_CLIENTE
ACTIVE BEFORE
  INSERT
POSITION 0
AS
BEGIN
  IF (NEW.ID_FACCLIENTE IS NULL) THEN
      NEW.ID_FACCLIENTE = GEN_ID(FAC_CLIENTE_ID_FACCLIENTE_GEN, 1);
END^

SET TERM ; ^

/* Structure for the `FAC_TARJETAS` table :  */

CREATE TABLE FAC_TARJETAS (
  ID_FACTARJETAS INTEGER NOT NULL,
  ID_TARJETA INTEGER NOT NULL,
  ID_FACTURA INTEGER NOT NULL,
  NUM_TARJETA INTEGER NOT NULL,
  NUM_TRANSTARJETA INTEGER NOT NULL,
  MONTOTARJETA DECIMAL(12, 2) NOT NULL);


ALTER TABLE FAC_TARJETAS ADD PRIMARY KEY (ID_FACTARJETAS);

CREATE INDEX IDX_FAC_TARJETAS ON FAC_TARJETAS(ID_FACTURA);

/* Definition for the `FAC_TARJETAS_ID_FACTARJETAS_GEN` generator :  */

CREATE GENERATOR FAC_TARJETAS_ID_FACTARJETAS_GEN;

/* Definition for the `BI_FAC_TARJETAS_ID_FACTARJETAS` trigger :  */

SET TERM ^ ;

CREATE TRIGGER BI_FAC_TARJETAS_ID_FACTARJETAS FOR FAC_TARJETAS
ACTIVE BEFORE
  INSERT
POSITION 0
AS
BEGIN
  IF (NEW.ID_FACTARJETAS IS NULL) THEN
      NEW.ID_FACTARJETAS = GEN_ID(FAC_TARJETAS_ID_FACTARJETAS_GEN, 1);
END^

SET TERM ; ^

/* Structure for the `FAC_CHEQUES` table :  */

CREATE TABLE FAC_CHEQUES (
  ID_FACCHEQUE INTEGER NOT NULL,
  ID_FACTURA INTEGER NOT NULL,
  MONTOCHEQUE DOUBLE PRECISION DEFAULT 0.0,
  NUM_CHEQUE INTEGER NOT NULL,
  BANCOCHEQUE VARCHAR(150) NOT NULL,
  FECHA_CHEQUE TIMESTAMP NOT NULL,
  VUELTOCHEQUE DECIMAL(12, 2) DEFAULT 0.0 NOT NULL);


ALTER TABLE FAC_CHEQUES ADD PRIMARY KEY (ID_FACCHEQUE);


CREATE INDEX IDX_FAC_CHEQUES ON FAC_CHEQUES(ID_FACTURA);

/* Definition for the `FAC_CHEQUES_ID_FACCHEQUE_GEN` generator :  */

CREATE GENERATOR FAC_CHEQUES_ID_FACCHEQUE_GEN;

/* Definition for the `BI_FAC_CHEQUES_ID_FACCHEQUE` trigger :  */

SET TERM ^ ;

CREATE TRIGGER BI_FAC_CHEQUES_ID_FACCHEQUE FOR FAC_CHEQUES
ACTIVE BEFORE
  INSERT
POSITION 0
AS
BEGIN
  IF (NEW.ID_FACCHEQUE IS NULL) THEN
      NEW.ID_FACCHEQUE = GEN_ID(FAC_CHEQUES_ID_FACCHEQUE_GEN, 1);
END^

SET TERM ; ^

/* Structure for the `FAC_COMBINADA` table :  */

CREATE TABLE FAC_COMBINADA (
  ID_FACCOMBI INTEGER NOT NULL,
  ID_FACTURA INTEGER NOT NULL);


ALTER TABLE FAC_COMBINADA ADD PRIMARY KEY (ID_FACCOMBI);

/* Definition for the `FAC_COMBINADA_ID_FACCOMBI_GEN` generator :  */

CREATE GENERATOR FAC_COMBINADA_ID_FACCOMBI_GEN;

/* Definition for the `BI_FAC_COMBINADA_ID_FACCOMBI` trigger :  */

SET TERM ^ ;

CREATE TRIGGER BI_FAC_COMBINADA_ID_FACCOMBI FOR FAC_COMBINADA
ACTIVE BEFORE
  INSERT
POSITION 0
AS
BEGIN
  IF (NEW.ID_FACCOMBI IS NULL) THEN
      NEW.ID_FACCOMBI = GEN_ID(FAC_COMBINADA_ID_FACCOMBI_GEN, 1);
END^

SET TERM ; ^

/* Structure for the `FAC_CREDITO` table :  */

CREATE TABLE FAC_CREDITO (
  ID_FACCREDITO INTEGER NOT NULL,
  ID_FACTURA INTEGER NOT NULL,
  ID_CLIENTE INTEGER NOT NULL,
  PENDIENTE CHAR(1) DEFAULT 'S' NOT NULL,
  FECHAINI_CRE TIMESTAMP NOT NULL,
  FECHAFIN_CRE TIMESTAMP NOT NULL,
  M_VENTACREDITO DECIMAL(12, 2) DEFAULT 0.0,
  DETALLE_FAC_CREDITO VARCHAR(125) NOT NULL,
  CONSECUTIVO_FAC INTEGER);


ALTER TABLE FAC_CREDITO ADD PRIMARY KEY (ID_FACCREDITO);


CREATE INDEX IDX_FAC_CREDITO ON FAC_CREDITO(ID_FACTURA);

/* Definition for the `FAC_CREDITO_ID_FACCREDITO_GEN` generator :  */

CREATE GENERATOR FAC_CREDITO_ID_FACCREDITO_GEN;

/* Definition for the `BI_FAC_CREDITO_ID_FACCREDITO` trigger :  */

SET TERM ^ ;

CREATE TRIGGER BI_FAC_CREDITO_ID_FACCREDITO FOR FAC_CREDITO
ACTIVE BEFORE
  INSERT
POSITION 0
AS
BEGIN
  IF (NEW.ID_FACCREDITO IS NULL) THEN
      NEW.ID_FACCREDITO = GEN_ID(FAC_CREDITO_ID_FACCREDITO_GEN, 1);
END^

SET TERM ; ^

/* Structure for the `FORMA_PAGO` table :  */

CREATE TABLE FORMA_PAGO (
  ID_FORMAPAGO INTEGER NOT NULL,
  FORMAPAGO VARCHAR(50));


ALTER TABLE FORMA_PAGO ADD PRIMARY KEY (ID_FORMAPAGO);

/* Structure for the `FAC_F_PAGO` table :  */

CREATE TABLE FAC_F_PAGO (
  ID_FAC_F_PAGO INTEGER NOT NULL,
  ID_FORMAPAGO INTEGER NOT NULL,
  ID_FACTURA INTEGER NOT NULL);


ALTER TABLE FAC_F_PAGO ADD PRIMARY KEY (ID_FAC_F_PAGO);

/* Definition for the `FAC_F_PAGO_ID_FAC_F_PAGO_GEN` generator :  */

CREATE GENERATOR FAC_F_PAGO_ID_FAC_F_PAGO_GEN;

/* Definition for the `BI_FAC_F_PAGO_ID_FAC_F_PAGO` trigger :  */

SET TERM ^ ;

CREATE TRIGGER BI_FAC_F_PAGO_ID_FAC_F_PAGO FOR FAC_F_PAGO
ACTIVE BEFORE
  INSERT
POSITION 0
AS
BEGIN
  IF (NEW.ID_FAC_F_PAGO IS NULL) THEN
      NEW.ID_FAC_F_PAGO = GEN_ID(FAC_F_PAGO_ID_FAC_F_PAGO_GEN, 1);
END^

SET TERM ; ^

/* Definition for the `FORMA_PAGO_ID_FORMAPAGO_GEN` generator :  */

CREATE GENERATOR FORMA_PAGO_ID_FORMAPAGO_GEN;

/* Definition for the `BI_FORMA_PAGO_ID_FORMAPAGO` trigger :  */

SET TERM ^ ;

CREATE TRIGGER BI_FORMA_PAGO_ID_FORMAPAGO FOR FORMA_PAGO
ACTIVE BEFORE
  INSERT
POSITION 0
AS
BEGIN
  IF (NEW.ID_FORMAPAGO IS NULL) THEN
      NEW.ID_FORMAPAGO = GEN_ID(FORMA_PAGO_ID_FORMAPAGO_GEN, 1);
END^

SET TERM ; ^

/* Structure for the `FAC_NCRECIB` table :  */

CREATE TABLE FAC_NCRECIB (
  ID_FACNC INTEGER NOT NULL,
  SUB_TOTALNC DECIMAL(12, 2) DEFAULT 0.0 NOT NULL,
  DESC_NC DECIMAL(12, 2) DEFAULT 0.0 NOT NULL,
  MONTO_IVANC DECIMAL(12, 2) DEFAULT 0.0 NOT NULL,
  MONTO_NC DECIMAL(12, 2) DEFAULT 0.0 NOT NULL,
  FECHA_RECIBO TIMESTAMP NOT NULL,
  ID_CAJA INTEGER NOT NULL,
  ID_USUARIO INTEGER NOT NULL,
  ID_CLIENTE INTEGER NOT NULL,
  ID_FACTURA INTEGER NOT NULL,
  CONSECUTIVO_FAC INTEGER NOT NULL,
  ID_NOTACRED INTEGER NOT NULL);


ALTER TABLE FAC_NCRECIB ADD PRIMARY KEY (ID_FACNC);


CREATE INDEX IDX_FAC_NCRECIB ON FAC_NCRECIB(ID_FACTURA);

/* Definition for the `FAC_NCRECIB_ID_FACNC_GEN` generator :  */

CREATE GENERATOR FAC_NCRECIB_ID_FACNC_GEN;

/* Definition for the `BI_FAC_NCRECIB_ID_FACNC` trigger :  */

SET TERM ^ ;

CREATE TRIGGER BI_FAC_NCRECIB_ID_FACNC FOR FAC_NCRECIB
ACTIVE BEFORE
  INSERT
POSITION 0
AS
BEGIN
  IF (NEW.ID_FACNC IS NULL) THEN
      NEW.ID_FACNC = GEN_ID(FAC_NCRECIB_ID_FACNC_GEN, 1);
END^

SET TERM ; ^

/* Structure for the `FAC_VENDEDOR` table :  */

CREATE TABLE FAC_VENDEDOR (
  ID_FACVENDEDOR INTEGER NOT NULL,
  ID_VENDEDOR INTEGER NOT NULL,
  ID_FACTURA INTEGER NOT NULL);


ALTER TABLE FAC_VENDEDOR ADD PRIMARY KEY (ID_FACVENDEDOR);

/* Definition for the `FAC_VENDEDOR_ID_FACVENDEDOR_GEN` generator :  */

CREATE GENERATOR FAC_VENDEDOR_ID_FACVENDEDOR_GEN;

/* Definition for the `BI_FAC_VENDEDOR_ID_FACVENDEDOR` trigger :  */

SET TERM ^ ;

CREATE TRIGGER BI_FAC_VENDEDOR_ID_FACVENDEDOR FOR FAC_VENDEDOR
ACTIVE BEFORE
  INSERT
POSITION 0
AS
BEGIN
  IF (NEW.ID_FACVENDEDOR IS NULL) THEN
      NEW.ID_FACVENDEDOR = GEN_ID(FAC_VENDEDOR_ID_FACVENDEDOR_GEN, 1);
END^

SET TERM ; ^

/* Structure for the `FACTURAS_VENTAS` table :  */

CREATE TABLE FACTURAS_VENTAS (
  ID_FACTURA INTEGER NOT NULL,
  NUMEROFAC INTEGER,
  FECHA TIMESTAMP DEFAULT 'NOW' NOT NULL,
  MONTOSUBTOTAL DECIMAL(12, 2) DEFAULT 0.0,
  MONTOSUBTOTALCONDESC DECIMAL(12, 2) DEFAULT 0.0,
  MONTODESCUENTO DECIMAL(12, 2) DEFAULT 0.0 NOT NULL,
  MONTOIMPUESTO DECIMAL(12, 2) DEFAULT 0.0 NOT NULL,
  MONTOTOTAL DECIMAL(12, 2) DEFAULT 0.0 NOT NULL,
  PAGACON DECIMAL(12, 2) DEFAULT 0.0 NOT NULL,
  CAMBIO DECIMAL(12, 2) NOT NULL,
  MONTO_EFECTIVO DECIMAL(12, 2) DEFAULT 0.0 NOT NULL,
  SERIAL_IMPRESORA VARCHAR(25),
  NUM_CUPONIMPRESORA INTEGER NOT NULL,
  FACTURA_IMPRESORA INTEGER NOT NULL,
  DATE_DEV DATE DEFAULT 'NOW' NOT NULL,
  CONSECUTIVO INTEGER NOT NULL,
  VENTASEXENTAS DECIMAL(12, 2) DEFAULT 0.0 NOT NULL,
  VENTASSIETE DECIMAL(12, 2) DEFAULT 0.0 NOT NULL,
  VENTASDIEZ DECIMAL(12, 2) DEFAULT 0.0 NOT NULL,
  VENTASQUINCE DECIMAL(12, 2) DEFAULT 0.0 NOT NULL);


ALTER TABLE FACTURAS_VENTAS ADD PRIMARY KEY (ID_FACTURA);


CREATE INDEX IDX_FACTURAS_VENTAS ON FACTURAS_VENTAS(ID_FACTURA);

CREATE INDEX IDX_FACTURAS_VENTAS1 ON FACTURAS_VENTAS(CONSECUTIVO);

/* Structure for the `CONSECUTIVO` table :  */

CREATE TABLE CONSECUTIVO (
  IDCONSECUTIVO INTEGER NOT NULL,
  ULTIMOASIGNADO INTEGER NOT NULL);


ALTER TABLE CONSECUTIVO ADD PRIMARY KEY (IDCONSECUTIVO);

/* Definition for the `SIGUIENTECONSECUTIVO` procedure :  */

SET TERM ^ ;

CREATE PROCEDURE SIGUIENTECONSECUTIVO(
  AIDCONSECUTIVO INTEGER NOT NULL)
RETURNS(
  CONSECUTIVO INTEGER)
AS
DECLARE VARIABLE BANDERA INTEGER;
BEGIN
   bandera = 0;
  while (bandera = 0) do
  begin
    update Consecutivo
       set IDConsecutivo = IDConsecutivo
     where IDConsecutivo = :AIDConsecutivo;
    bandera = 1;
    --901: Lock conflict
    --903: Deadlock
    when sqlcode -901 do
    begin
      bandera = 0;
    end
  end
  select UltimoAsignado + 1
    from Consecutivo
   where IDConsecutivo = :AIDConsecutivo
    into :Consecutivo;
  update Consecutivo
     set UltimoAsignado = :Consecutivo
   where IDConsecutivo = :AIDConsecutivo;
    SUSPEND;
END^

SET TERM ; ^

/* Definition for the `TOMACONSECUTIVO` procedure :  */

SET TERM ^ ;

CREATE OR ALTER PROCEDURE TOMACONSECUTIVO(
  AIDCONSECUTIVO INTEGER)
AS
DECLARE VARIABLE BANDERA INTEGER;
BEGIN
  bandera = 0;
  while (bandera = 0) do
  begin
    update Consecutivo
       set IDConsecutivo = IDConsecutivo
     where IDConsecutivo = :AIDConsecutivo;
    bandera = 1;
    --901: Lock conflict
    --903: Deadlock
    when sqlcode -901 do
    begin
      bandera = 0;
    end
  end
  SUSPEND;
END^


/* Structure for the `DETALLE_FACTURASVENTAS` table :  */

CREATE TABLE DETALLE_FACTURASVENTAS (
  ID_FACDET INTEGER DEFAULT 0 NOT NULL,
  ID_FACTURA INTEGER NOT NULL,
  ID_ARTICULO INTEGER DEFAULT 0,
  ID_DEPTO INTEGER NOT NULL,
  CODIGO_BARRAS VARCHAR(30) NOT NULL,
  PRECIO_UNITARIO DECIMAL(12, 2) DEFAULT 0.0,
  CANTIDAD DECIMAL(12, 2) DEFAULT 0.0 NOT NULL,
  IMPUESTO DECIMAL(12, 2),
  MONTOIMPUESTO DECIMAL(12, 2),
  DESCUENTO DECIMAL(12, 2),
  MONTO_DESCUENTO DECIMAL(12, 2),
  TOTAL DECIMAL(12, 2),
  DEVUELTO INTEGER DEFAULT 0 NOT NULL);

Disculpa la demora..


Saludos;

[mamcx]

Empezado por lbuelvas

utilizar LEFT JOIN en lugar de INNER JOIN

No, eso no esta bien. Eso altera los resultados (semantica diferente!)

[[egostar]]

Empezado por mamcx

No, eso no esta bien. Eso altera los resultados (semantica diferente!)

Ciertamente....

[Casimiro Notevi]

Empezado por egostar

Ciertamente....

Muy bueno

[lbuelvas]

Ya hicieron la prueba ? Si la llave foránea es "not null" funciona como un inner join. Sigo trabajando con bases de datos Firebird 1.5 y el preprocesador (el que define el plan de la consulta) no selecciona algunos índices como uno esperaría. Mis consultas funcionan como espero que funcionen. En Firebird 3 he tratado de usar inner join pero si no toma el índice que espero paso a usar left join siempre y cuando la llave foránea tenga valor (definiéndola not null). Lo que pasa es que trabajo casi exclusivamente con Interbase y luego Firebird desde el año 1998. Firebird 1.5 es suficiente para todo, pero ahora Windows 10 cada vez que hace una actualización importante desinstala el motor de base de datos por el defecto que tiene el instalador de Firebird 1.5 que el Applet que se adiciona al panel de control hace que éste cuando se abre se cierra inmediatamente. Para instalar Firebird 1.5 toca cambia el nombre del instalador pero de tanto en tanto Windows 10 lo desinstala. Estoy moviéndome a Firebird 3 pero los componentes IBX no van bien con este motor por lo que adquirí UniDac y estamos pasando de VCL a uniGui y de IBX a uniDac.

[novato_erick]

Empezado por Casimiro Notevi

Muy bueno

Ciertamente Si...

egostar, Casimiro, mamcx es un gran privilegio que lean mi post ayudadome como siempre.. Bendiciones Totales....



Por cierto uso el inner join porque se que cada fila de la tabla A exista una fila en la tabla B. bueno en teoría.


Provaré la sugerencia de lbuelvas del uso del CAST..

Saludos y les informo