Page 4 - Best Practices : ASP Coding, SQL Statements, and Database Structuring

Local variables are those declared within subroutines and functions. Within a function or subroutine, local variable access is faster than global variable access. Use of local variables also tends to make code cleaner, so use them when you can

When accessing COM objects in ASP, you should copy frequently-used object data to script variables. This will cut down on COM method calls, which are relatively expensive compared to accessing script variables. When accessing Collection and Dictionary objects, this technique also cuts down on expensive lookups.

In general, if you are going to access object data more than once, put that data into a script variable. Prime targets for this optimization are Request variables (Form and QueryString variables). For example, your site may pass around a QueryString variable called UserID. Suppose this UserID is referenced a dozen times on a particular page. Instead of calling Request("UserID") a dozen times, assign the UserID to a variable at the top of the ASP page. Then use that variable throughout the page. This will save 11 COM method calls.

In practice, accessing COM properties or methods can be deceptively expensive. Here is an example, showing some fairly common code (syntactically speaking):



When this code runs, here's what happens:

1. The variable Foo is resolved as a global object.
2. The variable bar is resolved as a member of Foo. This turns out to be a COM method call.
3. The variable blah is resolved as a member of Foo.bar. This, too, turns out to be a COM method call.
4. The variable qaz is resolved as a member of foo.bar.blah. Yes, this turns out to be a COM method call.
5. Invoke Foo.bar.blah.quaz(1). One more COM method call. Get the picture?
6. Do steps 1 through 3 again to resolve baz. The system does not know if the call to qaz changed the object model, so steps 1 through 3 must be done again to resolve baz.
7. Resolve baz as a member of Foo.bar.blah. Do the property put.
8. Do steps 1 through 3 again and resolve zaq.
9. Do steps 1 through 3 yet another time and resolve abc.
As you can see, this is terribly inefficient (and slow). The fast way to write this code in VBScript is:



If you're using VBScript 5.0 or later, you can write this using the With statement:

Try to avoid Redim arrays. As far as performance is concerned, if you have a machine that is constrained by physical memory size, it's much better to set the initial dimension of the array to its worst-case scenario—or to set the dimension to its optimal case and redim as necessary. This does not mean that you should just go out and allocate a couple of megabytes of memory if you know you aren't going to need it.

The code below shows you gratuitous use of Dim and Redim.


It is far better to simply Dim the array to the correct size initially (in this case, that's 5), than Redim the array to make it larger. You may waste a little memory (if you don't end up using all of the elements), but the gain will be speed

Many people build a string in a loop like this:



There are several problems with this approach. The first is that repeatedly concatenating a string takes quadratic time; less formally, the time that it takes to run this loop is proportional to the square of the number of records times the number of fields. A simpler example should make this clearer.



On the first iteration, you get a one-character string, "A". On the second iteration, VBScript has to reallocate the string and copy two characters ("AB") into s. On the third iteration, it has to reallocate s again and copy three characters into s. On the Nth (26th) iteration, it has to reallocate and copy N characters into s. That's a total of 1+2+3+...+N which is N*(N+1)/2 copies.

In the recordset example above, if there were 100 records and 5 fields, the inner loop would be executed 100*5 = 500 times and the time taken to do all the copying and reallocation would be proportional to 500*500 = 250,000. That's a lot of copying for a modest-sized recordset.

In this example, the code could be improved by replacing the string concatenation with Response.Write() or inline script (<% = fld.Value %>). If response buffering is turned on (as it should be), this will be fast, as Response.Write just appends the data to the end of the response buffer. No reallocation is involved and it's very efficient.

In the particular case of transforming an ADO recordset into an HTML table, consider using GetRows or GetString.

If you concatenate strings in JScript, it is highly recommended that you use the += operator; that is, use s += "some string", not s = s + "some string".

Response.Redirect tells the browser to request a different page. This function is often used to redirect the user to a log on or error page. Since a redirect forces a new page request, the result is that the browser has to make two round trips to the Web server, and the Web server has to handle an extra request. IIS 5.0 introduces a new function, Server.Transfer, which transfers execution to a different ASP page on the same server. This avoids the extra browser-to-Web-server round trip, resulting in better overall system performance, as well as better response time for the user. Check out New Directions in Redirection, which talks about Server.Transfer and Server.Execute.

make sure to use a trailing slash (/) in URLs that point to directories. If you omit the trailing slash, the browser will make a request to the server, only to be told that it's asking for a directory. The browser will then make a second request with the slash appended to the URL, and only then will the server respond with the default document for that directory, or a directory listing if there is no default document and directory browsing has been enabled. Appending the slash cuts out the first, futile round trip. For user-friendliness, you may want to omit the trailing slash in display names.

For example, write:



This also applies to URLs pointing to the home page on a Web site: Use the following: <a href="http://msdn.microsoft.com/">, not <a href="http://msdn.microsoft.com">.

If you are trying to improve performance on a site, set a performance goal, then make incremental improvements until you reach your goal. Don't save all performance testing for the end of the project. Often, at the end of a project, it's too late to make necessary architectural changes, and you disappoint your customer. Make performance testing a part of your daily testing. Performance testing can be done against individual components, such as ASP pages or COM objects, or on the site as a whole.

Many people test the performance of their Web sites by using a single browser to request pages. This will give you a good feel for the responsiveness of the site, but it will tell you nothing about how well the site performs under load.

Generally, to accurately measure performance, you need a dedicated testing environment. This environment should include hardware that somewhat resembles production hardware in terms of processor speed, number of processors, memory, disk, network configuration, and so on. Next, you need to define your client workload: how many simultaneous users, the frequency of requests they will be making, the types of pages they'll be hitting, and so forth. If you don't have access to realistic usage data from your site, you'll need to guesstimate. Finally, you need a tool that can simulate your anticipated client workloads. Armed with these tools, you can start to answer questions such as "How many servers will I need if I have N simultaneous users?" You can also sniff out bottlenecks and target these for optimization.

It's highly recommended to use the Microsoft Web Application Stress (WAS) Toolkit. WAS allows you to record test scripts and then simulate hundreds or thousands of users hitting your Web servers. WAS reports numerous statistics, including requests per second, response time distributions, and error counts. WAS has both a rich-client and a Web-based interface; the Web interface allows you to run tests remotely.

SQL Server 2000

delivers scalability for e-commerce, data warehousing, and line-of-business solutions. The table below summarizes a selection of SQL Server 2000 benchmark results for various applications and workloads. Of the 18 results listed, 12 are best on any platform, beating the results of Oracle, IBM, and other database vendors regardless of operating system or hardware.

http://www.microsoft.com/sql/evalua...ndustryStandard

Windows Server 2003

Windows Server 2003 combines the highest levels of performance and scalability with the lowest costs of operation

http://www.microsoft.com/windowsser...ks/default.mspx

When possible. They consume an extra byte on each NULLable column in each row and have more overhead associated when querying data.
Some Null values have good uses and simplify coding when "missing data" is part of your business rules. But sometimes NULLable columns that are used in situations like this:

CustomerName1
CustomerAddress1
CustomerEmail1
CustomerName2
CustomerAddress2
CustomerEmail3
CustomerName1
CustomerAddress2
CustomerEmail3

is a bad practice. Normalize your table. It will be more flexible and faster, and will reduce the NULLable columns. Instead of creating 3 of Name and email create 1 table for names and 1 for emails and link w/ a one to many relationship; since, a user can have many unique emails

Try to avoid wildcard characters at the beginning of a word while searching using the LIKE keyword, as that results in an index scan, which defeats the purpose of an index. The following statement results in an index scan, while the second statement results in an index seek:



Also avoid searching using not equals operators (<> and NOT) as they result in table and index scans.

Use 'Derived tables' wherever possible, as they perform better. Consider the following query to find the second highest salary from the Employees table:


The same query can be re-written using a derived table, as shown below, and it performs twice as fast as the above query:

This is just an example, and your results might differ in different scenarios depending on the database design, indexes, volume of data, etc. So, test all the possible ways a query could be written and go with the most efficient one.

Use the more readable ANSI-Standard Join clauses instead of the old style joins. With ANSI joins, the WHERE clause is used only for filtering data. Where as with older style joins, the WHERE clause handles both the join condition and filtering data. The first of the following two queries shows the old style join, while the second one shows the new ANSI join syntax: