Cherryex
asked on
Faster way of Inserting/Deleting and amending in my code
Hi there I am desperate for help!!
My finished app is being used by up to 80 people. It is VB.net 2005 using a msAccess database (Office 2003). The performance really slows down to a crawl when the database is opened manually(which isn't a big problem as this is rare) and when there is a record lock (this is the main problem as there is a record lock everything someone saves, deletes or amends). Everyone is updating the same tow tables in the database so it is having a lot of record locks and people have to wait around when they click save. I am using sql update statement using an executenonquery method. Is there a better way to update that will speed up the performance? Any advice on performance for this code would be very, very gratefully received.
I am doing a couple of execute scalar to get values out of the table to check what needs updating then using the following code to update the first table:
Dim sWeekNo As String = Format(SelectedDate.Date, "MM/dd/yyyy")
sqlStr = "INSERT INTO tblTimeWeekly (StaffID, ChargeRate, WeekNo )VALUES (" & zStaffID.Text & ", " & zRate.Text & ", #" & sWeekNo & "#)"
myCmd = New Data.OleDb.OleDbCommand(sq
Try
If myCmd.Connection.State <> ConnectionState.Open Then myCmd.Connection.Open()
x = myCmd.ExecuteNonQuery()
If conTRS.State = ConnectionState.Open Then conTRS.Close()
Catch ex As Exception
MessageBox.Show(ex.ToStrin
Cursor = Cursors.Arrow 'Stop hourglass
End Try
and this code to update the second table:
'.........................
'Get the TimeID from tblTimeWeekly
'.........................
Dim TID As String
Dim StaffID As Integer = zStaffID.Text
TID = TimeID(searchDate, StaffID)
'*************************
' INSERT INTO tblTimeDaily:
'*************************
If miWeeklyView.Checked = True Then
If chkNarrative.CheckState = CheckState.Checked Then
sqlStr = "INSERT INTO tblTimeDaily (TimeID, SuffixID ,Units, CIndex, Narrative)VALUES (" & TID & ", " & cbxSuffix.SelectedValue & "," & iUnits & ", " & cbxClient.SelectedValue & ", '" & Trim(txtNarrative.Text) & "')"
Else
sqlStr = "INSERT INTO tblTimeDaily (TimeID, SuffixID ,Units, CIndex )VALUES (" & TID & ", " & cbxSuffix.SelectedValue & "," & iUnits & ", " & cbxClient.SelectedValue & ")"
End If
ElseIf miDailyView.Checked = True Then
Dim myDay As Date = MonthCalendar1.SelectionSt
Dim sDate As String = Format(myDay, "MM/dd/yyyy")
If chkNarrative.CheckState = CheckState.Checked Then
sqlStr = "INSERT INTO tblTimeDaily (TimeID, SuffixID ,Units, CIndex, TStart, TFinish, dDate, Narrative)VALUES (" & TID & ", " & cbxSuffix.SelectedValue & "," & iUnits & ", " & cbxClient.SelectedValue & ", '" & cbxFrom.Text & "', '" & cbxTo.Text & "', #" & sDate & "#, '" & Trim(txtNarrative.Text) & "')"
Else
sqlStr = "INSERT INTO tblTimeDaily (TimeID, SuffixID ,Units, CIndex, TStart, TFinish, dDate)VALUES (" & TID & ", " & cbxSuffix.SelectedValue & "," & iUnits & ", " & cbxClient.SelectedValue & ", '" & cbxFrom.Text & "', '" & cbxTo.Text & "', #" & sDate & "#)"
End If
End If
myCmd = New Data.OleDb.OleDbCommand(sq
Try
If myCmd.Connection.State <> ConnectionState.Open Then myCmd.Connection.Open()
x = myCmd.ExecuteNonQuery()
If conTRS.State = ConnectionState.Open Then conTRS.Close()
Catch ex As Exception
MessageBox.Show(ex.ToStrin
Cursor = Cursors.Arrow 'Stop hourglass
End Try
My finished app is being used by up to 80 people. It is VB.net 2005 using a msAccess database (Office 2003). The performance really slows down to a crawl when the database is opened manually(which isn't a big problem as this is rare) and when there is a record lock (this is the main problem as there is a record lock everything someone saves, deletes or amends). Everyone is updating the same tow tables in the database so it is having a lot of record locks and people have to wait around when they click save. I am using sql update statement using an executenonquery method. Is there a better way to update that will speed up the performance? Any advice on performance for this code would be very, very gratefully received.
I am doing a couple of execute scalar to get values out of the table to check what needs updating then using the following code to update the first table:
Dim sWeekNo As String = Format(SelectedDate.Date, "MM/dd/yyyy")
sqlStr = "INSERT INTO tblTimeWeekly (StaffID, ChargeRate, WeekNo )VALUES (" & zStaffID.Text & ", " & zRate.Text & ", #" & sWeekNo & "#)"
myCmd = New Data.OleDb.OleDbCommand(sq
Try
If myCmd.Connection.State <> ConnectionState.Open Then myCmd.Connection.Open()
x = myCmd.ExecuteNonQuery()
If conTRS.State = ConnectionState.Open Then conTRS.Close()
Catch ex As Exception
MessageBox.Show(ex.ToStrin
Cursor = Cursors.Arrow 'Stop hourglass
End Try
and this code to update the second table:
'.........................
'Get the TimeID from tblTimeWeekly
'.........................
Dim TID As String
Dim StaffID As Integer = zStaffID.Text
TID = TimeID(searchDate, StaffID)
'*************************
' INSERT INTO tblTimeDaily:
'*************************
If miWeeklyView.Checked = True Then
If chkNarrative.CheckState = CheckState.Checked Then
sqlStr = "INSERT INTO tblTimeDaily (TimeID, SuffixID ,Units, CIndex, Narrative)VALUES (" & TID & ", " & cbxSuffix.SelectedValue & "," & iUnits & ", " & cbxClient.SelectedValue & ", '" & Trim(txtNarrative.Text) & "')"
Else
sqlStr = "INSERT INTO tblTimeDaily (TimeID, SuffixID ,Units, CIndex )VALUES (" & TID & ", " & cbxSuffix.SelectedValue & "," & iUnits & ", " & cbxClient.SelectedValue & ")"
End If
ElseIf miDailyView.Checked = True Then
Dim myDay As Date = MonthCalendar1.SelectionSt
Dim sDate As String = Format(myDay, "MM/dd/yyyy")
If chkNarrative.CheckState = CheckState.Checked Then
sqlStr = "INSERT INTO tblTimeDaily (TimeID, SuffixID ,Units, CIndex, TStart, TFinish, dDate, Narrative)VALUES (" & TID & ", " & cbxSuffix.SelectedValue & "," & iUnits & ", " & cbxClient.SelectedValue & ", '" & cbxFrom.Text & "', '" & cbxTo.Text & "', #" & sDate & "#, '" & Trim(txtNarrative.Text) & "')"
Else
sqlStr = "INSERT INTO tblTimeDaily (TimeID, SuffixID ,Units, CIndex, TStart, TFinish, dDate)VALUES (" & TID & ", " & cbxSuffix.SelectedValue & "," & iUnits & ", " & cbxClient.SelectedValue & ", '" & cbxFrom.Text & "', '" & cbxTo.Text & "', #" & sDate & "#)"
End If
End If
myCmd = New Data.OleDb.OleDbCommand(sq
Try
If myCmd.Connection.State <> ConnectionState.Open Then myCmd.Connection.Open()
x = myCmd.ExecuteNonQuery()
If conTRS.State = ConnectionState.Open Then conTRS.Close()
Catch ex As Exception
MessageBox.Show(ex.ToStrin
Cursor = Cursors.Arrow 'Stop hourglass
End Try
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
And if licensing costs are an issue I would suggest using a Firebird database server since it's free, has no limitations to speak of and is quite robust.
vb.net 2005 should have come wiht a sql 2005 express edition
you could possibly load that up with your tables and see your performance on it
you could possibly load that up with your tables and see your performance on it
in the abscense of going fully over to sql, you could write a small sockets application to pass the execute commands to a sockets server application that could run the execute commands for you remotely and in a sequential fashion. That *may* speed it up slightly but sql really is the way to go...
ASKER
Hi all,
Thank you for the responces. It has been so busy since it went live that I haven't had a second to do anything else. Not what I wanted to hear but looks like SQL is the way to go. The only problem is that I have never used SQL and don't have a clue where to start. We do have SQL server enterprise edition all ready to be set up with it's own server. Could anyone recommend any articles or tutorials that could get me started converting Access back end to SQL?
Thank you for the responces. It has been so busy since it went live that I haven't had a second to do anything else. Not what I wanted to hear but looks like SQL is the way to go. The only problem is that I have never used SQL and don't have a clue where to start. We do have SQL server enterprise edition all ready to be set up with it's own server. Could anyone recommend any articles or tutorials that could get me started converting Access back end to SQL?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Cherryex,
Access does have a built-in SQL upsizing wizard, this will make a reasonable stab at creating the tables etc from your access database on sql. As lojk says, you should be able initially to use the oledb objects with a different connection string though the sql specific objects will be slightly faster in the long run.
Tim
Access does have a built-in SQL upsizing wizard, this will make a reasonable stab at creating the tables etc from your access database on sql. As lojk says, you should be able initially to use the oledb objects with a different connection string though the sql specific objects will be slightly faster in the long run.
Tim
ASKER
Thank you. That you have all been a great help.
Oh one last thing... Access has a Access Data Project (.adp) that allows you to present a sql DB inside the access front end. Im not sure but you may be able to set up an adp and access that through code, in either case i find it a much easier way of viewing SQLDB tables than the enterprise manager or query analyser.