-
Oct 16th, 2020, 02:39 AM
#1
Thread Starter
Member
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.
-
Oct 16th, 2020, 03:16 AM
#2
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
-
Oct 16th, 2020, 04:05 AM
#3
Thread Starter
Member
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.
-
Oct 16th, 2020, 05:21 AM
#4
Thread Starter
Member
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
-
Oct 16th, 2020, 05:38 AM
#5
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
-
Oct 16th, 2020, 06:03 AM
#6
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.
-
Oct 16th, 2020, 07:47 AM
#7
Thread Starter
Member
Re: Access(mdb) Performance Issue
Thanks for the details, I have indexes on some fields.
-
Oct 16th, 2020, 08:05 AM
#8
Thread Starter
Member
Re: Access(mdb) Performance Issue
Originally Posted by NeedSomeAnswers
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.
-
Oct 16th, 2020, 08:56 AM
#9
Re: Access(mdb) Performance Issue
Originally Posted by adam.syd
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
-
Oct 16th, 2020, 09:10 AM
#10
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
-
Oct 16th, 2020, 05:06 PM
#11
Thread Starter
Member
Re: Access(mdb) Performance Issue
Originally Posted by Shaggy Hiker
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.
-
Oct 17th, 2020, 12:59 AM
#12
Re: Access(mdb) Performance Issue
Originally Posted by adam.syd
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.
-
Oct 17th, 2020, 02:33 AM
#13
Thread Starter
Member
Re: Access(mdb) Performance Issue
Originally Posted by ChrisE
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.
-
Oct 17th, 2020, 11:01 AM
#14
Re: Access(mdb) Performance Issue
Originally Posted by adam.syd
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|