Performance Penalty Of Supporting End-user Debugging/verbose Mode?
Dec 8, 2007
For those who work with *nix programs, you can specify -v to get program to tell you more about what it is doing while it's processing which can be useful for end users trying to figure out possible causes for failures/errors/unexpected results.
We both already know that there *will* be a performance hit if we were to leave debugging code, so that's not the question. The question is how much performance hit will a VBA module suffer if it has to check whether it should be verbose or not. Suppose we have a complex set of procedures where several routines will be called or maybe several events may occur, so logically, every routine would need to check whether the user has asked for verbosity or not. So,
Private Verbose As Boolean
Private Sub1
Do A
If Verbose Then
Msgbox "A worked!"
End If
Do B
If Verbose Then
Msgbox "B worked!"
End If
End Sub
Private Sub2
Do C
If Verbose Then
MsgBox "C worked!"
End If
End Sub
That's a lot of If statements, and assuming every routine was executed, that means the VBA had to test for condition Verbose three times for single iteration.
Is there any way to reduce the number of checks and hopefully have a final product that performs just as fast with verbose mode off than one without such mode at all?
We are encountering severe performance problems when running our access database on our Network.
Symptom:
Huge drop off in performance when 2nd and subsequent users refresh links to chosen back end database. If the database is opened by another users front end database.
Takes up to 10 minutes to refresh the links to 120 tables in Back-end MDB database.
The system is designed as a split front and backend Access database. The back-end Data MDB database is located on a Windows 2000 server.
The first time a user logs on they are required to locate and Re- Link the tables from the back-end database which holds data for the current year. If they wish to work on another year they re-link the tables from the relevant back end database.
When running locally the Front end takes between 5 and 10 seconds to refresh ALL the links to around 130 tables.
When the back end is on a network drive the process takes about twice as long. But is still quick.
If another user has the file open via their own (locally held) front end, then the refresh link process takes between 5 and 10 seconds PER TABLE !!
This means that that it takes up to 10 minutes to complete the re-link. Same behaviour if using Linked table Manager or refresh links using code
As soon as second user logs out of database the refresh link process speeds up.
The behaviour has been observed on a variety of servers and customer networks.
General data access is also much slower after 2nd user - but just about bearable. Data files are small – few records – occupy 7mb on disk.
Development Platforms – Windows XP / 200 clients, Windows 2000 Server hosts the back end database.
Front end is Access XP (MDB or MDE File) with al the latest service packs and security updates installed.
We have experienced this problem with a number of different systems running in various environments. But this wasn’t a problem with the old Access 1 database
HELP! Access should be able to support 10 – 20 users - we can barely run 2!
I trying to set my access database as a single user mode.(If i using the Mdb , other user cant use the same mdb by showing some warning msg and close ).
I hope this hasn't been addressed too many times before, I searched for 'corruption' but there were so many results that I could only parse the first few pages.
Anyway, I'm developing a C# app for personal use and I was planning on using some MS Access functionality, namely saving and loading data from MDB files. Unfortunately, I've heard a lot of scary stuff regarding Access's tendency to corrupt MDB files. This is really bad news, as I really can't afford to lose any data - information is fed into the application daily, even hourly, and the data isn't stored anywhere else.
I could - and will - add some code to back up MDB files on every access, but I'm still curious. Given the circumstances listed below, how likely is Access to corrupt a MDB from time to time ?
- I am the sole user of the database. It is never, ever, accessed by more than one user at the same time. There is no networking involved whatsoever. - The database contains only tables and maybe some forms. There are no constraints, stored queries, or any advanced features like that. The forms are never actually used by the program, they're just there to help in editing the database 'manually' should the need arise. - The tables contain only basic data, i.e. INTs, VARCHARs and DATETIMEs. No memos, images or any such stuff. - The database engine / system will not crash in the middle of a writing operation, or if it does, there will be a certified backup available. - There are only two possible operations that the program can perform on the database: either a bulk read on each table, or a complete replacement of all of the records in all tables. There are no selective insertions or deletions.
What I want to avoid most is a 'silent' corruption, i.e. Access reports having saved the table successfully, but it has actually corrupted some records. It's not a problem to keep a backup for the duration of a writing operation (to cover the case in which the engine crashes during writing), but if I need to keep it for anything longer than that, the situation becomes rather cumbersome.
I have several subform tabs and one that I have updated no longer shows up when I run the form on Form mode. The tab is there but no content or details of the subform
SELECT Orders.QtyAdd FROM Orders WHERE Orders.ProdID = 1;
SELECT Output.QtySub FROM Output WHERE Output.ProdID = 1;
What code do I use to subtract these 2 queries?
And also, where it says .ProdID = 1; what is the variable so it picks the ProdID from a basic single row form being shown? I guess it doesn't follow the Table.Field pattern?
This is the error I'm getting. A little obscure, I know, but this is happening in an executable Access application (2010 accde) when I click the close button on a form.
The code behind the form is checking a lot of stuff (data validation, etc.), but "Object or class does not support the set of events" is the error. Then the application continues as if nothing had happened.
The app was converted not long ago from XP ACC2003 to Win 7 ACC2007-2010.
I have 3 fields in my db, valueUSD, ValueOther, Currency. Obviously, valueUSD will always be shown in the US Dollar format $#,###.## however, I want to create VBA code so that when the user selects a specific currency (i.e. GBP or EUR), the number in ValueOther is formatted using the correct currency symbol.
Could having two look up queries, one on form an the other on table, and both of them take info from the same field and store it in a different field on a different table slow the DB? :)
case 1: Link the SQL Server tables to MS Access (mdb file) - there are 50 concurrent users access in the same file (network share drive).
case 2: create adp file - there are 50 concurrent users access in the same file (network share drive).
I know if each client has own copy on his local machine, it will not have any problem. But, if we want to put one file only in the sharing drive, then if either the client or server computer (or the connection between them) fails during the transaction or other operation that writes to the database file, which case is better to solve?
I'm starting a year-long university project which will be written in c#. The client app will be run on a LAN, with 35-40 users accessing the database concurrently, connecting to a database held on the server.
Could anyone tell me if Access can withstand 35-40 users at the same time? I found out that the maximum amount of users is 255, but what kind of performance impact(if any) will there be with 35-40?
I've searched all over the Internet for this, but I can only find references to using access behind a website, which isnt quite what I need. Any help would be great.
I'm hoping someone can offer me some advice on performance for a FE/BE database that will eventually be accessed by up to 60 people during the same day (usually only 3-4 people searching/writing at same time).
The system is to process complaints at a call centre - built from the ground up and my first true Acess DB application. I have noticed extremely slow times in loading forms - which is a pain considering we are a performance oriented workplace.
FE is on the desktop. BE on the server. Currently it is taking up to 10 seconds to open a form (even when the form contains virtually no information looked up from tables).
Could anyone offer any tips on how to improve performance.
I am work in a medium sized (80-100 people) Government Contact Centre. As some people may know from previous posts, for several months on and off I have been developing a database which could be best described as a 'ticket of work' system for many of our transaction channels.
I am currently rolling out the 'Beta' version of the database, and am noticing some slowdown in performance.
The database is Acces 2K, F/E to B/E X2 (writing to one backend 90% of the time) sitting on one of the networked drives (F/E is local).
I have addressed some of the issues that relate to performance (such as persistent locking of B/E) and am seeking some further advice. Unfortunately, as our I.S department do not support Access use I am unable to seek advice at work.
1. Does anyone know of any tools or methods for measuring database performance?
I am particularly interested in response times and how these are impacted depending on traffic/load. It would be particularly beneficial it there was a tool available that recorded data automatically(eg. in an excel worksheet) for later analysis.
2. A question re: code efficiency. I am primarily using ADO to open and manipulate recordsets. This may be a silly question, but being self taught I have missed lots of the obvious stuff on the way. With ADO, I can either open an entire table, or use an SQL select to open only a specific record. Do the two methods differ with respect to performance greatly (more so from the perspective of other users)?
Sorry about the long post, and thank you in advance for any advice you can offer!
I'd like to know whether other users have had performance problems with different versions of Access in a single environment.
In my office, two users are on A2007, and the rest of us are on A2003 or earlier. After some nasty conflicts in the (un-split) database when A2007 and A2003 users were in the same file, I split the database. Now the A2007 and A2003 people have individual frontends. Other than having a garbled LDB file, I haven't had any other conflicts between the two programs.
However, I have had some massively irritating problems ever since I split the database.
1)The Design View on my Front End runs very, very slow (like, five beats after every action to process). 2) If I try to edit one particular, very simple form, it will not return to form view... it is stuck in Design view. (That sounds like the form has been corrupted.) 3) The auto-save doesn't seem to be working... Access (2003) crashed on me yesterday and I lost an hour's work on a new form.
I've tried some of the database construction suggestions to speed performance in other posts - forms based on queries, record locking, short paths, etc., and have seen some improvement in overall performance, but none of these should affect design view. USING the database tends to be speedy-quick.
So, did our dalliance with A2007 mess up my database? Does any one else have experience with this? Or are these symptoms common with a slow network connection (the back end is on a server, front ends are on individual desktops)?
Bear with me on this one as the query looks a mouthful although it's fairly simple. I started with the following query, which was working working very quickly and almost instantaneously bringing back results. Essentially, it is a number of nested select statements bringing back data and joining on the first table, Structure, to group and filter the results. I had to do it this way as there are no distinct relationships between the zarinvreg, zarageddebt, or baddebt tables (not 100% anyway).
Attached on SQL_Ok.txt
I then had to add some extra joins in and all of a sudden the query has slowed to 10 seconds. There's nothing particularly heavy about these extra joins but they have a couple of WHERE caluses in them. I tried indexing all the fields in every table in the DB and that didn't help at all.
Attached on SQL_SLOW.txt
Any ideas on how to improve this, some things to try, or why the massive delay in processing ? The same query is pretty fast on SQL Server though...just not Access
We're going live with a database today, and running through some testing, some of the forms seem to freeze. It's only happened a couple of times, but my question is, what is the best way to distribute it.
At the moment it's just on a location on the network and the users in the team access it directly. Can anyone give me any suggestions. My neck is on the line here...
I have searched on this forum for other threads like this and in the db examples page, but didn't find anything. I have made a perfomance eval db in MSA 2003. It works fine except for one part. I need to score the individual on about 20 different criteria. Each one ranges from 1-5. I am having problems getting all the entries to sum when I run the report. Should I use combo boxes, check boxes, radio buttons or what? :confused: Then how do I get the individual scores to sum up when I am finished putting them in and run the report to print it? I have looked around the net for a Performance Evaluation template to see how it is done, but couldn't find one anywhere. Microsoft doesn't have one in their list of templates either. If anyone knows where to find one at, I'd appreciate that too. TIA for your help! I appreciate it.
Im developing a project database. A normal project will need 10 000 records in the biggest table. Does this effect the performance? I mean, when they have done 8 projects, there will be like 80 000 records in one table. Is this to much? Does this influence the performance very much ?
The database is gooing to be placed on a sequelserver.
thanks for your reply since this is a very importend mather!!!
I have split my database application that was approaching the 20MB size. This I have split into a front end (approx. 8 Mb) with linked tables to a back end database (approx. 12MB).
Network is 100Mb Ethernet.
However, since doing this, end users have noticed that scrolling through records and especially running reports takes significantly longer sometimes 3x/ 4x longer. I understood that splitting the DB would have a beneficial effect from a development / application 'release' point of view and maybe if I were to create an MDE file of the front end, I could also benefit from reduced network traffic given that end users are using a compiled executable etc.
With the speed issues I have been experiencing I have had no choice but to roll back to the original application format with everything in the the one MDB file.
Has anyone else had to do the same - given similar speed degradation issues?
Last week, my access97 db, with back end and front end, both residing on a network as they have been for the last 2 years, decided to start running at 1/4 of the speed that they usually have. The db is used by 16 users, and roughly 5 are on at any given time (Operating system - XP). Both front end and backend were compressed without any change in performance. No changes in programming or number of records was introduced as of late. Checking with our IT department indicated that the performance of the network and drive have not changed and are up to snuff. I moved a copy of the FE and BE to my hard drive and found performance to return to normal speed, although I am not sure if it always ran faster on a PC. Any experience with this irregularity and options to check would be greatly appreciated?
I have a access application which is split into 2 bits with the backend being on the network. The thing is its very slow performance wise and i want to try and optimise it...
Have a few questions after I ran the Access performance analyzer. Now these ideas are they good or just some generic recommendations. Dont know if I should take care of all these or not?
Anyone know if I should do all these things and about how I should do it?
I have designed an Access DB with various forms to display data populated by queries. This runs fairly efficiently when on the same system. As soon as I try to split the DB into a front and back end and place the data part in a network folder location (across a WAN) the performance is incredibly slow.
The strange thing is that when testing the queries out on SQL server (just as a test), they run quickly and the data can't be more than a few kb in size.
As it was explained to me the other day: "Access is an file oriented database. There is no client-server code, so all data manipulations are done on client side anyway. Access has to load data across network."
This sounds to me like Access would be loading all 20Mb of data across the WAN and processing it on the client end rather than running the query at the back end first and only sending through 10kb of data. Is this true?
A DB is split (FE / BE) with several FE users and the BE sat on a network. FE Access 2003. (runtime) The Sub form has record set type set to Snapshot.
Which of the following scenarios will perform fastest?
Scenario 1, The FE Queries a linked table and displays the results on a sub form (Datasheet Format).
Scenario 2, The BE table is copied to the FE (new table) and the query is run against the new table and displays the results on a sub form (Datasheet Format)
The reason for this question is to attempt to reduce the network traffic and further improve the speed performance of a split database.
