Optimizando Consulta

[novato_erick]

Hola Amigos:
Es un placer siempre saber como están y como siempre aprovechando de sus conocimiento avanzados me encargo de sacar provecho del mismo:

Tengo esta consulta la cual en mi db en Firebird 2.5.7.27050 de Desarrollo con diferencia de 150 mil registros a la de producción noto de no es el mismo rendimiento a pesar que ya se realizaron los indexados correcto en cada tabla aquí mostrada no veo porque la direrencia en hacer la misma consulta en tiempo de traer los datos es significativamente amplio una de la otra:


Base de Datos de producción
Prepare : 16 ms
Execute : 0 ms
Avg fetch time: 0 ms

Memory Usage
------------------------------------------------
Current: 1.11 MB
Max : 1.12 MB
Buffers: 75

Database Operations
------------------------------------------------
Reads : 63417
Writes : 4
Fetches: 15903532

Plan:
------------------------------------------------
PLAN SORT (JOIN (FAC_CAJA NATURAL, CAJAS INDEX (RDB$PRIMARY77), FACTURAS_VENTAS INDEX (RDB$PRIMARY45), FAC_CLIENTE INDEX (IDX_FAC_CLIENTE), CLIENTES INDEX (RDB$PRIMARY85)))

Table Operations:
+--------------------------+-----------+-----------+-----------+-----------+-----------+
| Table Name | Index | Non-Index | Updates | Deletes | Inserts |
| | reads | reads | | | |
+--------------------------+-----------+-----------+-----------+-----------+-----------+
| FAC_CAJA| 0 | 1,321,933 | 0 | 0 | 0 |
| FACTURAS_VENTAS| 1,321,933 | 0 | 0 | 0 | 0 |
| FAC_CLIENTE| 52 | 0 | 0 | 0 | 0 |
| CAJAS| 1,321,933 | 0 | 0 | 0 | 0 |
| CLIENTES| 52 | 0 | 0 | 0 | 0 |
+--------------------------+-----------+-----------+-----------+-----------+-----------+


ahora base de datos producción

Query Performance
------------------------------------------------
Prepare : 0 ms
Execute : 0 ms
Avg fetch time: 0 ms

Memory Usage
------------------------------------------------
Current: 98.30 MB
Max : 98.31 MB
Buffers: 2048

Database Operations
------------------------------------------------
Reads : 9495
Writes : 4
Fetches: 5132412

Plan:
------------------------------------------------

PLAN SORT (JOIN (FACTURAS_VENTAS NATURAL, FAC_CLIENTE INDEX (IDX_FAC_CLIENTE), CLIENTES INDEX (RDB$PRIMARY85), FAC_CAJA INDEX (IDX_FAC_CAJA), CAJAS INDEX (RDB$PRIMARY77)))

Table Operations:
+--------------------------+-----------+-----------+-----------+-----------+-----------+
| Table Name | Index | Non-Index | Updates | Deletes | Inserts |
| | reads | reads | | | |
+--------------------------+-----------+-----------+-----------+-----------+-----------+
| FACTURAS_VENTAS| 0 | 1,287,862 | 0 | 0 | 0 |
| FAC_CAJA| 140,898 | 0 | 0 | 0 | 0 |
| FAC_CLIENTE| 140,898 | 0 | 0 | 0 | 0 |
| CAJAS| 140,898 | 0 | 0 | 0 | 0 |
| CLIENTES| 140,898 | 0 | 0 | 0 | 0 |
+--------------------------+-----------+-----------+-----------+-----------+-----------+


esta es la consulta

