Solved

Faster way of Inserting/Deleting and amending in my code

Posted on 2006-06-12
9
254 Views
Last Modified: 2010-04-23
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(sqlStr, conTRS)

                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.ToString)
                    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.SelectionStart.Date
                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(sqlStr, conTRS)

            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.ToString)
                Cursor = Cursors.Arrow 'Stop hourglass
            End Try



0
Comment
Question by:Cherryex
  • 3
  • 2
  • 2
  • +2
9 Comments
 
LVL 43

Accepted Solution

by:
TimCottee earned 350 total points
ID: 16884749
Hi Cherryex,

The simple answer is to use something like sql server instead of access. With around 80 concurrent users, access is going to struggle. There is probably nothing much you can do to your code itself to improve things, you simply need to use a back-end database that is more capable of dealing with this number of users than access is.

Tim Cottee
0
 
LVL 11

Expert Comment

by:LordWabbit
ID: 16885763
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.
0
 
LVL 7

Expert Comment

by:foobarr
ID: 16887576
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
0
 
LVL 9

Expert Comment

by:lojk
ID: 16890525
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...
0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 

Author Comment

by:Cherryex
ID: 16895676
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?
0
 
LVL 9

Assisted Solution

by:lojk
lojk earned 150 total points
ID: 16896398
Depending on the complexity of your access DB Its pretty much as simple as using the DTS (Data Transformation Services) to import the data and changing all of your connection strings in your app... Ideally you should use the SQLClient from. net but if you are desperate to get it done quick (at a slight performance hit) you should be able to still use the existing OLEDB one but use sql connection string until you have time to finish migrating the code.

The worst thing you might run into is "Select * from Something where Field Like ... " commands will need to be changed slightly.

Check out the SQL Server Books Online at MS (Http:\\msdn.microsoft.com) and consult the apprpiate Section in your VS IDE MSDN, woudl recommend starting with 'Choosing Your Data Source' or similar
0
 
LVL 43

Expert Comment

by:TimCottee
ID: 16900480
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
0
 

Author Comment

by:Cherryex
ID: 16900537
Thank you.  That you have all been a great help.
0
 
LVL 9

Expert Comment

by:lojk
ID: 16900621
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.
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Suggested Solutions

Well, all of us have seen the multiple EXCEL.EXE's in task manager that won't die even if you call the .close, .dispose methods. Try this method to kill any excels in memory. You can copy the kill function to create a check function and replace the …
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now