1

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)

1
  • In case it helps, the time for the export text wizard to show up is almost instant if I exclude count(*) and group by. The rest of it is pretty quick too...
    – King
    Nov 14, 2011 at 12:19

2 Answers 2

1

Off the top of my head I might think it to be related having 4 different DB files and large ones at that (not sure why that would be). Can you easily scope the query to hit 1 vs 4 DB files to see if the preformance is increased?

2
  • 1
    Do you mean querying each file separately then combining the data by myself? I'll try that later to see how long that takes. Thanks for the suggestion.
    – King
    Oct 24, 2011 at 16:02
  • I'm even more confused now. The export is faster than simply running the query when I just do it on 1 DB...
    – King
    Oct 27, 2011 at 15:13
0

Sounds like you have an I/O issue. Did you try saving the result CSV locally v.s. a network location?

3
  • Thanks for answering. All the files and the databases are currently on my own computer not connected to any network files. Is that what you mean?
    – King
    Oct 24, 2011 at 13:49
  • King, yes that is what I meant. There is something definitely wrong with your computer, either at the OS level or at the drive level. This does not sound like a SQL Server problem at all. I suggest you start making backups and look into the matter asap.
    – datagod
    Oct 27, 2011 at 14:38
  • You are correct, DTest. This was meant to be a comment. I never noticed that I wrote it as an answer.
    – datagod
    Aug 29, 2012 at 23:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.