Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 6 of 6
  1. #1
    New Coder
    Join Date
    Feb 2006
    Location
    Tennessee
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Optimization question (C#, MSSQL)

    I've coded a simple console app that will read in the fields from an Excel file and then use that data to create Sql update statements and pass them to the Sql server.

    Here's the relevant code:

    Code:
    String sConnectionString =
                    "Provider=Microsoft.Jet.OLEDB.4.0;" +
                    "Data Source=C:\\UPDATE.xls;" +
                    "Extended Properties=Excel 8.0;";
    
                OleDbConnection objConn = new OleDbConnection(sConnectionString);
                objConn.Open();
                OleDbCommand objCmdSelect = new OleDbCommand("SELECT * FROM [DATABASE$]", objConn);
                OleDbDataReader excelReader;
    
                excelReader = objCmdSelect.ExecuteReader();
                //Console.WriteLine("hit");
    
                Console.WriteLine("Connecting to SQL Database");
    
                String connStr = connection string;
    
                SqlConnection objSqlConn = new SqlConnection(connStr);
                objSqlConn.Open();
    
                Console.WriteLine("SQL Connection Open. Beginning SQL Database update.");
    
                String strSql = "";
                SqlCommand objCommand = new SqlCommand();
                objCommand.Connection = objSqlConn;
    
                int i = 0;
                while (excelReader.Read())
                {
    
                    strSql = "UPDATE ZipMasterBack SET " +
                        "Distrib='" + excelReader.GetValue(2) + "', " +
                        "Region='" + excelReader.GetValue(3) + "', " +
                        "txtDistribName='" + excelReader.GetValue(4) + "'" +
                        "WHERE txtZip_Code = '" + excelReader.GetValue(1) + "'";
    
                    objCommand.CommandText = strSql;
                    objCommand.ExecuteNonQuery();
    
                    if (i % 1000 == 0)
                    {
                        Console.WriteLine(i.ToString() + " Records Updated");
                    }
                    else if (i % 50 == 0)
                    {
                        Console.Write(".");
                    }
                    i++;
                }//end while
    This works well on a test file with 50 records. On the real thing, however, it's more than a little slow. It took 2 hours to update 54,500 records. Is there a more efficient method to accomplish this?

    Thanks
    ZJRT

    "All programmers are playwrights and all computers are lousy actors." - Anonymous

  • #2
    $object->toCD-R(LP); vinyl-junkie's Avatar
    Join Date
    Jun 2003
    Posts
    3,100
    Thanks
    2
    Thanked 23 Times in 23 Posts
    Well, for one thing, you don't select every field in the entire database if you're only going to update a few fields. Change your SQL statement here:

    Code:
    OleDbCommand objCmdSelect = new OleDbCommand("SELECT * FROM [DATABASE$]", objConn);
    so that it only selects the fields you're updating, which are located in the following statement:

    Code:
    strSql = "UPDATE ZipMasterBack SET " +
                        "Distrib='" + excelReader.GetValue(2) + "', " +
                        "Region='" + excelReader.GetValue(3) + "', " +
                        "txtDistribName='" + excelReader.GetValue(4) + "'" +
                        "WHERE txtZip_Code = '" + excelReader.GetValue(1) + "'";
    Music Around The World - Collecting tips, trade
    and want lists, album reviews, & more
    SNAP to it!

  • #3
    New Coder
    Join Date
    May 2007
    Location
    SF, CA
    Posts
    55
    Thanks
    0
    Thanked 4 Times in 4 Posts
    If you're using Sql Server you should check out using Bulk Insert. The only issue is with bulk inserts the actual file needs to be on the database machine (or you need to use a unc path and share the file), also you would need to use a csv or xml file.

    You can get more info:
    http://www.sqlteam.com/article/using...ad-a-text-file
    http://msdn2.microsoft.com/en-us/lib...5(SQL.90).aspx

    I'm sure you can probably find some more references by using google too.

    semper fi...

  • #4
    Senior Coder
    Join Date
    Jun 2002
    Location
    Zwolle, The Netherlands
    Posts
    1,120
    Thanks
    2
    Thanked 31 Times in 31 Posts
    what also might be a performance gain, is using a stored procedure (with passing the right arguments to it) instead of 50000+ update sql statements.

    Try to add some timing output in your program to see where the bottleneck is. I think reading the xls file is probably the problem here. perhaps you can read the file at once in a 2-dimensional array, then use that array to feed the stored procedure (or each separate sql statement) for each record to update.
    I am the luckiest man in the world

  • #5
    Regular Coder
    Join Date
    May 2007
    Location
    UK
    Posts
    180
    Thanks
    0
    Thanked 18 Times in 18 Posts
    If you are using .NET 2.0 then you can use the SqlBulkCopy class to write the data to SQL Server.

    First use your OleDbConnection object to copy the data from Excel into a DataTable, and then use SqlBulkCopy to copy the contents of the DataTable to SQL Server, e.g.,
    Code:
    OleDbDataAdapter da = new OleDbDataAdapter("select fields from [Database$]", objConn);
    DataTable dt = new DataTable();
    da.Fill(dt);
    
    SqlBulkCopy bcp = new SqlBulkCopy(connStr, SqlBulkCopyOptions.KeepIdentity);
    bcp.DestinationTableName = tableName;
    bcp.WriteToServer(dt);
    You won't need any SqlConnection or SqlCommand objects with this approach.
    Last edited by SouthwaterDave; 06-16-2007 at 07:23 PM.

  • #6
    New Coder
    Join Date
    Feb 2006
    Location
    Tennessee
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thank you all for the replies. I eventually got the script trimmed down to about 2 minutes by using the SqlBulkCopy class, as SouthwaterDave suggested, as well as adding some limited recursion to the method that actually does the updating. The thing eats memory for lunch, but since it only has to run once a week, it doesn't really matter.

    Thanks again for all the help.
    ZJRT

    "All programmers are playwrights and all computers are lousy actors." - Anonymous


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •