UNON QUERY WITH SUB QUERIES

[luisbo69]

I have this union query, it takes about 5 minutes to run, but if I separate each select, each one takes seconds, what am I doing wrong?

SELECT "sin asignar" AS type, [chrbanco], [chrmoneda], [dtmfechamovimiento], [fltimporte], val([chrnumerooperacion]) AS operacion, [chrtransaccion], 1
FROM bcp_all
WHERE (((bcp_all.chrBanco) In (SELECT [chrBanco] FROM [bcp_all] As Tmp GROUP BY [chrBanco],[chrMoneda],[dtmFechaMovimiento],[fltImporte] HAVING Count(*)>0 And [chrMoneda] = [bcp_all].[chrMoneda] And [dtmFechaMovimiento] = [bcp_all].[dtmFechaMovimiento] And [fltImporte] = -[bcp_all].[fltImporte])))

union all

SELECT "asignado" AS type, mid(pps.key,1,3) AS chrbanco, mid(pps.key,12,3) AS chrmoneda, datevalue(mid(pps.key,10,2) & "/" & mid(pps.key,8,2) & "/" & mid(pps.key,4,4)) AS dtmfechamovimiento, val(mid(pps.key,15,instr(pps.key,"-")-15)) AS fltimporte, val(mid(pps.key,instr(pps.key,"-")+1,len(pps.key)-instr(pps.key,"-"))) AS operacion, "C:" & cliente & "; F:" & trim(facturas) & "; O:" & trim(ordenes) & "; OBS:" & trim(observacion) AS chrtransaccion, [c]
FROM (([C:\Documents and Settings\pelbu0\Desktop\@PRODUCCION\DB_pps.mdb;pwd=kOM6UF1XTi8E].pps LEFT JOIN bcp_all ON pps.key=[bcp_all].[key]) LEFT JOIN sco_all ON pps.key=[sco_all].[key]) LEFT JOIN [C:\Documents and Settings\pelbu0\Desktop\@PRODUCCION\DB_pps.mdb;pwd=kOM6UF1XTi8E].observaciones ON pps.key=observaciones.key
WHERE IsNull([bcp_all].[key] & [sco_all].[key]) And Not Nz([c],False)

UNION ALL

SELECT "sin asignar" AS type, [chrbanco], [chrmoneda], [dtmfechamovimiento], [fltimporte], val([chrnumerooperacion]) AS operacion, [chrtransaccion], 1
FROM SCO_ALL
WHERE (((SCO_ALL.chrBanco) In (SELECT [chrBanco] FROM [SCO_ALL] As Tmp GROUP BY [chrBanco],[chrMoneda],[dtmFechaMovimiento],[fltImporte] HAVING Count(*)>0 And [chrMoneda] = [SCO_ALL].[chrMoneda] And [dtmFechaMovimiento] = [SCO_ALL].[dtmFechaMovimiento] And [fltImporte] = -[SCO_ALL].[fltImporte])))

ORDER BY [chrbanco], [chrmoneda], [dtmfechamovimiento], [fltimporte];

 

[lameid]

First thought is that the order by clause is slowing it up.

Try killing it and see how fast it runs. If ok, base a query off the union query and try your sort there and see if it is any faster.

Otherwise, likely Access is not compiling the Union query...

I would SAVE each select statement as a query, change to Datasheet view and save it (the query is now saved).

Next Union the queries toether...

Select query1.*
From query1

Union All

Select query2.*
From query2

Union All

Select query3.*
From query3

Hopefully that fixes it. If not, check that the datatypes are all the same in each select. I have found managing unions this way to be easier as I can view each select in the qbe but arguably the sql text is easier.

 

[luisbo69]

I have tried without the ORDER BY, and a union query calling the three subqueries. still taking 5 minutes...

 

[JerryKlmns]

If you append all records of the 3 queries to a temp table?

 

[luisbo69]

yes, it is an option too. thanks...
but I am still intrigued about the reason of this behavior

 

[lameid]

Hmmm...

Your middle select statement... Try linking to the table in the other database as opposed to including the full reference. Also I notice you are left joining to a table you select from in your last select statmente. Try moving the middle statement last. For curiosity sake, you might try removing each of the selects with the same table and see how the union performs with the other 2 tables and finally with just those 2.