|
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.
|
|
|
|
|
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 
|
|
|
|
|
I will migrate next month but was looking for a fix for the rest of this month. 
|
|
|
|
|
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.
|
|
|
|
|
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
|
|
|
|
|
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". 
|
|
|
|
|
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
|
|
|
|
|
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
|
|
|
|
|
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.
|
|
|
|
|
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
|
|
|
|
|
I'm fairly new to SSRS so..
Is it possible to display an SSRS report on a .Net WinForm? I'm switching from Crystal Reports and I would like it to work the same way. I know that SSRS produces a report file. What I'm looking for is a report viewer control or something like that.
Everything makes sense in someone's mind
|
|
|
|
|
|
Excellent, thank you
Everything makes sense in someone's mind
|
|
|
|
|
You're welcome
|
|
|
|
|
fairly simple question with no clue as to the answer. I have a column in a tabe that was created as nchar(10). Now the powers that be want a little more information in that column (store identifiers) and I need to change the nchar(10) to nchar(50). Did that,saved it with no errors or warnings, but when new information is entered the database is truncating the data to (10) still for that column. How do I fix this? any ideas? It is the database that is doing it as the code that enters the information does not care how many characters are in the string its inserting. Its a SQL 2008 r2 database BTW.
edit:
I found the issue, it WAS in the code I forgot that my app had to set a scalar variable when doing the insert etc etc... And I never changed it from (10) there... duh.. leaving the post instead of deleting in case some other noob makes the same mistake as me... lol
Programming is a race between programmers trying to build bigger and better idiot proof programs, and the universe trying to build bigger and better idiots, so far... the universe is winning.
|
|
|
|
|
Hi,
Good that you found the solution. However, few other things in this post that made me wonder:
- do you really need fixed length strings (NCHAR)? Using variable length strings (NVARHCAR) would most likely make it easier to handle the data and probably also save space since the string doesn't need to be padded with empty chars
- the question sounded like you're going to concatenate different information pieces into a single column. If this really is the case you should avoid that solution since it's going to cause problems sooner or later (typically sooner) and require more complex logic (using substrings, fixed lengths, possibly delimiters etc etc). If the requirement is given, perhaps it would still be reasonable to talk it over one more time. Of course if you don't have the possibility to modify the application itself, it's a different situation but based on your post I understood that modifications are possible.
Best regards.
|
|
|
|
|
well, I was using fixed length since this was my first app that I have built using asp or sql as this is my first job out of an associates degree. I originally set them because I am controlling the string length from the app itself. Its not really a concatenation of strings though. We have one table with store locations that were just a string of numbers such as 101. They now want it to show 101-City.
This is linked to another table through the information entered in the app. So the string itself is not changed dynamically by any users. it is set by a separate page of the app that is only assessable to the admin. Thanks for the post and I will definitely keep that information in mind on future endeavors as I am still learning and have a long way to go. It will also clear up that pesky white space in my dang columns.. lol
As you can probably guess I have not had much database training , just code(c++,Java etc..) I had to teach myself sql and asp so I'm sure it would give a professional a headache but it works and is improving with every day that passes. Thanks again for your assistance and I'm sure you will hear from me some more.
Programming is a race between programmers trying to build bigger and better idiot proof programs, and the universe trying to build bigger and better idiots, so far... the universe is winning.
|
|
|
|
|
Yes, I understand. Just for the future use, if the code is the key to the city, you should have a lookup table for cities and fetch the name using a join. For example if the city name changes you'll now have to update part of the string field to correct the situation on every row.
At some point when you have time, have a look at W3Schools web site. There's lots of basic theory in clearly explained format. Even though it's mainly MySql, the theory is much the same. For example this could be interesting: http://w3schools.in/php-mysql.php?tutorial=Database-Normalization[^]
Good luck to the project
|
|
|
|
|
Glad you left the original post; it may, as you said, serve others in the future.
We learn by doing, and hopefully, in learning we assist others.
This site exists to assist others. Ask reasonable questions and you will probably receive reasonable advice.
Before long, you may be the one answering rather than asking.
Tim
|
|
|
|
|
I look forward to the day I have enough knowledge to pitch in and help rather than just ask.
Programming is a race between programmers trying to build bigger and better idiot proof programs, and the universe trying to build bigger and better idiots, so far... the universe is winning.
|
|
|
|
|
Hi friends,
I have a table like below,
account_no linked_acc_no
--------- -------------
aaa bbb
bbb ccc
bbb ddd
eee ccc
ddd fff
fff ggg
eee hhh
hhh iii
xxx zzz
xxx yyy
vvv www
I need a query to select account no which are linked with 'aaa'.
So the o/p should be like this,
o/p
---
bbb
ccc
ddd
eee
fff
ggg
hhh
iii
any one can help me on this?..
|
|
|
|
|
Hi friends,
I got a solutions.
Thanks.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
