Insert Speed

**Peter Rilling** · Jul 21 '05, 01:03 PM

Re: Insert Speed

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" <anonymous@disc ussions.microso ft.com> wrote in message
news:31FA5F60-3867-477C-AE02-4F7F55F2F996@mi crosoft.com...[color=blue]
> Hi,
> I am inserting 7000 rows in 9 columns, is 11 seconds slow or can that be[/color]
improved. I am inserting to SQL Server on a server on the network.[color=blue]
>
> Thanks[/color]

**Chris** · Jul 21 '05, 01:03 PM

Re: Insert Speed

Peter
This is my cod

Dim adoConnstr As String = "Server=nysvrpr od01\enterprise ;Database=POS;U ser ID=sa;Password= 0cram;Trusted_C onnection=False
Dim adoConnPos As New SqlClient.SqlCo nnection(adoCon nstr
'Dim instCmd As String = "Insert into POS (COL1,COL2) VALUES (@COL1,@COL2)
Dim instCmd As String = "INSERT INTO Pos_Data_File (POS_Store_Numb er, POS_UPC_Number, " &
" POS_Issue_Numbe r, POS_Quantity_So ld, POS_Sale_Date, POS_Cover_Price , POS_Issue_Year, " &
" POS_Vendor_Numb er, POS_Transaction _Date, POS_File_Date) VALUES (@Pos_Store_Num ber, @POS_UPC_Number ," &
" @POS_Issue_Numb er, @POS_Quantity_S old, @POS_Sale_Date, @POS_Cover_Pric e, @POS_Issue_Year ," &
" @POS_Vendor_Num ber, @POS_Transactio n_Date, @POS_File_Date)

Dim daPOS As New SqlClient.SqlDa taAdapte
Dim cmdInst As New SqlClient.SqlCo mmand(instCmd, adoConnPos

'Dim selCmd As String = "select * from pos ORDER BY COL1
'Dim cmdSel As New SqlClient.SqlCo mmand(selCmd, adoConnPos

Tr

'daPOS.SelectCo mmand = cmdSe
daPOS.InsertCom mand = cmdIns

adoConnPos.Open (

' daPOS.Fill(dsCS V, "POS"

' MsgBox(adoConnP os.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.Parame ters.Add(New SqlClient.SqlPa rameter
'("@COL1", SqlDbType.Char, 20)).Value = rowCus(0

'cmdInst.Parame ters.Add(New SqlClient.SqlPa rameter
'("@COL2", SqlDbType.Char, 20)).Value = rowCus(1

'<<<<<<<<<<<<<< <<<THIS IS WHERE I AM CONCERNED ABOUT >>>>>>>>>>>>>>> >>>>>>>>>>>>> >

cmdInst.Paramet ers.Add(New SqlClient.SqlPa rameter("@POS_S tore_Number", SqlDbType.Char, 4, "POS_Store_Numb er")).Value = rowCus(0
cmdInst.Paramet ers.Add(New SqlClient.SqlPa rameter("@POS_U PC_Number", SqlDbType.VarCh ar, 15, "POS_UPC_Number ")).Value = rowCus(1
cmdInst.Paramet ers.Add(New SqlClient.SqlPa rameter("@POS_I ssue_Number", SqlDbType.Char, 3, "POS_Issue_Numb er")).Value = rowCus(2
cmdInst.Paramet ers.Add(New SqlClient.SqlPa rameter("@POS_Q uantity_Sold", SqlDbType.VarCh ar, 10, "POS_Quantity_S old")).Value = rowCus(3
cmdInst.Paramet ers.Add(New SqlClient.SqlPa rameter("@POS_S ale_Date", SqlDbType.NChar , 8, "POS_Sale_Date" )).Value = rowCus(4
cmdInst.Paramet ers.Add(New SqlClient.SqlPa rameter("@POS_C over_Price", SqlDbType.NChar , 10, "POS_Cover_Pric e")).Value = rowCus(5
cmdInst.Paramet ers.Add(New SqlClient.SqlPa rameter("@POS_I ssue_Year", SqlDbType.Char, 4, "POS_Issue_Year ")).Value = rowCus(6
cmdInst.Paramet ers.Add(New SqlClient.SqlPa rameter("@POS_V endor_Number", SqlDbType.NChar , 9, "POS_Vendor_Num ber")).Value = rowCus(7
cmdInst.Paramet ers.Add(New SqlClient.SqlPa rameter("@POS_T ransaction_Date ", SqlDbType.NChar , 8, "POS_Transactio n_Date")).Value = rowCus(8
cmdInst.Paramet ers.Add(New SqlClient.SqlPa rameter("@POS_F ile_Date", SqlDbType.DateT ime, 8, "POS_File_Date" )).Value = Date.Toda

'Nex

cmdInst.Execute NonQuery(

cmdInst.Paramet ers.Clear(

Nex

Nex

MsgBox("Done"

adoConnPos.Clos e(

Catch EX As SqlClient.SqlEx ceptio

MessageBox.Show (ex.ToString

**Peter Rilling** · Jul 21 '05, 01:03 PM

Re: Insert Speed

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" <anonymous@disc ussions.microso ft.com> wrote in message
news:84313817-FDA4-4933-8577-EDFBE0A09540@mi crosoft.com...[color=blue]
> Peter,
> This is my code
>
> Dim adoConnstr As String =[/color]
"Server=nysvrpr od01\enterprise ;Database=POS;U ser
ID=sa;Password= 0cram;Trusted_C onnection=False "[color=blue]
> Dim adoConnPos As New SqlClient.SqlCo nnection(adoCon nstr)
> 'Dim instCmd As String = "Insert into POS (COL1,COL2) VALUES[/color]
(@COL1,@COL2)"[color=blue]
> Dim instCmd As String = "INSERT INTO Pos_Data_File[/color]
(POS_Store_Numb er, POS_UPC_Number, " & _[color=blue]
> " POS_Issue_Numbe r, POS_Quantity_So ld, POS_Sale_Date,[/color]
POS_Cover_Price , POS_Issue_Year, " & _[color=blue]
> " POS_Vendor_Numb er, POS_Transaction _Date, POS_File_Date) VALUES[/color]
(@Pos_Store_Num ber, @POS_UPC_Number ," & _[color=blue]
> " @POS_Issue_Numb er, @POS_Quantity_S old, @POS_Sale_Date,[/color]
@POS_Cover_Pric e, @POS_Issue_Year ," & _[color=blue]
> " @POS_Vendor_Num ber, @POS_Transactio n_Date, @POS_File_Date) "
>
> Dim daPOS As New SqlClient.SqlDa taAdapter
> Dim cmdInst As New SqlClient.SqlCo mmand(instCmd, adoConnPos)
>
> 'Dim selCmd As String = "select * from pos ORDER BY COL1"
> 'Dim cmdSel As New SqlClient.SqlCo mmand(selCmd, adoConnPos)
>
>
>
> Try
>
> 'daPOS.SelectCo mmand = cmdSel
> daPOS.InsertCom mand = cmdInst
>
> adoConnPos.Open ()
>
>
> ' daPOS.Fill(dsCS V, "POS")
>
> ' MsgBox(adoConnP os.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.Parame ters.Add(New SqlClient.SqlPa rameter _
> '("@COL1", SqlDbType.Char, 20)).Value = rowCus(0)
>
> 'cmdInst.Parame ters.Add(New SqlClient.SqlPa rameter _
> '("@COL2", SqlDbType.Char, 20)).Value = rowCus(1)
>
>
>
> '<<<<<<<<<<<<<< <<<THIS IS WHERE I AM CONCERNED ABOUT[color=green][color=darkred]
>>>>>>>>>>>>>>> >>>>>>>>>>>>> >>[/color][/color]
>
>
>
> cmdInst.Paramet ers.Add(New[/color]
SqlClient.SqlPa rameter("@POS_S tore_Number", SqlDbType.Char, 4,
"POS_Store_Numb er")).Value = rowCus(0)[color=blue]
> cmdInst.Paramet ers.Add(New[/color]
SqlClient.SqlPa rameter("@POS_U PC_Number", SqlDbType.VarCh ar, 15,
"POS_UPC_Number ")).Value = rowCus(1)[color=blue]
> cmdInst.Paramet ers.Add(New[/color]
SqlClient.SqlPa rameter("@POS_I ssue_Number", SqlDbType.Char, 3,
"POS_Issue_Numb er")).Value = rowCus(2)[color=blue]
> cmdInst.Paramet ers.Add(New[/color]
SqlClient.SqlPa rameter("@POS_Q uantity_Sold", SqlDbType.VarCh ar, 10,
"POS_Quantity_S old")).Value = rowCus(3)[color=blue]
> cmdInst.Paramet ers.Add(New[/color]
SqlClient.SqlPa rameter("@POS_S ale_Date", SqlDbType.NChar , 8,
"POS_Sale_Date" )).Value = rowCus(4)[color=blue]
> cmdInst.Paramet ers.Add(New[/color]
SqlClient.SqlPa rameter("@POS_C over_Price", SqlDbType.NChar , 10,
"POS_Cover_Pric e")).Value = rowCus(5)[color=blue]
> cmdInst.Paramet ers.Add(New[/color]
SqlClient.SqlPa rameter("@POS_I ssue_Year", SqlDbType.Char, 4,
"POS_Issue_Year ")).Value = rowCus(6)[color=blue]
> cmdInst.Paramet ers.Add(New[/color]
SqlClient.SqlPa rameter("@POS_V endor_Number", SqlDbType.NChar , 9,
"POS_Vendor_Num ber")).Value = rowCus(7)[color=blue]
> cmdInst.Paramet ers.Add(New[/color]
SqlClient.SqlPa rameter("@POS_T ransaction_Date ", SqlDbType.NChar , 8,
"POS_Transactio n_Date")).Value = rowCus(8)[color=blue]
> cmdInst.Paramet ers.Add(New[/color]
SqlClient.SqlPa rameter("@POS_F ile_Date", SqlDbType.DateT ime, 8,
"POS_File_Date" )).Value = Date.Today[color=blue]
>
>
>
> 'Next
>
> cmdInst.Execute NonQuery()
>
> cmdInst.Paramet ers.Clear()
>
>
>
> Next
>
> Next
>
>
>
> MsgBox("Done")
>
>
> adoConnPos.Clos e()
>
> Catch EX As SqlClient.SqlEx ception
>
> MessageBox.Show (ex.ToString)
>[/color]

**Chris** · Jul 21 '05, 01:03 PM

Re: Insert Speed

Hi
So you think it is best for me to pass parameter in the loop to a stored procedure?

**Patrick Sarnowski** · Jul 21 '05, 01:04 PM

Re: Insert Speed

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.

**Peter Rilling** · Nov 22 '05, 09:45 AM

Re: Insert Speed

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" <anonymous@disc ussions.microso ft.com> wrote in message
news:31FA5F60-3867-477C-AE02-4F7F55F2F996@mi crosoft.com...[color=blue]
> Hi,
> I am inserting 7000 rows in 9 columns, is 11 seconds slow or can that be[/color]
improved. I am inserting to SQL Server on a server on the network.[color=blue]
>
> Thanks[/color]

**Chris** · Nov 22 '05, 09:45 AM

Re: Insert Speed

Peter
This is my cod

Dim adoConnstr As String = "Server=nysvrpr od01\enterprise ;Database=POS;U ser ID=sa;Password= 0cram;Trusted_C onnection=False
Dim adoConnPos As New SqlClient.SqlCo nnection(adoCon nstr
'Dim instCmd As String = "Insert into POS (COL1,COL2) VALUES (@COL1,@COL2)
Dim instCmd As String = "INSERT INTO Pos_Data_File (POS_Store_Numb er, POS_UPC_Number, " &
" POS_Issue_Numbe r, POS_Quantity_So ld, POS_Sale_Date, POS_Cover_Price , POS_Issue_Year, " &
" POS_Vendor_Numb er, POS_Transaction _Date, POS_File_Date) VALUES (@Pos_Store_Num ber, @POS_UPC_Number ," &
" @POS_Issue_Numb er, @POS_Quantity_S old, @POS_Sale_Date, @POS_Cover_Pric e, @POS_Issue_Year ," &
" @POS_Vendor_Num ber, @POS_Transactio n_Date, @POS_File_Date)

Dim daPOS As New SqlClient.SqlDa taAdapte
Dim cmdInst As New SqlClient.SqlCo mmand(instCmd, adoConnPos

'Dim selCmd As String = "select * from pos ORDER BY COL1
'Dim cmdSel As New SqlClient.SqlCo mmand(selCmd, adoConnPos

Tr

'daPOS.SelectCo mmand = cmdSe
daPOS.InsertCom mand = cmdIns

adoConnPos.Open (

' daPOS.Fill(dsCS V, "POS"

' MsgBox(adoConnP os.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.Parame ters.Add(New SqlClient.SqlPa rameter
'("@COL1", SqlDbType.Char, 20)).Value = rowCus(0

'cmdInst.Parame ters.Add(New SqlClient.SqlPa rameter
'("@COL2", SqlDbType.Char, 20)).Value = rowCus(1

'<<<<<<<<<<<<<< <<<THIS IS WHERE I AM CONCERNED ABOUT >>>>>>>>>>>>>>> >>>>>>>>>>>>> >

cmdInst.Paramet ers.Add(New SqlClient.SqlPa rameter("@POS_S tore_Number", SqlDbType.Char, 4, "POS_Store_Numb er")).Value = rowCus(0
cmdInst.Paramet ers.Add(New SqlClient.SqlPa rameter("@POS_U PC_Number", SqlDbType.VarCh ar, 15, "POS_UPC_Number ")).Value = rowCus(1
cmdInst.Paramet ers.Add(New SqlClient.SqlPa rameter("@POS_I ssue_Number", SqlDbType.Char, 3, "POS_Issue_Numb er")).Value = rowCus(2
cmdInst.Paramet ers.Add(New SqlClient.SqlPa rameter("@POS_Q uantity_Sold", SqlDbType.VarCh ar, 10, "POS_Quantity_S old")).Value = rowCus(3
cmdInst.Paramet ers.Add(New SqlClient.SqlPa rameter("@POS_S ale_Date", SqlDbType.NChar , 8, "POS_Sale_Date" )).Value = rowCus(4
cmdInst.Paramet ers.Add(New SqlClient.SqlPa rameter("@POS_C over_Price", SqlDbType.NChar , 10, "POS_Cover_Pric e")).Value = rowCus(5
cmdInst.Paramet ers.Add(New SqlClient.SqlPa rameter("@POS_I ssue_Year", SqlDbType.Char, 4, "POS_Issue_Year ")).Value = rowCus(6
cmdInst.Paramet ers.Add(New SqlClient.SqlPa rameter("@POS_V endor_Number", SqlDbType.NChar , 9, "POS_Vendor_Num ber")).Value = rowCus(7
cmdInst.Paramet ers.Add(New SqlClient.SqlPa rameter("@POS_T ransaction_Date ", SqlDbType.NChar , 8, "POS_Transactio n_Date")).Value = rowCus(8
cmdInst.Paramet ers.Add(New SqlClient.SqlPa rameter("@POS_F ile_Date", SqlDbType.DateT ime, 8, "POS_File_Date" )).Value = Date.Toda

'Nex

cmdInst.Execute NonQuery(

cmdInst.Paramet ers.Clear(

Nex

Nex

MsgBox("Done"

adoConnPos.Clos e(

Catch EX As SqlClient.SqlEx ceptio

MessageBox.Show (ex.ToString

**Peter Rilling** · Nov 22 '05, 09:45 AM

Re: Insert Speed

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" <anonymous@disc ussions.microso ft.com> wrote in message
news:84313817-FDA4-4933-8577-EDFBE0A09540@mi crosoft.com...[color=blue]
> Peter,
> This is my code
>
> Dim adoConnstr As String =[/color]
"Server=nysvrpr od01\enterprise ;Database=POS;U ser
ID=sa;Password= 0cram;Trusted_C onnection=False "[color=blue]
> Dim adoConnPos As New SqlClient.SqlCo nnection(adoCon nstr)
> 'Dim instCmd As String = "Insert into POS (COL1,COL2) VALUES[/color]
(@COL1,@COL2)"[color=blue]
> Dim instCmd As String = "INSERT INTO Pos_Data_File[/color]
(POS_Store_Numb er, POS_UPC_Number, " & _[color=blue]
> " POS_Issue_Numbe r, POS_Quantity_So ld, POS_Sale_Date,[/color]
POS_Cover_Price , POS_Issue_Year, " & _[color=blue]
> " POS_Vendor_Numb er, POS_Transaction _Date, POS_File_Date) VALUES[/color]
(@Pos_Store_Num ber, @POS_UPC_Number ," & _[color=blue]
> " @POS_Issue_Numb er, @POS_Quantity_S old, @POS_Sale_Date,[/color]
@POS_Cover_Pric e, @POS_Issue_Year ," & _[color=blue]
> " @POS_Vendor_Num ber, @POS_Transactio n_Date, @POS_File_Date) "
>
> Dim daPOS As New SqlClient.SqlDa taAdapter
> Dim cmdInst As New SqlClient.SqlCo mmand(instCmd, adoConnPos)
>
> 'Dim selCmd As String = "select * from pos ORDER BY COL1"
> 'Dim cmdSel As New SqlClient.SqlCo mmand(selCmd, adoConnPos)
>
>
>
> Try
>
> 'daPOS.SelectCo mmand = cmdSel
> daPOS.InsertCom mand = cmdInst
>
> adoConnPos.Open ()
>
>
> ' daPOS.Fill(dsCS V, "POS")
>
> ' MsgBox(adoConnP os.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.Parame ters.Add(New SqlClient.SqlPa rameter _
> '("@COL1", SqlDbType.Char, 20)).Value = rowCus(0)
>
> 'cmdInst.Parame ters.Add(New SqlClient.SqlPa rameter _
> '("@COL2", SqlDbType.Char, 20)).Value = rowCus(1)
>
>
>
> '<<<<<<<<<<<<<< <<<THIS IS WHERE I AM CONCERNED ABOUT[color=green][color=darkred]
>>>>>>>>>>>>>>> >>>>>>>>>>>>> >>[/color][/color]
>
>
>
> cmdInst.Paramet ers.Add(New[/color]
SqlClient.SqlPa rameter("@POS_S tore_Number", SqlDbType.Char, 4,
"POS_Store_Numb er")).Value = rowCus(0)[color=blue]
> cmdInst.Paramet ers.Add(New[/color]
SqlClient.SqlPa rameter("@POS_U PC_Number", SqlDbType.VarCh ar, 15,
"POS_UPC_Number ")).Value = rowCus(1)[color=blue]
> cmdInst.Paramet ers.Add(New[/color]
SqlClient.SqlPa rameter("@POS_I ssue_Number", SqlDbType.Char, 3,
"POS_Issue_Numb er")).Value = rowCus(2)[color=blue]
> cmdInst.Paramet ers.Add(New[/color]
SqlClient.SqlPa rameter("@POS_Q uantity_Sold", SqlDbType.VarCh ar, 10,
"POS_Quantity_S old")).Value = rowCus(3)[color=blue]
> cmdInst.Paramet ers.Add(New[/color]
SqlClient.SqlPa rameter("@POS_S ale_Date", SqlDbType.NChar , 8,
"POS_Sale_Date" )).Value = rowCus(4)[color=blue]
> cmdInst.Paramet ers.Add(New[/color]
SqlClient.SqlPa rameter("@POS_C over_Price", SqlDbType.NChar , 10,
"POS_Cover_Pric e")).Value = rowCus(5)[color=blue]
> cmdInst.Paramet ers.Add(New[/color]
SqlClient.SqlPa rameter("@POS_I ssue_Year", SqlDbType.Char, 4,
"POS_Issue_Year ")).Value = rowCus(6)[color=blue]
> cmdInst.Paramet ers.Add(New[/color]
SqlClient.SqlPa rameter("@POS_V endor_Number", SqlDbType.NChar , 9,
"POS_Vendor_Num ber")).Value = rowCus(7)[color=blue]
> cmdInst.Paramet ers.Add(New[/color]
SqlClient.SqlPa rameter("@POS_T ransaction_Date ", SqlDbType.NChar , 8,
"POS_Transactio n_Date")).Value = rowCus(8)[color=blue]
> cmdInst.Paramet ers.Add(New[/color]
SqlClient.SqlPa rameter("@POS_F ile_Date", SqlDbType.DateT ime, 8,
"POS_File_Date" )).Value = Date.Today[color=blue]
>
>
>
> 'Next
>
> cmdInst.Execute NonQuery()
>
> cmdInst.Paramet ers.Clear()
>
>
>
> Next
>
> Next
>
>
>
> MsgBox("Done")
>
>
> adoConnPos.Clos e()
>
> Catch EX As SqlClient.SqlEx ception
>
> MessageBox.Show (ex.ToString)
>[/color]

**Chris** · Nov 22 '05, 09:45 AM

Re: Insert Speed

Hi
So you think it is best for me to pass parameter in the loop to a stored procedure?

**Patrick Sarnowski** · Nov 22 '05, 09:46 AM

Re: Insert Speed

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.

Insert Speed

Insert Speed

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment