Page 1 of 2 12 LastLast
Results 1 to 15 of 28
  1. #1
    Join Date
    Jan 2006
    Location
    Washington, D.C
    Posts
    29

    Unanswered: Optimizing a slow Access Database

    I have a big problem. I created a database that performs a number of calculations and access the tables frequently very frequently. It only accesses the table to read the data never to update or delete. Well when I ran the copy of the database on my computer it ran pretty fast. So I put it on the network and it seems to run a bit slower for people in my office. However for the people in offices in other parts of the country it seems that the database is running extremely slow.

    I changed the design to a front - end back- end scheme and this didn’t seem to solve the problem. In fact it seemed to make it slower. When I access data from the tables I use three different ways:

    Set rs = dbs.OpenRecordset(sSql, dbOpenDynaset)

    Only used when updating data
    rs.open sSql, CurrentProject.Connection, adOpenKeyset, adLockOptimistic

    rst.open sSql, CurrentProject.Connection, adOpenForwardOnly

    I read somewhere that the forward only option was the fastest however I can only use this when I know that I am returning more than one record. If I try it where there is only one record it returns -1.

    Is there anyways I can speed up access to the tables. Is there a way to leave them open through out all of the procedures? Are there different things I could use to produce the same result? Please help

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi

    It is unlikely to be Access that requires optimisation as such - the (principle) bottle neck is almost certainly the network.

    What are the specs for your LAN? Are you using Terminal Services\ CITRIX or another thin client technology?

    Forward only cursors don't return the number of records, only whether or not there are records (-1 in Access = True) when referring to the RecordCount property.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I'd agree with the Audacious one, its likely the cause of the response time is the connection to the remote systems.

    In that sort of environment using JET as the data storage is going to be a problem. you wil be pumipng a lot of data up and down the network for each client. It will be especailly painfull if you are using fully populated combo & list boxes.

    It may not help, in the short term, but I think you need to consider switching to a server based back end, do most of you r data accessing & processing there and pump the minimum information accross the network. The issue is going to be that to get the benefits of usign a server you may have to rewrite soem or all of your access application.
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    clear! - you need a decent SQL-serving machine and a total rewrite of the application to optimise the exploitation of the SQL-serving machine.

    meanwhile

    how about something stupidly simple: for non-edit users, the front end file-copies the backend .MDB to the frontend machine. compared with all the multi-user .LDB fuss of trying to share a "DOS" file that was not designed to be shared, the stupid copy will be relatively fast.

    the FE links to the local copy of course.

    no change required to the FE except what it links to
    (use a loader .MDE to ask the user Yes/No download a new backend, download if requested, then start the "real" FE)


    another thing i do often - not from an access BE tho - is schedule e-mail to the users with an appropriate .CSV data extract. (the full client details are sordid: autodelete the mail with an Outlook rule, make code that scoops up the the attached .CSV from the most recently deleted mail from sender:X with subject:Y when the user clicks <Update>)
    the users are used to e-mail timing so they really don't "see" any delay when the .CSV dumps to their deleted folder (and the .CSV extract is way smaller than a full .MDB anyway). true zero-perceived-delay, hard-core-read-only, (but totally faked) client/server database.

    izy
    currently using SS 2008R2

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Yep - a disconnected, server based BE will defo be an improvement.

    But before you spend however long it takes to learn to make good use of SQL Server, plus the licensing cost and the rewriting your entire application - check out the physical network limitations because those limitiations will remain even after six months of rewriting. The server set up will, if done correctly, reduce the traffic and increase the processing load on the server however this may not be enough.

    We have had fantastic, cost effective results using thin client technology. This will, in addition, extend to everything else you do across the network.

    Basically - investigate all options before leaping in.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Jan 2006
    Location
    Washington, D.C
    Posts
    29

    Angry Thank You All!!!

    I have rewritten my code so my functions access the tables a limited amount of times. On my computer the program runs super fast. Of course I am located at headquarters so it's only a small number of hops my program has to make to access the tables. Is it possible for them to put this database in a location that will make it run a little faster?

    Pootle: unfortunately I don't know that LAN specs. I am a contractor for a government agency and you know how that goes; they give you limited input on anything and expect you to know everything. It took me a while but I figured out that the problem was the network. This application will be used by different offices across the nation. So it looks like they will have no choice but to get a server or use an existing one that will be the most reliable. I will ask them what type of client technology they are using.

    I averaged about 1000 lines of code a week to put this app together. It has taken me about 8 weeks to complete. Can you give my some ideas for the backend so when I go talk to theses people I sound like I know what I am talking about.

    Healdem: This whole application is written in VBA so If they did change the backend to let’s say MS-SQL. What changes would I have to make to the front end? What if they don’t want to use any different technology? What if they want to whole thing is access? I was told that my application was only going to be used for three months!??!?!?!? Needed less to say I was pissed. They rushed the hell out of me and then there not even going to use it. It kills me because it took them 6 months to create a spreadsheet to do the calculations and they get mad when it takes me more than a month.

    Izyrider: The only problem is that all the people who use this app have the ability to edit. There are no no-edit users. They also want the calculations done in real time.

    Thank you all for your comments. It seems that I have two solutions. One change the backend of this application. Two uses local tables that update during the application open and close. Which means a whole lot more programming? Well thank all. Any more info would be nice.

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hmmm. The big problem is the amount of rewriting you would need to do. Upsizing the db to SQL Server won't really do much unless you take advantage of the power of the box the database is sat on and rewrite your app to put as much of the processing server side as you can. Don't get me wrong - I only use SQL Server as a BE and I am a big fan however you need to do a fair bit of extra work to reap the benefits.

    This is for 3 months only - Are your users only adding data or are there a lot of edits of existing data too? The former might be your God send.

    Low effort stuff you can do now:
    Reduce the number of trips to the server to a minimum and pull down ONLY the data you will need. NEVER use SELECT *, try to put as many restrictions into the WHERE clause as you can. Maybe try to get them to work on a record\ small group of records at a time. If you have some static, lookup tables - download the data locally at the beginning of the session and use these local tables rather than constantly querying the back end just to populate a fixed combo box.

    Quote Originally Posted by Izy
    the full client details are sordid
    Lol
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Another Q:
    I presume that your users each have a local copy of the FE sat locally on their workstation right?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    eh?
    Quote Originally Posted by alaoa
    It only accesses the table to read the data never to update or delete.
    does not seem to tie up with
    Quote Originally Posted by alaoa
    Izyrider: The only problem is that all the people who use this app have the ability to edit. There are no no-edit users.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  10. #10
    Join Date
    Jan 2006
    Location
    Washington, D.C
    Posts
    29
    Ok let me clarify. The users have the ablitly to edit data. There will hardly be any adds moslty edits. However the part that runs really slow is the the creation of the reoprts and they are read only. I have to perform a lot of calculations which requires me to access data from the database a lot. I tried to curve this by opening the database only once and filtering the data that I needed. I tried to use getrows but it didn't seem to be working right. The biggest table in the database has less than 300 records. I talk to the network guy and he said that it's really slow for users not at headquarters

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by alaoa
    The users have the ablitly to edit data. There will hardly be any adds moslty edits.
    Fair enough.
    Quote Originally Posted by alaoa
    However the part that runs really slow is the the creation of the reoprts and they are read only. I have to perform a lot of calculations which requires me to access data from the database a lot. I tried to curve this by opening the database only once and filtering the data that I needed. I tried to use getrows but it didn't seem to be working right. The biggest table in the database has less than 300 records. I talk to the network guy and he said that it's really slow for users not at headquarters
    Hmm - something doesn't sound right here. Would you mind posting the SQL and the code for the slowest report?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  12. #12
    Join Date
    Jan 2006
    Location
    Washington, D.C
    Posts
    29
    Ok here is a report that reads data from the tables and inputs calculations into a local table.
    Code:
    Option Compare Database
    Option Explicit
    Public ReportClose As Integer
    Private Sub Report_Open(Cancel As Integer)
    Dim rst As ADODB.Recordset
    Dim sSql, test As String
    Dim monthCount As Integer
    Dim i As Integer
    Dim regionCount As Integer
    Dim theYear, theMonth, stringDate As String
    Dim theDate As Date
    Dim compute As ModeCalculate
    Dim GraphObj As Object
    Dim stringSearch As String
    Dim pos As Integer
    Dim currentDept As String
    Dim Fiscal As Date
    
       ReportCaption.Caption = Trim(Left$(Form_Admin_Report.sCaption, Len(Form_Admin_Report.sCaption) - 1))
       
       If Form_Admin_Report.dCaption <> "" Then
          ReportCaption2.Caption = Trim(Left$(Form_Admin_Report.dCaption, Len(Form_Admin_Report.dCaption) - 1))
       Else
          ReportCaption2.Visible = False
       End If
    
       sSql = "SELECT * FROM UsedFTE"
       
       DoCmd.RunMacro "DeleteUsedFTE"
    
       i = 0
       Set rst = New ADODB.Recordset
       Set compute = New ModeCalculate
       
       rst.open sSql, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
       
       Form_Admin_Report.setProgressBar 11 * 12
       Fiscal = GetFiscal
       
       For regionCount = 1 To 11
          For monthCount = 1 To 12
             If ((monthCount = 10 Or monthCount = 11) Or monthCount = 12) Then
                theYear = DatePart("yyyy", Fiscal)
             Else
                theYear = DatePart("yyyy", Fiscal) + 1
             End If
             
             theDate = DateSerial(theYear, monthCount, 1)
             theMonth = format(theDate, "mmm, dd yyyy")
             theMonth = Left$(theMonth, InStr(theMonth, ",") - 1)
             
             stringSearch = "Region " & regionCount & ","
             
             If InStr(Form_Admin_Report.sCaption, stringSearch) Then
                If Form_Admin_Report.dCaption = "" Then
                   rst.AddNew
                      rst!Region = regionCount
                      rst!Dept = "N/A"
                      rst!Month = theMonth
                      rst!FTEUsed = FormatNumber(compute.FTEWeightProjByMonth(regionCount, CInt(theYear), theDate), _
                                           2)
                      rst!StartofMonth = theDate
                   rst.Update
                Else
                   stringSearch = Trim(Mid$(Form_Admin_Report.dCaption, 2, Len(Form_Admin_Report.dCaption)))
                   
                   Do While InStr(stringSearch, ",")
                   
                      currentDept = Mid$(stringSearch, 8, InStr(stringSearch, ",") - 9)
    'This is where the calculations are made and inserted into the local table             
                         rst.AddNew
                         rst!Region = regionCount
                         rst!Dept = currentDept
                         rst!Month = theMonth
                         rst!FTEUsed = FormatNumber(compute.FTEWeightProjByMonth(regionCount, CInt(theYear), theDate, currentDept), _
                                           2)
                         rst!StartofMonth = theDate
                      rst.Update
                       
                      stringSearch = Trim(Mid$(stringSearch, InStr(stringSearch, ",") + 1, Len(stringSearch)))
                     ' MsgBox stringSearch
                   Loop
                   
    
                End If
    
             End If
            
            Form_Admin_Report.progressBar
            If ReportClose = True Then
               Cancel = True
               Form_Admin_Report.closeProgressBar
               Exit Sub
            End If
          Next monthCount ' monthcount
              ' progress.Caption = progress.Caption & "-"
          If ReportClose = True Then
             Cancel = True
             Form_Admin_Report.closeProgressBar
             Exit Sub
          End If
       Next regionCount 'regioncount
       
       rst.Close
       Form_Admin_Report.closeProgressBar
       
    End Sub
    Public Function setCaption(value As String)
       ReportCaption.Caption = value
    End Function
    This is the class that does the calculations i.e compute As ModeCalculate
    Code:
    Option Compare Database
    Option Explicit
    Private start As Date
    Private First As Date
    Private Leave As ADODB.Recordset
    Private EOS As ADODB.Recordset
    Private Emp As ADODB.Recordset
    
    Public Function FTESingleToProjByMonth(empId As Integer, currentYear As Integer, _
                                           thisDate As Date) As Single
       Dim VH As ADODB.Recordset
       Dim dbs As Database
       Dim First As Date
       Dim one_year As Date
       Dim last_day As Date
       Dim num As Integer
       Dim periodSoFar As Single
       Dim hoursPerYear As Integer
       Dim sSql As String
       Dim empStart As Date
       Dim Hours As Integer
       Dim final As Single
       Dim perYear As Single
       Dim hoursWorked As Single
          
       ' Filters the database to find an employee's and fiscal information
       Leave.Filter = "EmployeeID = " & empId & " And Year = " & currentYear
       EOS.Filter = "EmployeeID = " & empId & " AND Date < #" & thisDate & "#"
       Emp.Filter = "FK = " & empId
       
       empStart = Emp!StartDate
       
       Hours = Emp!Hours
          
       last_day = DateAdd("m", 12, start) - 1
    
       perYear = PPYear(start)
       
       hoursPerYear = 80 * perYear
          
       If EOS.RecordCount = 0 Then
          If empStart < start Then
             periodSoFar = (((last_day) - (start)) / 14)
          Else
             periodSoFar = (((last_day) - (empStart)) / 14)
          End If ' end emp
           
      ElseIf EOS.RecordCount = 1 Then
         periodSoFar = (((EOS!Date) - (start)) / 14)
      End If 'eos.recordcount
                 
       If Leave.RecordCount = 1 Then
          final = ((periodSoFar * Hours) - Leave!Hours) / hoursPerYear
       ElseIf Leave.RecordCount > 1 Then
          
          Leave.MoveFirst
          hoursWorked = (periodSoFar * Hours) - Leave!Hours
          Leave.MoveNext
          
          Do While Leave.EOF
             hoursWorked = hoursWorked - Leave!Hours
             Leave.MoveNext
          Loop
          
          final = hoursWorked / hoursPerYear
       Else
          final = (periodSoFar * Hours) / hoursPerYear
       End If ' end leave
        FTESingleToProjByMonth = final
    End Function
    Public Function FTEWeightProjByMonth(RegionID As Integer, currentYear As Integer, thisDate As Date, _
                                         Optional DeptID As String) As Single
       Dim rst As ADODB.Recordset
       Dim sSql As String
       Dim test As Integer
       Set rst = New ADODB.Recordset
       Dim sum As Single
       
       test = 0
       sum = 0
       
       If DeptID = "" Then
          sSql = "SELECT * FROM Employee WHERE Employee.RegionID = " & RegionID
          rst.open sSql, CurrentProject.Connection, adOpenForwardOnly
          
          Do While Not rst.EOF
             sum = sum + FTESingleToProjByMonth(rst!FK, currentYear, thisDate)
             rst.MoveNext
          Loop
          
       Else
          sSql = "SELECT * FROM Employee WHERE Employee.RegionID = " & RegionID & _
                 " AND Employee.DeptID = " & quotedStr(DeptID)
          'MsgBox sSql
          rst.open sSql, CurrentProject.Connection, adOpenForwardOnly
          
          Do While Not rst.EOF
             sum = sum + FTESingleToProjByMonth(rst!FK, currentYear, thisDate)
             rst.MoveNext
          Loop
          rst.Close
                
       End If
       
       rst.Close
       FTEWeightProjByMonth = sum
    End Function
    Public Function findPP(thisDate As Date) As Integer
       Dim pos As Integer
       Dim sSql As String
       Dim fis As DAO.Recordset
       Dim dbs As Database
       Dim days As Integer
       Dim roundnumber As Single
       Dim numbertest As Single
       Dim NumString As String
       Dim DecimalPart As Single
       Dim NumPart As Integer
       Dim PayPeriod As Integer
       
       days = thisDate - First
       
       If days < 14 Then
          PayPeriod = 1
       ElseIf days = 14 Then
          PayPeriod = 2
       Else
          roundnumber = (days / 14) + 1
          
          NumString = CStr(roundnumber)
          
          pos = InStr(NumString, ".")
           
          If pos <> 0 Then
             PayPeriod = CInt(Mid$(NumString, 1, pos - 1))
             'DecimalPart = CSng(Mid$(NumString, pos, Len(NumString)))
          Else
             PayPeriod = CInt(NumString)
          End If
          
       End If
       
       findPP = PayPeriod
       
     End Function
    Public Function PPYear(thisDate As Date) As Single
       Dim last_day As Date
       Dim one_year As String
       Dim perYear As Single
       Dim num As Integer
       Dim testme As Integer
       Dim endYear As Date
       Dim holdDate As String
       
       last_day = DateAdd("m", 12, thisDate) - 1
       num = DateDiff("d", thisDate, last_day, vbSunday, vbFirstJan1)
       
       PPYear = num / 14
    End Function
    Public Function GetStart(pay As Integer)
      
        GetStart = First + (14 * (pay - 1))
       
     End Function
    Private Sub Class_Initialize()
    Dim sSql As String
    Dim dbs As Database
    Dim fis As DAO.Recordset
    
       start = GetFiscal
       
       Set dbs = CurrentDb
       sSql = "SELECT * FROM Fiscal"
       Set fis = dbs.OpenRecordset(sSql, dbOpenDynaset)
       
       First = fis!FirstPayPeriod
       fis.Close
          
       sSql = "SELECT * FROM Leave"
       Set Leave = New ADODB.Recordset
       Leave.open sSql, CurrentProject.Connection, adOpenStatic, adLockOptimistic
    
       sSql = "SELECT * FROM Employee_Done"
       Set EOS = New ADODB.Recordset
       EOS.open sSql, CurrentProject.Connection, adOpenStatic, adLockOptimistic
       
       sSql = "SELECT * FROM Employee"
       Set Emp = New ADODB.Recordset
       Emp.open sSql, CurrentProject.Connection, adOpenStatic, adLockOptimistic
    '
    End Sub
    Private Sub Class_Terminate()
       Leave.Close
       EOS.Close
       Emp.Close
    End Sub

  13. #13
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Cheers

    A few questions:

    I presume then that UsedFTE is a local table? And what about Fiscal, Leave, Employee and Employee_Done?

    VH and dbs do not appear to be used in FTESingleToProjByMonth - correct?
    In the same method - is this correct (not relevent to the problem though):
    Code:
          Do While Leave.EOF
             hoursWorked = hoursWorked - Leave!Hours
             Leave.MoveNext
          Loop
    Also fis and dbs in findPP don't appear to be used either - correct?

    There are loops within loops within loops so I would expect there to be a delay however this runs super fast locally right?
    Actually - this question is even more important now I've seen the code:
    Quote Originally Posted by Poots
    I presume that your users each have a local copy of the FE sat locally on their workstation right?
    Last edited by pootle flump; 02-08-06 at 05:10.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  14. #14
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Bump.

    Above post saved but not recognised - the post count did not increment and the last Post date did not either.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  15. #15
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Minor in the extreme but:
    Code:
        theMonth = Format(theDate, "mmm, dd yyyy")
        theMonth = Left$(theMonth, InStr(theMonth, ",") - 1)
    could be more easily written (and read) as:
    Code:
        theMonth = MonthName(Month(theDate), True)
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •