• 32 — Unleashing SQL
• by Frank Font
• Some SQL History and Background
• Harnessing the Power of SQL
• Examining the Dangers of SQL
• Understanding Some Common Terms
• Using SQL Syntax
• A Tip for Access Users
• Looking at Simple Query Examples
• Looking at Some Simple Action Query Examples
• Examining Some Advanced Concepts
• Working with Complex SQL Queries
• Optimizing Your Queries
• Making RDBMS-Specific Enhancements
• Summary

— 32 —

Unleashing SQL

by Frank Font

Everybody in the database world is talking about, has talked about, or will be talking about SQL. SQL stands for Structured Query Language. The reason it's such a hot topic is that this "query language" can express very complex database operations concisely and nonprocedurally. This is particularly significant when working in a client/server environment, as you will see in Chapter 33, "Visual Basic 4.0 and Client/Server Programming." However, as working with SQL in Visual Basic 4.0 demonstrates, client/server is not required to benefit from the power of SQL.

This chapter starts with a little background information about SQL and its standards. It then goes into a comparison of SQL database access versus other methods. The chapter uses introductory concepts and terms at first, but then moves into more advanced topics toward the end. Along the way, you will see examples that can be coded in Visual Basic 4.0.

If you are not already comfortable with general database concepts and the terminology commonly used when discussing them, now may be a good time to review Chapter 31. That chapter introduces many of the concepts that this chapter assumes the reader already knows.

Most of the examples in this chapter, as with Chapters 31 and 33, refer to the database of the BTS program. This program and its database are described in Chapter 36. Sample code for this program and its database can be found on the CD-ROM that accompanies this book.

Some SQL History and Background

Although it had its roots in IBM labs as far back as the late 1960s, SQL as a database interface language first was implemented commercially by Oracle in 1979. By 1986, when the ANSI committee published its first SQL standards, it was available from several other vendors and was growing in popularity. ANSI updated the standard in 1989 and then again in 1992. SQL-92 is the latest ANSI version of the SQL language. Among other things, the latest standard details language extensions for the creation and modification of databases.

The level of SQL-92 compliance you can expect in your Visual Basic 4.0 programs depends on the driver and database system you use. The Jet Engine database generally is only SQL-89 compliant, with some extra functionality and keywords not in that standard. This means that some databases, such as Access and FoxPro, do not support all the newer features and keywords when used directly though the Jet Engine.

If you are using SQL on an ODBC ISAM database, the level of support is entirely dependent on the ODBC driver and may be completely SQL-92 compliant. If you are using client/server, the degree of compliance is determined by your RDBMS. Most of the newer RDBMS servers, such as Oracle7, Sybase, and Microsoft SQL Server support SQL-92 extensions.

Harnessing the Power of SQL

You can do in one SQL statement what could take an entire program to do with procedural data access methods. Sometimes, particularly in the case of client/server environments, letting the RDBMS carry out the request specified by a SQL statement is faster than constructing a program to do the same job yourself step by step. Listing 32.1 shows you a simple example.

Listing 32.1. Iterative database manipulation.

'——————————————————————————————————

'Example of a table update via iterative methods.

'Visits each record of the tblCandidate Table because no

'assumption with respect to record order is made.

'

'This procedure assumes the following:

'1. Data1 control is on the form and is set to query

'   the BTS sample program database.

'——————————————————————————————————

Sub DoUpdateBySteps()

    Dim MyCriteria As String

    ' Open recordset.

    Data1.RecordSource = "tblCandidate"

    Data1.Refresh

    'Count the matching records.

    MyCriteria = "Title = 'Mr'"

    Data1.Recordset.FindFirst MyCriteria

    Do Until Data1.Recordset.NoMatch

        Data1.Recordset.Edit

        Data1.Recordset!Available = False

        Data1.Recordset.UPDATE

        Data1.Recordset.FindNext MyCriteria

    Loop    ' End of loop.

End Sub

This procedure sets the Available field to False for each tblCandidate record with a Title value of "Mr." The simpler procedure shown in Listing 32.2 accomplishes the same thing with one SQL action query.

Listing 32.2. SQL database manipulation.

UPDATE     tblCandidate

SET     Available = FALSE

WHERE             Title = 'Mr';

You can issue this action query from a Visual Basic program, as shown in Listing 32.3.

Listing 32.3. SQL database manipulation coded in Visual Basic 4.0.

'——————————————————————————————————

'Example of a table update via a single SQL statement.

'Only records containing 'Mr' are visited if an index starting

'with the Title field exists. Otherwise, all records are visited

'by the RDBMS.

'

'This procedure assumes the following:

'1. Data1 control is on the form and is set to query

'   the BTS sample program database.

'——————————————————————————————————

Sub DoUpdateBySQL()  Dim SQL As String

    'Define the query.

    SQL = "UPDATE tblCandidate "

    SQL = SQL + "SET Available = FALSE "

    SQL = SQL + "WHERE Title = 'Mr'"

    'Execute the query.

    Data1.DATABASE.Execute SQL, dbFailOnError

End Sub

Note

It is important to use the dbFailOnError option, as in the preceding code sample, when employing the Execute method to carry out your SQL action queries. This ensures that a trappable error is generated if any of the actions specified in the SQL statement cannot be carried out. This option is also important if you are wrapping your statements into transactions. Transactions are discussed in Chapter 33.

The effect of using Listing 32.1 at runtime depends on the type of database that the table it updates resides in. If the table is in an ODBC database, each iteration may require a new ODBC query at the Jet Engine level. Translation: Slow performance that gets noticeably slower as the database increases in size. The routine could be optimized by making sure that the records are first sorted by Title. Then the code can be rewritten so that only the "Mr" records are visited. However, in a multi-user environment, runtime database sorting or index creation can be impractical if not impossible. If the index with the particular sort you need does not already exist, you are out of luck.

In contrast, the SQL example sends the complete instruction of your desired action in one step to the database driver. If an appropriate index exists, it is used to speed the operation. The specific nature of the execution depends on your database:

• If the database is native Jet (MDB), the Jet Engine interprets the instruction and executes it directly.
  
  
• If the database is ODBC ISAM, the ODBC driver for the database interprets the instruction and executes it.
  
  
• If the database is client/server, the RDBMS server receives the instruction and carries it out on its CPU. This frees up the user workstation while the query is carried out.
  
  

Sometimes a SQL approach is not only faster to execute, but also simpler to code. For example, if you want to create a list of skills that no candidate in the tblCandidate table has, you can write a single SQL statement or a program to iterate through the tables. To code this iteratively, you, the programmer, would have to check each record of the tblSkill table against the tblCandidateSkill table to see if it was already in use. Listing 32.4 is such a program. However, the Jet RDBMS of Visual Basic 4.0 is smart enough to know and deliver what you want when it sees the SQL statement of Listing 32.5. All the benefits and issues that applied to the code of Listings 32.2 and 32.3 apply here also.

Listing 32.4. Filtered list created iteratively.

'——————————————————————————————————

'Show skills in a list box using iterative methods.

'All skills that the candidate does not have are shown.

'

'This procedure assumes the following:

'1. Data1 control is on the form and is set to query

'   the BTS sample program database.

'2. List1 control is on the form.

'——————————————————————————————————

Sub ListNewSkillsBySteps(CandidateKey As Long)

    Dim MyCriteria As String

    Dim Count As Integer

    'Clear the list box.

    List1.Clear

    ' Open recordsets.

    Data1.RecordSource = "tblSkill"

    Data1.Refresh

    Data2.RecordSource = "tblCandidateSkill"

    Data2.Refresh

    'Build the list.

    Count = 0

    Data1.Recordset.MoveFirst

    Do Until Data1.Recordset.EOF

        'Define the search criteria.

        MyCriteria = "CandidateKey = " + Str$(CandidateKey) + _

            " AND SkillKey = " + Str$(Data1.Recordset!SkillKey)

        Data2.Recordset.FindFirst MyCriteria

        If Data2.Recordset.NoMatch Then

            'Add the skill to the list since candidate doesn't have it.

            List1.AddItem Data1.Recordset!Name, Count

            Count = Count + 1

        End If

        Data1.Recordset.MoveNext

    Loop    ' End of loop.

End Sub

It is important to note that the program of Listing 32.4 uses two data controls. One is for the table that holds the skill names, and one is for the table that defines the skills a candidate possesses. Compare Listing 32.4 to the procedure shown in Listing 32.5, which needs only one data control. The result is that the SQL method in this case is not only easier to code, it may also use fewer workstation resources. (Since the amount of resources used depends heavily on the type of RDBMS employed, the database, and several other factors, a blanket statement that such SQL statements will always use fewer resources cannot be made.)

Listing 32.5. Filtered list created via SQL.

'——————————————————————————————————

'Show skills in a list box using a SQL statement.

'All skills that the candidate does not have are shown.

'

'This procedure assumes the following:

'1. Data1 control is on the form and is set to query

'   the BTS sample program database.

'2. DBList1 is on the form with RowSource as Data1.

'——————————————————————————————————

Sub ListNewSkillsBySQL(CandidateKey As Long)

    Dim SQL As String

    'Define the query.

    SQL = "SELECT DISTINCTROW tblSkill.Name"

    SQL = SQL + " FROM tblSkill LEFT JOIN"

    SQL = SQL+ " tblCandidateSkill ON tblSkill.SkillKey"

    SQL = SQL + " = tblCandidateSkill.SkillKey"

    SQL = SQL + " WHERE tblSkill.SkillKey NOT IN"

    SQL = SQL + " (SELECT tblCandidateSkill.SkillKey"

    SQL = SQL + "  FROM tblCandidateSkill"

    SQL = SQL + "  WHERE tblCandidateSkill.CandidateKey"

    SQL = SQL + " = " + Str$(CandidateKey) + ")"

    'Execute the query.

    Data1.RecordSource = SQL

    Data1.Refresh

    'Fill the listbox.

    DBList1.ListField = "Name"

    DBList1.Refresh

End Sub

Although the SQL statement in Listing 32.5 is complex, its performance can be tremendously better than that of the iterative method. The fact that some statements are complex should not be a significant deterrent to implementing SQL in Visual Basic programs, because there are several ways to create these statements automatically using third-party tools. Even if the statements are constructed manually, the payback in the performance of the application can be well worth the effort.

Now look at one more example that illustrates the potential for simplification and performance enhancement, as shown in Listing 32.6.

Listing 32.6. Count candidate skills iteratively.

'——————————————————————————————————

'Pass in the key of the candidate. Return the number of skills

'that candidate has. Processes record by record.

'

'This function assumes the following:

'1. Data1 control is on the form and is set to query

'   the BTS sample program database.

'——————————————————————————————————

Function HowManySkillsBySteps(CandidateKey As Long) As Integer

    Dim SkillsCount As Integer

    Dim MyCriteria As String

    'Initialize the counter.

    SkillsCount = 0

    ' Open recordset.

    Data1.RecordSource = "tblCandidateSkill"

    Data1.Refresh

    'Count the matching records.

    MyCriteria = "CandidateKey = " + Str$(CandidateKey)

    Data1.Recordset.FindFirst MyCriteria

    Do Until Data1.Recordset.NoMatch

        SkillsCount = SkillsCount + 1

        Data1.Recordset.FindNext MyCriteria

    Loop    ' End of loop.

    'Return the result.

    HowManySkillsBySteps = SkillsCount

End Function

The function in Listing 32.6 counts the number of skills a specified candidate possesses. This is done by finding the first skill and then iterating through each skill until all skills have been processed. Contrast that process to the SQL version shown in Listing 32.7.

Listing 32.7. Count candidate skills via SQL.

'——————————————————————————————————

'Pass in the key of the candidate. Return the number of skills

'that candidate has. Process via single SQL query.

'

'This function assumes the following:

'1. Data1 control is on the form and is set to query

'   the BTS sample program database.

'——————————————————————————————————

Function HowManySkillsBySQL(CandidateKey as Long) As Integer

    Dim SQL As String

    'Define the query.

    SQL = "SELECT count(*) as [Total] FROM tblCandidateSkill"

    SQL = SQL + " WHERE tblCandidateSkill.CandidateKey = " + _

        Str$(CandidateKey)

    'Execute the query.

    Data1.RecordSource = SQL

    Data1.Refresh

    'Return the result.

    HowManySkillsBySQL = Data1.Recordset![Total]

End Function

The BTS database does not have many skills assigned to any candidate, so the counts are not very large. If a count operation is executed on a large database, or there are many records to count, the performance of the SQL implementation can be remarkably better than that of the procedural method.

Examining the Dangers of SQL

The power of the SQL language to generate very complex results is also a source of danger. It is sometimes easy to forget that the requests defined by SQL statements will be carried out at some point by a computer somewhere. If the database is in a client/server environment, then that computer is the RDBMS server. Otherwise, the computer carrying out the SQL request is the user's machine.

There are many factors that can make a query execute inefficiently. In general, one way to ensure efficient query execution is to make sure your databases are properly indexed. If you are using SQL Passthrough, you also might want to order your statements in the best way for your particular RDBMS. Specifics vary from database to database. Consult the documentation of your RDBMS or your DBA, if you have one.

Additionally, if your database is on a network or you have a client/server database, network bandwidth is a consideration. To reduce the impact of data access on network bandwidth, it is best to return as few rows as possible. If you have a large set of data that a user needs to browse, consider requesting only a portion of that data at a time as the user needs to see it. Otherwise, the application user might need to wait as the entire data set is downloaded before seeing any of it.

Note

Network bandwidth is usually a reference to the amount of traffic that a network system can handle. Having high bandwidth is a good thing. Moving information across your network reduces available bandwidth while that information is transferring.

In general, however, properly constructed SQL queries combined with well-designed databases can be the best solutions for network environments or situations with low bandwidth (see Figure 32.1). The best old-style procedural access methods cannot compete with the best SQL access methods, with respect to bandwidth requirements on large database systems.

FIGURE 32.1. Query is sent to the server. Server sends results back.

Understanding Some Common Terms

Before getting into the mechanics of SQL syntax, it helps to be familiar with some of the subject matter language. Table 32.1 lists some of the common terms you may run across.

Table 32.1. SQL terms and their meanings.

Term

Description

Using SQL Syntax

The SQL language is very rich. Entire books exist that discuss nothing but SQL syntax and its subtleties. A very detailed discussion of the particulars is beyond the scope of this book.

An excellent source of the SQL language structure is available in the Visual Basic 4.0 Help system. Use Tables 32.2 through 32.4 to identify some SQL keywords that address your needs, and then refer to the Help system associated with the keyword for syntax details.

Table 32.2. Important SQL keywords for retrieving data.

Keyword

Abbreviated Function Description

Table 32.3. Important SQL keywords for changing data.

Keyword

Abbreviated Function Description

In general, the Jet Engine does not support SQL commands on non-Jet databases that alter a database structure. For this reason, some of the commands in the following table will only work if your RDBMS can handle them in the form of a SQL passthrough. The following table is presented with the Visual Basic method alternatives.

Table 32.4. Important SQL keywords to change a database structure.

Keyword

Abbreviated Description

VB Method

A Tip for Access Users

If you are using Access 2.0, you can copy/paste the SQL generated for your queries into Visual Basic by highlighting the statements in SQL view of Access and then pasting them into your application or a text file. If you would like to save your query SQL statements directly into a file, add the Access Basic program shown in Listing 32.8 to your MDB and execute it in Access 2.0 by passing in the query name and the name of the file you want the SQL statement saved to.

Listing 32.8. Access 2.0 program to save SQL statements.

'——————————————————————————————————

'Access 2.0 Basic procedure to save a generated SQL statement

'directly to a file.

'——————————————————————————————————

Sub WriteSQL (qryName$, fileName$)

    Dim db as Database

    Dim qd as QueryDef

    Set db = dbengine(0) (0)

    Set qd = db.OpenQueryDef(qry)

    Open fn$ For Output As 1

    Write #1, qd.sql

    Close #1

    MsgBox "Wrote"  & qryName$ &  "to"  & fileName$

EndSub

Looking at Simple Query Examples

Sometimes the best way to understand something is to see it in simple terms. With that thought, let's look at some simple examples of SQL queries that use the BTS database. All these examples can be executed with Visual Basic procedures like those of Listings 32.3 and 32.5 of the first section in this chapter.

Reminder: BTS is the "Best Team Selector" program of Chapter 36. Its database is used extensively in the examples of this chapter. Full source code for that program, and its database, can be found on the CD-ROM that accompanies this book.

The SQL query that lists the first and last name of all candidates in the tblCandidate table follows. The result is ordered by first name:

SELECT    FirstName, LastName

FROM        tblCandidate

ORDER BY    FirstName;

To return only those candidates that have a title of "Ms," use the following code:

SELECT    FirstName, LastName

FROM        tblCandidate

WHERE    Title = "Ms"

ORDER BY    FirstName, LastName;

To return the names and codes of all the available skills in the tblSkill table, use the following query:

SELECT    Name,SkillKey

FROM        tblSkill

ORDER BY    Name;

To see the names of all candidates with the skill with code value 1, you can issue this SQL command:

SELECT    FirstName, LastName

FROM        tblCandidate INNER JOIN tblCandidateSkill

ON        tblCandidate.CandidateKey = tblCandidateSkill.CandidateKey

WHERE    tblCandidateSkill.SkillKey = 1;

The main elements of the preceding statement are the INNER JOIN and the WHERE clause. A join "joins" two tables to produce a new kind of record that contains fields from both tables. An "inner join" keeps only those records that exist in both tables. In this case, the join occurs on the CandidateKey field that both the tblCandidate and tblCandidateSkill tables share. Since the WHERE clause filters the resulting join to records that have a skillkey value of 1, we get what we want.

If you want to see a table that mixes all skills and job titles, you can execute a union, as shown in the following code:

SELECT    Name,LastUpdate

FROM     tblJobTitle

UNION

SELECT    Name,LastUpdate

FROM        tblProject;

To count the number of candidates in the system, you can run the following query:

SELECT    COUNT(*)

FROM        tblCandidate;

To count only available candidates, you can run a query with a WHERE clause as follows:

SELECT    COUNT(*)

FROM        tblCandidate

WHERE    Available = TRUE;

You can group the available candidates by title and get a summary count of each title group by using the following query. The resulting table has two columns. A new column called "Total" is created to the right of the "Title" column. The "Total" column contains the result of the COUNT(Title) function:

SELECT    Title, COUNT(Title) AS Total

FROM        tblCandidate

WHERE    Available = TRUE

GROUP BY    Title;

Looking at Some Simple Action Query Examples

The following action query updates all records of the tblCandidate table so that all "Ms" titles are replaced with "Dr:"

UPDATE    tblCandidate

SET        Title = "Dr"

WHERE    Title = "Ms";

To ensure that every "Mr" candidate has a rating of at least 7 for any skill that they are listed as having, use the following query:

UPDATE    tblCandidateSkill

SET        Rating = 7

WHERE    Rating < 7

AND        Title = "Mr";

The following example creates a new candidate. Notice that no CandidateKey is specified, because this field is defined as a counter type. The RDBMS, in this case, automatically creates a new CandidateKey for any new record:

INSERT INTO

tblCandidate(Title,FirstName,LastName,MiddleInitial,Phone,Available,LastUpdate)

VALUES

("Mr", "Delamato", "Kode", "I", "(555) 999-9999", TRUE, Now);

To delete the skills of all the candidates in the system, you can issue a SQL command like the following:

DELETE    *

FROM        tblCandidateSkill;

To delete only the skills of those with a title of "Mr," you can use the following:

DELETE     *

FROM        tblCandidateSkill INNER JOIN tblCandidate

ON        tblCandidateSkill.CandidateKey = tblCandidate.CandidateKey

WHERE    tblCandidate.Title="Mr";

If you only want to delete skills of "Mr" candidates that are unavailable, you could use the following code:

DELETE     *

FROM        tblCandidateSkill INNER JOIN tblCandidate

ON        tblCandidateSkill.CandidateKey = tblCandidate.CandidateKey

WHERE    tblCandidate.Title="Mr"

AND        tblCandidate.Available=FALSE;

You can archive the candidates into a new table called arcCandidate by using this code:

SELECT    *

INTO         arcCandidate

FROM        tblCandidate;

Examining Some Advanced Concepts

Now it's time to take a closer look at some of the more complex aspects of SQL constructions and concepts. In particular, you'll learn about the following items:

• Inner joins
  
  
• Outer joins
  
  
• Stored queries
  
  
• Subqueries
  
  
• Relationships/constraints
  
  
• Table indexes
  
  

The inner join is the type of join most people find easiest to understand initially. In this type of join, the result is a table that only contains records from both tables. This also is known as a natural join. Figure 32.2 illustrates why the term inner applies: The only records used are "inside" both tables.

FIGURE 32.2. A conceptual diagram of an inner join.

The outer joins pretty much come in two flavors: left and right. The left and right refer to the relative placement of the joining tables in the SQL command that joins them. In a left join, the table specified to the left of the keyword has every record output to the result set. The table on the right only contributes to the result set record if it relates to the left table. If there are fields from both tables in the result set, and the second table does not relate to the left table, the fields from the second table are set to NULL for that record. In a right join, the same logic applies, but this time it is the table to the right that outputs all its records.

Figure 32.3 shows why these are considered outer joins: The result set includes records that are "outside" the domain of one of the tables.

Joins and other queries can be stored at design time in some databases. One type that does not support stored queries is the original dBASE type. Most of the client/server RDBMSs and the Access database type do support stored queries. A stored query is a query that already has been defined and is stored in the database itself. This is in contrast to all the examples so far, which code the query in the Visual Basic program. The advantage of a stored query is that, in some cases, its execution plan already has been defined to some degree.

FIGURE 32.3. A conceptual diagram of a left join.

To understand this, you need to know what steps Visual Basic goes through when you issue a SQL command at runtime through an Execute method. The steps are something like this:

1. The driver parses the SQL text into its logical parts for processing.
   
   
2. The driver translates the logical components into an execution plan.
   
   
3. The driver follows the execution plan to construct the result.
   
   
4. The driver returns the result to Visual Basic.
   
   

In this case, the driver could be the Jet Engine, a client/server RDBMS, or an ODBC driver. Depending on the complexity of the SQL query and the circumstances of the application, the first and second steps may have a marginal impact on performance. A stored query already has been through step 1 and, in some cases, has been through most of step 2. When you execute a stored query, the database driver picks right up at step 2 to create the result you want.

The data control accepts stored queries as its record source. Storing a join query for later use can be a good practice to implement when you know in advance what the join needs to be. Even when you are not able to hard code the query entirely—to show a subset of a join where that subset is defined at runtime, for example—you still can store a query and access it from your application with a parameter. To use parameter queries, define the query as having a parameter when you create it. (See the help system for detail on the PARAMETERS keyword and how to use it with Jet Engine queries in Visual Basic. Non-Jet parameter queries may have vendor specific syntax.)

A subquery is a Select statement nested inside another query. This is a powerful feature of the SQL language that enables a query to do what otherwise could take several queries and temporary tables to accomplish. The ListSkillsBySQL() procedure of Listing 32.5 uses a subquery to return all the skills that a candidate does not have. For clarity, take a look at it in Listing 32.9 outside the Visual Basic syntax.

Listing 32.9. Query with subquery.

SELECT DISTINCTROW tblSkill.Name

FROM        tblSkill LEFT JOIN tblCandidateSkill

ON        tblSkill.SkillKey = tblCandidateSkill.SkillKey

WHERE    tblSkill.SkillKey NOT IN

        (SELECT tblCandidateSkill.SkillKey

         FROM    tblCandidateSkill

         WHERE    tblCandidateSkill.CandidateKey = #);

The # in the subquery in Listing 32.9 is substituted with the CandidateKey of the candidate in question at runtime by the Visual Basic program before executing the query. (The query will not run with "#" in it.) Conceptually, the gist of why the query in Listing 32.9 works follows:

1. The main SELECT body builds a result that contains ALL skills.
   
   
2. The driver executes the subquery to produce a set of skills that the candidate already has.
   
   
3. The WHERE clause then removes all skills in the set produced by the subquery. Removing all skills the candidate already has from the complete set of skills leaves you with only those skills the candidate does not already have.
   
   

An important relational feature that not all databases share, but all current client/server RDBMSs and the Access type do share, is the definition of relationships or constraints. Both terms are used interchangeably to describe the definition of table relationships within an RDBMS. Visual Basic 4.0 supports the definition of relationships through the CreateRelation method on Jet databases. It does not, however, support the definition of relationships in Jet databases that involve attached tables. Presently, the dBASE database type does not support defined relationships.

Note

For more detail on the CreateRelation method, consult the Visual Basic help system. However, unless you need to create database relationships at runtime, you are better off using a third-party tool or the Visual Basic Data Manager Add-In because they can be easier to work with.

Defining relationships is a key ingredient to maintaining referential integrity of a database. Once a relationship is defined, the RDBMS can check the validity of application requests to modify the database. In the case of client/server RDBMSs, the system can prevent the modification of the database if doing so would contradict a defined relation.

Note to Access Users

The Access programming environment enforces relationships defined in an MDB, but Visual Basic does not. For example, although the BTS database requires a matching CandidateKey to exist in the tblCandidate table for any tblCandidateSkill record, it is possible to execute "DELETE * FROM tblCandidate" and orphan all the tblCandidateSkill records in the process.

Automatic referential integrity maintenance of the database by the RDBMS has the advantage of keeping coding errors from damaging the logical relationships of the database.

Although the specifics of when a database driver uses an index to assist in a query is very dependent on the way the driver itself is designed, in general the following is true:

1. If an index exists with the same order of fields as in an ORDER BY clause, the index is used to speed processing.
   
   
2. Some drivers only use one index per query. (SQL Server 4.2 is an example.)
   
   

The advantage of using an index on a SELECT query is that of speed: The index is a structure that tells the RDBMS directly where to find the records it needs. Without an index, it must visit each record until it finds what it seeks.

Indexes are not free, however. Otherwise, there would be no reason not to index every field in every table in every way. The price is paid when the database is updated and in disk space. In the case of ISAM databases, there also can be a price to pay in reliability. Indexes should be created judiciously. If there is consistently an ORDER BY clause that uses the same chain of fields to sort a selection and that selection can be large, it may be a good argument to create an index with the same ordered fields. You can create indexes at runtime in Visual Basic 4.0 via the CreateIndex method. (As with most database definition issues, however, you should use the Database Manager Add-In or a database-specific tool to do this.)

Working with Complex SQL Queries

Now let's dive into a few nasty looking SQL queries that do quite a bit of processing in one fell swoop.

Listing 32.10 shows a SQL query that returns a table with all used skills across the top as column headings, and all the candidate names along the left margin as rows. Each row has a 1 in any column that represents a skill the candidate of that row has.

Listing 32.10. Crosstab query.

TRANSFORM Count(tblSkill.SkillKey) AS CountOfSkillKey

SELECT tblCandidate.FirstName

FROM tblCandidate INNER JOIN (tblSkill INNER JOIN tblCandidateSkill ON tblSkill.SkillKey = tblCandidateSkill.SkillKey) ON tblCandidate.CandidateKey = tblCandidateSkill.CandidateKey

GROUP BY tblCandidate.FirstName, tblCandidate.CandidateKey

PIVOT tblSkill.Name;

To create more candidates automatically, you can execute the SQL query shown in Listing 32.11. It creates a new record for every existing candidate and appends it to the tblCandidate table. Each new candidate shares everything with its source record, with the exception that the CandidateKey is unique to the table and the first name is now "2." The CandidateKey is defined as a counter type and is the primary key, so the Jet Engine fills in that value for you.

Listing 32.11. A query that creates test data.

INSERT INTO tblCandidate ( Title, FirstName, LastName, MiddleInitial, Picture, Phone, Available, LastUpdate )

SELECT DISTINCTROW tblCandidate.Title, "2"

AS FirstName, tblCandidate.LastName, tblCandidate.MiddleInitial, tblCandidate.Picture, tblCandidate.Phone, tblCandidate.Available, tblCandidate.LastUpdate

FROM tblCandidate;

TIP

You can create a lot of test data with very few iterations of the type of action query shown in Listing 32.11. Each new execution of that query doubles the size of the database. Starting with 10 records, you would need to run it only 10 times to have 10,240 new records in the table.

Optimizing Your Queries

As mentioned in the "Examining Some Advanced Concepts" section, proper use of indexes and stored queries can increase performance of Visual Basic queries. A few other items also play a particularly obvious role in query optimization.

To reduce the number of rows returned by a query in a particular subquery, use the DISTINCTROWS or DISTINCT predicate with your SELECT statement. This produces a shorter table when duplicate records otherwise would appear. Listing 32.5 earlier in this chapter illustrates the use of the DISTINCT predicate.

If you are using an ISAM database locally, set the Exclusive database access mode to TRUE. If you are using a data control, this is a property. If you are using OpenDatabase methods, this is a parameter. Exclusive database access is much faster but can be implemented only when the database will not be shared.

Do not use ORDER BY unless you need to order the result. For example, an ORDER BY clause in the subquery of Listing 32.5 of this chapter would cause unnecessary processing, because the result of the subquery is only used internally to filter out records of the main query and is not displayed itself.

Use SQL queries instead of iterative methods whenever a result involves more than one table. Otherwise, you may need multiple data controls or data objects. In some cases, this can result in more open connections. More data controls and connections can reduce resources available to your program and other programs on your system.

Note

When the Jet Engine is given queries to apply against MDB or DBF files, it tries something called "Rushmore Optimization." This is a technology Microsoft inherited from the original creators of FoxPro that can speed up non-client/server queries considerably. All queries cannot be optimized by the Jet Engine this way, but some, if not most, can be converted into forms that can. If you are using MDB (Access) or DBF databases in your project, check the Visual Basic Help system and documentation for details that can help you ensure your queries run with Rushmore Optimization as much as possible.

Making RDBMS-Specific Enhancements

As mentioned earlier, most client/server RDBMSs go beyond the specifications of SQL-92 by adding enhanced functions. In some cases, SQL-92 specifications are violated in the interest of enhancing RDBMS functionality or performance. The only way to know what features are available with your RDBMS is to review its documentation or contact someone knowledgeable on the subject with respect to your RDBMS.

In particular with client/server RDBMSs, there are significant database-optimization features available to the DBA (Database Administrator) that are not SQL standard. Microsoft SQL Server, for example, supports a technology they call clustered indexing, which can improve data lookup times in larger tables by essentially "grouping" related files near each other on the server's disk. Oracle supports what they call interleaving, which gives a performance boost in large databases by factoring in characteristics of the disk storage system to improve physical access to the data.

Other nonstandard features that are available in SQL Server, Oracle, and other client/server RDBMSs are triggers and stored procedures. Triggers and stored procedures are programs that can be stored on the database server. When properly used, they can improve operation of a database noticeably by off-loading some of the business logic from the user computer to the database server. Client/server issues are discussed more thoroughly in the next chapter.

Summary

This chapter introduced many significant SQL concepts and terms. Along the way, it illustrated some ways to use SQL in Visual Basic 4. Although this chapter shows that SQL can be applied powerfully to both ISAM and client/server databases, Structured Query Language shines most in the client/server environment. For an introduction to client/server concepts, turn to Chapter 33, "Visual Basic 4.0 and Client/Server Programming."