Results 1 to 14 of 14

Thread: Access(mdb) Performance Issue

  1. #1

    Thread Starter
    Member
    Join Date
    Feb 2006
    Location
    Sydney, Australia
    Posts
    42

    Access(mdb) Performance Issue

    Hi All,

    I have found some interesting performance issues with a legacy application that uses an Access(mdb) database which is located on the local hard drive. When the main form performs an action(change filter, etc) changes are made to the data (various processing steps)and the script below has performance issues sometimes.

    • The sample script below sometimes takes 0.1 of a second then it can take 30 seconds other times
    • Checking the query in Access is always very quick
    • Compacting does fix some times but it will occur again shortly (note can be slow in .Net but checking in Access is quick even without compacting)
    • Started with a new database & re-created but still no difference
    • Note 'FieldNoIndex' has no index because this application changes the filter to various fields so we can't apply an index on all fields
    • Note it will be moved to SQL Server but at this stage, we must use Access


    Has anyone experienced this before or has any insights?

    -- Application Details --
    Table: Data: 12,000 rows
    Table: ITM: 8,000
    Framework 4.52

    Code:
    Public Sub TestDatatable()
            Dim SQL As String = " Select  Field1 From  ITM INNER JOIN 
                                DATA ON  (ITM.Site = DATA.Site) AND (ITM.Item = DATA.Item) 
                            Where  ITM.Site = 'Site1' AND  ITM.FieldNoIndex = 'Item1'"
    
            Dim dt As New DataTable("Table1")
    
            Using da As New OleDbDataAdapter(SQL, _Conn)
                ' Fill table
                da.Fill(dt)
            End Using
        End Sub
    Thanks,
    Adam.

  2. #2
    Superbly Moderated NeedSomeAnswers's Avatar
    Join Date
    Jun 2002
    Location
    Manchester uk
    Posts
    2,660

    Re: Access(mdb) Performance Issue

    Why wait?

    If your only using Access to store the data and have not built forms or used a lot of VBA in it, then just move your data to SQL Server asap.

    Access is notorious for performance issues, and you can get SQLExpress now for free, and save yourself a headache.

    Porting the data and tables to SQL Server is pretty straight forward and likey to take less time over all then you will spend trying to fix performance issues with Access.
    Please Mark your Thread "Resolved", if the query is solved & Rate those who have helped you



  3. #3

    Thread Starter
    Member
    Join Date
    Feb 2006
    Location
    Sydney, Australia
    Posts
    42

    Re: Access(mdb) Performance Issue

    Totally agree, we have 30k+ lines of code to migrate so it is not going to be an easy move.

  4. #4

    Thread Starter
    Member
    Join Date
    Feb 2006
    Location
    Sydney, Australia
    Posts
    42

    Re: Access(mdb) Performance Issue

    Just carried out some further testing and found that getting the data table is very slow after an update is carried out. I have also found that we keep the connection open all the time. If I close & re-open the connection getting the datatable is very quick as expected.
    Appears like the open connection is not getting access to the updated indexes and is causing the process to be very slow at a guess.

    Does this seem correct, not sure if it is index-related or something else Access is not updating back to the open connection? Are there any other options for updating the indexes to closing & re-opening the connection?

    Code:
    Public Sub TestDatatable()
                Dim SQLUpdate As String = 
    			"UPDATE [MPS tblITM] AS ITM SET ITM.FieldNoIndex = ITM.FieldNoIndex"
    
                 Using cmd As New OleDbCommand(SQLUpdate, Conn)
                    cmd.ExecuteNonQuery()
                End Using
    
    	' ** Get datatable next is very slow unless the connection is closed & re-openend
    
            Dim SQL As String = " Select  Field1 From  ITM INNER JOIN 
                                DATA ON  (ITM.Site = DATA.Site) AND (ITM.Item = DATA.Item) 
                            Where  ITM.Site = 'Site1' AND  ITM.FieldNoIndex = 'Item1'"
    
            Dim dt As New DataTable("Table1")
    
            Using da As New OleDbDataAdapter(SQL, _Conn)
                ' Fill table
                da.Fill(dt)
            End Using
    End Sub

  5. #5
    Superbly Moderated NeedSomeAnswers's Avatar
    Join Date
    Jun 2002
    Location
    Manchester uk
    Posts
    2,660

    Re: Access(mdb) Performance Issue

    Totally agree, we have 30k+ lines of code to migrate so it is not going to be an easy move.
    Ah so its not just the data then.

    I have also found that we keep the connection open all the time. If I close & re-open the connection getting the datatable is very quick as expected.
    Appears like the open connection is not getting access to the updated indexes and is causing the process to be very slow at a guess.
    Hmm with any decent database the advice would be to open and close your connection around each db operation and never keep the connection alway open.

    For Access, the standard advice is to always keep the connection open as there is a greater cost of opening and creating the lock file.

    How often are you changing / updating indexes ? is there anyway you can allow them to check for updated indexes and if there are any refresh the connection maybe?
    Please Mark your Thread "Resolved", if the query is solved & Rate those who have helped you



  6. #6
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,042

    Re: Access(mdb) Performance Issue

    @adam
    sure looks funny the way you create a Index in the Table

    here a sample how to create Table(s) with Foreign Key and Index

    Note: the .mdb has to exist
    Code:
     Public Sub MakeTables()
            Dim sSQL As String
            'the Database 'Contacts.mdb' has to exist already
            Dim con As OleDbConnection = New OleDbConnection("Provider=Microsoft.jet.oledb.4.0;data source=E:\Contacts.mdb")
            con.Open()
    
    
    
            'create your Table tbl_Employee
            sSQL = " Create Table tbl_Employee"
            sSQL &= "( [EM_ID] AutoIncrement CONSTRAINT PRIMARYKEY PRIMARY KEY"
            sSQL &= ", [EM_Firstname] varChar(50)"
            sSQL &= ", [EM_Lastname] varChar(50) CONSTRAINT EM_Lastname UNIQUE"
            sSQL &= ")"
            ExecuteSQL(con, sSQL)
    
            sSQL = " Create Table tbl_Contacts"
            sSQL &= "( [CO_ID] AutoIncrement CONSTRAINT PRIMARYKEY PRIMARY KEY"
            sSQL &= ", [FK_ID] Int NOT NULL"
            sSQL &= ", [CO_Firstname] varChar(50)"
            sSQL &= ", [CO_Lastname] varChar(50) CONSTRAINT CO_Lastname UNIQUE"
            sSQL &= ")"
            ExecuteSQL(con, sSQL)
    
            'alter Table create a Foreign Key
            sSQL = "ALTER TABLE tbl_Contacts "
            sSQL &= " ADD FOREIGN KEY (FK_ID) REFERENCES tbl_Employee(EM_ID)"
            ExecuteSQL(con, sSQL)
    
    
            'create a Index
            sSQL = "Create Index [PosDat] On [tbl_Contacts] (CO_Lastname Asc)"
            ExecuteSQL(con, sSQL)
    
            'add some Data
            sSQL = "Insert Into tbl_Employee (EM_Firstname,EM_Lastname)Values('ChrisE','Test')"
            ExecuteSQL(con, sSQL)
            sSQL = "Insert Into tbl_Employee (EM_Firstname,EM_Lastname)Values('John','Doe')"
            ExecuteSQL(con, sSQL)
            sSQL = "Insert Into tbl_Contacts (CO_Firstname,CO_Lastname,FK_ID)Values('Mary','Poppins',1)"
            ExecuteSQL(con, sSQL)
            sSQL = "Insert Into tbl_Contacts (CO_Firstname,CO_Lastname,FK_ID)Values('Joe','Dipp',2)"
            ExecuteSQL(con, sSQL)
    
    
            con.Close()
            con = Nothing
        End Sub
    
        Public Function ExecuteSQL(ByVal Con As OleDb.OleDbConnection, _
                                         ByVal sSQL As String, _
                                         Optional ByRef ErrMessage As String = Nothing, _
                                         Optional ByVal TransAction As  _
                                         OleDb.OleDbTransaction = Nothing) As Integer
            ErrMessage = Nothing
            Try
                Dim Result As Integer = 0
                Using Cmd As New OleDb.OleDbCommand(sSQL, Con, TransAction)
                    Result = Cmd.ExecuteNonQuery
                End Using
                Return Result
            Catch ex As Exception
                ErrMessage = ex.Message
                Return 0
            End Try
        End Function
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  7. #7

    Thread Starter
    Member
    Join Date
    Feb 2006
    Location
    Sydney, Australia
    Posts
    42

    Re: Access(mdb) Performance Issue

    Thanks for the details, I have indexes on some fields.

  8. #8

    Thread Starter
    Member
    Join Date
    Feb 2006
    Location
    Sydney, Australia
    Posts
    42

    Re: Access(mdb) Performance Issue

    Quote Originally Posted by NeedSomeAnswers View Post
    Ah so its not just the data then.



    Hmm with any decent database the advice would be to open and close your connection around each db operation and never keep the connection alway open.

    For Access, the standard advice is to always keep the connection open as there is a greater cost of opening and creating the lock file.

    How often are you changing / updating indexes ? is there anyway you can allow them to check for updated indexes and if there are any refresh the connection maybe?

    I agree to always keep the connection open with Access. We do quite a lot of operations so opening and closing will also deteriorate the performance. Interestingly this performance issue is only related to getting datatables such as the script shown. I have tested closing the connection before getting each datatable and the performance issue resolved. Note when there is no where clause in the SQL there are no performance issues. This leads me to think that after updating the data the indexes or similar is not correctly updated / available until the connection is closed & re-opened.

    My next step is to see if we can get the same refresh for the open connection without closing & opening, I have tried some of the available methods in the connection object but they have not helped.

    In relation to indexes, we only update / create indexes on temporary tables where applicable. We do have indexes on primary and / or key fields in standard tables. In Access I am not sure how to force this refresh via OLEDB, that could also be a solution.

    Thanks for the points.
    Adam.

  9. #9
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    38,988

    Re: Access(mdb) Performance Issue

    Quote Originally Posted by adam.syd View Post
    I agree to always keep the connection open with Access. We do quite a lot of operations so opening and closing will also deteriorate the performance.
    Adam.
    Are you sure about that?

    .NET uses connection pooling, which means that you aren't totally destroying the connection even when you get rid of it. This is a very efficient process, because it kind of has to be. In multi-user scenarios, there could be numerous applications working with the database, so keeping all the connections open throughout would be pretty bad. Eventually, it would fail completely. Therefore, the typical way to manage connections is to open it, use it, close it, all in a Using block which will clean it up. I have applications that might do that dozens of times in a row (in different parts of the program, to be sure), and there just isn't any particular issue with it.
    My usual boring signature: Nothing

  10. #10
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    38,988

    Re: Access(mdb) Performance Issue

    Just did a quick test using this code:

    Code:
       Private Sub test1()
            Dim x As Integer
            Dim n As Long = 0
            Dim lGD As TestGUID = Nothing
            Dim a As Integer = 1
            Dim b As Integer = 1
            Dim c As Integer = 1
    
            For x = 0 To 10
                Using cn As New SqlClient.SqlConnection(mConString)
                    Using cmd As SqlClient.SqlCommand = cn.CreateCommand
                        cn.Open()
                        cmd.CommandText = "SELECT * FROM GEN_Configuration"
                        Dim obj = cmd.ExecuteScalar
                    End Using
                End Using
            Next
    
        End Sub
    
        Private Sub test2()
            Dim x As Integer
            Dim n As Long = 0
            Dim lGD As TestGUID = Nothing
            Dim a As Integer = 1
            Dim b As Integer = 1
            Dim c As Integer = 1
    
            Using cn As New SqlClient.SqlConnection(mConString)
                Using cmd As SqlClient.SqlCommand = cn.CreateCommand
                    cn.Open()
                    For x = 0 To 10
                        cmd.CommandText = "SELECT * FROM GEN_Configuration"
                        Dim obj = cmd.ExecuteScalar
                    Next
                End Using
            End Using
        End Sub
    In Test1, I open and close the connection every time through the loop. In Test2, I open the connection, then perform just the query inside the loop. So, the second test shows keeping the connection open for the duration of the test, while the first test shows opening a new connection each iteration.

    The results show that the second test is probably faster than the first, but not clearly so. The cost of each can vary wildly on this system, with a variation range of at least 100ms. After running the two several times, I feel that Test 2 was slightly more likely to be faster, but it was a near thing. Test1 was faster almost half the time, so if there is an advantage to keeping the connection open, it's going to boost performance on ALL the queries by a few milliseconds, total, at MOST. As a percentage, using an open connection is probably no more than 1 or 2% faster, and maybe not even that.
    My usual boring signature: Nothing

  11. #11

    Thread Starter
    Member
    Join Date
    Feb 2006
    Location
    Sydney, Australia
    Posts
    42

    Re: Access(mdb) Performance Issue

    Quote Originally Posted by Shaggy Hiker View Post
    Just did a quick test using this code:

    Code:
       Private Sub test1()
            Dim x As Integer
            Dim n As Long = 0
            Dim lGD As TestGUID = Nothing
            Dim a As Integer = 1
            Dim b As Integer = 1
            Dim c As Integer = 1
    
            For x = 0 To 10
                Using cn As New SqlClient.SqlConnection(mConString)
                    Using cmd As SqlClient.SqlCommand = cn.CreateCommand
                        cn.Open()
                        cmd.CommandText = "SELECT * FROM GEN_Configuration"
                        Dim obj = cmd.ExecuteScalar
                    End Using
                End Using
            Next
    
        End Sub
    
        Private Sub test2()
            Dim x As Integer
            Dim n As Long = 0
            Dim lGD As TestGUID = Nothing
            Dim a As Integer = 1
            Dim b As Integer = 1
            Dim c As Integer = 1
    
            Using cn As New SqlClient.SqlConnection(mConString)
                Using cmd As SqlClient.SqlCommand = cn.CreateCommand
                    cn.Open()
                    For x = 0 To 10
                        cmd.CommandText = "SELECT * FROM GEN_Configuration"
                        Dim obj = cmd.ExecuteScalar
                    Next
                End Using
            End Using
        End Sub
    In Test1, I open and close the connection every time through the loop. In Test2, I open the connection, then perform just the query inside the loop. So, the second test shows keeping the connection open for the duration of the test, while the first test shows opening a new connection each iteration.

    The results show that the second test is probably faster than the first, but not clearly so. The cost of each can vary wildly on this system, with a variation range of at least 100ms. After running the two several times, I feel that Test 2 was slightly more likely to be faster, but it was a near thing. Test1 was faster almost half the time, so if there is an advantage to keeping the connection open, it's going to boost performance on ALL the queries by a few milliseconds, total, at MOST. As a percentage, using an open connection is probably no more than 1 or 2% faster, and maybe not even that.
    Thanks for replying, your testing is for SQL client & I am using Access so there is no pooling as noted.

    I have tested and in one operation we are connecting to the database 300 times which results in ~ 11+ seconds additional time if we open & close the connection. So the user would see the time going from ~ 3 seconds to 14 seconds which they won't accept.

    As we can't change the database or the businesses logic at the moment I will need to find a solution for refreshing the connection without closing it, the joys of Access.

  12. #12
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,042

    Re: Access(mdb) Performance Issue

    Quote Originally Posted by adam.syd View Post

    As we can't change the database or the businesses logic at the moment I will need to find a solution for refreshing the connection without closing it, the joys of Access.
    see Post#6 the Function ExecuteSQL
    it uses OleDb.OleDbTransaction

    Code:
    Public Sub MakeTables()
            Dim sSQL As String
            'the Database 'Contacts.mdb' has to exist already
            Dim con As OleDbConnection = New OleDbConnection("Provider=Microsoft.jet.oledb.4.0;data source=E:\Contacts.mdb")
            con.Open()
    
    'your SQL's here
    
          
            con.Close()
            con = Nothing
        End Sub
    EDIT:
    in some cases using DAO can out perform .NET
    I don't know all you needs, but it should be worth a try
    Code:
     Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
            Dim databaseName As String = "E:\base2000.mdb" 
            Dim start As DateTime = DateTime.Now
            Dim dbEngine As New DAO.DBEngine()
            Dim db As DAO.Database = dbEngine.OpenDatabase(databaseName)
            Dim rst As DAO.Recordset
            'db.Execute("DROP Table TEMP")
            Dim SQL As String = "select * from NewTable"
            rst = db.OpenRecordset(SQL)
            rst.MoveLast()
            Debug.Print(" RecordCount = " & rst.RecordCount)
           Dim elapsedTimeInSeconds As Double =DateTime.Now.Subtract(start).TotalSeconds
            Debug.WriteLine("took {0} seconds", elapsedTimeInSeconds)
            rst.Close()
        End Sub
    Last edited by ChrisE; Oct 17th, 2020 at 01:16 AM.
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  13. #13

    Thread Starter
    Member
    Join Date
    Feb 2006
    Location
    Sydney, Australia
    Posts
    42

    Re: Access(mdb) Performance Issue

    Quote Originally Posted by ChrisE View Post
    see Post#6 the Function ExecuteSQL
    it uses OleDb.OleDbTransaction

    Code:
    Public Sub MakeTables()
            Dim sSQL As String
            'the Database 'Contacts.mdb' has to exist already
            Dim con As OleDbConnection = New OleDbConnection("Provider=Microsoft.jet.oledb.4.0;data source=E:\Contacts.mdb")
            con.Open()
    
    'your SQL's here
    
          
            con.Close()
            con = Nothing
        End Sub
    EDIT:
    in some cases using DAO can out perform .NET
    I don't know all you needs, but it should be worth a try
    Code:
     Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
            Dim databaseName As String = "E:\base2000.mdb" 
            Dim start As DateTime = DateTime.Now
            Dim dbEngine As New DAO.DBEngine()
            Dim db As DAO.Database = dbEngine.OpenDatabase(databaseName)
            Dim rst As DAO.Recordset
            'db.Execute("DROP Table TEMP")
            Dim SQL As String = "select * from NewTable"
            rst = db.OpenRecordset(SQL)
            rst.MoveLast()
            Debug.Print(" RecordCount = " & rst.RecordCount)
           Dim elapsedTimeInSeconds As Double =DateTime.Now.Subtract(start).TotalSeconds
            Debug.WriteLine("took {0} seconds", elapsedTimeInSeconds)
            rst.Close()
        End Sub

    Thanks for the detail relating to DAO, we have found it is quite good for mass inserts.

    Issue we having is related to datatable time increasing from 0.3 seconds to 20+ seconds if it proceeds a data update, closing & opening the connection fixes the datatable performance issue.

  14. #14
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,042

    Re: Access(mdb) Performance Issue

    Quote Originally Posted by adam.syd View Post
    closing & opening the connection fixes the datatable performance issue.
    that is the way to go then
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

Posting Permissions

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



Click Here to Expand Forum to Full Width