Using stored procedures, views, and triggers can reduce network traffic. These Transact-SQL tools can store large chunks of code on the server so that only short commands need to be sent across the network. If your applications send large batches of Transact-SQL commands to Adaptive Server, converting them to use stored procedures can reduce network traffic.
Stored procedures
Applications that send large batches of Transact-SQL can place less load on the network if the SQL is converted to stored procedures. Views can also help reduce the amount of network traffic.
You may be able to reduce network overhead by turning off “doneinproc” packets.
See Performance and Tuning: Monitoring and Analyzing for Performance for more information.
Ask for only the information you need
Applications should request only the rows and columns they need, filtering as much data as possible at the server to reduce the number of packets that need to be sent. In many cases, this can also reduce the disk I/O load.
Large transfers
Large transfers simultaneously decrease overall throughput and increase the average response time. If possible, large transfers should be done during off-hours. If large transfers are common, consider acquiring network hardware that is suitable for such transfers. Table 3-1 shows the characteristics of some network types.
Type |
Characteristics |
|---|---|
Token ring |
Token ring hardware responds better than Ethernet hardware during periods of heavy use. |
Fiber optic |
Fiber-optic hardware provides very high bandwidth, but is usually too expensive to use throughout an entire network. |
Separate network |
A separate network can be used to handle network traffic between the highest volume workstations and Adaptive Server. |
Network overload
Overloaded networks are becoming increasingly common as more and more computers, printers, and peripherals are network equipped. Network managers rarely detect problems before database users start complaining to their System Administrator
Be prepared to provide local network managers with your predicted or actual network requirements when they are considering the adding resources. You should also keep an eye on the network and try to anticipate problems that result from newly added equipment or application requirements.
