I'm populating data in a DataGridView, the data are populated by Days of a selected Month (January 31 rows + 1 Row for the Total)

The users has access to 2 columns and make changes after that they have to click a button to write the data back to SQL Server Database Table, the procedure that I'm using is taking too long to process because the Insert Into command is looping inside each row and writing values back to the database.

I believe that there is a better way of doing this like using SqlDataAdapter.InsertCommand but I can't figure out how to use this command inside a Loop.

Below find my code for the current procedure. I appreciate any help, thanks a lot.

Code Snippet

Public Sub SubmitForecastTest()

Dim mySqlConnection As SqlConnection

Dim myItem As DataGridViewRow

Dim mySqlCommandRmRevFO As SqlCommand

Dim mySqlCommandDelete As SqlCommand

Dim i, myYear, myMonth, myDay As Integer

Dim myDate As DateTime

Dim myGridNumRows As Integer

'SQL Connection

mySqlConnection = New SqlConnection("Data Source=MyDataSource;" & _

"Initial Catalog=MyDataBase;Integrated Security=True")

'Clear Data From Database Before Insert new data.

mySqlCommandDelete = New SqlCommand _

("DELETE FROM [MyDataBase].[dbo].[TransSummaryHotels] " & _

"WHERE TR_Entity = '" & MainForm.ToolStripLabelInnCodeID.Text & "' AND " & _

"TR_Period = '" & MainForm.TextBoxPeriod.Text & "' AND " & _

"TR_Year = '" & MainForm.DateTimePickerToDate.Value.Year & "' ", mySqlConnection)

mySqlConnection.Open()

mySqlCommandDelete.ExecuteNonQuery()

myGridNumRows = MainForm.DataGridViewDailyForecast.Rows.Count - 2

For i = 0 To myGridNumRows

myItem = MainForm.DataGridViewDailyForecast.Rows(i)

myDay = i + 1

myMonth = CInt(MainForm.TextBoxPeriod.Text)

myYear = MainForm.DateTimePickerToDate.Value.Year

myDate = DateSerial(myYear, myMonth, myDay)

With myItem

'RmRev FO

mySqlCommandRmRevFO = New SqlCommand _

("INSERT INTO [MyDatabase].[dbo].[TransSummaryHotels] ([TR_Entity], " & _

"[TR_Date],[TR_Period],[TR_Year],[TR_ACCOUNTNO],[TR_AMOUNT],[DataType],[TR_Dept], " & _

"[TR_Main],[TR_Sub],[TR_Origin]) " & _

"VALUES " & _

"('" & MainForm.ToolStripLabelInnCodeID.Text & "', " & _

"'" & myDate & "', " & _

"'" & myMonth & "', " & _

"'" & myYear & "', " & _

"'010512051', " & _

"'" & CInt(.Cells("colFORmRev").Value) * -1 & "', " & _

"'2', '10', '5120', '01', " & _

"'" & .Cells("colOrigin").Value & "') ", mySqlConnection)

mySqlCommandRmRevFO.ExecuteNonQuery()

End With

Next

mySqlConnection.Close()

End Sub

ok here is another sample closer to what you are doing

it did these 1500 records in just about 1 second

its for sql server compact but you can convert it to work with your needs

Private Sub insert_table1_record2()

For i As Integer = 1 To 1500

table1_string = "INSERT INTO table1 (id, name, address) VALUES (@id" & i.ToString & ", @name" & i.ToString & ", @address" & i.ToString & ")"

table1_command = New SqlServerCe.SqlCeCommand(table1_string, con)

If table1_command.Connection.State = ConnectionState.Closed Then

table1_command.Connection.Open()

End If

With table1_command

table1_command.Parameters.Add(New SqlServerCe.SqlCeParameter("@id" & i.ToString, SqlDbType.NVarChar, 100)).Value = "id " & i.ToString

table1_command.Parameters.Add(New SqlServerCe.SqlCeParameter("@name" & i.ToString, SqlDbType.NVarChar, 100)).Value = "name " & i.ToString

table1_command.Parameters.Add(New SqlServerCe.SqlCeParameter("@address" & i.ToString, SqlDbType.NVarChar, 100)).Value = "address " & i.ToString

End With

table1_command.ExecuteNonQuery()

Next

table1_command.Connection.Close()

End Sub

ok John,

i played with sql server a little bit and it does seem server is slower than compact

i could only do about 500 records in about a second

but there must be some issues with your system or app because i can still do thousands in the same time it takes yours to do just a few. you might try a new project and see what you can eliminate and maybe even consider using sql compact 3.5 as it does seem to be about 3 times faster for this situation

but on the other hand the sql express database that i used has primary keys and the compact one does not. So that may be a big slow down. The reason i don't have primary keys in the compact one is for testing. And i don't use designer or code generated constraints and relationships. I handle all that with code to make sure i don't have duplicates in the tables. so the slow down may actually be in your constraints also. there has to be something more than that also though.

here is the code that i ran to compare to yours

If Bid_command.Connection.State = ConnectionState.Closed Then

Bid_command.Connection.Open()

End If

For i As Integer = 0 To 500

Bid_string = "INSERT INTO Bid (BidID, ProjID, BidType, BidName, DateDue, TimeDue, Description, Notes) VALUES (@BidID, @ProjID, @BidType, @BidName, @DateDue, @TimeDue, @Description, @Notes)"

Bid_command = New SqlClient.SqlCommand(Bid_string, Deck_connection)

With Bid_command

Bid_command.Parameters.Add(New SqlClient.SqlParameter("@BidID", SqlDbType.NVarChar, 50)).Value = Me.TextBox1.Text & i.ToString

Bid_command.Parameters.Add(New SqlClient.SqlParameter("@ProjID", SqlDbType.NVarChar, 50)).Value = Me.TextBox2.Text & i.ToString

Bid_command.Parameters.Add(New SqlClient.SqlParameter("@BidType", SqlDbType.NVarChar, 50)).Value = Me.TextBox3.Text

Bid_command.Parameters.Add(New SqlClient.SqlParameter("@BidName", SqlDbType.NVarChar, 50)).Value = Me.TextBox4.Text

Bid_command.Parameters.Add(New SqlClient.SqlParameter("@DateDue", SqlDbType.NVarChar, 50)).Value = Me.TextBox5.Text

Bid_command.Parameters.Add(New SqlClient.SqlParameter("@TimeDue", SqlDbType.NVarChar, 50)).Value = Me.TextBox6.Text

Bid_command.Parameters.Add(New SqlClient.SqlParameter("@Description", SqlDbType.NVarChar, -1)).Value = Me.TextBox7.Text

Bid_command.Parameters.Add(New SqlClient.SqlParameter("@Notes", SqlDbType.NVarChar, -1)).Value = Me.TextBox8.Text

End With

Bid_command.ExecuteNonQuery()

Bid_string = "INSERT INTO Bid (BidID, ProjID, BidType, BidName, DateDue, TimeDue, Description, Notes) VALUES (@BidID, @ProjID, @BidType, @BidName, @DateDue, @TimeDue, @Description, @Notes)"

Bid_command = New SqlClient.SqlCommand(Bid_string, Deck_connection)

With Bid_command

Bid_command.Parameters.Add(New SqlClient.SqlParameter("@BidID", SqlDbType.NVarChar, 50)).Value = Me.TextBox1.Text & i + 10000.ToString

Bid_command.Parameters.Add(New SqlClient.SqlParameter("@ProjID", SqlDbType.NVarChar, 50)).Value = Me.TextBox2.Text & i + 10000.ToString

Bid_command.Parameters.Add(New SqlClient.SqlParameter("@BidType", SqlDbType.NVarChar, 50)).Value = Me.TextBox3.Text

Bid_command.Parameters.Add(New SqlClient.SqlParameter("@BidName", SqlDbType.NVarChar, 50)).Value = Me.TextBox4.Text

Bid_command.Parameters.Add(New SqlClient.SqlParameter("@DateDue", SqlDbType.NVarChar, 50)).Value = Me.TextBox5.Text

Bid_command.Parameters.Add(New SqlClient.SqlParameter("@TimeDue", SqlDbType.NVarChar, 50)).Value = Me.TextBox6.Text

Bid_command.Parameters.Add(New SqlClient.SqlParameter("@Description", SqlDbType.NVarChar, -1)).Value = Me.TextBox7.Text

Bid_command.Parameters.Add(New SqlClient.SqlParameter("@Notes", SqlDbType.NVarChar, -1)).Value = Me.TextBox8.Text

End With

Bid_command.ExecuteNonQuery()

Next

Bid_command.Connection.Close()

MsgBox("Bid added")

In general stored procedures run much quicker on sql server then text commands like you are using.

How many records are you inserting and how much time is it taking

I have been testing some loops to insert data and it seems to handle about 6700 records per second

Using the code that I posted, I'm inserting one record at a time (31 records in Total), I tried to run and put startwatch and stopwatch on it and It's taking a while (I'm talkingmore than a minute) I didn't even attempt to let it finish.

When I stop the debug and I went back to the database I saw thatI have like 5 records there.

How did you test your loop using my code or you wrote your own? did you use SqlAdapter or you're inserting data directly in the database?

I see in the loop you are creating a new command each time. This is slow because it has to destroy the old command and then create a new one. I would declare the command inside the loop if I was doing it this way to prevent having to wait for the old command to be destroyed.

For i = 0 To myGridNumRows

myItem = MainForm.DataGridViewDailyForecast.Rows(i)

myDay = i + 1

myMonth = CInt(MainForm.TextBoxPeriod.Text)

myYear = MainForm.DateTimePickerToDate.Value.Year

myDate = DateSerial(myYear, myMonth, myDay)

With myItem

'RmRev FO

Dim mySqlCommandRmRevFO As New SqlCommand _

("INSERT INTO [MyDatabase].[dbo].[TransSummaryHotels] ([TR_Entity], " & _

"[TR_Date],[TR_Period],[TR_Year],[TR_ACCOUNTNO],[TR_AMOUNT],[DataType],[TR_Dept], " & _

"[TR_Main],[TR_Sub],[TR_Origin]) " & _

"VALUES " & _

"('" & MainForm.ToolStripLabelInnCodeID.Text & "', " & _

"'" & myDate & "', " & _

"'" & myMonth & "', " & _

"'" & myYear & "', " & _

"'010512051', " & _

"'" & CInt(.Cells("colFORmRev").Value) * -1 & "', " & _

"'2', '10', '5120', '01', " & _

"'" & .Cells("colOrigin").Value & "') ", mySqlConnection)

mySqlCommandRmRevFO.ExecuteNonQuery()

End With

Here is an example i used

Private Sub insert_table1_record()

table1_string = "INSERT INTO table1 (id, name, address) VALUES (@id, @name, @address)"

table1_command = New SqlServerCe.SqlCeCommand(table1_string, con)

With table1_command

table1_command.Parameters.Add(New SqlServerCe.SqlCeParameter("@id", SqlDbType.NVarChar, 100)).Value = "test" 'idTextBox.Text

table1_command.Parameters.Add(New SqlServerCe.SqlCeParameter("@name", SqlDbType.NVarChar, 100)).Value = "test" 'nameTextBox.Text

table1_command.Parameters.Add(New SqlServerCe.SqlCeParameter("@address", SqlDbType.NVarChar, 100)).Value = "test" 'addressTextBox.Text

End With

If table1_command.Connection.State = ConnectionState.Closed Then

table1_command.Connection.Open()

End If

'Try

For i As Integer = 1 To 50000

table1_command.ExecuteNonQuery()

Next

'Catch ex As Exception

'MsgBox(ex.Message)

'End Try

table1_command.Connection.Close()

End Sub

ok here is another sample closer to what you are doing

it did these 1500 records in just about 1 second

its for sql server compact but you can convert it to work with your needs

Private Sub insert_table1_record2()

For i As Integer = 1 To 1500

table1_string = "INSERT INTO table1 (id, name, address) VALUES (@id" & i.ToString & ", @name" & i.ToString & ", @address" & i.ToString & ")"

table1_command = New SqlServerCe.SqlCeCommand(table1_string, con)

If table1_command.Connection.State = ConnectionState.Closed Then

table1_command.Connection.Open()

End If

With table1_command

table1_command.Parameters.Add(New SqlServerCe.SqlCeParameter("@id" & i.ToString, SqlDbType.NVarChar, 100)).Value = "id " & i.ToString

table1_command.Parameters.Add(New SqlServerCe.SqlCeParameter("@name" & i.ToString, SqlDbType.NVarChar, 100)).Value = "name " & i.ToString

table1_command.Parameters.Add(New SqlServerCe.SqlCeParameter("@address" & i.ToString, SqlDbType.NVarChar, 100)).Value = "address " & i.ToString

End With

table1_command.ExecuteNonQuery()

Next

table1_command.Connection.Close()

End Sub

Ken,

Thank you for your response, I tried to declare the command inside the loop unfortunately it didn't work the excecution remained slow.

thanks again.

js,

Thanks for your helpful code example, I made modification to it and it worked perfectly. To Insert 29 records (February) it's taking 2.6 seconds, I had to add more Insert command to this procedure (I have 6 Insert Into commands in Total) and now it's taking between 20 and 25 seconds.

Do you think I'm still missing something in this procedure that causing the insert to be slow? If that's the case, maybe I should show the user a progress bar showing the progress of the "Saving". What do you suggest?

Thanks again for all your support.

I am not sure.

Would you post your code?

I think the next thing i would try would be to declare all your variables outside of the loop and inside the loop you can just set them.

example instead of dim mysqlcommandRmRevFA as new sqlcommand etc...

outside the loop

dim mysqlcommandRmRevFA as sqlclient.sqlcommand

inside the loop

mysqlcommandRmRevFA = new sqlcommand etc...

same thing with my day and myyear

declare it outside the loop and then use myday = i + 1

it may increase your performance quite a bit just resusing the variables instead or initializing them over and over

try it and see. I would have to play with some more code to test something similar to see what all is happening.

Let me know if that helps any

Jeff

Jeff,

I declared them outside the Loop, now it's taking between 15 and 18 Seconds, which is better but I think I still have to put the progress bar because of the time that's taking. Any thoughts? Thanks again for all your help

Dim mySqlConnection As SqlConnection

Dim i As Integer

Dim myGridNumRows As Integer

Dim myItem As DataGridViewRow

Dim myDay, myPeriod, myYear As Integer

Dim myDate As DateTime

'Sql Commands

Dim mySqlCommandDelete As New SqlCommand

Dim mySqlCommandRmRevFO As New SqlCommand

Dim mySqlCommandRAFO As New SqlCommand

Dim mySqlCommandRSFO As New SqlCommand

Dim mySqlCommandRmRevFA As New SqlCommand

Dim mySqlCommandRAFA As New SqlCommand

Dim mySqlCommandRSFA As New SqlCommand

Dim myWatch As New Stopwatch

mySqlConnection.Open()

mySqlCommandDelete.ExecuteNonQuery()

myPeriod = CInt(MainForm.TextBoxPeriod.Text)

myYear = MainForm.DateTimePickerToDate.Value.Year

myGridNumRows = MainForm.DataGridViewDailyForecast.Rows.Count - 2

For i = 0 To myGridNumRows

myItem = MainForm.DataGridViewDailyForecast.Rows(i)

myDay = i + 1

myDate = DateSerial(myYear, myPeriod, myDay)

With myItem

'RmRev FO

mySqlCommandRmRevFO = New SqlCommand( _

"INSERT INTO [myDatabase].[dbo].[Table5DeleteLater] ([TR_Entity], " & _

"[TR_Date],[TR_Period],[TR_Year],[TR_ACCOUNTNO],[TR_AMOUNT],[DataType],[TR_Dept], " & _

"[TR_Main],[TR_Sub],[TR_Origin]) " & _

"VALUES " & _

"(@Entity, @Date, @Period, @Year, @AccountNO, @Amount, @DataType, @Dept, " & _

"@Main, @Sub, @Origin) ", mySqlConnection)

mySqlCommandRmRevFO.Parameters.Add(New SqlParameter("@Entity" _

, SqlDbType.NVarChar, 6)).Value = _

MainForm.ToolStripLabelInnCodeID.Text

mySqlCommandRmRevFO.Parameters.Add(New SqlParameter("@Date" _

, SqlDbType.DateTime)).Value = myDate

mySqlCommandRmRevFO.Parameters.Add(New SqlParameter("@Period" _

, SqlDbType.Char, 2)).Value = myPeriod

mySqlCommandRmRevFO.Parameters.Add(New SqlParameter("@Year" _

, SqlDbType.Char, 10)).Value = myYear

mySqlCommandRmRevFO.Parameters.Add(New SqlParameter("@AccountNO" _

, SqlDbType.NVarChar, 15)).Value = "010512051"

mySqlCommandRmRevFO.Parameters.Add(New SqlParameter("@Amount" _

, SqlDbType.Decimal, 18)).Value = _

CInt(.Cells("colFORmRev").Value) * -1

mySqlCommandRmRevFO.Parameters.Add(New SqlParameter("@DataType" _

, SqlDbType.Char, 6)).Value = "2"

'RA FO

mySqlCommandRAFO = New SqlCommand( _

"INSERT INTO [myDatabase].[dbo].[Table5DeleteLater] ([TR_Entity], " & _

"[TR_Date],[TR_Period],[TR_Year],[TR_ACCOUNTNO],[TR_AMOUNT],[DataType],[TR_Dept], " & _

"[TR_Main],[TR_Sub],[TR_Origin]) " & _

"VALUES " & _

"(@Entity, @Date, @Period, @Year, @AccountNO, @Amount, @DataType, @Dept, " & _

"@Main, @Sub, @Origin) ", mySqlConnection)

mySqlCommandRAFO.Parameters.Add(New SqlParameter("@Entity" _

, SqlDbType.NVarChar, 6)).Value = _

MainForm.ToolStripLabelInnCodeID.Text

mySqlCommandRAFO.Parameters.Add(New SqlParameter("@Date" _

, SqlDbType.DateTime)).Value = myDate

mySqlCommandRAFO.Parameters.Add(New SqlParameter("@Period" _

, SqlDbType.Char, 2)).Value = myPeriod

mySqlCommandRAFO.Parameters.Add(New SqlParameter("@Year" _

, SqlDbType.Char, 10)).Value = myYear

mySqlCommandRAFO.Parameters.Add(New SqlParameter("@AccountNO" _

, SqlDbType.NVarChar, 15)).Value = "9105120RA"

mySqlCommandRAFO.Parameters.Add(New SqlParameter("@Amount" _

, SqlDbType.Decimal, 18)).Value = _

CInt(.Cells("colFORA").Value)

mySqlCommandRAFO.Parameters.Add(New SqlParameter("@DataType" _

, SqlDbType.Char, 6)).Value = "2"

'RS FO

mySqlCommandRSFO = New SqlCommand( _

"INSERT INTO [myDatabase].[dbo].[Table5DeleteLater] ([TR_Entity], " & _

"[TR_Date],[TR_Period],[TR_Year],[TR_ACCOUNTNO],[TR_AMOUNT],[DataType],[TR_Dept], " & _

"[TR_Main],[TR_Sub],[TR_Origin]) " & _

"VALUES " & _

"(@Entity, @Date, @Period, @Year, @AccountNO, @Amount, @DataType, @Dept, " & _

"@Main, @Sub, @Origin) ", mySqlConnection)

mySqlCommandRSFO.Parameters.Add(New SqlParameter("@Entity" _

, SqlDbType.NVarChar, 6)).Value = _

MainForm.ToolStripLabelInnCodeID.Text

mySqlCommandRSFO.Parameters.Add(New SqlParameter("@Date" _

, SqlDbType.DateTime)).Value = myDate

mySqlCommandRSFO.Parameters.Add(New SqlParameter("@Period" _

, SqlDbType.Char, 2)).Value = myPeriod

mySqlCommandRSFO.Parameters.Add(New SqlParameter("@Year" _

, SqlDbType.Char, 10)).Value = myYear

mySqlCommandRSFO.Parameters.Add(New SqlParameter("@AccountNO" _

, SqlDbType.NVarChar, 15)).Value = "9105120RS"

mySqlCommandRSFO.Parameters.Add(New SqlParameter("@Amount" _

, SqlDbType.Decimal, 18)).Value = _

CInt(.Cells("colFORS").Value)

mySqlCommandRSFO.Parameters.Add(New SqlParameter("@DataType" _

, SqlDbType.Char, 6)).Value = "2"

'RmRev FA

mySqlCommandRmRevFA = New SqlCommand( _

"INSERT INTO [myDatabase].[dbo].[Table5DeleteLater] ([TR_Entity], " & _

"[TR_Date],[TR_Period],[TR_Year],[TR_ACCOUNTNO],[TR_AMOUNT],[DataType],[TR_Dept], " & _

"[TR_Main],[TR_Sub],[TR_Origin]) " & _

"VALUES " & _

"(@Entity, @Date, @Period, @Year, @AccountNO, @Amount, @DataType, @Dept, " & _

"@Main, @Sub, @Origin) ", mySqlConnection)

mySqlCommandRmRevFA.Parameters.Add(New SqlParameter("@Entity" _

, SqlDbType.NVarChar, 6)).Value = _

MainForm.ToolStripLabelInnCodeID.Text

mySqlCommandRmRevFA.Parameters.Add(New SqlParameter("@Date" _

, SqlDbType.DateTime)).Value = myDate

mySqlCommandRmRevFA.Parameters.Add(New SqlParameter("@Period" _

, SqlDbType.Char, 2)).Value = myPeriod

mySqlCommandRmRevFA.Parameters.Add(New SqlParameter("@Year" _

, SqlDbType.Char, 10)).Value = myYear

mySqlCommandRmRevFA.Parameters.Add(New SqlParameter("@AccountNO" _

, SqlDbType.NVarChar, 15)).Value = "010512051"

mySqlCommandRmRevFA.Parameters.Add(New SqlParameter("@Amount" _

, SqlDbType.Decimal, 18)).Value = _

CInt(.Cells("colFARmRev").Value) * -1

mySqlCommandRmRevFA.Parameters.Add(New SqlParameter("@DataType" _

, SqlDbType.Char, 6)).Value = "3"

'RA FA

mySqlCommandRAFA = New SqlCommand( _

"INSERT INTO [myDatabase].[dbo].[Table5DeleteLater] ([TR_Entity], " & _

"[TR_Date],[TR_Period],[TR_Year],[TR_ACCOUNTNO],[TR_AMOUNT],[DataType],[TR_Dept], " & _

"[TR_Main],[TR_Sub],[TR_Origin]) " & _

"VALUES " & _

"(@Entity, @Date, @Period, @Year, @AccountNO, @Amount, @DataType, @Dept, " & _

"@Main, @Sub, @Origin) ", mySqlConnection)

mySqlCommandRAFA.Parameters.Add(New SqlParameter("@Entity" _

, SqlDbType.NVarChar, 6)).Value = _

MainForm.ToolStripLabelInnCodeID.Text

mySqlCommandRAFA.Parameters.Add(New SqlParameter("@Date" _

, SqlDbType.DateTime)).Value = myDate

mySqlCommandRAFA.Parameters.Add(New SqlParameter("@Period" _

, SqlDbType.Char, 2)).Value = myPeriod

mySqlCommandRAFA.Parameters.Add(New SqlParameter("@Year" _

, SqlDbType.Char, 10)).Value = myYear

mySqlCommandRAFA.Parameters.Add(New SqlParameter("@AccountNO" _

, SqlDbType.NVarChar, 15)).Value = "9105120RA"

mySqlCommandRAFA.Parameters.Add(New SqlParameter("@Amount" _

, SqlDbType.Decimal, 18)).Value = _

CInt(.Cells("colFARA").Value)

mySqlCommandRAFA.Parameters.Add(New SqlParameter("@DataType" _

, SqlDbType.Char, 6)).Value = "3"

'RS FA

mySqlCommandRSFA = New SqlCommand( _

"INSERT INTO [myDatabase].[dbo].[Table5DeleteLater] ([TR_Entity], " & _

"[TR_Date],[TR_Period],[TR_Year],[TR_ACCOUNTNO],[TR_AMOUNT],[DataType],[TR_Dept], " & _

"[TR_Main],[TR_Sub],[TR_Origin]) " & _

"VALUES " & _

"(@Entity, @Date, @Period, @Year, @AccountNO, @Amount, @DataType, @Dept, " & _

"@Main, @Sub, @Origin) ", mySqlConnection)

mySqlCommandRSFA.Parameters.Add(New SqlParameter("@Entity" _

, SqlDbType.NVarChar, 6)).Value = _

MainForm.ToolStripLabelInnCodeID.Text

mySqlCommandRSFA.Parameters.Add(New SqlParameter("@Date" _

, SqlDbType.DateTime)).Value = myDate

mySqlCommandRSFA.Parameters.Add(New SqlParameter("@Period" _

, SqlDbType.Char, 2)).Value = myPeriod

mySqlCommandRSFA.Parameters.Add(New SqlParameter("@Year" _

, SqlDbType.Char, 10)).Value = myYear

mySqlCommandRSFA.Parameters.Add(New SqlParameter("@AccountNO" _

, SqlDbType.NVarChar, 15)).Value = "9105120RS"

mySqlCommandRSFA.Parameters.Add(New SqlParameter("@Amount" _

, SqlDbType.Decimal, 18)).Value = _

CInt(.Cells("colFARS").Value)

mySqlCommandRSFA.Parameters.Add(New SqlParameter("@DataType" _

, SqlDbType.Char, 6)).Value = "3"

mySqlCommandRmRevFO.ExecuteNonQuery()

mySqlCommandRAFO.ExecuteNonQuery()

mySqlCommandRSFO.ExecuteNonQuery()

mySqlCommandRmRevFA.ExecuteNonQuery()

mySqlCommandRAFA.ExecuteNonQuery()

mySqlCommandRSFA.ExecuteNonQuery()

End With

Next

mySqlConnection.Close()

myWatch.Stop()

MainForm.ButtonSave.Text = "Elapsed Time " & myWatch.Elapsed.ToString() & " Elapsed"

MainForm.ButtonSave.Enabled = True

End Sub

i'm not sure why it would still take that long

Maybe there are other things that are affecting the performance.

is it running over a network, etc...

well the next thing you can try also would be to eliminate the parameters and just use the values directly in your insert statement. That may speed it up also by not adding new parameters repeatedly. If you don't have to worry about security then that will be ok.

all you need to do is to remove the parameter add statements and then add the value from the parameter add statements in place of the parameter name in the insert values. And you need to add characters to enclose each one.

Example

"INSERT INTO [myDatabase].[dbo].[Table5DeleteLater] ([TR_Entity]) " & _

"VALUES " & _

"('" & MainForm.ToolStripLabelInnCodeID.Text & "',) ", mySqlConnection)

