SQL may be much more useful to you than you think. In this first in a series of articles on SQL, Peter introduces SQL and compares it to DAO for ease of use and performance. He also includes a utility to transfer table designs among SQL databases.
IF I have one complaint about Access it’s that using it can prevent you from fully discovering the power of SQL. There are experienced Access developers out there who have never written a SQL command and see no reason to. They assume that as long as they can create Access queries or write code using Data Access Objects to manipulate tabledefs and recordsets they don’t need to learn SQL.
There are also people out there who are so deep into SQL that they’ll tell you that anything you can do to data with code can be done better with SQL. Having met people like this I can assure you that, while they’re nice enough people, you wouldn’t want your sister/brother/maiden aunt to marry one. However, they’re probably right. I’m going to use this article (and several others that will follow) to show you some of the things that SQL can do for you.
For this article, a lot of the points I’m going to make are related to speed. To demonstrate those points I’ve made available some sample queries and routines in an Access 95 database called SQLDDL.MDB (you can find it in the Subscriber Downloads area at www.pinpub.com/access). I chose Access 95 so that the results reflect the latest version of the Jet database engine that I had at the time I wrote this article; all the code shown in this article, however, will work with Access 2.0 and the new Access 97. These routines perform some action 10 or 100 times and print the elapsed time in the Debug Window. I’ll refer to the relevant routines as I go through the article and quote the timings that I got. Of course, the numbers will be different on your computer, but the relative differences between the timings should be constant. So, if I say that one routine ran in 10 seconds and another in 20 you may find that your timings are five seconds and 10 seconds. While the numbers change, the faster routine should remain twice as fast as the slower. I ran each sample three times and I’ll be quoting the lowest time for the three trials.
This article will concentrate on just one part of the SQL language. The SQL commands that most of us use most of the time are related to manipulating data. These commands (Select, Update, and Delete) are referred to as the Data Manipulation Language (DML) portion of SQL. In this article, however, I’m going to focus on some of the commands that make up the Data Definition Language (DDL) portion of SQL, the commands used for creating and maintaining data structures such as tables and indexes. I’m going to do this by looking at a common programming problem: creating tables from within your program.
Why would you want to create a table from a program? The first and most common reason is that you’re creating a work table to hold some data for further processing. The second reason is that you don’t know the format of the table until the user starts running the application and, as a result, you must create the table on the fly. Let’s look at the work table situation first.
Working fast
While it’s quite easy to generate a work table using a Make Table query, the resulting table will lack one important ingredient: indexes. Without indexes any subsequent queries on the work table’s data will run very slowly indeed
If you work entirely with Access queries you may have resolved this problem by creating the work table in advance with all the necessary indexes. When the time comes to load the table, you first run a query to delete the records already in it and then run an Append query to add the new records to the work table. I have two problems with this solution. The first is that, if the format of the work table changes, you’ll have to change both the format of the table and the append query that loads it. I hate doing maintenance, so this is an unattractive solution. The other problem is more important: this delete and append process runs longer than the table create method. A lot longer.
In the sample database, the routine TestAddByAppend deletes 2,000 records from a table and then appends them back to the table. Doing this 10 times takes about 29 seconds on my computer. The routine TestAddByCreate runs a Make Table query and then creates the equivalent index on the table. TestAddByCreate takes only 12 seconds for 10 executions. As I said, your mileage may vary, but this difference (more than 200 percent) should remain.
What’s great about the Make Table method is that the SQL command to add the index to the table is trivial. This is the syntax for creating indexes:
Create Index <indexname> on <tablename> _ (<columnname>, <columnname>,…,);
In my example, which creates an index named “Test” over the columns OrderId and ProductId in the table tblTargetCreate, the code looks like this:
Dim db as Database Set db = CurrentDb() db.Execute "Create Index Test on _ tblTargetCreate (OrderId, ProductId);" db.Close
What about DAO?
Of course you could create an index using Data Access Objects (DAO). While I’m very fond of using DAO, it’s often not the easiest coding style in the world. Compare the code we just used to create an index to the equivalent DAO-based code:
Dim db as Database
Dim tbl as TableDef
Dim fld as Field
Dim idx as Index
Set db = CurrentDb()
Set tbl = db.TableDefs("tblTargetCreate")
Set idx = tbl.CreateIndex("Test")
Set fld = idx.CreateField("OrderId")
idx.Fields.Append fld
Set fld = idx.CreateField("ProductId")
idx.Fields.Append fld
tbl.Indexes.Append idx
The SQL-based code is much simpler, though the DAO code, surprisingly enough, runs marginally faster (about one second over 10 iterations). However, I find the SQL so much easier to write and the difference so small that I prefer to use the SQL. Besides, the testing strategy I’m using for these time trials is so rough and ready that I wouldn’t put much stock in small differences in timing.
By the way, the SQL syntax for deleting an index is even simpler than the SQL for creating it:
Dim db as Database
Set db = CurrentDb()
db.Execute("Drop Index Test on tblTargetCreate;")
db.Close
Or you could use DAO:
Dim db as Database
Dim tbl as TableDef
Set db = CurrentDb()
Set tbl = db.TableDefs("tblTargetCreate")
tbl.Indexes.Delete "Test"
tbl.Close
db.close
While in this case the DAO code seems only slightly more complicated than the SQL, it’s difficult for me to see any benefits from using DAO as opposed to SQL.
If you’re working with macros rather than code you must use SQL rather than DAO. You can execute SQL commands two different ways from within a macro. The first way is to embed the SQL in the macro as the argument for the RunSQL action. The second way to use SQL commands in macros is to create a query containing the SQL command and run the query using
the OpenQuery action. To create a query containing DDL statements you must open a new query in design mode, but don’t add a table to it. Once you’ve done that, you pick SQL from the View menu and type your command into the edit window that appears. You can then save the query like any other. I prefer this method because the simple act of naming the query when you save it provides some documentation for it. You can also take advantage of the query’s description property to provide further documentation.
Building tables
Well, then, now that you’ve decided to create your work tables on the fly, what’s the best way to create a table on the fly? In fact, when you don’t know the format of the table until runtime, you can’t create the table in advance so your options are more limited than the work table situation. You can build a table using either DAO or SQL. You can probably figure out which method I prefer, but let’s look at the code first.
The DAO code for creating a table isn’t quite as mind-numbingly complicated as the code for creating an index. Here’s the code to create the table tblTest with three fields (Sam, Mary, and Jane) of three different data types:
Dim db As DATABASE
Dim tbl As TableDef
Dim fld As Field
Set db = CurrentDb()
Set tbl = db.CreateTableDef("tblTest")
Set fld = tbl.CreateField("Sam")
fld.Type = dbText fld.Size = 2
tbl.Fields.Append fld
Set fld = tbl.CreateField("Mary")
fld.Type = dbInteger tbl.Fields.Append fld
Set fld = tbl.CreateField("Jane")
fld.Type = dbDouble tbl.Fields.Append fld
db.TableDefs.Append tbl
db.Close
The equivalent activity using SQL requires only four lines: Dim db As Database
Set db = CurrentDb() db.Execute "Create Table tblTest " & _ "(sam char(2), mary integer, jane double);" db.Close
To be fair to DAO, though, you can set a lot of field properties through DAO that aren’t part of the SQL standard (the Caption and ValidationRule properties leap to mind, for instance). However, in the work table scenario we’re discussing here, you probably won’t be using them.
Of course if you want to create a table, you first have to make sure that it doesn’t already exist. Deleting a table that doesn’t exist using DAO or using SQL looks about the same. Here’s the SQL version:
Dim db As Database
Set db = CurrentDb()
db.Execute ("Drop Table tblTest;")
db.Close
And here’s the DAO:
Dim db As Database
Set db = CurrentDb()
db.TableDefs.Delete "tlbTest"
db.Close
There’s very little to choose between the two syntactically and I tend to switch between the two methods arbitrarily. However, in writing this article I started wondering which method runs faster. In the sample database, the two routines I created to test the methods are TestCreateTableBySQL and TestCreateTableByDAO. Both routines create a table and delete it 100 times. Unlike the Index creation, the SQL create and delete is significantly faster than the DAO create and delete, typically by 50 percent (20 seconds for the SQL compared to 30 seconds for the DAO). I’ll use the SQL method for dropping tables more consistently in the future
The format of Create Table command is only slightly more complicated than the Create Index:
Create Table <tablename> (<columndefinition>, <columndefinition>, ….);
A column definition consists of the name of the field followed by its type (for example, “Mary Integer”). By the way, this particular data type runs into one of the inconsistencies between the SQL and VBA data types: what SQL calls Integer is what VBA calls Long. If the column’s type is character, you must also provide a length in parentheses (for example, “Sam Char(2)” to create a two-character field). If you read my March 1997 article on creating a data warehouse using Access, you’ve seen a routine that assembles a SQL Create Table statement from a data dictionary and then executes it to create a table.
If you thought the SQL for deleting an index was simple, the syntax for deleting a table is even more trivial:
Drop Table <tablename>;
Using queries
I also ran an additional set of tests using queries. I’ve always advocated keeping SQL statements in queries and running those queries from code, rather than using the Execute statements to run SQL commands. The theory is that the SQL commands stored in queries are already parsed, compiled, and optimized, so their execution time should be shorter than submitting a raw SQL command at runtime. This may well be true for DML statements, but it isn’t true for DDL.
The routine TestTableCreateByQuery calls two queries repeatedly. One query contains a SQL statement to create our sample table, the second a SQL query to delete it. For 100 iterations, the query-based routine runs about 33 percent longer than the DAO version and almost twice as long as the SQL version. Because this was the exact opposite of what I expected, I probably spent more time on this routine than any other, trying to get the practice to match my theory. In the end I decided that there seems to be no optimization associated with (at least some) DDL stored in queries.
A lingua franca
My mom always said that good manners will take you anywhere, and SQL will too. The best thing about being familiar with SQL is that it’s supported on virtually every data storage system in the known universe, even if the underlying storage mechanism isn’t relational. If you create a table in Access but want to transfer it to another database system, you may be stuck. But, if you can create the equivalent SQL command for that table, you can just execute that SQL command on your target database system to re-create your table.
Unfortunately, Access doesn’t provide a way to generate the equivalent SQL statement for a table in a database. The code to do so is relatively trivial if you use DAO to walk through the table’s structure and some standard string handling to assemble the SQL commands.
In the sample database I’ve provided a form called frmMakeSQL (see Figure 1). The form presents a list of tables in the database and allows you to select one. The code behind the form (see Listing 1) will then write to the file specified in the filename text box the SQL commands to create that table (with its indexes). The file’s name defaults to <tablename>.SQL, but you can change it to whatever you want.
Listing 1. This routine accepts a table name and a filename, and then writes the SQL command to re-create the table to the file.
Public Sub WriteSQL(strTableName As String, strFileName As String)
‘ Passed a table name and a filename,
‘ write to the file the SQL to create the table.
Dim db As DATABASE
Dim tbl As TableDef
Dim fld As Field
Dim idx As INDEX
Dim ysnFirst As Boolean
Set db = CurrentDb()
Set tbl = db.TableDefs(strTableName)
Open strFileName For Output As #1
‘ Write the SQL to create the table.
Print #1, "Create Table " & _ strTableName
Print #1, "("; ysnFirst = True
For Each fld In tbl.Fields
If Not ysnFirst Then
Print #1, ","
End If
ysnFirst = False
Print #1, fld.Name & " ";
Select Case fld.Type
Case dbText
Print #1, "Char(" & fld.Size & ")";
Case dbInteger
Print #1, "Integer";
Case dbLong
Print #1, "Long";
End Select
Next fld
Print #1, ");"
‘ Write the SQL to create the indexes.
For Each idx In tbl.Indexes
Print #1, "Create Index " & idx.Name & " on " & strTableName
Print #1, "(";
ysnFirst = True
For Each fld In idx.Fields
If Not ysnFirst Then
Print #1, ","
End If
ysnFirst = False
Print #1, fld.Name & " ";
Next fld
Print #1, ");"
Next idx
Close #1
End Sub
Stay tuned
This is just the tip of the iceberg. Next time I’m going to look at what happens when you use two tables in a query and don’t join them together. The result isn’t a bug but a frequently useful feature that tells you a lot about the underlying design of SQL itself. I, for one, can’t wait. In later articles I’ll discuss (briefly) the SQL required to create relationships and allow you to select multiple tables and write them all to the same file, along with any relationships between them.
Buy the Smart Access PDFs and Downloads
