Implementing Effective Database and Query Design

Database and query design have a dramatic impact on the performance of your SQL Server application. Successful planning and design at this stage can have positively influence your application’s performance.

The first step in effective query design is to limit the amount of data transferred between the client and the server by limiting the columns specified in a Transact-SQL statement to the values required by the application (for example, through the efficient use of the SELECT and FROM clauses), and by limiting the number of rows fetched from the database (for example, through the efficient use of the WHERE clause).

After reviewing the Transact-SQL statements to ensure that they request only the required rows and columns, a database developer must consider the use of indexes, stored procedures, and efficient coding to improve application performance.

Selecting Specific Table Columns

In the Btrieve environment, most operations retrieve every column or attribute for each record involved in an operation. With SQL Server, this technique can be inefficient, especially in cases where only a small number of columns are actually required. For example, the GetSales function in Btrvapp.exe retrieves all of the sales record attributes from Sales.btr even though only the TitleID and Qty attributes are needed to complete the task. The following is the code fragment from the GetSales function in Mybtrv32.c that exhibits this behavior:

/* Get TITLE/PUBLISHER with OPERATION*/

// Copy the desired TitleID to the keyBuffer for use by Btrieve and

// initialize parameters

strcpy(TitleID, keyBuf);    

memset( &salesRec, 0, sizeof(salesRec) );

dataLen = sizeof(salesRec);'

// Retrieve the sales record    

salesStat = BTRV( op, salesPB, &salesRec, &dataLen, keyBuf, 1 );

    if (salesStat != B_NO_ERROR)

        return salesStat;

  

The same design inefficiency can be implemented in the SQL Server environment by selecting all of the columns from tables, views, or cursors involved in an operation. For example, the following code fragment from the GetSales function retrieves all of the sales record attributes from the BSALESCURSOR even though only TitleID and Qty are needed to complete the task.

// Bind result set columns to buffers

SQLBindCol(hstmt, 1, SQL_C_CHAR, salesRec.StorID, 5, &cbStorID);

SQLBindCol(hstmt, 2, SQL_C_CHAR, salesRec.TitleID, 7, &cbTitleID);

SQLBindCol(hstmt, 3, SQL_C_CHAR, salesRec.OrdNum, 21,  &cbOrdNum);

SQLBindCol(hstmt, 4, SQL_C_SLONG, &salesRec.Qty, 0, &QtyInd);

SQLBindCol(hstmt, 5, SQL_C_CHAR, salesRec.PayTerms, 13, &cbPayTerms);

// Fetch records one-at-a-time from the server until the desired

// record is found

while(!found)