by itself it would look like this ' thevalue '

or to easier to read you could use variables instead of the controlname and text

but if it is going to take a while then to add the progressbar just set the maximum to the number or gridview rows and then in the last line of your loop before next add yourprogressbar.performstep() to increase it. you may have to also set the step value to 1 and the style to continuous. But that is up to you ofcourse. depends on how you want it to look and update.

Thanks Jeff,

I willreplace the parameters with values directly in the statement, and if it doesn't speed up the procedure I guess I'm going with the progress bar.

I will keep you posted.

Thanks again

You might even create a new project and just run the insert code and nothing else.

See if it runs any faster and you might find that it is a probleminside your project.

or since you are using sql server then it might be your initial connection that is making it appear to take a long time.

i found out a while back that there is an option in the installation for sql that handles the connection. I was using sql express and i started using compact because it just requires a runtime file to use the database files. The resource usage is much smaller also. The samples i have been testing have been on compact 3.5 so i don't know if that would make any difference. i have used server and most other code i run seems to be just as quick. if i get a chance i will try a similar code with server. but either way, 15 seconds seems way too long for such a small amount of records. something just doesn't seem right with that.

ok John,

i played with sql server a little bit and it does seem server is slower than compact

i could only do about 500 records in about a second

but there must be some issues with your system or app because i can still do thousands in the same time it takes yours to do just a few. you might try a new project and see what you can eliminate and maybe even consider using sql compact 3.5 as it does seem to be about 3 times faster for this situation

