Cypris' lookout

Office 2010 SP1 and Access 2010 Runtime SP1 are broken

(Updated Saturday 10DEC2011.) On 28th of June 2011, Microsoft Office 2010 Service Pack 1 and the Access 2010 Runtime Service Pack 1 were issued.

After upgrading my development machine (Win7 x64) and a few clients (Windows 2008R2 x64) to SP1 (x86), I started to get strange issues:

• I use .Net libraries from my Access application and suddenly, even when not instantiating any .Net objects, Access would crash, usually on startup, but sometimes when opening the VBE.
  Decompiling and re-compacting the database would be OK, usually once, but the problem would reappear the next time I would restart the application.
  
• In the Runtime, I would get strange errors, such as The setting your entered isn’t valid for this property, or Action Failed Error Number: 2950, or Runtime Error 3270 Property not found.
  The strange thing about these errors is that they would occur in places that had not been modified for many releases of our application, parts that have been running without problem until now.
  
• Another weird issue was the systematic reset of our custom ribbon to its first tab. this could happen randomly, but most it could also be reproduced by simply opening a report as a tab page (that fill-in the whole MDI window). When closing that form, the first tab of the ribbon would select itself automatically. This wasn’t happening when closing pop-up windows.

After removing the Office and Runtime Service Pack 1, everything went back to normal.

A fix, finally!

A good 6 months after SP1 was released, Microsoft finally issued hotfix for the issue!
So, read about it on the MS Forums and get your KB2596585 hotfix, then decompile your database to clean it out.

12 comments July 13th, 2011

Access: checking blank variables

I often have to test String, Variant or Object variables that have no content and could be considered ‘blank’.

The problem is that testing for “blankness” can mean many different things to different types:

• For an Object type, the variable can be Nothing.
• For a String type, the string can have no content at all: "", vbNullString.
• For a Variant type, the string can have any of the following attributes or values:
  • it can be Missing if the variable is an unused optional parameter,
  • it can be Empty if it was never assigned,
  • it can be Null if, for instance it’s bound to a nullable field or unbound with no value,
  • it can be an empty string "", vbNullString.

When having to check these variables in code, it can be tiresome to have to go through testing some of these possibilities just to find out that your variable does or not not contains something useful, regardless of the type of variable you are using.

To avoid having to do all these tests, make the code a bit more tidy and allow me to move on to more important things, I use this small utility function quite often:

'----------------------------------------------------------------------------- ' True if the argument is Nothing, Null, Empty, Missing or an empty string . '----------------------------------------------------------------------------- Public Function IsBlank(arg As Variant) As Boolean Select Case VarType(arg) Case vbEmpty IsBlank = True Case vbNull IsBlank = True Case vbString IsBlank = (arg = vbNullString) Case vbObject IsBlank = (arg Is Nothing) Case Else IsBlank = IsMissing(arg) End Select End Function

So now I don’t have to worry so much about the type of the variable I’m testing when I want to know if it contains useful data:... ' Here assume that CustomerReference is a control on a form. ' By using IsBlank() we avoid having to test both for Null and empty string. If IsBlank(CustomerReference) Then MsgBox "Customer Reference cannot be left blank." End If ...

Obviously, IsBlank() doesn’t replace the other tests but I found it to be more straightforward to use in most cases.

2 comments September 9th, 2009

Access: Run-time Error 3155 ODBC insert on a linked table failed

I have been spending a lot of time trying to find out why some of the code used to insert new records into a linked SQL Server table would systematically fail with an error:

Run-time Error '3155' ODBC--insert on a linked table  failed

It was driving me mad.
I could insert a simple record using SQL Server Management Studio, I could add new records to the table in datasheet mode within Access, but as soon as I tried to insert a record from code, whether using DAO recordset or executing the same SQL INSERT, it would miserably fail.

After a fair bit of investigation and tests, of which you can read the full account on the question I asked on StackOverflow, it turns out that this is a long-standing bug in the ODBC Driver (or Access).

