How to Load MS Access Tables as Quickly as Possible

Last week’s post focused on the differences between tables sourced from MS Access versus SQL.  My current project is hitting the limits of MS Access, and we are discussing migrating to SQL.  For now, I’m stuck with MS Access.  I say “stuck with” because loading tables into Spotfire from MS Access can be incredibly slow.  Because many other users have the same problem, let’s talk about how to make the best of it.

Prelude

Before I begin, I want to mention one thing.  I write about Spotfire every week, and I realized recently I don’t ever specify which version of Spotfire I use.  That is kind of a big deal, so from this point forward, I will always include a closing notation to specify which version of Spotfire I use to develop content.

Connecting to MS Access

Now, as mentioned last week, there are two ways to connect to MS Access — Open from File or via a database connection (ODBC, OLEDB, etc).

Testing MS Access

I have historically used Open from File because it’s just easy.  However, you cannot edit the query, which is a big downside.  Also, load time critical in my current project.  Thus, I whipped out the iPhone stopwatch and tested the two methods.  Here are the conditions of my tests

  • Platform: Spotfire Analyst Client on my computer with 16GB of RAM
  • Location of the data: Database in Denver, and I am in NW Montana.
  • Conditions:
    • VPN to Denver.  The users of this project will be located in Midland with a marginally better internet connection on the company network.
    • Tests were performed one after another, and other tasks were minimized during load (ie. checking email and putzing on the internet).
  • Data Set: 541,350 records and 10 columns.

The results are shown below.  Now, the absolute time isn’t as important as the relative time.  This is not good.  This is slow.  Real slow.

Source Start Time End Time Total Load Time
ODBC 12:34:34 12:53:11 0:18:37
File 12:56:21 13:13:49 0:17:28

For the purpose of comparison, I asked a user in Denver and a user in Midland to Open from File.  The Denver user experienced significantly better performance.  Tables loaded in approximately 30 seconds (remember, the database is in Denver), but the Midland user (the users of the DXP) experienced load times similar to mine, between 13 and 15 minutes.

Options

Okay, so what can we do about this? The project contains at least two large MS Access tables that load in 13-18 minutes each, which isn’t acceptable.  Two options remain — Citrix and load the table as a data set to the library and connect to the data set.

  1. Load as a data set
    • Pros: Loads in 30s (Yay!)
    • Cons: Static, not ta live data connection (Boo!)
  2. Citrix
    • Pros: Loads in 30s (Yay!)
    • Cons: If using ODBC connection, must install ODBC and configure on Citrix.  Some users dislike Citrix and/or experience latency or connectivity issues with Citrix.  More steps to launch Spotfire. (meh, okay fine…).

If your company does not have Citrix, then loading as a data set is the only way to get the table to load faster.  For my purposes, I will use Citrix, even though I have latency and connectivity issues occasionally.  The load time is an order of magnitude faster, so I will suck it up.  However, if you want to try out loading to the library as a data set, follow the steps below.

Loading from Library as a Data Set

If you aren’t familiar with loading a data set, here is how it works.

  1. Load the MS Access table as you normally would (ODBC or Open from File).
  2. Go to File > Export > Data to Library to save the data set to the library.  Essentially, you export to import.  It’s counter-intuitive.
  3. Replace the table using the Data from Library option to connect the DXP to the data set instead of MS Access.

And those are all the options.  I hope this helps you get the most out of MS Access.

Spotfire Version

Spotfire version 7.9.1 was used to develop this content.

Guest Spotfire blogger residing in Whitefish, MT.  Working for SM Energy’s Advanced Analytics and Emerging Technology team!

Leave a Comment

Your email address will not be published. Required fields are marked *