In my work, I need to obtain data from large databases with several highly normalized tables of at least 10 million records. I have been using INNER join queries with indexes to optimize the queries and reducing the execution speed. However, this does not work for large result sets. The time taken is simply too long. The query took more than 12 hours to run and have no sign of results.
One of my colleagues suggested to use temporary tables, and I followed the instructions here to create temporary tables, and broke the query to smaller data sets before joining them together. After several trial and error, I managed to get the optimal result set into a temporary table. The whole execution takes around 10 minutes to obtain the result set of 1 million records.
Then comes the next problem. It is to extract this result set to a csv. I have tried several ways, including running the query and extract it by copy and paste into Microsoft Excel. This process alone takes up to 3 hours to do it. I chanced upon this site and described the use of BCP to extract the result into csv.
It takes a while to realize that extracting data set using bcp is not possible. Some of the suggestions require the use of xp_cmdshell in the stored procedure. I am faced with another challenge that I do not have permissions to run xp_cmdshell. The next alternative is to write the results into a normal table and extract it using bcp. That works well, and the extraction takes less than 10 minutes to complete.
So all in all, I managed to extract the result set that I wanted in 20 minutes instead of waiting more than 12 hours and not getting any result. Hope that helps you.