Memo fields in Access are usually translated into nvarchar(MAX) in SQL Server by tools like SSMA.
Unfortunately, when you link tables having these fields using the SQL Server Client driver, these fields get incorrectly interpreted as string, even though they appear ok from the table design view.
It’s only if you try to insert something into the field, either text larger than 255 chars or NULL, that you get the error message.

So, the solution, at least in this case, is to revert to the older SQL Server ODBC driver instead, or use varchar() instead of nvarchar(), but if you’re dealing with Unicode, you have to stick with nvarchar().

References

• My question Inserting NULL in an nvarchar fails in MSAccess on StackOverflow.
• A reference I found on this issue: Ms Access linking table with nvarchar(max).

2 comments June 11th, 2009

Access vs SQL Server: some stats (part 1)

In the perspective of upsizing my current Access 2007 application, I have been trying to understand a bit more about the possible performance impact of various choices of Primary Keys.

My problem is that currently, the Access application uses autoincrement numbers as surrogate Primary Keys (PK). Since I will need to synchronise the data over multiple remote sites, including occasionally disconnected clients, I can’t use the current autoincrement PK and will need to change to GUID.

To see for myself what could be the impact, I made a series of benchmarks.
This first part is fairly simple:

• Populate a Product table that contains 3 fields: ID, SKU and Designation with 1,000,000 records.
• Test natively on SQL Server and Access 2007.
• The records are inserted in transactions batches of 1000 records.
• I collect the time taken for each of these transactions and plot it.

Test setup

Nothing much to say about that:

All tests are performed on a dedicated Windows Server 2008 x64 rack running Access 2007 and SQL Server 2008 Standard (SP1) x64.

Test database

In SQL Server, we created a database with two tables ProductGUID and ProductInt:

CREATE TABLE ProductGUID ( ID UNIQUEIDENTIFIER DEFAULT NEWSEQUENTIALID(), SKU NVARCHAR(32) NOT NULL, Description NVARCHAR(255) NULL ); CREATE CLUSTERED INDEX ProdGUIDix ON ProductGUID(ID); GO CREATE TABLE ProductINT ( ID INT IDENTITY(1,1), SKU NVARCHAR(32) NOT NULL, Description NVARCHAR(255) NULL ); CREATE CLUSTERED INDEX ProdINTix ON ProductINT(ID); GO

For the table using a GUID, we use the NewSequentialID() instead of NewID() to create new keys. This is supposed to offer much better performance as the generated GUIDs are guaranteed to be sequential rather than random, resulting in better index performance on insertion.

For the Access version of the tables, we basically use the same definition, except that we used 4 tables:

• ProductINT: let Jet/ACE autonumbering create the sequential integer Primary Key.
• ProductINTRandom: let Jet/ACE autonumbering create the random integer Primary Key.
• ProductGUIDRandom: let Jet/ACE use its own internal GenGUID() for the key which generates random GUIDs instead of sequential ones.
• ProdcutGUIDSequential: call the Windows API (UuidCreateSequential) to create sequential ID instead.

SQL Server Test code

Using the SQL Server Management Studio, we performed the following test once for each table (resetting the database in-between):SET NOCOUNT ON; GO DECLARE @i INT = 1; WHILE (@i <= 1000) BEGIN DECLARE @tstart DATETIME2 = SYSDATETIME(); BEGIN TRAN DECLARE @a INT = 1; WHILE (@a <= 1000) BEGIN INSERT INTO ProductGUID (SKU,Description) VALUES ('PROD' + CONVERT(CHAR,@a), 'Product number ' + CONVERT(CHAR,@a)); SELECT @a = @a + 1; END; COMMIT TRAN; SELECT DATEDIFF(MILLISECOND, @tstart, SYSDATETIME()) AS timespan; SELECT @i = @i + 1; END; GO

Basically, we perform 1000 transactions each inserting 1000 records into the table ProductGUID or ProductINT.

Access 2007 Test code

To duplicate the same conditions, the following VBA code will perform 1000 transactions each inserting 1000 records.
Note that the recordset is opened in Append mode only.
The importance of this will be discussed in another article.

' Run this to inset 1,000,000 products in batches of 1000 ' In the given table Public Sub Benchmark(TableName as String, InsertSeqGUID as Boolean) Dim i As Integer For i = 1 To 1000 Insert1000Products TableName, InsertSeqGUID Next i End Sub ' Insert 1000 products in a table Public Sub Insert1000Products(TableName as String, InsertSeqGUID as boolean) Dim i As Long Dim db As DAO.Database Dim rs As DAO.Recordset Dim ws As DAO.Workspace Dim starttime As Long Dim timespan As Long Set ws = DBEngine.Workspaces(0) DoEvents starttime = GetClock ' Get the current time in ms ws.BeginTrans Set db = CurrentDb Set rs = db.OpenRecordset(TableName, dbOpenDynaset, dbAppendOnly) With rs For i = 1 To 1000 .AddNew If InsertSeqGUID Then !ID = "{guid {" & CreateStringUUIDSeq() & "}" !SKU = "PROD" & i !Description = "Product number " & i .Update Next i End With ws.CommitTrans rs.Close timespan = GetClock() - CDbl(starttime) Set rs = Nothing Set db = Nothing ' Print Elapsed time in milliseconds Debug.Print timespan DoEvents End Sub

We call this code to perform inserts on each of our Access tables:

• ProductINT table: we just insert data in the ProductINT table, letting Access create autonumber IDs.
• ProductINTRandom table: we just insert data in the ProductINTRandom table, letting Access create random autonumber IDs.
• ProductGUIDRandom table: we let Access create the Random GUID for the primary key.
• ProductGUIDSequential: we use the Windows API to create a sequential ID that we insert ourselves.

Test results

Without further ado, here are the raw results, showing the number of inserted record per second that we achieve for each test over the growing size of the database (here are only shown tests comapring Sequantial GUID and Autoincrement on SQL Server and Access, see next sections for the other results):

What we clearly see here is that performance when using autoincrement and Sequential GUID stays pretty much constant over the whole test.
That’s good new as it means that using Sequential GUIDs do not degrade performance over time.

As a side note, in this particular test, Access offers much better raw performance than SQL Server. In more complex scenarios it’s very likely that Access’ performance would degrade more than SQL Server, but it’s nice to see that Access isn’t a sloth.

Using Sequential GUID vs Autoincrement in Access

The results show that we do take a performance hit of about 30% when inserting Sequential GUID vs just using autonumbers.
We’re still getting good results, but that’s something to keep in mind.

In terms of CPU consumption, here is what we get:

Random PK, whether they are simple integer or GUID do consume substantially more CPU resources.

Using Sequential GUID vs Identity in SQL Server

Out-of-the box, SQL Server performs quite well and there is not much difference whether you’re using Sequential GUIDs or autoincrement PK.

There is however a surprising result: using Sequential GUIDs is actually slightly faster than using autoincrement!

There is obviously an explanation for this but I’m not sure what it is so please enlighten me :-)

CPU Consumption:

Using Random GUID vs Sequential GUID vs Random Autonumber in Access

So, what is the impact of choosing a Sequential GUID as opposed to letting Access create its own random GUIDs?

It’s clear that random GUIDs have a substantial performance impact: their randomness basically messes up indexing, resulting in the database engine having to do a lot more work to re-index the data on each insertion.
The good thing is that this degradation is pretty logarithmic so while it degrades over time, the overall performance remains pretty decent.
While GUIDs are larger than Random Integers (16 bytes vs 4 bytes) the actual performance of inserting records whose PK is a random integrer is actually worse than random GUID…

Provisional conclusions

Here we’ve check the baseline for our performance tests. In the next article, we’ll look exclusively at the performance of inserting data from a remote Access 2007 front end using our VBA code.

Having this baseline will allow us to check the performance overhead of using ODBC and letting Jet/ACE manage the dialogue with the SQL Server backend.

Feel free to leave your comments below, especially if you have any resources or information that would be useful.

Updates

• 16APR2009: added test of random autonumber as PK in Access.
• 13APR2009: Original Article

15 comments April 13th, 2009

Sysadmin: SQL server performance madness

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:

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:

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.

Add comment April 12th, 2009