Re: Insert Speed
From: Peter Rilling (peter_at_nospam.rilling.net)
Date: 03/04/04
- Next message: Mattias Sjögren: "Re: .NET Framework version"
- Previous message: John Timney \(Microsoft MVP\): "Re: .NET Framework version"
- In reply to: Chris: "Re: Insert Speed"
- Next in thread: Chris: "Re: Insert Speed"
- Reply: Chris: "Re: Insert Speed"
- Messages sorted by: [ date ] [ thread ]
Date: Thu, 4 Mar 2004 14:18:09 -0800
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@discussions.microsoft.com> wrote in message
news:84313817-FDA4-4933-8577-EDFBE0A09540@microsoft.com...
> 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
>
>
>
> 'Next
>
> cmdInst.ExecuteNonQuery()
>
> cmdInst.Parameters.Clear()
>
>
>
> Next
>
> Next
>
>
>
> MsgBox("Done")
>
>
> adoConnPos.Close()
>
> Catch EX As SqlClient.SqlException
>
> MessageBox.Show(ex.ToString)
>
- Next message: Mattias Sjögren: "Re: .NET Framework version"
- Previous message: John Timney \(Microsoft MVP\): "Re: .NET Framework version"
- In reply to: Chris: "Re: Insert Speed"
- Next in thread: Chris: "Re: Insert Speed"
- Reply: Chris: "Re: Insert Speed"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
