A project is 50% efficient if its database is well managed. Database processing plays a very important role.
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 for a standard SELECT statement
of 206 characters, including formatting, and approximately 170 without spaces.
Listing 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
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.
0 comments: