Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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)

share|improve this question
    
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 '11 at 12:19
add comment

2 Answers

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?

share|improve this answer
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 '11 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 '11 at 15:13
add comment

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

share|improve this answer
    
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 '11 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 '11 at 14:38
    
This does not provide an answer to the question. To critique or request clarification from an author, leave a comment below their post. –  Derek Downey Aug 28 '12 at 15:09
    
You are correct, DTest. This was meant to be a comment. I never noticed that I wrote it as an answer. –  datagod Aug 29 '12 at 23:13
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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