Databases Reference
In-Depth Information
•
Are there any connection option settings in the driver that could be changed
to improve performance?
Thinking Through the Issue
Let's think about what we know:
•
The photo is long data, and long data can slow performance. However, the
issue is not about the performance of displaying the records.
•
A connection pool is being used. It might affect the opening of the applica-
tion, but not the closing.
•
CPU and memory are plentiful, so that isn't the issue.
•
Microsoft SQL Server is a streaming protocol database.
What could be causing the issue?
•
The driver is not configured for optimal performance. For example, the dri-
ver's packet size is configured smaller than the server's packet size, which
results in more network round trips.
•
The application is accessing a streaming protocol database. When a query is
executed on a streaming protocol database, all records are returned. This
means that even if the user doesn't look at all the records when he closes the
application, all the records must be processed off the network before the
application actually closes.
•
The application design is not optimal, especially when accessing a streaming
protocol database. The application is written to return long data with each
record.
The Resolution
The performance issue revolves around the fact that the application is accessing a
streaming protocol database and that long data is retrieved for each record. Do
you know why this issue happens only occasionally? Let's say a user queries on
the state of North Carolina and 15,000 records are returned. The user finds what
he is looking for after displaying 21 records and closes the application. Before the
application can close, all the other 14,979 records must be processed off the net-
work. In this case, the application could take a few seconds to close. On the other
hand, if the user displayed 10,000 records before closing the application, fewer
records would need to be processed before the application closed, which would
make the application close more quickly.
