Posts filed under 'Programming'
I’ve just lost 2 days going completely bananas over a performance issue that I could not explain.
I’ve got this Dell R300 rack server that runs Windows Server 2008 that I dedicate to running IIS and SQL Server 2008, mostly for development purposes.
In my previous blog entry, I was trying some benchmark to compare the performance of Access and SQL Server using INT and GUID and getting some strange results.
Here are the results I was getting from inserting large amounts of data in SQL Server:
| Machine |
Operating System |
Test without Transaction |
Test with Transaction |
| MacbookPro |
Windows Server 2008 x64 |
324 ms |
22 ms |
| Desktop |
Windows XP |
172 ms |
47 ms |
| Server |
Windows Server 2008 x64 |
8635 ms!! |
27 ms |
On the server, not using transactions makes the query run more than 8 seconds, at least an order of magnitude slower than it should!
I initially thought there was something wrong with my server setup but since I couldn’t find anything, I just spend the day re-installing the OS and SQL server, applying all patches and updates so the server is basically brand new, nothing else on the box, no other services, basically all the power is left for SQL Server…
Despair
When I saw the results for the first time after spending my Easter Sunday rebuilding the machine I felt dread and despair.
The gods were being unfair, it had to be a hardware issue and it had to be related to either memory or hard disk, although I couldn’t understand really why but these were the only things that I could see have such an impact on performance.
I started to look in the hardware settings:
And then I noticed this in the Policies tab of the Disk Device Properties :
Just for the lulz of it, I ticked the box, close the properties
And then tried my query again:
| Machine |
Operating System |
Test without Transaction |
Test with Transaction |
Server |
Windows Server 2008 x64 |
254 ms!! |
27 ms |
A nearly 35 fold increase in performance!
Moral of the story
If you are getting strange and inconsistent performance results from SQL Server, make sure you check that Enable advanced performance option.
Even if you’re not getting strange results, you may not be aware of the issue, only that some operations may be much slower than they should.
Before taking your machine apart and re-installing everything on it, check your hardware settings, there may be options made available by the manufacturer or the OS that you’re not aware of…
Lesson learnt.
April 12th, 2009
When you start building an Access application, it’s tempting to just think about today’s problem and not worry at all about the future.
If your application is successful, people will want more out of it and, over time, you’ll be faced with the task of moving the back-end database to a more robust system like SQL Server.
While there are tools like SSMA that can help you move an Access database to SQL Server, a lot of the problems you’ll encounter can be solved before you even have to think about upsizing.
Abiding by a few simple rules will cost you nothing when creating your Access application but will save you a lot of headache if -when- the time comes to upsize.
So here are a few things to keep in mind.
Naming conventions
Access is pretty liberal about naming conventions and it will let you freely name your tables, columns indexes and queries.
When these get moved to another database you’ll most probably be faced with having to rename them.
In some cases, you could actually create subtle bugs because something that used to work fine in Access may be tolerated in the new database but be interpreted differently.
Do not use spaces or special characters in your data object names.
Stick to characters in the range A through Z, 0 to 9 with maybe underscores _ somewhere in between (but not at the start or the end).
Also try to respect casing wherever you reference this name (especially for databases like MySQL which are case-sensitive if the hosted on a Linux platform for instance).
eg:
Customer Order Lines (archive) should be CustomerOrderLines_Archive.
Query for last Year's Turnover should be QueryLastYearTurnover.
Index ID+OrderDate should become instead ID_OrderDate.
Do not use keywords that are reserved or might mean something else whether they are SQL keywords or functions names:
A column called Date could be renamed PurchaseDate for instance.
Similarly, OrderBy could be renamed SortBy or PurchaseBy instead, depending on the context of Order.
Failing to do so may not generate errors but could result in weird and difficult to debug behaviour.
Do not prefix tables with Sys, USys, MSys or a tilde ~.
Access has its own internal system tables starting with these prefixes and it’s best to stay away from these.
When a table is deleted, Access will often keep it around temporarily and it will have a tilde as its prefix.
Do not prefix Queries with a tilde ~.
Access use the tilde to prefix the hidden queries kept internally as recordsource for controls and forms.
Database design
Always use Primary keys.
Always have a non-null primary key column in every table.
All my tables have an autonumber column called ID. Using an automatically generated column ID guarantees that each record in a table can be uniquely identified.
It’s a painless way to ensure a minimum level of data integrity.
Do not use complex multivalue columns.
Access 2007 introduced complex columns that can record multiple values.
They are in fact fields that return whole recordset objects instead of simple scalar values. Of course, this being an Access 2007 only feature, it’s not compatible with any other database.
Just don’t use it, however tempting and convenient it might be.
Instead use a table to record Many-To-Many relationships between 2 tables or use a simple lookup to record lists of choices in a text field itself if you’re only dealing with a very limited range of multivalues that do not change.
Do not use the Hyperlink data type.
Another Access exclusive that isn’t available in other databases.
Be careful about field lookups.
When you create Table columns, Access allows you to define lookup values from other tables or lists of values.
If you manually input a list of values to be presented to the user, these won’t get transferred when upsizing to SQL Server.
To avoid having to maintain these lookup lists all over your app, you could create small tables for them and use them as lookup instead; that way you only need to maintain a single list of lookup values.
Be careful about your dates.
Access date range is much larger than SQL Server.
This has 2 side-effects:
1) if your software has to deal with dates outside the range, you’ll end-up with errors.
2) if your users are entering dates manually, they could have made mistakes when entering the year (like 09 instead of 2009).
Ensure that user-entered dates are valid for your application.
VBA
While most of your code will work fine, there are a few traps that will bomb your application or result in weird errors:
Always explicitly specify options when opening recordsets or executing SQL.
With SQL Server, the dbSeeChange is mandatory whenever you open a recordset for update.
I recommend using dbFailOnError as well as it will ensure that the changes are rolled back if an error occurs.
Get the new autonumbered ID after updating the record.
In Access, autonumbered fields are set as soon as the record is added even if it hasn’t been saved yet.
That doesn’t work for SQL Server as autonumbered IDs are only visible after the records have been saved.
Never rely on the type of your primary key.
This is more of a recommendation but if you use an autonumbered ID as your primary key, don’t rely in your code or you queries on the fact that it is a long integer.
This can become important if you ever need to upsize to a replicated database and need to transform your number IDs into GUID.
Just use a Variant instead.
Parting thoughts
These simple rules will not solve all your problems but they will certainly reduce the number of issues you’ll be faced with when upsizing you Access application.
Using a tool like SSMA to upsize will then be fairly painless.
If you have other recommendations, please don’t hesitate to leave them in the comments, I’ll regularly update this article to included them.
References
April 1st, 2009
I’m currently researching ways to move my main MS Access application from a simple local network client/backend setup to a global, multiple remote sites configuration using SQL Server.
One of the challenges is to upsize the current MS Access 2007 backend database to SQL Server 2008.
If you try it from Access itself using the Upsizing Wizard, you may end up getting this error message:
The Upsizing Wizard only works with Microsoft SQL Server (Versions 6.50 SP5 or higher). Please log in to a SQL Server data source.
After spending some time fiddling around with SQL Server settings I couldn’t understand why I was still getting this error.
Turns out that the upsizing wizard is apparently sensitive to the version of SQL Server you’re using and it doesn’t consider SQL Server v10 (2008) as being later than v6.50…
This issue is in fact a blessing.
Microsoft provides a migration tool for upsizing MS Access database to SQL Server 2008 that’s orders of magnitude better than anything the basic wizard can do: the SQL Migration Assistant for Access, or SSMA.
SSMA lets you take a bunch of Access databases and move the tables and queries you choose to SQL Server, automatically linking them in your original database if you want.
It’s not just a one-off thing either: SSMA keeps track of the objects that where transferred and allows you to synchronise both schema and data as often as you need.
So here you are: do not use the basic MS Access Upsizing Wizard, download and use SSMA instead.
Strange COM Error
While SSMA works perfectly fine on my Windows 2008 x64 laptop, on my main Windows XP desktop it throws an exception when trying to load an Access database:
Unable to cast COM object of type ‘Microsoft.Office.Interop.Access.Dao.DBEngineClass’ to interface type ‘Microsoft.Office.Interop.Access.Dao._DBEngine’
… {00000021-0000-0010-8000-00AA006D2EA4}…
It was a COM error saying that the library for DAO couldn’t be loaded.
I couldn’t find any relevant information on the web.
After a while, I had a look at the DAO driver in
C:\Program Files\Common Files\Microsoft Shared\DAO\dao360.dll
and I noticed that the filename was in blue: that reminded me that I had set compression on the filesystem.
I disabled compression for the file and, magically, SSMA worked again…
Moral of the story: be careful about compressing your filesystem, some registered libraries and system files may work in unpredictable ways…
References
Updates
- 17MAR2009: Added section on the strange COM error.
- 14MAR2009 : Added links to Tony’s Access MVP website.
- 05JAN2009 : Original publication.
March 17th, 2009
(Updated Wednesday 30JAN2009.) Developing applications that manipulate OpenOffice documents has always been rather tricky; not very difficult, but just tricky to get right.
With OpenOffice 3, things got trickier and applications that used to work will now fail.
I’ve just spend nearly a day trying to get a mail-merge application I built to work again with the new version of OO.
Changes and limitations
Developing .Net applications with OpenOffice 3 now requires that the .Net framework 3.5 be installed.
Only one version of OpenOffice/StarOffice must be installed.
Getting the CLI assemblies in your project
OpenOffice used to ship with a bunch of CLI DLLs for manipulating Open Documents from .Net.
With OpenOffice version 3, these DLLs are directly installed in the GAC and not available in the Program installation folder any longer, making them unavailable when you need to add them as references to your Visual Studio project.
The trick is to extract the DLLs from the installation CAB, then reference those and make sure you exclude them in your setup file so only the latest version installed on the user’s computer’s GAC will get used.
- Open the installation folder where the installation files are extracted (usually left on your desktop under a folder like
OpenOffice.org 3.0 (en-US) Installation Files during the OO installation process.
- Open the
openofficeorg1.cab file (using a utility like 7zip if necessary) and extract the files matching cli_*.dl.
- Add the ones you need to your VS Project’s references and make sure the properties of these references have their Copy Local and Specific Version properties set to false.
- If you have a setup project that added these references to the list of files, right-click each of them and select Exclude to make sure they won’t be packaged.
The reason for not deploying the DLLs is that they are very specific to a given OpenOffice version and the latest assemblies relevant to the user’s OpenOffice installation will already be deployed to the target machine’s GAC.
When .Net loads assemblies, if they are not included in the path of the application being launched, they will be loaded from the GAC if they can be found there.
Path and environment setup
Before being able to call OpenOffice from your application you now must set your application’s environment variables to the correct paths so the assemblies can find the actual OpenOffice library and program files.
Basically, you need to add to your PATH the path to the folder where the UNO java libraries reside.
You also need to add a UNO_PATH environment variable that points to the program folder of OpenOffice.
Basically, before any call to OpenOffice functions you must:
- Append to
PATH something like C:\Program Files\OpenOffice.org 3\URE\bin.
- Create a
UNO_PATH variable set to C:\Program Files\OpenOffice.org 3\program.
Because there is no guarantee that these paths will not change or are valid for all systems you must get them from specific keys located in the Registry:
PATH is appended with the vaue of HKLM\SOFTWARE\OpenOffice.org\Layers\URE\1\UREINSTALLLOCATION to which you must append the bin directory.UNO_PATH is set to the content of the HKLM\SOFTWARE\OpenOffice.org\UNO\InstallPath key.
See the C# and VB.Net code below for working examples.
Special Considerations for x64 systems
My development machine runs Windows Server 2008 x64 and I’ve ran into some specific issues that you’re likely to encounter when deploying to a 64 bits OS.
OpenOffice is 32 bits only
That means that your .Net project must be set to target x86 systems only:
Open your Solution’s Configuration Manager and under Active solution platform click New… then:
Make sure you repeat this for both the Debug and Release configurations.
Registry keys are elsewhere
32 bit applications see their registry keys normally expected under:
HKEY_LOCAL_MACHINE\Software moved to:
HKEY_LOCAL_MACHINE\Software\Wow6432Node instead.
This of course creates issues when you’re trying to read a registry key that’s not where it should be…
The Code
The code below will allow you to correctly connect to OpenOffice 3 under 32 or 64 bit systems.
It reads the registry to find the proper paths and appends the PATH and creates the UNO_PATH environment variables expected by the the bootstrapper to find the OpenOffice program and libraries.
The code is built upon information and a test program made available by Marten Feldtmann on his blog (more information, in English, is available on OOoForum ).
Please let me know if this works for you or if you have any corrections.
In VB.Net:
Updates
07MAY2009 -- Added reference link to OOo documentation.
03DEC2008 -- Added VB.Net translation. Thanks to Stefan for suggesting it.
30JAN2009 -- Added reference to Aleksandr Sazonov’s article on CodeProject (thanks for the the article).
References
November 6th, 2008
There are times when the coexistence of 64 and 32 bit code on the same machine can cause all sorts of seemingly strange issues.
One of them just occurred to me while trying to run the ASPx demos from Developer Express, my main provider of .Net components (the best supplier I’ve ever been able to find).
I was getting the following error:
The ‘Microsoft.Jet.OLEDB.4.0′ provider is not registered on the local machine:
It may look otherwise, but this error is generally due to either of two thing:
- you don’t have Office 2007/2010 Jet drivers installed
- or you are running a 32 bit application in a default x64 environment.
The first issue is easy to solve, just download the Access 2010 Database Engine from Microsoft (works with Access 2007 databases as well).
For the second one, the fix is also easy enough:
- For Windows 2008: Navigate to Server Manager > Roles > Web Server (IIS) > Internet Information Services (IIS) Manager, then look under your machine name > Application Pool.
- For Windows 7: Navigate to Programs > Administrative Tools > Internet Information Services (IIS) Manager, then look under your machine name > Application Pool.
Under there you can call the DefaultAppPool’s advanced settings to change Enable 32-Bits Applications to True:
You may have to restart the service for it to take effect but it should work.
References
Updates
- 10DEC2011: Updated driver link to use the Access 2010 engine.
- 03APR2010: Added instructions for Windows 7
- 12FEB2009: Added reference to Scott’s article.
- 28OCT2008: Original version
October 28th, 2008
Next Posts
Previous Posts
