Delphi Connectivity

| Tips & Tricks | Bug List | Cool Delphi Sites |
| Connectivity | Delphi 2.0 Features | Internet |

 Using Delphi with Microsoft SQL Server 6.0

Delphi makes it easy to access Microsoft SQL Server for application development. In fact, Delphi encapsulates so much database functionality, it is possible to design an application to use a variety of database formats including SQL Server, Interbase and local Paradox tables. While this is possible, for many applications, it is not likely because of the significant architectural differences between the different database platforms. For this reason, many Delphi developers need to carefully consider a host of issues when using Delphi against SQL Server.

This paper discusses the following topics:

When to use and how to handle the identity fields in SQL Server 6.0.
When and how to use a database component
Understanding the SQL Server transaction model
Clustered indexes: How they can improve and hurt performance
Avoiding record-at-a-time access and other client server gotchas
Using TList objects (or other data structures) for high-performance "tables-in-memory"
When to use TTable, TStoredProc, and TQuery

Identity Fields

In SQL Server, identity fields are similar to Paradox autoincrement fields and Microsoft Access counter fields. These fields automatically increment themselves each time a record is inserted in the table. In certain circumstances in SQL Server development, it is useful to have identity fields as the primary key, jast as it is useful to use autoincrement fields in Paradox tables. However, when you use Delphi 1.0 with SQL Server 6.0, Delphi reports an exception when inserting records via a TTable or a TQuery component. The BDE error is 8708 which means the record key has been deleted. It is as if the BDE loses the record on an insert because a field value is provided by SQL Server, not Delphi. In addition if the tables are defined with default values (e.g., using the CREATE DEFAULT statement), the same error occurs. Again, SQL Server is providing the values not the BDE.

To get around this problem, in our development, we do either one of two things:

Put a try..except block around the insert statement and trap for and ignore the BDE exception
Insert records into a temporary SQL Server table without defaults or identity fields and then, in a batch, move the records to the final SQL Server table.

Option 1 is easy, but performance can suffer somewhat due to the overhead of handling exceptions. Whether this exception handling overhead significantly affects performance is another matter. It depends on the size of the record being inserted, the number of indexes on the table, exactly how your application inserts records and many other factors. There is some additional overhead which Delphi maintains in order to handle exceptions. This overhead does take some time. (See "Exceptions Can Be Costly" by Bob Swart in the February, 1996 issue of Delphi Developer published by Pinnacle Publishing, Inc.). If you plan ion inserting tens, hundreds or thousands of records, excessive numbers of exceptions can potentially be a performance bottleneck.

Option 2 takes more work and has concurrency issues as well but it does allow you to insert records without the accompanying exceptions.

If you are using Delphi 2.0 to access SQL Server, the problem with the identity field goes away. However, you will still get an exception on insertion for columns that have a default value.

Use A Database Component

When using Delphi against client-server data, it is wise to use a database component. The database component lets you maintain a connection to the server independent of any data sets (TTable, TQuery and TStoredProc components). Thus you can keep the connection to a SQL Server database open and open and close data sets at will. This keeps the number of SQL Server connections down, since access to each data set is handled through a single connection. In addition, the database component lets you specify the transaction isolation level (discussed below).

For Delphi applications which access local Paradox or Dbase tables, developers often forgo using a database component. After all, you can select an alias in DatabaseName property for a data set component or you can type in the full path to the table in the table name property and never need a database component. When you use a database component, the component's DatabaseName appears in the data set's DatabaseName property list along with all the defined aliases. Keep in mind that each database component is global for the entire application. All data sets every where in your project can see the database component.

The database component has several properties which determine how to access a SQL Server database. You can connect to a SQL Server database in one of the following ways:

