Excel Uploading Speed

[chaw12]

Hi Members, Currently, i am uploading a file from excel going to Sql Server with the help of VB.net as my front end. My approach is by making use of a stored procedure.So, from excel file i make use of a Datatable, where I loop in the excel file and stored every rows and columns to Datatable then once all the rows and columns are collected inside the Datatable, I will now pass the Datatable going to stored procedure which resides on the server. my problem is, it is taking a lot of minutes if i am going to upload an excel file containing 10,000 rows. can someone give me an insight on how could i make it a lot faster. Below would be my sample code. Dim OutletUploadRec As New Tbl_OutletUpload Private sub Form1 Dim myTmpDT as new datatable For i as integer=4 to excel.usedrange.rows.countWith myTmpDT .Rows.Add( _ Trim(xlWS.Range("A" & iDx).Value), _ Replace(xlWS.Range("B" & iDx).Value, "", "0"), _ Trim(xlWS.Range("C" & iDx).Value), _ Trim(xlWS.Range("D" & iDx).Text), _ Trim(xlWS.Range("E" & iDx).Text)End withnext OutletUploadRec.AppendFiledataServerSide(myTmpDT) end sub Public Class Tbl_OutletUpload Function AppendFiledataServerSide(ByRef DT As DataTable) As DataTable If myConn.State = ConnectionState.Closed Then myConn.Open() Dim myCommand As New SqlCommand("[dbo].[AppendTblFileDatas]", myConn) With myCommand .CommandType = CommandType.StoredProcedure .Parameters.AddWithValue("@TblFileData", DT) .ExecuteNonQuery() End With myCommand.Dispose() myConn.Close() Return DT End Function end Class i assigned OutletUploadRec to inherit the functions i created inside the Class Tbl_OutletUpload. AppendFileDataServerSide

[justsomeguy]

Several minutes for 10,000 rows would be pretty excessive, but it's hard to suggest how to speed it up without knowing what is taking the time. I would suggest adding some timing variables so that you can print out how long the various things take to identify what is taking the most time. Make sure to time both the actual loop and adding the row separately, either of those may be taking most of the time. It may take a while to iterate through the rows in an Excel file, or maybe adding rows to the datatable is taking a while. Appending to the other file should also be timed.

[chaw12]

Yes, your right 10,000 rows taking that much time to process is actually killling me. Specially that the system has to process sometimes up to 500,000 rows with 52 columns. I actually don't know what kind of approach i will make. Ill try your suggestion and thanks for your reply.