Modular Programming - Microsoft SQL Server Black Book

Stored procedures not only provide the client developer with a standard

method of dealing with the server, that they also give the SQL programmer the ability to write modular code. For instance, in many systems you will need to develop a standard set of string functions. The method of parsing tokens from a string could be written as a stored procedure and called from both the client applications and other stored procedures.

By analyzing your existing stored procedures or SQL statements and looking for redundant code that performs the same task in many places, you usually can find candidates for utility functions. In this situation, consider writing a stored procedure that performs the task with as few parameters as possible while returning a standard response. Once you have created the new

procedure, you can easily replace the redundant code with a call to the new procedure.

Using stored procedures can greatly reduce the amount of actual SQL code you have to write because you can call other stored procedures to perform like functions. This feature appears in all popular programming languages and allows for more reliable, error-free SQL code.

Once a procedure is written and tested, you can call that procedure from many places and it will work the same way in each case. If you need to enhance the feature set of the stored procedure, you make the change in one place, debug the change, and you’re done. In addition, stored procedures can cut the development cycle of your applications dramatically. A library of standard stored procedures can be developed once and reused many times by many different client applications.

Previous Table of Contents Next

Brief Full Advanced

Search Search Tips

To access the contents, click the chapter and section titles.

Microsoft SQL Server Black Book

(Publisher: The Coriolis Group)

Author(s): Patrick Dalton ISBN: 1576101495 Publication Date: 07/01/97

Search this book:

Previous Table of Contents Next

Reduced Client Processing

There is one feature of stored procedures that even hard-core, talented client programmers often miss. In earlier applications, the programmer was responsible for determining all the related and supporting code for modifying data. Primary keys, lookup tables, and calculations were maintained by the applications individually. With server-side processing becoming more a requirement, stored procedures can reduce the amount of client-side processing by looking up data and maintaining key values and internal integrity.

Server-based stored procedures allow you to develop “thin” client applications—applications concerned only with displaying data in a manner that meets the user’s needs. Very little data logic is needed in client

applications with strong server-side processing. With the speed and power of database servers today, we can offload the time-consuming tasks to the server, while letting the users perform the tasks.

Network Traffic

Network bandwidth is typically in the 10- to 100-megabit range in most enterprises. Unless you are developing an application for modem dial-up connection to the database, you will have plenty of throughput to support your applications. If you do support modems or Internet access to your applications, the amount of traffic that goes across the pipe is very important.

If we analyze the amount of characters that must be sent to a server for a typical SELECT statement, you will begin to see the difference that a stored procedure can make. See Listing 6.1 for a standard SELECT statement of 206 characters, including formatting, and approximately 170 without spaces.

Listing 6.1 Regular SELECT statement.

/* Phone list in alpha order (CA) */

SELECT ‘Name’ = UPPER(SUBSTRING(au_fname,1,1)) + ‘. ’ + SUBSTRING(au_lname, 1, 15),

‘Phone’ = ‘(’ + SUBSTRING(phone,1,3) + ‘) ’ + SUBSTRING(phone,5,8) FROM authors

WHERE state = ‘CA’ ORDER BY au_lname

/* Same data returned through a call to a stored procedure. */ EXEC CP_GETAUTHORLIST ‘CA’

The last line is a call to a stored procedure. EXEC tells SQL Server to execute the procedure named

http://www.itknowledge.com/reference/standard/1576101495/ch06/194-196.html (1 of 3) [1/27/2000 6:16:32 PM] Go!

Keyword

---

CP_GETAUTHORLIST with a parameter of the two-character state code for added flexibility. I can call the stored procedure in this manner and allow it to return any state I wish to specify as the parameter in the call. Although the result list for these queries would be identical, by using the stored procedure, I would be passing only 26 characters across the network.

If this query is called by 50 different client applications 20 times per day, I would see a reduction of 180,000 characters in character-based traffic on my network. Although you would still have the same traffic volume return from each query, you would see reduced traffic with regard to the actual request.

Now if we add the modem or even a full T1 bandwidth to the equation, you can begin to see the impact. Remember that a full T1 line can reach a speed of just over 1 megabit per second and that even a fast modem can reach only 28,800 bps. In tested dial-in modem access, by using stored procedures and a well-designed client application, I have seen client/server applications attain speeds almost equal to those of a regular network application.

Keep in mind that to get that level of performance across a modem, you must keep network traffic to a

minimum. The benefit of this kind of application is that it responds very well over your network compared to fat client applications that pay no attention to how much traffic is passed between client and server.

Calling A Stored Procedure

As Listing 6.1 shows, a call to a stored procedure is not complicated. The EXECUTE, or EXEC, statement followed by the stored procedure name and any parameters is about all you need. You can return a result set or a single record with a stored procedure. The beauty of stored procedures is that the code is hidden from the client application. The client developer does not need to know much about SQL to use them in his or her applications.

Note: For more information on the many uses of the EXECUTE statement, see Microsoft SQL Server Books

Online. EXECUTE can be used in many ways to create powerful SQL scripts and stored procedures.

Stored procedures can be nested and call other stored procedures to return the results to the client. In fact, you can even use recursion in a controlled environment to make your code more useful. I have developed stored procedures called with no parameters that literally processed hundreds of records in many separate tables to return a single value to the client. Cursors and control-of-flow language features can be used in your stored procedures for added functionality.

Query Optimizer

Microsoft SQL Server uses a cost-based query analyzer that determines the best method to return the data that you request to the client. Indexes, join conditions, WHERE clauses, ORDER BY statements, and optimizer hints all come into play when determining the best way to access the data. You should look for any known resource-intensive item in your queries to help reduce the amount of time it takes for the Query Optimizer to determine the best plan for retrieving data. Whenever possible, you should avoid the following items when writing queries:

• Large results sets

• IN, NOT IN, and OR queries • <> (not equal)

• Row aggregate functions such as SUM, AVG, MAX, etc.

• Local variables, expressions, or data conversions in WHERE clauses • Highly nonunique WHERE clauses or no WHERE clause at all • Complex views with GROUP BY or ORDER BY statements

Remember that the Query Optimizer will look at the current state of the indexes and data distribution to choose the best plan possible. If you do not keep the statistics and integrity of your data current by periodically

rebuilding indexes and updating statistics, the Optimizer will recognize this and create worktables even when there are indexes that should have been used to return the data faster.

Previous Table of Contents Next

Brief Full Advanced

Search Search Tips

To access the contents, click the chapter and section titles. Microsoft SQL Server Black Book

(Publisher: The Coriolis Group)

Author(s): Patrick Dalton ISBN: 1576101495 Publication Date: 07/01/97

Search this book:

Previous Table of Contents Next

In document Microsoft SQL Server Black Book (Page 182-186)