but on the other hand the sql express database that i used has primary keys and the compact one does not. So that may be a big slow down. The reason i don't have primary keys in the compact one is for testing. And i don't use designer or code generated constraints and relationships. I handle all that with code to make sure i don't have duplicates in the tables. so the slow down may actually be in your constraints also. there has to be something more than that also though.

here is the code that i ran to compare to yours

If Bid_command.Connection.State = ConnectionState.Closed Then

Bid_command.Connection.Open()

End If

For i As Integer = 0 To 500

Bid_string = "INSERT INTO Bid (BidID, ProjID, BidType, BidName, DateDue, TimeDue, Description, Notes) VALUES (@BidID, @ProjID, @BidType, @BidName, @DateDue, @TimeDue, @Description, @Notes)"

Bid_command = New SqlClient.SqlCommand(Bid_string, Deck_connection)

With Bid_command

Bid_command.Parameters.Add(New SqlClient.SqlParameter("@BidID", SqlDbType.NVarChar, 50)).Value = Me.TextBox1.Text & i.ToString

Bid_command.Parameters.Add(New SqlClient.SqlParameter("@ProjID", SqlDbType.NVarChar, 50)).Value = Me.TextBox2.Text & i.ToString

Bid_command.Parameters.Add(New SqlClient.SqlParameter("@BidType", SqlDbType.NVarChar, 50)).Value = Me.TextBox3.Text

