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
10  1270 
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)
Hi
So you think it is best for me to pass parameter in the loop to a stored procedure?
The problem is you are instantiating new parameters for your insert
command in a loop, instead of re-using them.
Do this once, at the top, when you first construct your command
object. Then, in your loop, just set their values instead of
recreating them, and forget about the .Clear method call you are
doing.
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)
Hi
So you think it is best for me to pass parameter in the loop to a stored procedure?
The problem is you are instantiating new parameters for your insert
command in a loop, instead of re-using them.
Do this once, at the top, when you first construct your command
object. Then, in your loop, just set their values instead of
recreating them, and forget about the .Clear method call you are
doing.
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: John Keeling |
last post by:
Dear all,
I tried the test program below. My interest is to examine timing
differences between insert vs. append & reverse for a list. My results
on my XP Python 2.3.4 are as follows:...
|
by: Curtis Stanford |
last post by:
I'm in a situation where I need to load an ASCII file into a database. No
sweat so far right? The records are indexed by date and I have a unique index
on date. The ASCII can overlap, meaning it...
|
by: Sezai YILMAZ |
last post by:
Hello
I need high throughput while inserting into PostgreSQL. Because of that I
did some PostgreSQL insert performance tests.
------------------------------------------------------------
--...
|
by: surya |
last post by:
i have a table name is HH table
it has two columns 'hhno' and hhname'
HH tabele
hhno hhname
100 suresh
101 baba
103 ram...
|
by: SaltyBoat |
last post by:
Needing to import and parse data from a large PDF file into an Access
2002 table: I start by converted the PDF file to a html file. Then
I read this html text file, line by line, into a table...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
| |
