FiveTech Support Forums

//Tarda 3 min para 60 Mil registros (demasiado tiempo) 
//This query takes 3 minutes on a table with 60,000 records. Too much time.

SELECT a.codicont, a.nombre
FROM facturas a
WHERE NOT EXISTS ( SELECT b.codicont FROM catalogo b
                   WHERE b.codicont = a.codicont ) ;
                    

//Esta tarda 3 min para 60 Mil registros (demasiado tiempo) 
//This query takes 3 minutes on a table with 60,000 records. Too much time.

SELECT codicont, nombre 
FROM facturas 
WHERE codicont NOT IN (SELECT codicont FROM catalogo) ;  


//Esta tarda 5 min para 60 Mil registros (demasiado tiempo) 
//This query takes 5 minutes on a table with 60,000 records. Too much time.

SELECT t1.codicont, t1.nombre
  FROM facturas t1
  LEFT JOIN catalogo t2
    ON t2.codicont = t1.codicont
 WHERE t2.codicont IS NULL ;         
 

//Esta se toma3 segundos, pero trae todos los datos, y solo queremos los nulos. (los que no existen en tabla catalogo)
//This query takes 3 seconds on a table with 60,000 records, but we wants null records only.

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 ;

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 ;

#include "fivewin.ch"

function Main()

   local oCn   := FW_DemoDB()
   local oRs, cSql, cCodes, nSecs

   ? cCodes := oCn:QueryResult( "SELECT GROUP_CONCAT( code ) AS codes FROM states" )
   ? cCodes := "( '" + StrTran( cCodes, ",", "','" ) + "' )"

   nSecs := SECONDS()
   oRs   := oCn:RowSet( "SELECT ID,FIRST,STATE FROM custbig WHERE STATE NOT IN " + cCodes )
   nSecs := SECONDS() - nSecs
   XBROWSER oRs TITLE "Read in " + cValToChar( nSecs ) + " seconds"

   oCn:Close()

return nil

DROP TABLE IF EXISTS pruebas ;

CREATE TABLE IF NOT EXISTS pruebas LIKE facturas ;

INSERT INTO pruebas SELECT * FROM facturas GROUP BY codicont ;

SELECT fechemis,td,numdoc,codicont,nombre FROM pruebas 
WHERE codicont NOT IN (SELECT codicont FROM catalogo) ;

WHERE codicont NOT IN (SELECT codicont FROM catalogo)