|
 |
 |
 |
 |
 |
 |
When creating a Command object to execute a stored procedure against SQL Server, you can use either the adCmdText or the adCmdStoredProc property to tell ADO that you want to execute a stored procedure. Always use the adCmdStoredProc property, which uses RPCs between the client and SQL Server. This acts to bypass parameter translation and boosts performance from 20 to 30 percent over using the adCmdText property. [6.5, 7.0, 2000]
*****
When retrieving data from a SQL Server 7 database, take full advantage of views when appropriate. This is especially true if you are not encapsulating your Transact-SQL in stored procedures as recommended. While calling a view is not usually as efficient as using a stored procedure to retrieve data, it is much more efficient that using dynamic Transact-SQL in your ASP code or COM components. [6.5, 7.0, 2000]
*****
If you need to execute a stored procedure from a Command object, and if the stored procedure will not return any rows, you can boost performance of the Command object by setting the adExecuteNoRecords option. This tells the Command object to not ask for a returning rowset, which saves a little overhead and reduce memory usage. [6.5, 7.0, 2000]
*****
When building a web page based on data retrieved from a SQL Server database, only return the exact amount of data you need, no more. Returning data you won't use when building your web page wastes resources and hurts performance. [6.5, 7.0, 2000] Added 6-27-2002
*****
If possible in your application, use stored procedures to "batch" a set of related Transact-SQL statements together, instead of calling a separate stored procedure for every database task you want to perform. This helps to reduce network traffic and server overhead. [6.5, 7.0, 2000]
*****
When calling SQL Server stored procedures from the ADO Command object, don't use the Refresh method to identify the parameters of a stored procedure. This produces extra network traffic and slows performance. Instead, explicitly create the parameters yourself using ADO code. [7.0, 2000]
*****
Don't store your web page images in a database using SQL Server's image data type, it is much too slow. The image data type should generally be avoided because of its poor performance. Instead, store images on the web server in a folder, and then store the URL in the SQL Server database. Whenever you need to dynamically create a page with images, your code can retrieve the URLs of the images and then insert the URLs into the page as it is dynamically created. When the page is displayed in a visitor's web browser, the images are retrieved directly from your web server.
This way, you have the advantage of managing image information in a database, but you don't have the overhead of storing the actual image in the database. [6.5, 7.0, 2000]
*****
While ADO makes database manipulation easy for the ASP page developer, using ADO methods to access SQL Server data can often kill SQL Server performance. As a rule of thumb, encapsulate your DML (Data Manipulation Language) in stored procedures instead of using ADO methods. This bypasses object library overhead, reduces the chatter between the VB application and SQL Server over the network, and lets you take advantage of the pre-compilation and reuse stored procedures offer. [6.5, 7.0, 2000]