I have searched but could not find anything similar. If I just run the query, I can get the result pretty quickly (~5 mins); but after I saved the query to export as CSV, the exporting part is extremely slow (>30mins).
The result only has 3 columns and approximately 30 records; I can easily copy and paste the query result I get. However, I would like to understand the strange behaviour. It's a huge database of about 7GB, that's why it's split into 4 different files. I am not allowed to use other software such as POSTgreSQL or mySQL at work. Here is my code anyway (modified the variable names):
SELECT var1, var2, count(*)
FROM (
SELECT var1,var2
FROM [XXX10_2006-2010] INNER JOIN [XXXLong_2006-2010]
ON [XXX10_2006-2010].ID = [XXXLong_2006-2010].id
WHERE [XXXLong_2006-2010].var4 = 'hamburger'
AND [XXXLong_2006-2010].cat1 = 'c'
AND [XXXLong_2006-2010].cat2 BETWEEN 0 AND 96
AND [XXX10_2006-2010].var5 = 'eggs'
UNION all
SELECT var1,var2
FROM [XXX10data_2001-2005] INNER JOIN [XXXLong_2001-2005]
ON [XXX10data_2001-2005].ID = [XXXLong_2001-2005].id
WHERE [XXXLong_2001-2005].var4 = 'hamburger'
AND [XXXLong_2001-2005].cat1 = 'c'
AND [XXXLong_2001-2005].cat2 BETWEEN 0 AND 96
AND [XXX10data_2001-2005].var5 = 'eggs'
UNION all
SELECT var1,var2
FROM [XXX9_data_1997-2000] INNER JOIN [XXXLong_1997-2000]
ON [XXX9_data_1997-2000].ID = [XXXLong_1997-2000].id
WHERE [XXXLong_1997-2000].var4 = 'hamburger'
AND ([XXXLong_1997-2000].cat1 ='1' AND [XXXLong_1997-2000].cat2 BETWEEN 40 AND 99)
OR ([XXXLong_1997-2000].cat1 ='2' AND [XXXLong_1997-2000].cat2 BETWEEN 0 AND 39)
AND [XXX9_data_1997-2000].var5 = 'eggs'
UNION all
SELECT var1,var2
FROM [XXX9_data_1993-1996] INNER JOIN [XXXLong_1993-1996]
ON [XXX9_data_1993-1996].ID = [XXXLong_1993-1996].id
WHERE [XXXLong_1993-1996].var4 = 'hamburger'
AND ([XXXLong_1993-1996].cat1 ='1' AND [XXXLong_1993-1996].cat2 BETWEEN 40 AND 99)
OR ([XXXLong_1993-1996].cat1 ='2' AND [XXXLong_1993-1996].cat2 BETWEEN 0 AND 39)
AND [XXX9_data_1993-1996].var5 = 'eggs'
)
GROUP BY var1, var2
EDIT: In case this little bit of detail helps: It takes slightly more than 10 mins for the export text wizard to show up, then the rest of the process takes another 20 mins or so. (sometimes much longer though)