Bid_command.Parameters.Add(New SqlClient.SqlParameter("@BidName", SqlDbType.NVarChar, 50)).Value = Me.TextBox4.Text

Bid_command.Parameters.Add(New SqlClient.SqlParameter("@DateDue", SqlDbType.NVarChar, 50)).Value = Me.TextBox5.Text

Bid_command.Parameters.Add(New SqlClient.SqlParameter("@TimeDue", SqlDbType.NVarChar, 50)).Value = Me.TextBox6.Text

Bid_command.Parameters.Add(New SqlClient.SqlParameter("@Description", SqlDbType.NVarChar, -1)).Value = Me.TextBox7.Text

Bid_command.Parameters.Add(New SqlClient.SqlParameter("@Notes", SqlDbType.NVarChar, -1)).Value = Me.TextBox8.Text

End With

Bid_command.ExecuteNonQuery()

Bid_string = "INSERT INTO Bid (BidID, ProjID, BidType, BidName, DateDue, TimeDue, Description, Notes) VALUES (@BidID, @ProjID, @BidType, @BidName, @DateDue, @TimeDue, @Description, @Notes)"

Bid_command = New SqlClient.SqlCommand(Bid_string, Deck_connection)

With Bid_command

Bid_command.Parameters.Add(New SqlClient.SqlParameter("@BidID", SqlDbType.NVarChar, 50)).Value = Me.TextBox1.Text & i + 10000.ToString

