PRB: Improving Response Time When Communicating over Network (46438)
The information in this article applies to:
- Microsoft SQL Server 4.2x
- Microsoft SQL Server 6.0
- Microsoft SQL Server 6.5
- Microsoft SQL Server 7.0
This article was previously published under Q46438 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.
| Modification Type: | Minor | Last Reviewed: | 3/2/2005 |
|---|
| Keywords: | kbProgramming KB46438 |
|---|
|