Cypris’ lookout

Wednesday, September 9, 2009

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.

1 comment   |   Filed under :  Database, MSAccess, Programming

Wednesday, August 26, 2009

A story about exceptional service

Recently I found myself constrained by the puny 200GB of my Mac Book Pro and I bought a 500GB Seagate drive to replace it (a fast 7200 rpm one).
The Macbook Pro has no easy access for the drive so you have to resort to dismantling the case to access it. This put me off replacing the drive because I would probably be voiding the warranty and was running the risk of damaging this expensive piece of equipment.

I’ve been filling the drive with pictures from my recent camera purchase and I couldn’t put it off any longer, so I bought the new drive and went online to find some good tutorial on how to crack open the Macbook Pro case.

After a few searches, I noticed that many people were referring to the iFixit.com website. It was very easy to find the tutorial I was looking for, I didn’t have to register, and each step was made very clear and simple.
It took no time to open the case and replace the drive.
I was very happy with that find.

Now, that’s not the end of the story.

A couple of days before I replaced the drive the left fan of the laptop suddenly became noisy. This would happen a few times a day, at random, and would last 10-20 minutes.
My only solution to get this repaired was to get to the local Apple service shop. Even though I knew exactly which part number was to be replaced, they still wanted me to:

• go across town to visit them so they could see for themselves what the problem was: annoying because the problem was intermittent so I may have to go for nothing.
• wait for the part to arrive a few days later.
• go back to leave the laptop
• go again to collect the repaired laptop the next day or so. So all in all: about 6h spend travelling back and forth + no laptop for a couple of day + the risk that some indiscreet technician start looking through my personal stuff.

Instead, I went back to the iFixit website:

• identified my machine
• found out the list of spare parts available from their store
• added the fan to my cart
• paid for it.
• found a guide that showed how to replace the part.

That took me all of 10 minutes; I placed my order on Thursday and the next Monday I received the part … halfway across the globe!

I also got a survey request from iFixit and left some comments, from which I got back two nice detailed email follow-ups, one from the CEO saying they were implementing my remarks as part of their site improvement efforts.

Well, I thought I would share this story. It’s not that often that you get excited by an online vendor that not only does its job well but goes beyond expectations.

Add comment   |   Filed under :  Business, Hardware, Reviews, sysadmin

Thursday, June 11, 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).

1 comment   |   Filed under :  Database, MSAccess, Programming, SQL Server

Monday, April 13, 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   |   Filed under :  Database, MSAccess, SQL Server

Sunday, April 12, 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:

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.

Add comment   |   Filed under :  Database, Hardware, SQL Server, sysadmin