Access / ADO Performance
-
We have a legacy Access DB that is located on a file server. We use Excel VBA to manipulate the database and are having performance issues. There is a query (stored procedure) in the DB that zeros out data in a large table (~250,000 rows) in preparation for the day's P/L. If I log directly into the DB, I can run the procedure in a second or two. If I execute the procedure in VBA, it takes several minutes and creates a 68 MB temporary file on the local computer. I tried begin/commit/rollback transactions but that had no effect. I also tried client and server side cursors. Very little difference. I'm guessing it is saving undo information locally. Is there a switch to prevent this? Is there something I am missing here? Thanks very much, Mark Jackson strCnn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=S:\Database\database.mdb;" & _ "Jet OLEDB:System Database=s:\database\security.mdw;" & _ "User ID=xxx;Password=xxx;" Set conn = New ADODB.Connection conn.ConnectionString = strCnn conn.Open conn.CommandTimeout = 0 Set cmd = New ADODB.Command cmd.ActiveConnection = conn cmd.CommandTimeout = 0 ' Set Active values to NULL cmd.CommandType = adCmdStoredProc strCmd = "UpdateActiveSetNullValues" cmd.CommandText = strCmd cmd.Execute Options:=adExecuteNoRecords
-
We have a legacy Access DB that is located on a file server. We use Excel VBA to manipulate the database and are having performance issues. There is a query (stored procedure) in the DB that zeros out data in a large table (~250,000 rows) in preparation for the day's P/L. If I log directly into the DB, I can run the procedure in a second or two. If I execute the procedure in VBA, it takes several minutes and creates a 68 MB temporary file on the local computer. I tried begin/commit/rollback transactions but that had no effect. I also tried client and server side cursors. Very little difference. I'm guessing it is saving undo information locally. Is there a switch to prevent this? Is there something I am missing here? Thanks very much, Mark Jackson strCnn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=S:\Database\database.mdb;" & _ "Jet OLEDB:System Database=s:\database\security.mdw;" & _ "User ID=xxx;Password=xxx;" Set conn = New ADODB.Connection conn.ConnectionString = strCnn conn.Open conn.CommandTimeout = 0 Set cmd = New ADODB.Command cmd.ActiveConnection = conn cmd.CommandTimeout = 0 ' Set Active values to NULL cmd.CommandType = adCmdStoredProc strCmd = "UpdateActiveSetNullValues" cmd.CommandText = strCmd cmd.Execute Options:=adExecuteNoRecords
Most likely this is because Access is just a flat-file and the operations against the data are done by the (local) driver. So there's no server side application to handle this. This causes the data to be transferred over the network etc. One thing is that you could try to modify the stored procedure. If you're having cursors and loops, try to modify them to work on sets. Another this is that you could create a small program on the server side (for example as a service) which would do the actual operation when called. The operation itself wouldn't be faster but much less network traffic would be included. And of course if you have the option, you could consider migrating to SQL Server which would include a real server side RDBMS.
The need to optimize rises from a bad design.My articles[^]
-
We have a legacy Access DB that is located on a file server. We use Excel VBA to manipulate the database and are having performance issues. There is a query (stored procedure) in the DB that zeros out data in a large table (~250,000 rows) in preparation for the day's P/L. If I log directly into the DB, I can run the procedure in a second or two. If I execute the procedure in VBA, it takes several minutes and creates a 68 MB temporary file on the local computer. I tried begin/commit/rollback transactions but that had no effect. I also tried client and server side cursors. Very little difference. I'm guessing it is saving undo information locally. Is there a switch to prevent this? Is there something I am missing here? Thanks very much, Mark Jackson strCnn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=S:\Database\database.mdb;" & _ "Jet OLEDB:System Database=s:\database\security.mdw;" & _ "User ID=xxx;Password=xxx;" Set conn = New ADODB.Connection conn.ConnectionString = strCnn conn.Open conn.CommandTimeout = 0 Set cmd = New ADODB.Command cmd.ActiveConnection = conn cmd.CommandTimeout = 0 ' Set Active values to NULL cmd.CommandType = adCmdStoredProc strCmd = "UpdateActiveSetNullValues" cmd.CommandText = strCmd cmd.Execute Options:=adExecuteNoRecords
As Mika suggested, it'd be a good time to move to a real database-server. It'd perform a lot better, and there's a Wizard in Access that can migrate all your data (if Sql Server is installed). There are "free" versions of Sql Server available, so the cost shouldn't be a problem.
I are Troll :suss:
-
As Mika suggested, it'd be a good time to move to a real database-server. It'd perform a lot better, and there's a Wizard in Access that can migrate all your data (if Sql Server is installed). There are "free" versions of Sql Server available, so the cost shouldn't be a problem.
I are Troll :suss:
If you migrate, watch for column type conversions that do not make sense. i.e. Postal code converted to FLOAT. Just because you have an MS Access column as "text" does not mean Microsoft will keep it. Microsoft knows better, you really wanted it as "float". :mad:
-
Most likely this is because Access is just a flat-file and the operations against the data are done by the (local) driver. So there's no server side application to handle this. This causes the data to be transferred over the network etc. One thing is that you could try to modify the stored procedure. If you're having cursors and loops, try to modify them to work on sets. Another this is that you could create a small program on the server side (for example as a service) which would do the actual operation when called. The operation itself wouldn't be faster but much less network traffic would be included. And of course if you have the option, you could consider migrating to SQL Server which would include a real server side RDBMS.
The need to optimize rises from a bad design.My articles[^]
For a variety of reasons migrating isn't an option in the short term although I will do that eventually. I am curious why I can log into the DB via Access on my local computer with the files on the server, and run the query in two seconds. If I run the exact same query in ADO/VBA, it takes a couple minutes. I know Access mostly uses the old DAO objects. That may be the difference.
-
For a variety of reasons migrating isn't an option in the short term although I will do that eventually. I am curious why I can log into the DB via Access on my local computer with the files on the server, and run the query in two seconds. If I run the exact same query in ADO/VBA, it takes a couple minutes. I know Access mostly uses the old DAO objects. That may be the difference.
The difference is that it's not a database-server. It's not meant to be accessed from a remote computer, and even though it's possible, it'll never be fast. Why is migrating not an option? You could migrate the tables and leave your logic, code, forms and data in the MS-Access database. The database could fetch it's records from Sql Server, without the embedded code ever noticing a difference. --edit There's a list of possible bottlenecks over here[^], could be usefull.
I are Troll :suss:
-
The difference is that it's not a database-server. It's not meant to be accessed from a remote computer, and even though it's possible, it'll never be fast. Why is migrating not an option? You could migrate the tables and leave your logic, code, forms and data in the MS-Access database. The database could fetch it's records from Sql Server, without the embedded code ever noticing a difference. --edit There's a list of possible bottlenecks over here[^], could be usefull.
I are Troll :suss:
I will migrate next month but was looking for a fix for the rest of this month. :-)
-
We have a legacy Access DB that is located on a file server. We use Excel VBA to manipulate the database and are having performance issues. There is a query (stored procedure) in the DB that zeros out data in a large table (~250,000 rows) in preparation for the day's P/L. If I log directly into the DB, I can run the procedure in a second or two. If I execute the procedure in VBA, it takes several minutes and creates a 68 MB temporary file on the local computer. I tried begin/commit/rollback transactions but that had no effect. I also tried client and server side cursors. Very little difference. I'm guessing it is saving undo information locally. Is there a switch to prevent this? Is there something I am missing here? Thanks very much, Mark Jackson strCnn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=S:\Database\database.mdb;" & _ "Jet OLEDB:System Database=s:\database\security.mdw;" & _ "User ID=xxx;Password=xxx;" Set conn = New ADODB.Connection conn.ConnectionString = strCnn conn.Open conn.CommandTimeout = 0 Set cmd = New ADODB.Command cmd.ActiveConnection = conn cmd.CommandTimeout = 0 ' Set Active values to NULL cmd.CommandType = adCmdStoredProc strCmd = "UpdateActiveSetNullValues" cmd.CommandText = strCmd cmd.Execute Options:=adExecuteNoRecords
How 'bout this ... Can you create an EXE (VB, C#) that you can place on the server which will perform the "zero out" function. You could then schedule that program to run every morning. The folks here could help you with connecting to a MS-Access database and execute SQL statements. I will help you if you want the solution in VB.NET. Regards, David
-
For a variety of reasons migrating isn't an option in the short term although I will do that eventually. I am curious why I can log into the DB via Access on my local computer with the files on the server, and run the query in two seconds. If I run the exact same query in ADO/VBA, it takes a couple minutes. I know Access mostly uses the old DAO objects. That may be the difference.
One main reason is, as you said, the difference in the technology stack. Access has some more capabilities to handle the database file than if you connect to the database from Excel/VBA. Creating a small application using .Net framework could result into very different response times, so that could also be an option. But as I wrote, if you want to minimize the network traffic, the best option before migrating could be to use a small server side application.
The need to optimize rises from a bad design.My articles[^]
-
We have a legacy Access DB that is located on a file server. We use Excel VBA to manipulate the database and are having performance issues. There is a query (stored procedure) in the DB that zeros out data in a large table (~250,000 rows) in preparation for the day's P/L. If I log directly into the DB, I can run the procedure in a second or two. If I execute the procedure in VBA, it takes several minutes and creates a 68 MB temporary file on the local computer. I tried begin/commit/rollback transactions but that had no effect. I also tried client and server side cursors. Very little difference. I'm guessing it is saving undo information locally. Is there a switch to prevent this? Is there something I am missing here? Thanks very much, Mark Jackson strCnn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=S:\Database\database.mdb;" & _ "Jet OLEDB:System Database=s:\database\security.mdw;" & _ "User ID=xxx;Password=xxx;" Set conn = New ADODB.Connection conn.ConnectionString = strCnn conn.Open conn.CommandTimeout = 0 Set cmd = New ADODB.Command cmd.ActiveConnection = conn cmd.CommandTimeout = 0 ' Set Active values to NULL cmd.CommandType = adCmdStoredProc strCmd = "UpdateActiveSetNullValues" cmd.CommandText = strCmd cmd.Execute Options:=adExecuteNoRecords
I don't understand the problem. Obviously it is a manual process. But one that in the worst case only takes a couple of minutes. And you said you plan to move to a different database in a month. So the obvious solution for this month is either to do it on your local box and find something else to do for a couple of minutes or log into the file server.
-
I don't understand the problem. Obviously it is a manual process. But one that in the worst case only takes a couple of minutes. And you said you plan to move to a different database in a month. So the obvious solution for this month is either to do it on your local box and find something else to do for a couple of minutes or log into the file server.
Unfortunately the upsizing wizard doesn't work in Access 2007 now? Is it possible to just move the tables and leave the procedures in Access? There are 50 or so procedures and the syntax is just different enough they will all have to be re-written. Mark Jackson
-
Unfortunately the upsizing wizard doesn't work in Access 2007 now? Is it possible to just move the tables and leave the procedures in Access? There are 50 or so procedures and the syntax is just different enough they will all have to be re-written. Mark Jackson
mjackson11 wrote:
Is it possible to just move the tables and leave the procedures in Access?
With Access that might be possible because one can create an ODBC link. However I doubt it is a good idea. And I suspect it would make the procs slower, if that matters. And that doesn't make your original problem any clearer. You shouldn't move to a different database only because you want to save a few minutes.
-
We have a legacy Access DB that is located on a file server. We use Excel VBA to manipulate the database and are having performance issues. There is a query (stored procedure) in the DB that zeros out data in a large table (~250,000 rows) in preparation for the day's P/L. If I log directly into the DB, I can run the procedure in a second or two. If I execute the procedure in VBA, it takes several minutes and creates a 68 MB temporary file on the local computer. I tried begin/commit/rollback transactions but that had no effect. I also tried client and server side cursors. Very little difference. I'm guessing it is saving undo information locally. Is there a switch to prevent this? Is there something I am missing here? Thanks very much, Mark Jackson strCnn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=S:\Database\database.mdb;" & _ "Jet OLEDB:System Database=s:\database\security.mdw;" & _ "User ID=xxx;Password=xxx;" Set conn = New ADODB.Connection conn.ConnectionString = strCnn conn.Open conn.CommandTimeout = 0 Set cmd = New ADODB.Command cmd.ActiveConnection = conn cmd.CommandTimeout = 0 ' Set Active values to NULL cmd.CommandType = adCmdStoredProc strCmd = "UpdateActiveSetNullValues" cmd.CommandText = strCmd cmd.Execute Options:=adExecuteNoRecords
Turns out the network folks were backing up the entire database every 60 seconds. Without telling us. Performance is fine now that we turned that off. About 25% greater than local file. Thx for the pointers. Mark Jackson