none
How to retrieve large amount ( more than 50,000) of data records from a sql server 2005 database.

    Question

  •  

    Hello,

     

    I faced a problem while retrieveing the data from database ( sql server 2005). Previously i wont get a problem while retrieving the data from DB. Because i used to retrieve considerably small amount of data ( below 1,000). But now i am going to retrieve large amount of data ( more than 50,000) records. It is taking very large amount of time ( more than 30 mins) some times timed out. After retrieving all records, i am preparing CSV ( comma seperated value) file with that data.

     

    My question is, How to retrieve large amount of data in less amount of time? Is there any way in ADO.NET ? My actual requirement is, I have to retrieve large amount of data and prepare CSV file.

     

    Can anyone help me in this?

     

    Thanks,

    Ashok.

    Tuesday, December 18, 2007 5:52 AM

All replies

  • You might consider using SQL Server Integration Services to transfer data from SQL Server into CSV directly. I believe it will be much faster and you do not need to retrieve data to the client application. It is not the case for you, then you need to check query execution plan to see if you could improve performance of your SQL query building some indexes.

     

    Tuesday, December 18, 2007 11:28 AM
    Moderator
  • You should identify where the problem is before you try to fix anything.  What's the part of your process that's taking the longest time?  Is it in the execution of the query?  If so, then analyzing the query will help.  Is it in reading the data from the database once the query executes?  If so, then you should be looking at issues like network latency and memory utilization.  Is it in preparing the CSV file?  Is it in writing the CSV file?

     

    My first guess, given the problem you're describing, is that reading the entire result set into memory before preparing the CSV file is creating a low-memory condition, and you're losing time to paging.  If I had to dump 50K rows into a CSV file, I'd keep memory use to a minimum by reading through the result set with a SqlDataReader and using a FileStream to write each row as I read it.  But I can't say for sure that this will fix your problem.

     

    I can say, however, that there's no way reading through a 50K row result set and dumping it to a file should be taking 30 minutes.  Unless the rows are insanely large, it shouldn't be taking 30 seconds.

    Tuesday, December 18, 2007 7:03 PM
  • the fastest way to export data to CSV is to use bcp utility.

    If you need to export regulary, build Integration Services package, install it on Integration Services, and create shedule for it.

     

    For your situation, 30 mins is fantasticaly slow. It seems, that your select operation at least isn't optimal. If it's true, optimization of the select operation - is the only way to speed up export.

    Thursday, December 20, 2007 8:58 AM