Solved

Fastest way to retrieve a million records and write to a file

Posted on 2011-02-17
8
920 Views
Last Modified: 2012-06-21
What is the most efficient way to retrive a million records from a Sybase database stored proc and write the result to a file via C# 2010?

A sample code snippet / example would be great!!!
0
Comment
Question by:2ooth
8 Comments
 
LVL 10

Expert Comment

by:John Claes
ID: 34917878
using (SqlConnection sqlConnection= new SqlConnection(connectionString))
{
     sqlConnection= ();
     SqlCommand sqlCommand = new SqlCommand("YourProcedureName");
     sqlCommand.CommandType = CommandType.StoredProcedure;
     sqlCommand.Parameters.Add(new SqlParameter("@parameterName", SqlDbType.ParameterType)).Value = ParameterValue;
                using (SqlDataReader sqlDataReader = sqlCommand.ExecuteReader())
                {
                    if (sqlDataReader.HasRows == true)
                    {
                  System.IO.FileStream fileStream = new FileStream("c:\\test.txt",FileMode.CreateNew);
                        while (sqlDataReader.Read())
                        {
                      
                            fileStream.Write(sqlDataReader["Field1"]);
                            fileStream.Write(";");
                            fileStream.Write(sqlDataReader["Field2"]);
                            fileStream.Write(";");
                            fileStream.Write(sqlDataReader["Field3"]);
                            fileStream.WriteLine();


                        }
                  fileStream.Close();
                    }
                }
}
0
 

Author Comment

by:2ooth
ID: 34917978
Thank you for your response.

as mentioned I am retrieving data from a sybase database so i cannot use SQL classes. But i am dependent on OLEDB class. Also, i am curious to know how using (SqlDataReader sqlDataReader = sqlCommand.ExecuteReader()) will be efficient in retrieving a million rows?
0
 
LVL 26

Accepted Solution

by:
wilcoxon earned 500 total points
ID: 34922734
I don't think there is a really efficient way to do so in C# (or any other high-level language).  If all the data you need is in a single table, I'd suggest using bcp directly.  If you need to run a specific query, I'd suggest running it via sqsh with -mbcp and redirecting to a file.  You can always do a system call from C# to run bcp or sqsh (I assume it supports output redirection but I don't know much C#).
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 10

Expert Comment

by:John Claes
ID: 34924178
I've not used SyBase myself but I'm sure you can change all the sql objects into Sybase objects so you can work with Sybase

For this hugh set of records I suggest indeed to work upon a other level (DB).

0
 
LVL 15

Expert Comment

by:Aaron Shilo
ID: 34932821
hi

you should use BCP out  utill this is the best and fastest way to extract data from sybase.
0
 
LVL 26

Expert Comment

by:wilcoxon
ID: 35369614
As 2ooth requested in my request for attention, my recommendation...

3) I would recommend awarding full points to wilcoxon.  wilcoxon suggested using bcp a full 11 hours before ashilo suggested using it.  wilcoxon also suggested sqsh as a way to work around limitations in bcp if needed (original question did not give enough details to determine if bcp was sufficient).
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Upgrading my SSIS package in VS 2012 6 60
backup and restore 21 30
Code works but breaks when I add one section 4 20
SQL Syntax Grouping Sum question 7 27
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Performance in games development is paramount: every microsecond counts to be able to do everything in less than 33ms (aiming at 16ms). C# foreach statement is one of the worst performance killers, and here I explain why.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question