Create an alias for the SQL Server database using the alias page in the BDE Configuration application.
Create an alias using the Database Desktop's Alias Manager.
Don't create an alias. Instead use the Driver Name property to specify MSSQL as the driver type, and then specify database, user name and password.
Use Microsoft ODBC drivers (or another vendor's) to access SQL Server. This option requires an alias which uses the ODBC driver.

Each option has its advantages and disadvantages. However, any option which does not require creating an alias makes it easier to install and deploy your application. You won't need to either automatically create the aliases or run the BDE configuration program to create the alias.

In addition, you can set many properties easily with the TDatabase component editor. To access this editor, simply double-click on the database component.

This editor lets you specify many properties all in one dialog box. Most importantly, it lets you specify a host of parameters some of which you can set when creating the alias. These include:

The SQL Server transaction model

Using Delphi to access SQL Server tables is quite different than using Delphi to access local Paradox tables. At first blush, everything will seem harder and more difficult. Creating and administering Paradox tables is easy. Accessing them is quick, provided the size of the data is not large (from 1-50 MB of data). Once the local tables grow towards the 100MB size, client-server application development is in order.

However, creating and administering SQL Server tables takes considerably more work. One area that you will find yourself spending quite a bit of time is in understanding and working with the SQL Server transaction model.

SQL Server differs from local Paradox tables in a number of ways. When programming multi-user access to Paradox records, programmers typically rely on row-level locking to enforce pessimistic concurrency schemes. In this approach, you lock a record, preventing other users from changing the record. SQL Server does not support a row lock. Instead it relies on a page lock, which can potentially lock several records (pages are 2k in size in SQL Server). Since pages are larger than records, page locking databases tend to require far fewer locks than row locking database. This results in improved locking performance. However, the problem is that when you change data in a row, the entire page is locked. For this reason, database developers typically use an optimistic concurrency scheme in which the application assumes that the page (hence row) is always available. Only if another user has changed the data between the time it was first read and when it is written will the application report a problem.

Because of this very basic and very fundamental difference developers run into problems. Applications previously written which relied on pessimistic row-level locking will need to be changed to allow for optimistic page-level locking. (By the way, row versus page level locking is more than just a technical issue. It is a long-standing religious debate between vendors and practitioners.) Delphi supports optimistic concurrency approaches quite nicely.

The way it does this is through the UpdateMode property on all data set components (TTable, TQuery and TStoredProc). This property has three possible values:

upWhereAll
upWhereChanged
upWhereKeyOnly

To understand these options better, consider an example where a Delphi application is updating the au_fname field in the authors table on the pubs database that comes with SQL Server. When the UpdateMode is upWhereAll, Delphi generates the following SQL statement to update a single row (taken from the SQL Monitor):

SQL Execute: MSSQL - UPDATE dbo.authors SET au_fname=:1 WHERE au_id=:2 AND au_lname=:3 AND au_fname=:4 AND phone=:5 AND address=:6 AND city=:7 AND state=:8 AND zip=:9 AND contract=:10

The BDE creates a parameter update SQL statement in which the value for the au_fname field is passed as the 1^st parameter. But note that the WHERE clause includes all the fields in the table, including the old value for the au_fname fields (the 4^th parameter). If the BDE can't find the record because any other user has changed a value, the update will fail.

Now let's look at the SQL statement to update a single row with the UpdateMode property set to upWhereChanged:

SQL Execute: MSSQL - UPDATE dbo.authors SET au_fname=:1 WHERE au_id=:2 AND au_fname=:3

Notice that with upWhereChanged, the WHERE clause is much simpler; it only contains two fields: the key field (au_id) and the au_fname field.

Finally, let's look at the SQL Statement to update a single row with the UpdateMode property set to upWhereKeyOnly:

SQL Execute: MSSQL - UPDATE dbo.authors SET au_fname=:1 WHERE au_id=:2

Using one of these three schemes, you have some control over precisely how many fields in a row can be changed by other users before the update will fail.

Managing transactions

Updating a single record is easy. Even creating an application where hundreds of users update single records is relatively straightforward. Each update is enclosed in a single transaction. Therefore, transactions are small, fast and manageable. Problems ensue when batch update programs need to be run or when queries need to update a large number of pages. In these cases, transactions can get quite large causing the following problems:

Running out of transaction log space. In this case, the transaction log will need to be dumped and most likely will need to be expanded in size.
Deadlocks. Large transactions which lock a large number of pages can cause deadlock or can stall another process trying to access data. In this case, the deadlocked process or stalled process will need to be terminated.
Poor performance. Transactions take time and resources. A badly design Delphi application which is not judicious in its use of transactions can degrade system performance.

For example, if you have a Delphi application which needs to step through a TTable or TQuery component's records to make complex updates that are not possible through normal SQL queries, you need to carefully consider your potential transaction size. Here are some hints:

Transactions are best when they are small. If you have to perform a batch update of some kind, write the code so that a group or records, about 100 or so, are updated at a time.
Write your routine so that the batch process can restart, ignoring the successfully processed records. Placing each of 20,000 records in an batch update in its own transaction will keep transaction size down but it will also reduce performance. Placing groups of 100 records or so in a transaction improves performance yet still keeps transaction size down.
If transactions are too large, SQL Server may escalate the locking strategy to lock the entire table, which will block other processes. (SQL Server lets you specify at what point locks should be escalated.)
Don't keep a transaction open that depends on user input to complete. If the user goes to lunch, the transaction may be blocking other processes.

Isolation Levels

Using the database component, you can set isolation levels to control how transactions behave in a multi-user setting. Isolation levels arose because database programmers need to handle concurrent access, chiefly write access, to tables in different ways. For example, if one user is querying a large number of records, should the database system let another user make changes to the records being queried? If it does, what does this mean for the user running a query? Do they get to see the changes? Does the query abort because records have changed? This is just one problem with multi-user access to data.

The SQL-92 standard identifies several isolation levels for transactions. These are:

Read uncommitted
Read committed
Repeatable read
Serializable

In order to understand these isolation levels, it is important for you to understand the consistency anomalies that these levels address. These anomalies are:

Dirty read
Nonrepeatable read
Phantom

Dirty reads refer to transactions (or read accesses to data) which run with no regard for other transactions. Consider the following example:

One user starts a transaction T1 on the Customer table which changes the credit limit for four customers in Arizona.
During this transaction, another user starts a transaction T2 on the Customer table which queries the credit limit for all customers in Arizona.
Transactions T1 fails and rolls back the changes to the credit limits for the four customers.

When step 3 occurs, then transaction T2 has read changes that were never committed. These changes did not really exist and should not have been read by transaction T1.

Nonrepeatable reads are reads that handle transaction roll back properly but do not read the exact same data twice. Given the example described above for dirty reads, assume the following sequence of actions:

User A starts transaction T1 which, as a first of a two-step transaction, queries for customers in Arizona, returning credit limits.
User B starts and completes transaction T2 which changes the credit limit for four customers in Arizona.
User A completes transaction T1 which, as the final step, queries customers in Arizona, returning outstanding balances. After all, User A wants to compare the account balances with the total credit limits.

Without the proper isolation level set, User A will have read the same customer records twice, but with different information. Hence, the read for data was non-repeatable.

The phantom anomaly is just a variation on the repeatable read problem. It goes like this:

User A starts transaction T1, which first queries for customers in Arizona, returning credit limits.
User B starts and completes transaction T2, which adds a new customer account in Arizona.
User A completes transaction T1 which, as the final step, queries customers in Arizona, returning outstanding balances. After all, User A wants to compare the account balances with the total credit limits.

User A will see in the final step of transaction T1 a new record (a phantom) which did not exist in the prior read. This can occur if User B adds a new record, or if User B changes other customer records, which now causes them to land in the set of customer records in Arizona. The second part of transaction T1 returns a different set of records, not the same set with different data.

This sets up the following matrix of isolation levels and consistency anomalies:

This table shows that if you choose an isolation level of read uncommitted, then you will not be able to stop any of the consistency anomalies. If you choose read committed, then your transaction will only read committed records, but will not stop the non-repeatable read or phantom problem. If you choose the repeatable real isolation level, then you solve the first two consistency anomalies but fail to stop phantoms. The highest level of isolation is serializable. This stops all three consistency anomalies.

Delphi supports the first three of the SQL-92 isolation levels: read uncommitted (Delphi calls it dirty read), read committed, and repeatable read. SQL Server implements read uncommitted, read committed and serializable. The following table lists each of the SQL Server isolation levels, what it does and how it maps to Delphi's isloation levels:

Indexing Tables

When working with SQL Server tables, you will need to think about what kinds of indexes you want to create to speed up access to a table. This article does not discuss all of the indexing options, just one option: creating a clustered index.

In SQL Server, each table can have at most one clustered index. A clustered index physically arranges the data pages in the index order. This can speed up certain types of transactions. Queries which select data using that index can be sped up because the data resides on the disk in sorted order. However, putting a clustered index on a primary key which steadily increases, such as an identity field, can cause a serious performance degradation since the data being inserted is always inserted at the same data page. In addition, other types of insertions can be significantly slowed down by clustered indexes.

One technique we employ to still use clustered indexes and avoid terribly slow batch insertions is to insert the data into a temporary table (via a TTable or TQuery component) on the server without any indexes. Then, using a stored procedure, move the records from the temporary table to the base table. The combined time for this two-step process is much shorter than batch inserts into a table with a clustered index.

TTable vs. TQuery vs. TStoredProc

There are two cardinal rules for developing client-server applications. One of them says to treat tables as sets of records, not as 100,000 single records. What this translates to is to use queries (which the server runs) to perform as much heavy data work as possible. Don't try to use ObjectPascal code to do the work of queries. In fact, the generalization of this rule leads one to the conclusion that the database server (SQL Server) should try to do as much work as possible.

However, one should also not treat a table of 500,000 records as a single set of 350,000 records. It is best to always access small subsets of data rather than letting the user browse around the entire table. If you have a strong Paradox or Dbase background, your tendency will be to use a TTable component, a TDataSource and a grid control to let the user browse the entire table. While Paradox and Dbase are optimized for this sort of access, SQL Server is not.

Each time you open a table, query or stored procedure, stop and think about how many rows it is returning and how long the server will take to build the result set. This has lead many to say that client-server Delphi applications should use the TQuery or TStoredProc component for data retrieval. However, with Delphi 2.0, it is now possible to use a TTable component and take advantage of it's filter property. The filter property lets you specify a WHERE clause which restricts the number of rows returned. Let's look at what a TTable component does with a filter. The following SQL statement is taken from the Delphi SQL Monitor while opening the authors table (in the pubs database that comes with SQL Server) with a filtered TTable:

SQL Execute: MSSQL - SELECT au_id, au_lname, au_fname, phone, address, city ,state ,zip ,contract FROM dbo.authors WHERE (au_id = :1) ORDER BY au_id ASC

In this example, the filter is being applied as a where clause with a parameter (:1).

Also keep in mind that it is not a good idea to auto-create your data entry forms with their data sets active for client-server development. The reason for this is that the BDE queries the SQL Server system tables to fetch field names and other system information as part of opening a data set. This takes time. If you have 5 auto created forms with a total of 30 data sets active, that's quite a bit of overhead which you might not want to bear at that moment in time. In addition, once you have your data set open, unless you absolutely need to close it, keep it active.

Other than these issues, there's not a lot of performance differences between using TTable or TQuery components for data access. Of course, the TQuery object gives more flexibility since you can construct any valid query. In our performance testings of insertions, updates and selects, both components perform about the same for the same tasks.

TStoredProc, however, is a different component. Although a TStoredProc component looks like a data set, a SQL Server stored procedure can do anything, the least significant of which is return some rows of data. Stored procedures are like DOS batch files. They let you string together several SQL statements and they provide some basic control-of-flow language constructs. SQL Server's Transact-SQL language has the following control-of-flow language constructs:

You can use these constructs to create elaborate programs that include queries and many other built-in functions. In order to run a stored procedure, SQL Server must build two internal data structures:

a query tree
an execution plan

When you create a stored procedure, the text of the stored procedure is stored in the syscomments system table in SQL Server and a normalized form of the query or queries within the stored procedure are stored in the sysprocedures system table. The first time a stored procedure is executed, the query tree is read from the sysprocedures table and read into memory. SQL Server then passes the query tree to the query optimizer which creates an optimal access plan. This access plan remains in memory for all subsequent calls to the stored procedure. This makes subsequent calls to a stored procedure very fast.

In fact, stored procedures are faster than views. Although SQL Server will cache the query tree for views, it will not cache the access plan for views. However, this is one of the problems with a stored procedure. If the nature of the data changes significantly after the stored procedure's access plan was read into memory, the access plan may no longer be optimal. Fortunately, you can create stored procedures that tell SQL Server to generate a new access plan upon each execution.

Stored procedures can accept parameters. This lets you pass variable data to the stored procedure, enabling dynamic queries and dynamic control-of-flow. In fact, the Delphi TStoredProc component lets you specify parameters for a stored procedure and the TStoredProc can be used as a data set for a TDataSource component.

The following code is an example of a simple stored procedure which returns all the authors in a given state:

create proc authors_in_state @state char(2) as select * from authors where state = @state

In this stored procedure, @state is an argument of type char(2). This stored procedure can be used in a Delphi application as the data set for a grid in which the user can select a given state.

Cached Updates

The CachedUpdates property for the data sets has two values: true or false. True indicates that cached updates are enabled. While cached updates are enabled, updates you make to the dataset (such as posting changes or deleting records) are cached locally on the PC instead of being written to the SQL Server. You can call the ApplyUpdates method to apply all the cached updates. Setting CachedUpdates to False while there are pending cached updates or calling the CancelUpdates method will discard them.

In the limited testing we've done so far, we haven't seen a big performance impact for committing changes with cached updates. However, we have seen a big performance impact for canceling updates. Logically, canceling updates is the same as rolling back a transaction. The difference is the database server is not involved in canceling cached updates. Rolling back a large transaction on SQL Server can take quite some time and cause concurrency problems due to a large transaction being held open. However, you will need to have plenty of local disk space available on your PC since the BDE will use local disk space to hold the cached updates.

Reading Tables into Memory

One overlooked method for drastically improving client-server application development is to store read-only tables into memory. Depending on the application, this might be a viable alternative. In fact, it might be more viable than you think. First, Delphi 2.0 runs in a 32-bit flat address space, which means we can allocate huge tracts of memory easily. In our tests, we've created a 1 million item array with each item holding 4 bytes (a long integer) and accessed all 1 million items in about one second using a sequential scan. This time does not include the time it would take to read 1 million records into memory. The test was just measuring Delphi's raw memory access via a large array.

Nonetheless, Delphi comes equipped with a wide array of tools for creating sophisticated data structures in memory (arrays, records, TList objects). If your application has read-only data, consider loading that data into memory. Since Delphi is a compiler (and a very fast one at that) Delphi can rapidly access memory. We've used this technique to drastically speed up (by a factor of 100) a complex SQL Server batch update process.

There is a sample application on this disk which demonstrates reading a table with many fields in the primary key into a series of TList objects. The TList objects simulate tables with a 1:M:M:M:M relationship. Normally, we would write the program as a series of queries, but instead we opted for the more code intensive approach of reading the data into memory instead.

Conclusion

Despite some minor irritations, Delphi makes a very powerful, flexible and rapid application development tool for accessing SQL Server data. As long as you study the capabilities within SQL Server and clearly understand how client-server development differs from local Paradox and Dbase table application development, you will find using Delphi and SQL Server straight forward.

| Borland Delphi | About the Authors | Home |

For Queries Mail To Webmaster

Copyright © 1996 Asylum Software Pvt. Ltd. This is an ASPL production.
Produced in conjunction with ASPL DELPHI TEAM.
Last revised November 27, 1996.