speed up sql INSERTs

Posted by sean717 on Stack Overflow See other posts from Stack Overflow or by sean717
Published on 2010-05-31T17:15:23Z Indexed on 2010/05/31 17:23 UTC
Read the original article Hit count: 180

Filed under:

I have the following method to insert millions of rows of data into a table (I use SQL 2008) and it seems slow, is there any way to speed up INSERTs?

Here is the code snippet - I use MS enterprise library

        public void InsertHistoricData(List<DataRow> dataRowList)
        {
            string sql = string.Format( @"INSERT INTO [MyTable] ([Date],[Open],[High],[Low],[Close],[Volumn])
                VALUES( @DateVal, @OpenVal, @High, @Low, @CloseVal, @Volumn )");

            DbCommand dbCommand = VictoriaDB.GetSqlStringCommand( sql );
            DB.AddInParameter(dbCommand, "DateVal", DbType.Date);
            DB.AddInParameter(dbCommand, "OpenVal", DbType.Currency);
            DB.AddInParameter(dbCommand, "High", DbType.Currency );
            DB.AddInParameter(dbCommand, "Low", DbType.Currency);
            DB.AddInParameter(dbCommand, "CloseVal", DbType.Currency);
            DB.AddInParameter(dbCommand, "Volumn", DbType.Int32);

            foreach (NasdaqHistoricDataRow dataRow in dataRowList)
            {
                DB.SetParameterValue( dbCommand, "DateVal", dataRow.Date );
                DB.SetParameterValue( dbCommand, "OpenVal", dataRow.Open );
                DB.SetParameterValue( dbCommand, "High", dataRow.High );
                DB.SetParameterValue( dbCommand, "Low", dataRow.Low );
                DB.SetParameterValue( dbCommand, "CloseVal", dataRow.Close );
                DB.SetParameterValue( dbCommand, "Volumn", dataRow.Volumn );

                DB.ExecuteNonQuery( dbCommand );
            }
        }

© Stack Overflow or respective owner

Related posts about sql-server-2008