{

memset(&salesRec, 0, sizeof(salesRec)); // Initialize the record buffer

// Fetch the record from the server cursor

rc = SQLFetchScroll(hstmt, FetchOrientation, FetchOffset);

if ((rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO))

        .

        .

        .

  

You can avoid design inefficiency by accessing only the record attributes required to satisfy a particular task. Odbcapp.exe demonstrates a more efficient design concept in its GetSales function. The GetSales function in Odbcapp.exe calls the GetSales stored procedure from the SQL Server pubs database to retrieve only the TitleID and Qty columns for the desired title. The following code fragment presents the GetSales stored procedure. It demonstrates how the stored procedure is executed and its results processed in the Odbcapp.exe GetSales function.

/*Get Sales stored procedure */

CREATE PROCEDURE GetSales @titleid char(6) AS

SELECT TITLE_ID, QTY FROM SALES WHERE TITLE_ID = @titleid

GO

// Execute the stored procedure and bind client buffers for each

// column of the result

rc = SQLExecDirect(hstmtS, "{callGetSales(?)}", SQL_NTS);

    .

    .

    .

    SQLBindCol(hstmtS,1, SQL_C_CHAR, TitleID, 7, &cbTitleID);

    SQLBindCol(hstmtS, 2, SQL_C_SLONG, & Qty, 0, &QtyInd);

// Fetch result set from the server until SQL_NO_DATA_FOUND

    while( rc == SQL_SUCCESS || rc == SQL_SUCCESS_WITH_INFO)

    {

        rc = SQLFetch(hstmtS);

.

.

.

  

Using WHERE Clauses to Reduce
the Result Set Size

Use WHERE clauses to restrict the quantity of rows returned. Using WHERE clauses reduces the total amount of data affected by the operation, reduces unnecessary processing, and minimizes the number of locks needed to process the request. By using the WHERE clause to reduce the result set, you can avoid table contention, reduce the amount of data transferred between the client and the server, and increase the processing speed of the request.

For example, the following cursor code was taken from the CreateCursor function of Mybtrv32.c:

// Creates the BSALESCURSOR

if (!strcmp(curname, "BSALESCURSOR"))

    rc=SQLExecDirect(*hstmt2,

"SELECT STOR_ID, TITLE_ID, ORDNUM, QTY, PAYTERMS FROM 

BSALES",    SQL_NTS);

  

The BSALESCURSOR is created without using a WHERE clause. As a result, the server creates a cursor that retrieves all rows in the bsales table. This cursor results in more resources and processing at the client than it needs.

The application actually requires that the query obtain the sales information for a particular TitleID column. It would be more efficient to use a WHERE clause that defines the exact TitleID or even a range of TitleID columns. This would reduce the amount of data sent for client-side examination and the number of round-trips between the client and the server. This example is shown below. Notice that the cursor is more efficient because it only requests the TitleID and Qty columns from the bsales table; in this application, only those columns are used by the business logic.

if (!strcmp(curname, "BSALESCURSOR")){

    SQLBindParameter(hstmtU, 1, SQL_PARAM_INPUT, SQL_C_CHAR, 

        SQL_CHAR, 6, 0, inval, 7, &cbInval);

    rc=SQLExecDirect(*hstmt2,

    "SELECT  TITLE_ID, QTY FROM BSALES WHERE TITLE_ID LIKE ?",

    SQL_NTS);

  

Using the Singleton SELECT

A singleton SELECT returns one row based on the criteria defined in the WHERE clause of the statement. Singleton SELECTs are often performed in applications and are worthy of special attention. Because only one row is returned, you should always use a default result set SELECT statement rather than a server-side cursor to retrieve the record. The default result set SELECT statement retrieves the record faster and requires far fewer resources on both the client and the server. The following code fragment is an example of a singleton SELECT that returns the Pub_ID and Title for a single Title_ID:

SELECT PUB_ID, TITLE FROM TITLES WHERE TITLE_ID = 'PC8888'

  

Because singleton SELECTs are performed frequently in applications, consider creating stored procedures to perform these SELECT statements. By using a stored procedure rather than issuing a SELECT statement directly, you can reduce the parse, compile, and execute time necessary to process the request. The following code, taken from the GetTitlePubRec function in Odbcapp.exe, executes a singleton SELECT through the GetTPByTitleId stored procedure. Notice the small amount of processing needed in Odbcapp.exe to execute this stored procedure.

switch(key)

    {

    case 1:    // Title_ID search

        strcpy(StoredProc, "{call GetTPByTitleID(?)}");

        // Identify stored procedure to call

        // Bind the input parameter buffer

        SQLBindParameter(hstmtU, 1, SQL_PARAM_INPUT, 

        SQL_C_CHAR, 

        SQL_CHAR, 6, 0, inval, 7, &cbInval);

        break;

    .

    .

    .

// Execute the stored procedure and bind result set row columns to variables

    memset( &tpRec, 0, sizeof(tpRec) );  // Initialize buffer record

                           // structure

    rc=SQLExecDirect(hstmtU, StoredProc, SQL_NTS );

    .

    .

    .

    SQLBindCol(hstmtU, 1, SQL_C_CHAR, tpRec.TitleID, 7,

    &cbTitleID);

    SQLBindCol(hstmtU, 2, SQL_C_CHAR, tpRec.Title, 81, &cbTitle);

    .

    .

    .

    SQLBindCol(hstmtU, 12, SQL_C_CHAR, tpRec.Country, 31, 

    &cbCountry);

// Process the results until SQL_NO_DATA_FOUND

    while (rc==SQL_SUCCESS || rc==SQL_SUCCESS_WITH_INFO) 

    {

        rc=SQLFetch(hstmtU);

        if (rc==SQL_SUCCESS || rc==SQL_SUCCESS_WITH_INFO) {

    .

    .

Using Effective Indexing Techniques

Careful index design improves the performance of a SQL Server database. The query optimizer in SQL Server selects the most effective index for most cases; however, the following new features of SQL Server 7.0 allow you to create the best indexes for your application:

For guidelines about index creation before you implement the Index Tuning Wizard and SQL Server Query Analyzer, see “Recommendations for Creating Indexes” later in this chapter.

Implementing Stored Procedures

Stored procedures enhance the power, efficiency, and flexibility of Transact-SQL and can dramatically improve the performance of Transact-SQL statements and batches. Stored procedures differ from individual Transact-SQL statements because they are precompiled. The first time a stored procedure is run, the SQL Server query engine creates an execution plan and stores the procedure in memory for future use. Subsequent calls to the stored procedure run almost instantaneously since most of the preprocessing work has already been completed. The Odbcapp.exe application demonstrates how stored procedures are called using ODBC within an application.

For more information on stored procedures, see SQL Server Books Online and the Microsoft Knowledge Base.

Keeping Transactions Short and Efficient

Short and efficient transactions decrease the number of row-level locks managed by the system at any given point in time and are considered to be good programming practice. This is especially true in mixed decision support and online transaction processing (OLTP) environments.