Bid_command.Parameters.Add(New SqlClient.SqlParameter("@ProjID", SqlDbType.NVarChar, 50)).Value = Me.TextBox2.Text & i + 10000.ToString

Bid_command.Parameters.Add(New SqlClient.SqlParameter("@BidType", SqlDbType.NVarChar, 50)).Value = Me.TextBox3.Text

Bid_command.Parameters.Add(New SqlClient.SqlParameter("@BidName", SqlDbType.NVarChar, 50)).Value = Me.TextBox4.Text

Bid_command.Parameters.Add(New SqlClient.SqlParameter("@DateDue", SqlDbType.NVarChar, 50)).Value = Me.TextBox5.Text

Bid_command.Parameters.Add(New SqlClient.SqlParameter("@TimeDue", SqlDbType.NVarChar, 50)).Value = Me.TextBox6.Text

Bid_command.Parameters.Add(New SqlClient.SqlParameter("@Description", SqlDbType.NVarChar, -1)).Value = Me.TextBox7.Text

Bid_command.Parameters.Add(New SqlClient.SqlParameter("@Notes", SqlDbType.NVarChar, -1)).Value = Me.TextBox8.Text

End With

Bid_command.ExecuteNonQuery()

Next

Bid_command.Connection.Close()

MsgBox("Bid added")

In general stored procedures run much quicker on sql server then text commands like you are using.

I found the source that was causing my Insert command to be slow, it was a Table Trigger.

Thank you all for your help