PRB: Improving Response Time When Communicating over Network

• Microsoft SQL Server versions 4.2x, 6.0, 6.5, 7.0

SYMPTOMS

A data-acquisition system is being designed that reads data from the COM port and uses it to update tables in SQL Server. The response time for the update is adequate to keep up with the COM port when no other users are accessing SQL Server. However, when other users are reading the data, response time can increase sufficiently to cause lost COM port data.

CAUSE

A programmer should not expect to be able to keep up with a COM port while communicating with SQL Server over the network.

WORKAROUND

A program must be capable of buffering the COM port data so that it does not overrun if other users happen to be using the same data. This type of functionality should be implemented with a thread that is running at a higher priority than the one communicating with SQL Server.

In SQL Server, pages cannot be updated while other users are reading those pages. This cannot be deactivated because it would violate the rules of consistency established by J.N. Gray in his paper titled "Granularity of Locks and Degrees of Consistency in a Shared Data Base."

The delay time due to locking can be minimized by receiving all results generated from select statements with the dbnextrow() function as quickly as possible. That which has been retrieved from the database and not actually sent to the client application remains locked until the client is ready for it. Perhaps an extract to a temporary table would make sense in this case. Also, minimize the use of HOLDLOCK and construct queries to use existing indexes. Complex queries and updates should be made into stored procedures to minimize the time spent in analyzing and optimizing the statement.

Additional query words: Optimization tuning Windows NT dblib

Keywords : kbprg SSrvDB_Lib SSrvServer SSrvWinNT
Version : winnt:4.2x,6.0,6.5,7.0
Platform : winnt
Issue type :