Solved

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

Posted on 2011-02-17
8
925 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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
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

Secure Your Active Directory - April 20, 2017

Active Directory plays a critical role in your company’s IT infrastructure and keeping it secure in today’s hacker-infested world is a must.
Microsoft published 300+ pages of guidance, but who has the time, money, and resources to implement? Register now to find an easier way.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
point in time restore in SQL server 26 46
Logical Operator should return Integer value in SSIS 9 39
SQL- GROUP BY 4 25
Need to find substring in SQL 3 13
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

730 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