Web sites that publish large amounts of text to web pages from text stored in SQL Server often experience performance problems. This is because it is time-consuming to run one or more queries against SQL Server data, retrieve the data from the database, and serve it up into a formatted web page, ready to display. While a request for a single web page is no big deal, but if you have a busy site and there are hundreds of people all wanting to see the same page at the same time, this can produce a large load on SQL Server.
What is especially frustrating is that each of your users will end up viewing the same page, even though SQL Server has to retrieve the same text over and over. This is a lot of wasted effort on SQL Server’s part. Wouldn’t it be more efficient if SQL Server only had to retrieve the data once, and not repeatedly?
The solution to this problem is to produce the static web pages only one time, and then serve up the static web page each time it is requested. This of course assumes that each page is not individually customized for the user. But if this is the case, why bother to store the text in a database in the first place? The reason has to do with management of the text. If you have hundreds or thousands of text articles to manage, this is very difficult to do by hand using HTML editors.
What is needed is a solution that combines storing and managing text in SQL Server, but that produces the static pages every time new text is added, or if the text should change.
There are several ways to deal with this. One way is to create your own ASP pages or COM objects to create the static pages. Another option is to purchase a third party product. Whichever method you choose, don’t force SQL Server to do the exact same thing over and over when it is not required. [6.5, 7.0, 2000]
*****
Always explicitly declare variables in your ASP code, and to prevent you from forgetting this important step, always add the OPTION EXPLICIT statement at the beginning of your ASP pages. Variables that are explicitly declared are faster than variables that are implicitly declared, boosting your ASP page performance.
Explicitly declared variables are faster because the scripting run-time code references undeclared variables by their name every time they are used. Declared variables, instead, are assigned an ordinal number, and then referenced by this ordinal number during execution, which is much faster. [6.5, 7.0, 2000]
*****
If there are objects that you may on may not need to instantiate from your ASP pages, such as instantiating a COM object to access SQL Server, don’t use the Server.CreateObject method, which creates an object immediately. Why go to all the trouble of instantiating an object that may not be needed, wasting server resources and hindering performance? Instead, declare the object using the <object runat=server id=objname> tag. This way, the object is only instantiated when one of its methods or properties is used for the first time, assuming if they ever are. [6.5, 7.0, 2000]
*****
When using ADO in ASP pages, it is convenient to use the various ADO enumerated constants instead of the obscure numeric values that can be used as an alternative. But the only problem with this on ASP pages is that the adovbs.inc file, which declares these constants, must be included with the each ASP page that uses the constants. While this makes the developer’s job easier, it increases the size of the ASP pages and adds overhead when the ASP page is compiled and run when it is called. So if you are interested in ASP speed, then don’t use ADO constants, use the numeric values instead. [6.5, 7.0, 2000]
*****
If you don’t like the idea of including the adovbs.inc file in your ASP pages because of the related overhead that is incurred, but you would like the convenience of not having to use meaningless constants in your code, there is one option you may want to try that uses less overhead than including the adovbs.inc file, but still gives you the ability to create readable code, although it still is not as fast as using constants.
The option is to load the adovbs.inc file using a <METADATA> tag instead of adding it as an include, using this format:
<!–METADATA
TYPE=”typelib”
FILE=”FileName”
UUID=”TyleLibraryUUID”
–>
TYPE needs to be set to “typelib,” while you only need to specify either the FILE or the UUID options, such as:
<!–METADATA
TYPE=”typelib”
FILE=”C:Program FilesCommon FilesSystemADOmsado20.tlb”
–>
You can add the <METADATA> tag to each page, or even better, it can be added to the global.asa file, which means that the constants will automatically be added to each of your ASP pages. [6.5, 7.0, 2000] Updated 4-3-2006
*****
When creating COM components to access SQL Server, try to design the component to have as few properties as possible. For example, instead of having a property for every column of data you want to send back or forth between the database and your application, create one generic property that can be used to send all of the columns at one time. What this does is reduce the number of calls that must be made by the component, reducing overhead on the component and SQL Server. [6.5, 7.0, 2000]
*****
If you need your ASP application to generate a unique value for use in a primary key column in a SQL Server table, performance will be slightly better if you let SQL Server, instead of your ASP application, create the unique value. SQL Server can generate unique keys using either an Identity (using the Integer data type) column or by using the NEWID function in a UniqueIdentifier column. Of these two, Identify columns offer better performance. [6.5, 7.0, 2000] Added 8-7-2000
*****
When referencing recordset values in your ASP code, don’t use the commonly used “rs.(“fieldname”) form to refer to a field reference. For better performance use the SET clause to assign a variable to the field reference, then use this variable in your code instead. This option incurs much less overhead and is much faster. For example, instead of using, “rs.(“fieldname”) to refer to a field reference, use: “Set variablename = rs.(“fieldname”)” instead, then use the variablename in your code.
Another way to reference record values that is faster than referring to the fieldname directly, although not as fast as using the SET clause, is to refer to the fieldname by its ordinal value, or a constant that refers to the ordinal value.
[6.5, 7.0, 2000] Updated 12-13-2000
*****
If you need to create multiple RecordSets on an ASP page, the most efficient way to do this is to create only one Connection object, and then reuse it in the ActiveConnection property. [6.5, 7.0, 2000] Added 8-17-2000
*****
Unless required by the application, don’t use disconnected recordsets with your ASP pages. They are slower than using standard RecordSets because of the inefficiency of the client-side cursor used. [6.5, 7.0, 2000] Added 8-17-2000
*****
ADO allows you to instantiate RecordSet objects using either the Connection object or the Command object. If the nature of your application allows you to choose, always choose the Connection object, as it instantiates the RecordSet object faster. [6.5, 7.0, 2000] Added 8-17-2000
*****
If you need to access SQL Server data, but don’t have the time or interest in learning how to write stored procedures, consider using the GetRows method of the RecordSet object. The GetRows method is used to pull all the records from the recordset into an array, which is much faster than using dynamic Transact-SQL to download a RecordSet to your application. [6.5, 7.0, 2000]
*****
If you need to build a HTML table based on data from a SQL Server database, one of the most common ways is to loop through an ADO recordset. Although this code is easy to write, it is not efficient or fast. A much faster technique is to extract all of the data from the recordset using the recordset’s GetString method. [6.5, 7.0, 2000] Added 10-26-2000
*****
Don’t instantiate, initialize, use, and then destroy an object within a loop. If the loop repeats itself much at all, you create an inordinate amount of overhead for your application. Instead, reuse the same object in the loop. One of the best ways to do this is to include a reinitialize method for the object that can be called from within the loop. [6.5, 7.0, 2000] Added 10-26-2000