Page 2 of 3
Re: OT Optimizar query MySql
Posted: Wed Mar 04, 2020 4:53 pm
by FranciscoA
nageswaragunupudi wrote:FranciscoA wrote:What do you think about the 1000 records specified?
1000 seems large. But let us give it a try.
Please also let me know if the `codicont` field is numeric or character and what is the size of the field.
Code: Select all | Expand
"CODICONT VARCHAR(20) NOT NULL DEFAULT '' COMMENT 'Codigo Contable'," +;
Re: OT Optimizar query MySql
Posted: Wed Mar 04, 2020 4:57 pm
by nageswaragunupudi
Not sure.
Can you still try and see?
Re: OT Optimizar query MySql
Posted: Wed Mar 04, 2020 5:03 pm
by nageswaragunupudi
You said this takes only 3 seconds
Code: Select all | Expand
SELECT A.codicont AS codigo, A.nombre AS nombre, B.codicont AS catalogo
FROM facturas A LEFT JOIN catalogo B
ON A.codicont = B.codicont ;
If so, you may try this
Code: Select all | Expand
SELECT * FROM
(
SELECT A.codicont AS codigo, A.nombre AS nombre, B.codicont AS catalogo
FROM facturas A LEFT JOIN catalogo B
ON A.codicont = B.codicont ;
) AB
WHERE catalogo IS NULL
Please try both approaches
Re: OT Optimizar query MySql
Posted: Wed Mar 04, 2020 5:06 pm
by FranciscoA
Let me make some changes. I'll back
Re: OT Optimizar query MySql
Posted: Wed Mar 04, 2020 5:23 pm
by FranciscoA
nageswaragunupudi wrote:You said this takes only 3 seconds
Code: Select all | Expand
SELECT A.codicont AS codigo, A.nombre AS nombre, B.codicont AS catalogo
FROM facturas A LEFT JOIN catalogo B
ON A.codicont = B.codicont ;
If so, you may try this
Code: Select all | Expand
SELECT * FROM
(
SELECT A.codicont AS codigo, A.nombre AS nombre, B.codicont AS catalogo
FROM facturas A LEFT JOIN catalogo B
ON A.codicont = B.codicont ;
) AB
WHERE catalogo IS NULL
Please try both approaches
The result is correct but... it took 5 minutes 5 seconds.
I'm using SqlYog for the tests.
Re: OT Optimizar query MySql
Posted: Wed Mar 04, 2020 5:30 pm
by FranciscoA
The demo server is now taking 1.56 to deliver the result.
Re: OT Optimizar query MySql
Posted: Wed Mar 04, 2020 5:37 pm
by nageswaragunupudi
FranciscoA wrote:The demo server is now taking 1.56 to deliver the result.
Are you referring to the FWH demo server? Now it took 90 seconds for me.
From your country it may be slower.
But how are you connecting the FWH demo server?
Re: OT Optimizar query MySql
Posted: Wed Mar 04, 2020 5:48 pm
by FranciscoA
nageswaragunupudi wrote:FranciscoA wrote:The demo server is now taking 1.56 to deliver the result.
Are you referring to the FWH demo server? Now it took 90 seconds for me.
From your country it may be slower.
But how are you connecting the FWH demo server?
Yes, FWH demo server.
I'am connecting with your code, from FW1709\samples.
Re: OT Optimizar query MySql
Posted: Wed Mar 04, 2020 6:02 pm
by nageswaragunupudi
Are you using 1709 or 1204?
Re: OT Optimizar query MySql
Posted: Wed Mar 04, 2020 6:08 pm
by FranciscoA
1709
Re: OT Optimizar query MySql
Posted: Wed Mar 04, 2020 6:12 pm
by nageswaragunupudi
Please try this approach on your tables and see.
Re: OT Optimizar query MySql
Posted: Thu Mar 05, 2020 12:52 am
by FranciscoA
The resulting string of 1000 records exceeds the maximum length for the Group_Concat () function.
I will continue investigating to see if I manage to overcome the 3 seconds that I have achieved so far.
Many thanks for your support.
Re: OT Optimizar query MySql
Posted: Fri Mar 06, 2020 8:40 pm
by xmanuel
Hola Francisco...
En principio no tengo intención de hacer HDO para xHarbour.
Re: OT Optimizar query MySql
Posted: Fri Mar 06, 2020 11:23 pm
by FranciscoA
Ok.
Gracias Manu.
Re: OT Optimizar query MySql
Posted: Sun May 03, 2020 3:45 pm
by FranciscoA
Hasta hoy pude retomar esto.
Continuando con las pruebas.
Code: Select all | Expand
DROP TABLE IF EXISTS pruebas ;
CREATE TABLE pruebas
SELECT * FROM facturas GROUP BY codicont ;
SELECT fechemis,td,numdoc,codicont,nombre FROM pruebas
WHERE codicont NOT IN (SELECT codicont FROM catalogo WHERE nv = 'S') ;
TOTAL TIEMPO: 3.132 Secs
Saludos.