select
CAST(FACTURAS_VENTAS.FECHA AS DATE) AS FECHA_COMPRA,
CLIENTES.ID_CLIENTE,
CLIENTES.NOMBRE_1 ||' '|| CLIENTES.NOMBRE_2 ||' '|| CLIENTES.APELLIDO_1||' '|| CLIENTES.APELLIDO_2 AS CLIENTE_NOMBRE,
CLIENTES.CEDULA,
CLIENTES.CELULAR,
CLIENTES.TELEFONO,
CLIENTES.DIRECCION,
CLIENTES.EMAIL,
CAJAS.NUM_CAJA,
FACTURAS_VENTAS.CONSECUTIVO AS NUM_REGISTRO,
FACTURAS_VENTAS.MONTOIMPUESTO,
FACTURAS_VENTAS.MONTOSUBTOTAL,
FACTURAS_VENTAS.MONTODESCUENTO,
FACTURAS_VENTAS.MONTOSUBTOTALCONDESC,
FACTURAS_VENTAS.MONTOTOTAL,
FACTURAS_VENTAS.NUM_CUPONIMPRESORA,
FACTURAS_VENTAS.ID_FACTURA
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
WHERE CAST(FACTURAS_VENTAS.FECHA AS DATE) BETWEEN :FECHAINI AND :FECHAFIN
ORDER BY FAC_CLIENTE.ID_FACTURA DESCENDING


otra parte interesante es que porque firebird me muestra las bases de datos diferente plan sort ejemplo:


db de produccion:
PLAN SORT (JOIN (FAC_CAJA NATURAL, CAJAS INDEX (RDB$PRIMARY77), FACTURAS_VENTAS INDEX (RDB$PRIMARY45), FAC_CLIENTE INDEX (IDX_FAC_CLIENTE), CLIENTES INDEX (RDB$PRIMARY85)))

db de desarrollo
PLAN SORT (JOIN (FACTURAS_VENTAS NATURAL, FAC_CLIENTE INDEX (IDX_FAC_CLIENTE), CLIENTES INDEX (RDB$PRIMARY85), FAC_CAJA INDEX (IDX_FAC_CAJA), CAJAS INDEX (RDB$PRIMARY77)))




¿Podrían sacarme de la duda?

saludos cordial;


novato_erick

[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

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
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]

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.

[mamcx]

utilizar LEFT JOIN en lugar de INNER JOIN

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

[egostar]

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

Ciertamente....


http://www.delphienmovimiento.mx/wp/wp-content/uploads/2019/11/SQL_JOIN.png

[Casimiro Notevi]

Ciertamente....
http://www.delphienmovimiento.mx/wp/wp-content/uploads/2019/11/SQL_JOIN.png Muy bueno :D

[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.

[Casimiro Notevi]

Estoy moviéndome a Firebird 3 pero los componentes IBX no van bien con este motor...¿En qué no van bien?

[mamcx]

Ya hicieron la prueba ?
Si la llave foránea es "not null" funciona como un inner join.

Estas "abusando" del comportamiento interno, pero es semántica errónea. Al ver con LEFT uno asume que hay nulos. Eso es lo que dice el codigo.


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).


Aquí hay un error de apreciación de cómo funcionan los motores de BD. De entrada, hay que dudar MUCHISIMO que un motor mas nuevo sea menos eficiente que el mas viejo.

Ni te imaginas lo mucho que ha avanzado el tema de los RDBMS estos años. Y esos avances están siendo aplicados constantemente. Los RDBMs son MUY competitivos entre ellos.

---

No siempre elegir un indice es lo mejor.

Una de las tareas del query planer es determinar la manera menos costosa de ejecutar la consulta. Si este determina que usar el indice trae un mayor costo, lo DESCARTA.

Ahora, es posible que este se "equivoque?". Si. Y es posible que DIO LA CASUALIDAD que en la version vieja no cometa el error y en la nueva sí? Claro. Y eso significa que es mejor usar la vieja? NOOOOOOOO.

Porque es MUY probable que la nueva manifieste un ERROR de logica y/o diseño que la vieja, por casualidad NO VE.

Asi que:


Usa la version(estable) mas nueva del motor siempre. Eso te dara de "gratis" todo lo que lo nuevo traiga y este estara compilado con las mejores que hay en lo mas nuevo (como soporte a SIMD).
Ejecuta la consulta que quieres. Sea que use o no indices, si esta se ejecuta rapido entonces paras.
Si ves un problema, es porque hay un problema en el codigo! Que es:


Como le estas diciendo LEFT JOIN en vez de INNER JOIN le estas diciendo al query planer que es MAS COSTOSO UNIR AMBAS TABLAS. Inner join es MUCHO más eficiente de ejecutar. Lo se, estoy armando un lenguaje relacional y la implementación del inner join es pan comido, y cada otro es mas y mas complejo.

