Generating 100,000 files from SQL Server, Part 2.

I’ve been tasked to generate a bunch of files at work for an actuarial application to consume; without getting into the details of what the app does with the files (because I have no idea, frankly), I need to query SQL Server, generate a file for each “key” whereby each corresponding foreign key has approximately forty records, and one column of type decimal. Data looks like so in a SQL Server table:

1  1.119
1  2.225
1  3.330

37 more times
…

2 10.3
2 1.1
2 4.5

I’ve tried eight or ten methods up to now, and I’m going to cut to the chase and skip right to the fastest method that I have found, and then go back and jot down the others in subsequent blogs.  Check the first post to get an idea of some of the ideas that I have brainstormed to accomplish this task.  

The method to generate files the fastest up to this point? A c# console application. I could have written it in c++ or c as well of course, but c# seems just as good, so I chose this language. Also, because I may try to take this code and put in an SSIS script task as additional method, I’d be set since you can use c# and vb.net there as well.

Anyway, here’s the code. I’m still tweaking this so I’ll be working on it as well for improvements.  And, if you’re thinking that it could be much better than below, by all means send your thoughts to me while keeping the hate mail to yourself, even if you are 1) the “de-facto” expert among us on c# I/O, 2) you buddies with the guy that wrote the c# compiler 3) I happen to be treading on someone’s “sacred turf”, or 4) you just don’t like me or my blog.  Fair enough?  I think we’re gold then.

This thing generates approximately 25-35 files per second; the fastest SSIS package that I have built does about 12 files/second, and I have yet to find anything to beat this time, including bcp.  Come up with your own method and share with us if you like.

Thanks for reading!

Lee Everest, MS

--------------------------

He was wearing my Harvard tie. Can you believe it? My Harvard tie. Like oh, sure he went to Harvard

using System;
using System.IO;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data;

public class Program
{
  static StringBuilder sb = new StringBuilder();
  
  public static void Main() {          
   
  SqlConnection sqlConnection = null;
  int maxvalue=151000;
  string dbconn = "Application Name=SqlTest;Data Source=SQLVM1;Trusted_Connection=yes;
                          database=tempdb";
  using(sqlConnection = new SqlConnection(dbconn))
  {
      sqlConnection.Open();
      SqlParameter param = new SqlParameter("@id", SqlDbType.Int, 2);
      SqlDataReader reader;  
      
      using (SqlCommand cmd = new SqlCommand(null, sqlConnection))
      {
        param.Value = 1;
        cmd.CommandType = CommandType.Text;
        param.Direction = ParameterDirection.Input;
        cmd.Parameters.Add(param);            
   
        while ((int)param.Value<maxvalue)
        {   
           cmd.CommandText = "SELECT id FROM deleteTablexus WHERE idx = @id";
           reader = cmd.ExecuteReader();     
           while (reader.Read())
                sb.Append(reader[0].ToString() + "\r\n");
      
           SaveToFile(param.Value.ToString());               
           reader.Close();
           param.Value = (int)param.Value + 1;
        }
      } 
  }     
}
   public static void SaveToFile(string fileName)
   {
       System.IO.TextWriter w = new System.IO.StreamWriter(@"c:\temp\files\"+fileName+".dat");
       w.Write(sb.ToString());
       w.Flush();
       w.Close();
   }
}