When accessing data on a SQL Server, write your ASP code so as to minimize the number of round-trips between the application running on the IIS server and SQL Server. Each and every time you use ADO to execute Transact-SQL code to get data from SQL Server, multiple, time-consuming steps have to occur. For example:
- Your VB code must generate a request to SQL Server in the form of a Transact-SQL statement.
- The statement is sent to the database through the Connection object.
- The request from the Connection object has to be translated into packets that can be sent over the network.
- The packets move over the network.
- When the packets arrive at SQL Server, they must be converted back into a form useable by SQL Server.
- SQL Server must then process the Transact-SQL statement. Assuming a stored procedure is not used, then this code must be optimized and compiled, then executed.
- The results, in the form of TDS (Tabular Data Stream), are then translated into packets that can be sent over the network.
- The packets move over the network, again.
- When the packets arrive at SQL Server, they must be converted back into TDS format.
- When ADO received the TDS data, it is converted into a recordset, ready to be used by the application.
If you know much about the technical details of networking, then you know that the above steps have been oversimplified. The point to remember is that round-trips between your application and SQL Server are expensive in time and resources, and you need to do your best in your code to minimize them. [6.5, 7.0, 2000] 7-19-01
*****
When you intersperse HTML code and data retrieved in a webpage, such as when you create a table based on data from a SQL Server database, you have essentially three different ways to do this. For example:
- You mix HTML code and ASP code (which displays data from SQL Server) in the same webpage to create the table.
- You use the Response.Write method to create the HTML code and to display data from SQL Server.
- You use the Response.Write method to create the HTML code and to display data from SQL Server, but you include in within a With…End With structure.
Each of these options will produce the same results, but each have different performance factors. Based on real-world testing, the order you see above indicates the fastest to the slowest methods of displaying data from a SQL Server database in a table. While the performance of the first two are very similar, the last method is significantly slower and should always be avoided. [7.0, 2000] Added 3-29-2002
*****
Does your web server have the latest MDAC (Microsoft Data Access Components) installed on it? Older versions have contained memory leaks and other performance-related problems. By installing the latest version of MDAC, performance can often be boosted. [7.0, 2000] Added 6-27-2002
]]>