Estas metiendo una condición que hace ineficiente el uso de indices. Al 100% esta en los wheres o los group by, o lo anulas con un sort. Pero los wheres es mas común. Si necesitas ejecutar una expresión, indexa por esta: https://firebirdsql.org/rlsnotesh/indexing-expression.html

Una mala configuracion del motor pudiera ser el problema. Si el motor esta bajo precion (por estar configurado de forma negativa versus su entorno de hardware, ej: Falta de memoria) entonces puede verse forzado a optimizar por bajo recursos vs velocidad

Luego de mover mucho la BD (ej: un insert masivo, o grandes cambios en la estructura) las estadisticas se pueden desbalancear. Firebird usa estadisticas para ESTIMAR los costos. Si estas estan mal, puede pensar que la tabla es "pequeña" donde meter indices es bobada. Resetea: http://www.firebirdfaq.org/faq110/

Por ultimo, puedes decirle al motor que deje de fumarsela y que tu REALMENTE sabes mas: Fuerza al query planer a hacer lo que tu dices: http://www.firebirdfaq.org/faq224/


Pero en toda mi vida, usando como 8 rdbms diferentes solo he tenido que forzar a Mysql (que en versiones antes tenia el query planer mas imbecil del mundo. Todos los join era nested loops, que asco!) y aun asi, termine reescribiendo el query mejor. Siempre sigue estos pasos (https://henriquebastos.net/en_us/the-make-it-work-make-it-right-make-it-fast-misconception/):


Haz que funciones
Hazlo correcto
Hazlo rapido

Y en el caso de compiladores eficientes como SQL, los 2 primeros pasos logran el tercero.

[novato_erick]

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


/* 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;

[novato_erick]

Muy bueno :D

Ciertamente Si... :D:D:D:D:D

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

[novato_erick]

Descartado funcion CAST en Campo TimesTamp

La verdad como había mostrado dos resultado de respuesta a mi consulta en Base de datos de Desarrollo y la misma en producción obteniendo resultado lento en la de producción mas no en la de Desarrollo descarto totalmente el campo fecha como causa sugerido por nuestro compañero lbuelvas.

aun me encuentro en pruebas. Agregué el script solicitado por ustedes.

Saludos;

[Casimiro Notevi]

Otra cosa a tener en cuenta y que es más importante de lo que puedas pensar, usa dominios.
De siempre, en todos los campos, usar dominios.

CREATE DOMAIN DOMANO AS smallint NOT NULL;
CREATE DOMAIN DOMCODIGO AS integer;
CREATE DOMAIN DOMFECHA AS timestamp;
CREATE DOMAIN DOMHORA AS timestamp;
...

CREATE TABLE TBCABECERASALBARANESVENTAS
(
CODIGO DOMCODIGONONULO,
ANO DOMANO,
TIPODOCUMENTO DOMTIPODOCUMENTO NOT NULL,
SERIE DOMSERIE,
NUMERO DOMCODIGONONULO,
FECHA DOMFECHA,
HORA DOMHORA,

[lbuelvas]

Hola, me alegra que se haya dado la discusión. Gracias por las recomendaciones sobre el uso de Inner Join, voy a revisar una parte de un proyecto actual donde en las pruebas generé 1.00.000 de registros en dos tablas que están relacionadas 1:M.

Colocaré los resultados para continuar con el tema que se me hace interesante, por eso respondí tan pronto el compañero escribió su inquietud.

Revisaré mañana el script y espero que mis comentarios sean de ayuda.

Un abrazo para todos.

[novato_erick]

Otra cosa a tener en cuenta y que es más importante de lo que puedas pensar, usa dominios.
De siempre, en todos los campos, usar dominios.



Interesante Casimiro Notevi nunca me pareció relevante usar los dominios en fin será porque en la teoría normalmente se lo dejo al motor de base de datos
En este caso según usar dominion en Firebird no son los tipos de datos estándar sino los creados por el programador la para cubrir sus propias necesidades. al principio mi necesidad fue simplemente usar tipos de Datos que normalmente están en el motor.

En fin ahora quedé con la duda 'Perdonen mi ignorancia' del uso del dominio a la hora de realizar consulta anidadas de diferentes tablas.


Saludos a todos;

novato_erick

[novato_erick]

Hola, me alegra que se haya dado la discusión. Gracias por las recomendaciones sobre el uso de Inner Join, voy a revisar una parte de un proyecto actual donde en las pruebas generé 1.00.000 de registros en dos tablas que están relacionadas 1:M.

Colocaré los resultados para continuar con el tema que se me hace interesante, por eso respondí tan pronto el compañero escribió su inquietud.

Revisaré mañana el script y espero que mis comentarios sean de ayuda.

Un abrazo para todos.

Hola tengo en un archivo rar los mas de 1.2 millones de registros para hacer las pruebas el que quiera puede mandarme su email o me sugiera donde subirlo ya que pesa como 70mb para que este al alcance de otros para realizar pruebas.


Saludos;

[mamcx]

Puedes ponerlo en un link de dropbox o similar.

[lbuelvas]

Hola, puedes subirlo a mi servidor

http://45.77.164.42/HFS_Publica/

Se pueden subir archivos y bajarlos, pero no borrarlos, me avisas cuando lo puedo borrar.

[novato_erick]

Compañeros dejo el enlace para descarga:

https://drive.google.com/open?id=1uv2NHskc9sIF8W4-9C1_ATD9mOXaQ-1I

Saludos;

novato_erick

[novato_erick]

Hola, puedes subirlo a mi servidor


Hola Ivuelvas:

Deje en enlace y tambien lo subi a tu servidor.

Saludos y muchas gracias por tu colaboración;


novato_erick.

pd: el que administre este foro no estaría interesado que colaboremos en un servidor para este tipo de situaciones de subir info a los miembros?

[lbuelvas]

Estuve revisando tus metadatos y encuentro que no tienes llaves foráneas, entonces suspendí la revisión. Regálanos por favor una base de datos donde recortes lo que no estés interesado en que nosotros veamos y una porción de registros. Luego de recortar lo que es dispensable, haces Backup/Rrestore para disminuir el tamaño del archivo, lo comprimes y lo envías.

Lo otro que podemos hacer es hacer una sesión remota, yo tengo licencia de Ammyy y hablamos por Telegram o Whatsapp porque veo que tu base de datos tiene muchos registros.

Como estás en Panamá tenemos el mismo horario, yo estuve por allá hace unos 15 días en un Crucero por el Caribe que nos ganamos mi esposa y yo, estuvimos medio día por allá, es muy bonito.

[novato_erick]

Como estás en Panamá tenemos el mismo horario, yo estuve por allá hace unos 15 días en un Crucero por el Caribe que nos ganamos mi esposa y yo, estuvimos medio día por allá, es muy bonito.

lBuelvas. Si Panamá es muy Bonito para mi cada país tiene sus atractivo he ido a Colombia y me parece espectacular depende de las perspectiva y las mujeres bellas.

En referencia a las Llaves foráneas cuando tomé el proyecto también noté lo mismo en la Tabla FACTURAS_VENTAS con sus tablas hijas o dependiente en fin a pesar de no poseer dicha llave foraneas no ha perdido integridad en cuanto a las transacciones a cada tabla.

Casi son dos millones de registros.

en Fin realizé una recreación de los indixes de todas las tablas leyendo un poco este link https://firebird21.wordpress.com/tag/reindex/ la cual al ejecutarlo se corrigió el problema de consulta también cree un nuevo campo la cual guarda solamente el tipo de dato de FECHA sin la hora para evitar usar la función cast de Firebird en la consulta y tenias razón si mejoró notablemente.

También el consejo de:


mamcx
(Todos los join era nested loops, que asco!) y aun asi, termine reescribiendo el query mejor.


Definiitva mamcx no reescribí pero tan solo quitando la clausula order by y la función DESCENDING fue notable ejemplo:


ORDER BY FAC_CLIENTE.ID_FACTURA DESCENDING --quite aquí




Antes de quitar la función DESCENDING


Query Performance
------------------------------------------------
Prepare : 16 ms
Execute : 0 ms
Avg fetch time: 0 ms

Memory Usage
------------------------------------------------
Current: 98.30 MB
Max : 98.35 MB
Buffers: 2048

Database Operations
------------------------------------------------
Reads : 10167
Writes : 0
Fetches: 6145639

Plan:
------------------------------------------------
PLAN SORT (JOIN (FACTURAS_VENTAS NATURAL, FAC_CLIENTE INDEX (IDX_FAC_CLIENTE), CLIENTES INDEX (RDB$PRIMARY85), FAC_CAJA INDEX (IDX_FAC_CAJA), CAJAS INDEX (RDB$PRIMARY77)))

Table Operations:
+--------------------------+-----------+-----------+-----------+-----------+-----------+
| Table Name | Index | Non-Index | Updates | Deletes | Inserts |
| | reads | reads | | | |
+--------------------------+-----------+-----------+-----------+-----------+-----------+
| FACTURAS_VENTAS| 0 | 1,329,368 | 0 | 0 | 0 |
| FAC_CAJA| 182,404 | 0 | 0 | 0 | 0 |
| FAC_CLIENTE| 182,404 | 0 | 0 | 0 | 0 |
| CAJAS| 182,404 | 0 | 0 | 0 | 0 |
| CLIENTES| 182,404 | 0 | 0 | 0 | 0 |
+--------------------------+-----------+-----------+-----------+-----------+-----------+





Quitando la función DESCENDING


Query Performance
------------------------------------------------
Prepare : 0 ms
Execute : 0 ms
Avg fetch time: 0 ms

Memory Usage
------------------------------------------------
Current: 33.32 MB
Max : 98.35 MB
Buffers: 2048

Database Operations
------------------------------------------------
Reads : 8725
Writes : 0
Fetches: 2316311

Plan:
------------------------------------------------
PLAN JOIN (FACTURAS_VENTAS NATURAL, FAC_CLIENTE INDEX (IDX_FAC_CLIENTE), CLIENTES INDEX (RDB$PRIMARY85), FAC_CAJA INDEX (IDX_FAC_CAJA), CAJAS INDEX (RDB$PRIMARY77))

Table Operations:
+--------------------------+-----------+-----------+-----------+-----------+-----------+
| Table Name | Index | Non-Index | Updates | Deletes | Inserts |
| | reads | reads | | | |
+--------------------------+-----------+-----------+-----------+-----------+-----------+
| FACTURAS_VENTAS| 0 | 1,147,164 | 0 | 0 | 0 |
| FAC_CAJA| 200 | 0 | 0 | 0 | 0 |
| FAC_CLIENTE| 200 | 0 | 0 | 0 | 0 |
| CAJAS| 200 | 0 | 0 | 0 | 0 |
| CLIENTES| 200 | 0 | 0 | 0 | 0 |
+--------------------------+-----------+-----------+-----------+-----------+-----------+




Sus aportes son significativo y en resumen:
- recrear los indice ya que en la db habia 160 mil registros de diferencia entre mi tabla de desarrollo y la tabla de producción sin indexar eso también jugó un papel importante así que significativamente la recreación de los indice debe de realizarse cada cierto tiempo en fin no encontré por el momento alguna sugerencia en ese tema.

- usar la función Cast de firebird para convertir la fecha si afecta algo en el rendimiento de la consulta Mejor utilizar el tipo de Dato correspondiente a sólo Fecha

- El uso de las funciones hay que saber cuando hay que usarla en caso de consultas de búsquedas de mucha cantidad de registros. Esto juega un papel importante (Tener bien claro cada consulta).


Doy solucionado dejaré terminado este tema.

Saludos y Bendiciones a todos;


novato_erick

[mamcx]

recreación de los indice debe de realizarse cada cierto tiempo en fin no encontré por el momento alguna sugerencia en ese tema.


Solo es recrea si hay una inserción masiva (quizás, solo si es realmente grande y trastea enormente con los datos, triggers, etc. No es tan necesario si los registros son un "APPEND") . De resto es innecesario.

[novato_erick]

mancx:
Solo es recrea si hay una inserción masiva (quizás, solo si es realmente grande y trastea enormente con los datos, triggers, etc. No es tan necesario si los registros son un "APPEND") . De resto es innecesario.




^\||/ Gracias.

Saludos;

novato_erick