Optimize SQL query

The problem is the subquery, there are too many fields to compare.

The first thing would be to perform an analysis of the information you should obtain. Normalize your table to correctly have the foreign keys and be able to join the two tables.

If the idea is to obtain from the two tables the maximum date of the records that match your information, you could obtain all the records that are equivalent with a INTERSECT . Obtaining that information you get the maximum record according to your date. This to get the date you get from the subquery . You just need to make a JOIN to the table of tmp_resumen1 to get the rest of the information

I think the problem is because of the subquery, but not because of the fields (which may also be the case), but because that subquery executes it for each record that has the table tmp_resumen1 .

What I would do is, on the one hand, execute the subquery to see what it pulls well. If you throw the problem right, it's the one I tell you, that executes it for each record in the table. Even so, crossing through so many fields at the same time is not a good policy. You would have to solve it using unique keys and crossing those keys.

In any case, to solve it as you have it mounted, I propose an alternative:

SELECT
'XXXXXXX' AS fakeField,
Tmp.f_mov,
a.c_linea,
a.c_almacen,
a.c_producto,
a.d_producto,
a.d_referencia_prov,
a.c_barra,
a.c_plu,
a.c_talla,
a.c_color_proveedor,
a.c_proveedor,
a.c_clasificacion,
a.c_categoria,
a.c_subcategoria,
a.c_segmento,
a.c_sector,
a.c_marca,
a.c_coleccion,
a.d_presentacion,
a.ubicacion,
a.f_creacion,
a.c_grupo,
a.c_ciudad
FROM tmp_resumen1 A 
INNER JOIN (
SELECT 
    'XXXXXXX' AS fakeField,
    NVL(max(f_mov), '') AS f_mov        
FROM   tmp_vtas_clientes AS b
WHERE  a.c_linea = b.c_linea
     AND a.c_almacen = b.c_almacen
     AND a.c_producto = b.c_referencia
     AND a.d_producto = b.d_referencia
     AND a.d_referencia_prov = b.d_referencia_prov
     AND a.c_barra = b.c_barra
     AND a.c_plu = b.c_plu
     AND a.c_talla = b.c_talla
     AND a.c_color_proveedor = b.c_color_proveedor
     AND a.c_proveedor = b.c_proveedor
     AND a.c_clasificacion = b.c_clasificacion
     AND a.c_categoria = b.c_categoria
     AND a.c_subcategoria = b.c_subcategoria
     AND a.c_segmento = b.c_segmento
     AND a.c_sector = b.c_sector
     AND a.c_marca = b.c_marca
     AND a.c_coleccion = b.c_coleccion
     AND a.d_presentacion = b.d_presentacion
     AND a.ubicacion = b.ubicacion
     AND a.f_creacion = b.f_creacion
     AND a.c_grupo = b.c_grupo
     AND a.c_ciudad = b.c_ciudad
)Tmp  ON a.fakeField = Tmp.fakeField
INTO TEMP tmp_resumen1_ventas_clientes WITH NO LOG;

You will see that what I have done is solve the queries separately and create a fictitious field to be able to hook the two results. I have not been able to prove it but I think you understand what I mean.

Let's see, after passing the query a little bit for the tidy I see that the problem is in the subquery that is too heavy.

Especially for blame for the functions you apply to the f_mov field . Think that the functions applied in this way must be applied to all the results of the query. In this case, since you use a subquery, it would be best to apply these functions to the f_mov field of outside of the subquery so that they are only executed once.

The next thing to optimize would be the where order of the subquery , unless the database engine that you use is Oracle as commented by the sstan partner, which seems to rearrange internally where to speed up queries. Think that all this string of constraints should be compared with each of the fields of the table tmp_vtas_clientes, so that his would be to put the conditions that are more accurate at the time of discarding the more at the beginning of the string of ANDs . That is, the article reference is obviously more specific than the store it is in, because in that query you will surely have many products in the same store a.c_almacen. Simply rearranging the AND by this question and you should save yourself a lot of time.