Results 1 to 6 of 6
  1. #1
    tcox's Avatar
    tcox is offline Advanced Beginner
    Windows 7 64bit Access 2013
    Join Date
    Oct 2015
    Posts
    46

    Performance of access

    I'm about to build a very complex report that is going to cycle through a ton of queries.
    They cascade down from department, to equipment, to production code, to batch records.
    by my math, with current list of equipment, departments etc. The db will be hit with 880 individual queries to formulate the data.
    This report will be generating an html web page every time a new entry is made for equipment usage.
    (doing it this way not all management has to load up access to view the report, a quick easy link on the intranet)

    The question is: How is access going to handle this?
    My workstation is a core2 @ 3ghz and 4gb of ram.


    I've never had to measure the query throughput of a db.

    -Tevis

  2. #2
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    How is it going to handle it? My guess is very poorly.
    880 queries really jumps out at me. That really makes me question the design of the database. I am willing to bet that there may be some major design flaws here that should be corrected.
    Is the database Normalized (that is, have the tables been designed according to the Rules of Normalization)?

    Based on the complexity, it is possible that even after you correct everything, it may still be too much for Access to handle. In that case, you may need to incorporate the use of a more powerful database program, like SQL. In many cases, you can still use Access as the front-end, and just use SQL as the back-end to store the data tables. However, performance will be better if you can set up a lot of your queries as "Views" right in the SQL database as opposed to Queries in Access (especially the "heavier hitters").

  3. #3
    tcox's Avatar
    tcox is offline Advanced Beginner
    Windows 7 64bit Access 2013
    Join Date
    Oct 2015
    Posts
    46
    I'm fairly sure I have my db fairly well normalized, its the problem that management wants to know how much time each equipment spent in each part of a process. for a given period in production (day and week, month, quarterly)

    ex. # of departments * X#equipment * 22 time codes, summed up for each piece of equipment.

    I'm doing the report programmatically, so I'm recursively generating the queries, populate the variables or array (not decided yet) then output results into an css/html table.

    I already have a few pages generated like this.. but they were much simpler!
    example:
    (there is some jscript and .css that completes the web page)

    Code:
    Public Function CompletedWorkordershtm()
    'This Function outputs an HTM file that is called CompletedWorkorders.htm. It displays 50 most recent Complete batch records.
    'They are displayed in, as most recently updated.
    '
        Set fs = CreateObject("Scripting.FileSystemObject")
        Set a = fs.CreateTextFile(CurrentProject.Path & "\CompletedWorkorders.htm", True)
        
        'Declare Variables
        Dim brID(51) As Variant
        Dim brRecordNum As Integer
        brRecordNum = 0
        Dim db As DAO.Database
            
        'Variables for SQL strings
        Dim brSQL As String
        brSQL = "SELECT TOP 50 Batch_Record.ID, " _
                 & "Batch_Record.BRDate, " _
                 & "Batch_Record.ThorneID, " _
                 & "Batch_Record.ProductFormula, " _
                 & "Batch_Record.LastUpdate, " _
                 & "Batch_Record.CompletedDate " _
                 & "FROM Batch_Record " _
                 & "WHERE (((Batch_Record.Status) = ""COMPLETE""))" _
                 & "ORDER BY Batch_Record.LastUpdate DESC"
    
    
    Set db = CurrentDb
    Set BRrst = db.OpenRecordset(brSQL)
    
    
    a.WriteLine ("<!DOCTYPE html>")
    a.WriteLine ("<html>")
    a.WriteLine ("<head>")
    a.WriteLine ("<link rel=""stylesheet"" type=""text/css"" href=""cssfile.css"">")
    a.WriteLine (" <img src=""thorne.jpg"" align = ""right"" /> <H1>&nbsp Completed Workorders</h1>")
    a.WriteLine ("Generated: ")
    a.WriteLine (Now())
    a.WriteLine ("<br><div>")
    a.WriteLine ("<script src=""navbar.js"" type=""text/javascript""> </script>")
    a.WriteLine ("</div>")
    a.WriteLine ("</head>")
    a.WriteLine ("<body>")
    
    
    If BRrst.RecordCount > 0 Then
    BRrst.MoveFirst
    Do Until BRrst.EOF
        'Make sure we dont over run the array
        If brRecordNum < 100 Then
        brID(brRecordNum) = BRrst.Fields(0)
        a.WriteLine ("<table>")
        a.WriteLine ("<tr><td>&nbsp<td></tr>")
        a.WriteLine ("<tr><td>Thorne ID &nbsp <b>" & BRrst.Fields(2) & "</b></td><tr>")
        a.WriteLine ("<tr><td>Formula &nbsp&nbsp&nbsp&nbsp  <b>" & BRrst.Fields(3) & "</b></td><tr>")
        a.WriteLine ("<tr><td>Date Entered &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp" & Format(BRrst(1), "mm/dd/yyyy") & "</td></tr>")
        a.WriteLine ("<tr><td>Date Completed &nbsp" & Format(BRrst(5), "mm/dd/yyyy") & "</td></tr>")
        a.WriteLine ("<tr><td>&nbsp &nbsp &nbsp &nbsp&nbsp&nbsp&nbsp&nbsp  Updated &nbsp" & Format(BRrst(4), "mm/dd/yyyy  HH:MM:SS") & "</td></tr>")
        a.WriteLine ("</table>")
        a.WriteLine ("<table>")
           'create the SQL queries for Dashboard output.
            yldSQL = "SELECT Batch_Record_Details_Yield.BatchRecord_FK, " _
                          & "Batch_Record_Details_Yield.Department, " _
                          & "Batch_Record_Details_Yield.Units, " _
                          & "Batch_Record_Details_Yield.Start_Units, " _
                          & "Batch_Record_Details_Yield.End_Units, " _
                          & "Batch_Record_Details_Yield.Yield " _
                   & "FROM Batch_Record_Details_Yield " _
                   & "WHERE (((Batch_Record_Details_Yield.BatchRecord_FK)=" & brID(brRecordNum) & "))"
                   
             eqSQL = "SELECT Batch_Record_Details_Equipment.Department, " _
                          & "Batch_Record_Details_Equipment.Equipment, " _
                          & "Batch_Record_Details_Equipment.KPI_Code, " _
                          & "Batch_Record_Details_Equipment.Start_Time, " _
                          & "Batch_Record_Details_Equipment.End_Time, " _
                          & "Batch_Record_Details_Equipment.tTime " _
                   & "FROM Batch_Record_Details_Equipment " _
                   & "WHERE (((Batch_Record_Details_Equipment.BatchRecord_FK)=" & brID(brRecordNum) & "))"
    
    
          prsnlSQL = "SELECT Batch_Record_Details_Personnel.Department, " _
                          & "Batch_Record_Details_Personnel.Employee, " _
                          & "Batch_Record_Details_Personnel.KPICode, " _
                          & "Batch_Record_Details_Personnel.Start_Time, " _
                          & "Batch_Record_Details_Personnel.End_Time, " _
                          & "Batch_Record_Details_Personnel.tTime " _
                    & "FROM Batch_Record_Details_Personnel " _
                    & "WHERE (((Batch_Record_Details_Personnel.BatchRecord_FK)=" & brID(brRecordNum) & "))"
                    
                          
            'Begin Yield Dashboard Output
            Set YLDrst = db.OpenRecordset(yldSQL)
            Set EQrst = db.OpenRecordset(eqSQL)
            Set PRSNLrst = db.OpenRecordset(prsnlSQL)
            
            'Test to see if there are records, if so, output them
            Dim OverallYield As Double
            OverallYield = 1
            If YLDrst.RecordCount > 0 Then
               YLDrst.MoveFirst
               
               
               'Table Header
               a.WriteLine ("<tr><td id = """ & "heading" & """ ><b>Yield</b></td></tr><tr><th>Department</th><th>Start Units</th><th>End Units</th><th>Yield %</th><th></th><th></th></tr>")
                
                    Do Until YLDrst.EOF
                    OverallYield = OverallYield * YLDrst(5)
                    a.WriteLine ("<tr><td>" & YLDrst(1) & "</td><td>" & Format(YLDrst(3), "#,###,###") & YLDrst(2) & "</td><td>" & Format(YLDrst(4), "#,###,###") & YLDrst(2) & "</td><td>" & FormatPercent(YLDrst(5)) & "</td></tr>")
                    YLDrst.MoveNext
                    Loop
                    a.WriteLine ("<tr><td>  </td><td> </td><td id = """ & "total" & """ &><u>Overall Yield</td><td id = """ & "total" & """ &><u>" & FormatPercent(OverallYield) & "</u></td><tr>")
            End If
    
    
           Dim TotalHrs As Double
           Dim dMixing As Double
           Dim dEncap As Double
           Dim dPackaging As Double
           Dim dPowderfill As Double
           Dim dSorting As Double
           
           dMixing = 0
           dEncap = 0
           dPackaging = 0
           dPowderfill = 0
           dSorting = 0
           
           'Test to see if there are records, if so, output them
           If EQrst.RecordCount > 0 Then
              EQrst.MoveFirst
              'Table header
              a.WriteLine ("<tr><td id = """ & "heading" & """ ><b>Equipment</b></td></tr><tr><th>Department</th><th>Equipment</th><th>KPI Code</th><th>Start Time</th><th>End Time</th><th>Total Time</th></tr>")
              
              TotalHrs = 0
            
                    Do Until EQrst.EOF
                        If EQrst(0) = "Mixing" Then
                        dMixing = dMixing + EQrst(5)
                        End If
                        If EQrst(0) = "Encap" Then
                        dEncap = dEncap + EQrst(5)
                        End If
                        If EQrst(0) = "Packaging" Then
                        dPackaging = dPackaging + EQrst(5)
                        End If
                        If EQrst(0) = "Powderfill" Then
                        dPowderfill = dPowderfill + EQrst(5)
                        End If
                        If EQrst(0) = "Sorting" Then
                        dSorting = dSorting + EQrst(5)
                        End If
                        a.WriteLine ("<tr><td>" & EQrst(0) & "</td><td>" & EQrst(1) & "</td><td>" & EQrst(2) & "</td><td>" & Format(EQrst(3), "mm/dd/yyyy  HH:MM") & "</td><td>" & Format(EQrst(4), "mm/dd/yyyy  HH:MM") & "</td><td>" & Format(EQrst(5), "0.00") & "</td><tr>")
                        If EQrst(5) > 0 Then
                        TotalHrs = TotalHrs + EQrst(5)
                            End If
                        EQrst.MoveNext
                    Loop
                    'Output total hours
                    a.WriteLine ("<tr><td></td><td> </td><td> </td><td> </td></td><td><u>Total Mixing Time</td><td><u>" & Format(dMixing, "0.00") & "</u></td><tr>")
                    a.WriteLine ("<tr><td></td><td> </td><td> </td><td> </td></td><td><u>Total Encap Time</td><td><u>" & Format(dEncap, "0.00") & "</u></td><tr>")
                    a.WriteLine ("<tr><td></td><td> </td><td> </td><td> </td></td><td><u>Total Sorting Time</td><td><u>" & Format(dSorting, "0.00") & "</u></td><tr>")
                    a.WriteLine ("<tr><td></td><td> </td><td> </td><td> </td></td><td><u>Total Packaging Time</td><td><u>" & Format(dPackaging, "0.00") & "</u></td><tr>")
                    a.WriteLine ("<tr><td></td><td> </td><td> </td><td> </td></td><td><u>Total Powderfill Time</td><td><u>" & Format(dPowderfill, "0.00") & "</u></td><tr>")
                    a.WriteLine ("<tr><td></td><td> </td><td> </td><td> </td><td id = """ & "total" & """ &><u>Run Total Hrs</td><td id = """ & "total" & """ &><u>" & Format(TotalHrs, "0.00") & "</u></td><tr>")
    
    
           End If
           
           'Reset hour counters
           dMixing = 0
           dEncap = 0
           dPackaging = 0
           dPowderfill = 0
           dSorting = 0
           
           'Test to see if there are records, if so, output them
           If PRSNLrst.RecordCount > 0 Then
              PRSNLrst.MoveFirst
              'Table header
              a.WriteLine ("<tr><td id = """ & "heading" & """><b>Personnel</b></td></tr><tr><th>Department</th><th>Employee</th><th>KPI Code</th><th>Start Time</th><th>End Time</th><th>Total Time</th></tr>")
              TotalHrs = 0
                    Do Until PRSNLrst.EOF
                        If PRSNLrst(0) = "Mixing" Then
                        dMixing = dMixing + PRSNLrst(5)
                        End If
                        If PRSNLrst(0) = "Encap" Then
                        dEncap = dEncap + PRSNLrst(5)
                        End If
                        If PRSNLrst(0) = "Packaging" Then
                        dPackaging = dPackaging + PRSNLrst(5)
                        End If
                        If PRSNLrst(0) = "Powderfill" Then
                        dPowderfill = dPowderfill + PRSNLrst(5)
                        End If
                        If PRSNLrst(0) = "Sorting" Then
                        dSorting = dSorting + PRSNLrst(5)
                        End If
                        a.WriteLine ("<tr><td>" & PRSNLrst(0) & "</td><td>" & PRSNLrst(1) & "</td><td>" & PRSNLrst(2) & "</td><td>" & Format(PRSNLrst(3), "mm/dd/yyyy  HH:MM") & "</td><td>" & Format(PRSNLrst(4), "mm/dd/yyyy  HH:MM") & "</td><td>" & Format(PRSNLrst(5), "0.00") & "</td><tr>")
                        If PRSNLrst(5) > 0 Then
                        TotalHrs = TotalHrs + PRSNLrst(5)
                        End If
                        PRSNLrst.MoveNext
                    Loop
                    'Output total hours
                    a.WriteLine ("<tr><td></td><td> </td><td> </td><td> </td></td><td><u>Total Mixing Time</td><td><u>" & Format(dMixing, "0.00") & "</u></td><tr>")
                    a.WriteLine ("<tr><td></td><td> </td><td> </td><td> </td></td><td><u>Total Encap Time</td><td><u>" & Format(dEncap, "0.00") & "</u></td><tr>")
                    a.WriteLine ("<tr><td></td><td> </td><td> </td><td> </td></td><td><u>Total Sorting Time</td><td><u>" & Format(dSorting, "0.00") & "</u></td><tr>")
                    a.WriteLine ("<tr><td></td><td> </td><td> </td><td> </td></td><td><u>Total Packaging Time</td><td><u>" & Format(dPackaging, "0.00") & "</u></td><tr>")
                    a.WriteLine ("<tr><td></td><td> </td><td> </td><td> </td></td><td><u>Total Powderfill Time</td><td><u>" & Format(dPowderfill, "0.00") & "</u></td><tr>")
                    a.WriteLine ("<tr><td></td><td>  </td><td> </td><td> </td><td id = """ & "total" & """ &><u>Personnel Total Hrs</td><td id = """ & "total" & """ &><u>" & Format(TotalHrs, "0.00") & "</u></td><tr>")
           End If
            
        a.WriteLine ("<tr><td><hr width = 105%></td><td><hr width = 105%></td><td><hr width = 105%></td><td><hr width = 105%></td><td><hr width = 105%></td></tr>")
        BRrst.MoveNext
        brRecordNum = brRecordNum + 1
        
        End If
    Loop
    End If
    
    
    a.WriteLine ("</table>")
    a.WriteLine ("</html>")
    
    
    BRrst.Close
    
    
    Set BRrst = Nothing
    db.Close
    Set db = Nothing
    
    
    'close html file
    a.Close
    End Function

  4. #4
    tcox's Avatar
    tcox is offline Advanced Beginner
    Windows 7 64bit Access 2013
    Join Date
    Oct 2015
    Posts
    46
    Thought I would show you what the output looked like.
    (all data is dummy data)
    Click image for larger version. 

Name:	CompletedWorkorders.jpg 
Views:	12 
Size:	262.9 KB 
ID:	23283

  5. #5
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    Without seeing your entire database structure, it is difficult to really determine if there are better ways of doing things or not. But I definitely can see how this could be problematic for Access. So the best advice I can give is as follows (I have had to do the following on some large Access databases myself):
    - split your database, if not done already, into a separate front-end and back-end
    - move the back-end to SQL
    - trying to take advantage of the power of SQL by putting a lot of your queries as Views directly in SQL, and maybe make use of SQL stored procedures for certain updates

    Hope that helps.

  6. #6
    tcox's Avatar
    tcox is offline Advanced Beginner
    Windows 7 64bit Access 2013
    Join Date
    Oct 2015
    Posts
    46
    As a test, I can confirm that access can handle a couple hundred queries in a flash. db only has dummy data, not loaded with production data so I will see how it performs with production data during its testing.. maybe by then I will have learned enough .NET to migrate it..

    I think that it helps that my queries are very specific for their results.

    I'll post back with results of Access's performance as I continue to test and push it over the next few weeks.
    -Tevis

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Access 2010 Performance
    By EddieN1 in forum Access
    Replies: 31
    Last Post: 02-03-2015, 09:00 PM
  2. Replies: 8
    Last Post: 06-29-2014, 08:01 PM
  3. Replies: 2
    Last Post: 08-29-2013, 01:19 PM
  4. Replies: 5
    Last Post: 11-17-2011, 03:04 PM
  5. Replies: 1
    Last Post: 11-17-2010, 08:18 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums