OT Optimizar query MySql

[FranciscoA]

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

          "CODICONT   VARCHAR(20)   NOT NULL DEFAULT '' COMMENT 'Codigo Contable'," +;
 

[nageswaragunupudi]

Not sure.
Can you still try and see?

[nageswaragunupudi]

You said this takes only 3 seconds

Code: Select all

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

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

[FranciscoA]

Let me make some changes. I'll back

[FranciscoA]

You said this takes only 3 seconds

Code: Select all

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

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.

[FranciscoA]

The demo server is now taking 1.56 to deliver the result.

[nageswaragunupudi]

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?

[FranciscoA]

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.

[nageswaragunupudi]

Are you using 1709 or 1204?

[FranciscoA]

1709

[nageswaragunupudi]

Please try this approach on your tables and see.

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

[xmanuel]

Hola Francisco...

En principio no tengo intención de hacer HDO para xHarbour.

[FranciscoA]

Ok.
Gracias Manu.

[FranciscoA]

Hasta hoy pude retomar esto.
Continuando con las pruebas.

Code: Select all

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.