I would suggest moving the SQL comment into a stored procedure?
For each row that you want to insert, not only does it send the entire SQL
commend to the server, but the server also has to parse and compile the
command before it is secured. This is a processor intensive operation. A
stored procedure is parsed and compiled once.
Chris said:
Peter,
This is my code
Dim adoConnstr As String = "Server=nysvrprod01\enterprise;Database=POS;User
ID=sa;Password=0cram;Trusted_Connection=False"
Dim adoConnPos As New SqlClient.SqlConnection(adoConnstr)
'Dim instCmd As String = "Insert into POS (COL1,COL2) VALUES (@COL1,@COL2)"
Dim instCmd As String = "INSERT INTO Pos_Data_File
(POS_Store_Number, POS_UPC_Number," & _
" POS_Issue_Number, POS_Quantity_Sold, POS_Sale_Date,
POS_Cover_Price, POS_Issue_Year," & _
" POS_Vendor_Number, POS_Transaction_Date, POS_File_Date) VALUES
(@Pos_Store_Number, @POS_UPC_Number," & _
" @POS_Issue_Number, @POS_Quantity_Sold, @POS_Sale_Date,
@POS_Cover_Price, @POS_Issue_Year," & _
" @POS_Vendor_Number, @POS_Transaction_Date, @POS_File_Date)"
Dim daPOS As New SqlClient.SqlDataAdapter
Dim cmdInst As New SqlClient.SqlCommand(instCmd, adoConnPos)
'Dim selCmd As String = "select * from pos ORDER BY COL1"
'Dim cmdSel As New SqlClient.SqlCommand(selCmd, adoConnPos)
Try
'daPOS.SelectCommand = cmdSel
daPOS.InsertCommand = cmdInst
adoConnPos.Open()
' daPOS.Fill(dsCSV, "POS")
' MsgBox(adoConnPos.State)
Dim dt As DataTable
For Each dt In dsCSV.Tables
Dim rowCus As DataRow
For Each rowCus In dt.Rows
'Dim colCus As DataColumn
'For Each colCus In dt.Columns
'MsgBox(rowCus(colCus))
' MsgBox(rowCus(0) & " " & rowCus(1))
'cmdInst.Parameters.Add(New SqlClient.SqlParameter _
'("@COL1", SqlDbType.Char, 20)).Value = rowCus(0)
'cmdInst.Parameters.Add(New SqlClient.SqlParameter _
'("@COL2", SqlDbType.Char, 20)).Value = rowCus(1)
'<<<<<<<<<<<<<<<<<THIS IS WHERE I AM CONCERNED ABOUT
cmdInst.Parameters.Add(New
SqlClient.SqlParameter("@POS_Store_Number", SqlDbType.Char, 4,
"POS_Store_Number")).Value = rowCus(0)
cmdInst.Parameters.Add(New
SqlClient.SqlParameter("@POS_UPC_Number", SqlDbType.VarChar, 15,
"POS_UPC_Number")).Value = rowCus(1)
cmdInst.Parameters.Add(New
SqlClient.SqlParameter("@POS_Issue_Number", SqlDbType.Char, 3,
"POS_Issue_Number")).Value = rowCus(2)
cmdInst.Parameters.Add(New
SqlClient.SqlParameter("@POS_Quantity_Sold", SqlDbType.VarChar, 10,
"POS_Quantity_Sold")).Value = rowCus(3)
cmdInst.Parameters.Add(New
SqlClient.SqlParameter("@POS_Sale_Date", SqlDbType.NChar, 8,
"POS_Sale_Date")).Value = rowCus(4)
cmdInst.Parameters.Add(New
SqlClient.SqlParameter("@POS_Cover_Price", SqlDbType.NChar, 10,
"POS_Cover_Price")).Value = rowCus(5)
cmdInst.Parameters.Add(New
SqlClient.SqlParameter("@POS_Issue_Year", SqlDbType.Char, 4,
"POS_Issue_Year")).Value = rowCus(6)
cmdInst.Parameters.Add(New
SqlClient.SqlParameter("@POS_Vendor_Number", SqlDbType.NChar, 9,
"POS_Vendor_Number")).Value = rowCus(7)
cmdInst.Parameters.Add(New
SqlClient.SqlParameter("@POS_Transaction_Date", SqlDbType.NChar, 8,
"POS_Transaction_Date")).Value = rowCus(8)
cmdInst.Parameters.Add(New
SqlClient.SqlParameter("@POS_File_Date", SqlDbType.DateTime, 8,
"POS_File_Date")).Value = Date.Today
