插入速度 [英] Insert Speed
问题描述
嗨
我在9列中插入7000行，慢11秒或者可以改进。我在网络上的服务器上插入SQL Server
谢谢
你如何插入行？你在使用存储过程吗？你是否使用交易
？你在执行插入之前是否正在进行查找？
有很多事情会影响性能。
" Chris" <一个******* @ discussions.microsoft.com>在消息中写道
news：31 ********************************** @ microsof t.com ...
我在9列中插入7000行，慢了11秒，或者可以改善
。我在网络服务器上插入SQL Server。
谢谢
Peter
这是我的鳕鱼
Dim adoConnstr As String =" Server = nysvrprod01\enterprise; Database = POS; Us er 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.SqlDataAdapte
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
Tr $ />
''daPOS.SelectCommand = cmdSe
daPOS.InsertCommand = cmdIns
adoConnPos.Open（
''daPOS.Fill（dsCSV，POS"
''MsgBox（adoConnPos.State
Dim dt As DataTabl
每个dt in dsCSV.Table
Dim rowCus As DataRo
For each rowCus in dt.Row
''Dim colCus As DataColum
''For each colCus in dt。专栏
''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
''<<<<<<<<<<<<<<<< <<<这就是我所关注的>>>>>>>>>>>>>>>>>>>>> ;>>>>>>>>>>>
cmdInst.Parameters.Add（新的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&quo t;））。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
cmdI nst.Parameters.Add（New SqlClient.SqlParameter（" @ POS_File_Date"，SqlDbType.DateTime，8，" POS_File_Date"））。Value = Date.Toda
''Nex
cmdInst.ExecuteNonQuery（
cmdInst.Parameters.Clear（
Nex
Nex
MsgBox（完成
adoConnPos.Close（
Catch EX As SqlClient.SqlExceptio
MessageBox.Show（ex.ToString
我建议将SQL注释移动到存储过程中？
对于要插入的每一行，它不仅会向服务器发送整个SQL
，而且服务器也必须解析并且在受到保护之前编译
命令。这是处理器密集型操作。一个
存储过程被解析并编译一次。
" Chris" <一个******* @ discussions.microsoft.com>在留言中写道
news：84 ********************************** @ microsof t.com ...彼得，
这是我的代码
Dim adoConnstr As String =
" Server = nysvrprod01\enterprise;数据库= POS;我们是
ID = sa;密码= 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）
尝试
''daPOS.SelectCommand = cmdSel
daPOS.InsertCommand = cmdInst
adoConnPos.Open（）
''daPOS.Fill（dsCSV，" POS"）
''MsgBox（adoConnPos.State ）
Dim dt As DataTable
每个dt in dsCSV.Tables
Dim rowCus As DataRow
For Each rowCus in dt.Rows
''Dim colCus As DataColumn
''for the 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 ）
''<<<<<<<<<<<<<<<<<<<这就是我在哪里关于>>>>>>>>>>>>>>>>> >>>>>>>>>>>
cmdInst.Parameters.Add（新
SqlClient.SqlParameter（" @ POS_Store_Number"，SqlDbType.Char，4，
" POS_Store_Number"））。Value = rowCus（0）cmdInst.Parameters.Add（New
SqlClient .SqlParameter（" @ POS_UPC_Number"，SqlD bType.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。添加（新
SqlClient.SqlParameter（" @ POS_File_Date"，SqlDbType.DateTime，8，
" POS_File_Date"））。Value = Date.Today
''下一页
cmdInst.ExecuteNonQuery（）
cmdInst.Parameters.Clear（）
下一页
下一页
MsgBox（完成）
adoConnPos.Close（）
抓住EX作为SqlClient.SqlException
MessageBox.Show（ex.ToString）
Hi
I am inserting 7000 rows in 9 columns, is 11 seconds slow or can that be improved. I am inserting to SQL Server on a server on the network
Thanks
How are you inserting the rows? Are you using stored procedures? Are you
using transactions? Are you doing lookups before performing the insert?
There are a lot of things that can affect performance.
"Chris" <an*******@discussions.microsoft.com> wrote in message
news:31**********************************@microsof t.com...Hi,
I am inserting 7000 rows in 9 columns, is 11 seconds slow or can that be improved. I am inserting to SQL Server on a server on the network.
Thanks
Peter
This is my cod
Dim adoConnstr As String = "Server=nysvrprod01\enterprise;Database=POS;Us er 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.SqlDataAdapte
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
Tr
''daPOS.SelectCommand = cmdSe
daPOS.InsertCommand = cmdIns
adoConnPos.Open(
'' daPOS.Fill(dsCSV, "POS"
'' MsgBox(adoConnPos.State
Dim dt As DataTabl
For Each dt In dsCSV.Table
Dim rowCus As DataRo
For Each rowCus In dt.Row
''Dim colCus As DataColum
''For Each colCus In dt.Column
''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.Toda
''Nex
cmdInst.ExecuteNonQuery(
cmdInst.Parameters.Clear(
Nex
Nex
MsgBox("Done"
adoConnPos.Close(
Catch EX As SqlClient.SqlExceptio
MessageBox.Show(ex.ToString
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" <an*******@discussions.microsoft.com> wrote in message
news:84**********************************@microsof t.com...Peter,
This is my code
Dim adoConnstr As String = "Server=nysvrprod01\enterprise;Database=POS;Us er
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
''Next
cmdInst.ExecuteNonQuery()
cmdInst.Parameters.Clear()
Next
Next
MsgBox("Done")
adoConnPos.Close()
Catch EX As SqlClient.SqlException
MessageBox.Show(ex.ToString)
这篇关于插入速度的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
