|
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]
*****
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]
*****
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 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 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]
*****
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]
*****
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]
*****
ADO allows you to create four different types of
SQL Server cursors. Each has its own place, and
you will want to choose the cursor that uses
the least possible resources for the task at
hand. When at all possible, attempt to use the
Forward-Only cursor, which provides the least
amount of overhead of the four cursor types.
[6.5, 7.0, 2000]
*****
Implement database connection pooling to
reduce the number of connections there are
between IIS and SQL Server. Connection pooling
allows a single database connection to be shared
by multiple users at the same time, reducing SQL
Server overhead, and increasing scalability and
performance. Connection pooling can be
implemented through ODBC and MTS (Microsoft
Transaction Server). [6.5, 7.0, 2000]
Pooling Article from Microsoft
*****
If
your ASP pages are connecting to SQL Server via
either OLE DB (version 2.0 or higher) or ODBC
(version 3.0 or higher), SQL Server
connection pooling is automatically implemented
for you. Because of this, you don't have to
write special code to implement connection
pooling yourself. In addition, you don't want to
even reuse an ADO connection object, which is
commonly done by many VB developers.
If
you want to take the best advantage of database
connection pooling, and optimize your VB
application's SQL Server data access, the best
advice you can receive is to be sure that you
only open a database connection just before you
need it, and then close it immediately after you
are done with it. Don't leave database
connections open if you are not using them.
When you create or tear down a database
connection in your code, you aren't really
creating a new connection or tearing down a
current connection. What is happening is that
your connection requests are send to OLE DB or
ODBC, and they determine if a connection needs
to be created or torn down. If a new connection
is needed, then one is created, or one is used
from the current connection pool. And if you
request that a connection be torn down, it will
actually pool the unused connection until it is
needed, or tear it down if it is not reused
within a given time period. [6.5, 7.0, 2000]
*****
In
order for connection pooling to work correctly,
be sure each connection you open uses the
same ConnectionString parameters. Connection
pooling only works if all of the parameters for
the ConnectionString are identical. If they are
all not identical, then a new connection will be
opened, circumventing connection pooling. [6.5,
7.0, 2000]
*****
Avoid using the TEXT or NTEXT data types in
your ASP applications. Both of these data
types can slow down data retrieval
substantially. Instead, try to use CHAR, NCHAR,
VARCHAR, or NVARCHAR data types instead. If the
data you need to retrieve is greater than 8,000
characters, consider splitting your data between
two or more columns, retrieving the data as a
unit, and the recombining it on a web page.
Another option, if the text is just too long, is
to store the a TEXT or NTEXT data type, but then
to write it out as static HTML file onto the web
server whenever the text is changed, not every
time the ASP page is called. This will
substantially reduce the overhead due to the use
of the TEXT or NTEXT data type. Then have your
database store the URL or file location to the
static text page, and then insert the file into
your ASP pages when the page is called by a
user. If you like, use #INCLUDE FILE to insert
the static HTML page into your ASP page. [6.5,
7.0, 2000]
*****
If your ASP pages reuse the same static data
over and over again, don't automatically
just store this data in your database and
retrieve it every time it is needed. Instead,
cache it on the web server itself. It is much
faster for IIS to get the data from the web
server than from the database each time it is
needed. Of course this doesn't apply to dynamic
data. But many web sites have some relatively
static data that is kept in a database that is
better stored and served from a web server
instead.
A
real world example of this is a list of all 50
states that needs to appear in a drop-down box
on a web page form. Other examples of the types
of data that you might want to cache include:
DHTML scraps, XML strings, menu items,
configuration variables, DSNs, IP addresses, and
web paths.
There are a number of ways to cache commonly
used data. Some of them include using the
LookupTable object, or to store the data in an
Application variable. [6.5, 7.0, 2000]
*****
Don't cache an ADO database connection in the
Application object for use on multiple web
pages. If you do, then all the pages will fight
for the use of this connection. In addition,
don't cache an ADO connection in the Session
object. If you do, a database connection
will be created for every user, defeating
connection pooling and overusing server
resources. Instead, ADO connections should be
created and destroyed on every ASP pages that
needs to use ADO. This permits connection
pooling and ensures all connections are
efficiently created and destroyed. [6.5, 7.0,
2000]
More info from Microsoft
*****
If
your ASP pages perform a lot of database access,
consider putting the database access code in
compiled COM objects instead of in ASP code
on individual pages. The COM objects can be
loaded on the web server (probably using MTS,
but not necessarily), and they can then be
called from your ASP pages. Because COM objects
are compiled, they often can speed up database
access and improve scalability.
Making the decision whether to use code in an
ASP page or in a COM object is not always so
clear. In many cases, using COM objects to
access SQL Server is faster than using ASP code,
but in other cases ASP code can be faster than
using COM components. This is especially true if
you are using IIS 5.0 under Windows 2000.
While COM objects, keep in mind that
instantiating a COM object also takes time. If
your data access code is small, and is not
reused in your application, then including it in
an ASP page may be more efficient that putting
in in a COM object.
So
what should you do, use COM objects or ASP code
to access SQL Server? The only way you will know
for sure, for your particular situation, is to
test, trying both methods to see which option
provides the best performance for you. [6.5,
7.0, 2000]
More info from Microsoft
*****
Don't cache COM objects (database or of any
type for that matter) in either Session or
Application objects. If you do, COM objects
cached in Session objects will tie the Session
to an ASP worker thread, preventing it from
being used elsewhere. If a COM object is cached
in an Application object, all calls to the
object have to be marshaled and serialized,
adding substantial overhead to your application.
In other words, don't cache COM objects in
Session or Application objects. [6.5, 7.0, 2000]
*****
If
the database access via your web server is
maxing out your SQL Server and you have done
everything you can to optimize the SQL Server's
performance, consider replicating the databases
hit by your web server onto multiple SQL
Servers. Then rewrite the ASP code or COM
objects to take turns accessing each separate
physical SQL Server in a round-robin approach.
To
replicate the data between your SQL Servers, you
can use SQL Server's built-in replication
feature, or you can periodically manually copy
the database from one SQL Server to another.
What method you select will depend on if the
databases are read-only, or include
transactions.
If
you need to replicate transactions, then you
will need to use SQL Server's Merge Replication
feature. If you only need to replicate read-only
data, you can use SQL Server's Transactional
Replication, Snapshot replication, or manually
copy the databases between servers on a regular
basis. E-commerce applications that require
constant access to the same database server may
not be able to take advantage of this tip. [6.5,
7.0, 2000]
*****
If a single SQL Server is not big enough to
handle the load of your website, consider using
two or more SQL Servers, separating
transactional data from read-only data. For
example, all the transactional data from an
e-commerce application could be sent to one SQL
Server, and all requests for building web pages
that show-off products could come from a
separate SQL Server with read-only data. [6.5,
7.0, 2000]
*****
Use the SQL Server Web Assistant Wizard
to speed up web pages generated from data in
your database. If the data from your database is
relatively static, or doesn't change often, use
the SQL Server 7 Web Assistant Wizard to
automatically generate static HTML-based web
pages on a periodic basis. For example, the
wizard can be set up to go out to your database
once an hour and then generate a static web page
based on that data that is automatically served
up to your users. This requires much less
overhead than generating the same page from the
database each time the page is requested by a
user. [7.0, 2000]
*****
If your web server, and its backend SQL
Server database server, are not overwhelmed with
work, consider locating both of them on the same
physical server. This will eliminate the
overhead of sending network communications
between separate web and SQL Servers, providing
an incremental boost to performance.
But if your web server and SQL Server running
together max out a single server, then separate
them. Although the network overhead comes back
to haunt you when you do this, running them on
two servers makes your application much more
scalable for busy applications. [6.5, 7.0,
2000]
*****
If the database your ASP pages are hitting is
read-only, consider setting the database
option to "read only". This will turn off
locking in the database, giving a small
performance boost to your queries. If you do
make your database "read-only, be sure to update
the database's statistics first. This is
especially important for SQL 7.0 and 2000 as
"Auto Update Statistics" is turned off when a
database is set to "read only".
If
you have only one database that handles both
reads and writes, consider segregating the data
into two databases. Use one database for
read-only data and use the other database for
writing data. This will boost performance
because locking is eliminated in the "read-only"
database and reduced in the "write" database.
[6.5, 7.0, 2000]
*****
If you have retrieved data from SQL Server
and need to reuse the data over and over in your
ASP page, assign the data to local
variables. If you try to retrieve the data from
a collection over and over, you will be facing
unnecessary overhead. Local variables will
always be faster to access than a collection.
[6.5, 7.0, 2000]
*****
Perform simple data validation at the browser
instead of at the SQL Server. This helps to
reduce traffic between the browser and the
server, and reduces the load on both the web
server and the SQL Server. [6.5, 7.0, 2000]
*****
Instead of using session variables to store
state information, use SQL Server tables instead.
While session variables are very convenient for
the developer, they carry with them extra
overhead and aren't very scalable. While there
are a variety of ways to avoid session
variables, one technique you can use which is
faster and much more scalable is to store the
state information in a SQL Server row, and then
retrieve it later when needed. Many large web
sites use this method for their shopping cards.
At first glance, it may appear that creating a
database connection for this purpose may create
too much overhead, but this is not true if
database connection pooling is properly
implemented. [6.5, 7.0, 2000]
*****
Don't use transactions when accessing SQL
Server if you don't need them. Not all
requests to SQL Server, such as generic SELECT
statements used to download read-only data to a
browser, don't need to be encapsulated inside a
transaction. While transactions server an
important purpose, they do incur overhead, and
should only be used when they are really needed.
So don't include BEGIN TRANSACTION and COMMIT
TRANSACTION statements in your Transact-SQL code
unless needed. [6.5, 7.0, 2000]
*****
Don't use ASP-based Component Services
transactions if you don't need them. ASP
transaction processing is based on the Component
Services transaction environment, which is used
to update databases, such as SQL Server,
reliably. This is invoked through ASP code by
including <%@ TRANSACTION=Required %> in your
ASP pages. If you need to force two or more
operations to perform as a single unit, then use
this feature. But if you do not, avoid using it,
as it adds substantial overhead to your ASP
page. [6.5, 7.0, 2000]
*****
If you are using MTS (Microsoft Transaction
Server) components to access SQL Server,
don't forget to call the SetComplete or SetAbort
methods as appropriate. This helps to ensure
that server resources are released as soon as
possible, freeing up overhead and boosting
scalability and performance. [6.5, 7.0, 2000]
*****
When creating a Connection object, always
create it explicitly, not implicitly.
Opening a Connection object explicitly consumes
less resources than opening it implicitly, and
it also allows you to more efficiently manage
multiple connections and to reassign the various
roles that the Connections objects perform
within your application. [6.5, 7.0, 2000]
*****
When using recordsets, be sure to open them
explicitly, not implicitly. When recordsets
are opened implicitly, you cannot control the
default cursor and lock types, which are,
respectively, forward-only and read-only. If you
always open your recordsets explicitly, then you
can specify which cursor and lock types you want
to invoke for this particular situation,
specifying the types with the least amount of
overhead to accomplish the task at hand. [6.5,
7.0, 2000]
*****
Be sure you explicitly close any recordsets
or object variables you create to access SQL
Server, and so so as soon as they are no
longer required. This helps to free up resources
as soon as possible and reducing overhead. In
addition, close them as soon as your done with
them, not at the end of the ASP page. This helps
to reduce database connection overhead. [6.5,
7.0, 2000]
*****
When making your connection to SQL Server,
choose DSN-less connections for the fastest
connection. If you do need to use a DSN,
select System DSNs over File DSNs, because they
are faster when making connections. [6.5, 7.0,
2000]
*****
If
you have a choice between using ODBC or OLE DB
to access your SQL Server database, choose
OLE DB as it is generally faster. [7.0,
2000]
*****
When using ADO to make connections to SQL
Server, always be sure you explicitly close any
Connection, Recordset, or Command objects you
have opened. While letting an object go out
of scope will in affect close the object, it is
not the same as explicitly closing an object. By
explicitly closing these objects and setting
them to nothing, you do two things. First, you
remove the object sooner than later, helping to
free up resources. Second, you eliminate the
possibility of "connection creep". Connection
creep occurs when connection or resource pooling
is used and when connections are not properly
closed and released from the pool. This helps to
defeat the purpose of pooling and reduces SQL
Server's performance. [6.5, 7.0, 2000]
*****
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 use the SQL Server Web Assistant
Wizard. Another 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]
More info from Microsoft
*****
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 meaningless 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 Files\Common
Files\System\ADO\msado20.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]
More from Microsoft
*****
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]
*****
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]
More info from Microsoft
*****
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]
*****
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]
*****
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]
*****
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]
*****
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]
*****
In your ASP code, are you looping through
data extracted from SQL Server? Or are you
manipulating large datasets within your ASP
code? If so, these can hurt performance.
Instead, consider performing most, if not all,
data manipulation from within stored procedures
on SQL Server. This will not only speed data
manipulation, it will also reduce network
bandwidth between your web server and SQL
Server, boosting overall performance. [6.5, 7.0,
2000]
*****
For maximum performance, consolidate most, if
not all, of your COM components in a single DLL.
COM components instantiated from a single DLL
instantiate faster than if they are called from
multiple DLLs. [6.5, 7.0, 2000]
*****
If you need to display a lot of data from SQL
Server on a web page, consider enabling page
buffering for the web server (buffering is
off by default in IIS 4, but is on by default in
IIS 5). You can also enable page buffering via
ASP code, using "response.buffer=true" on each
page that you want to enable page buffering on.
Enabling page buffering can significantly speed
up pages with lots of data. If you do enable
page buffering, the entire page will not be
displayed until it is complete. If your page
takes a while to create, and you don't want to
make your users look at a blank screen, you can
use "response.flush" as needed to break up the
page into two or more parts that are displayed
on the screen as they are completed. [6.5, 7.0,
2000]
*****
Because users are fickle, you can't depend on
them waiting for a web page to be processed and
displayed on their screen. If the time it
takes between when they submit a page request
and for the final page to be displayed is very
long, they may get tired of waiting and move
onto another page. If this happens, then you
have wasted a lot of processing for nothing. If
you have complex pages that take some time to
create, you may want to consider using the "response.isclientconnected"
method after running long queries, but before
building complex pages. This way, if the visitor
has left your web page, you don't have to bother
with building the complex page, saving your
server some overhead. To abort the page, all you
have to do is run "response.end". [6.5, 7.0,
2000]
*****
In order to take full advantage of ADO's
connection pooling with your ASP pages, you
will always want to use the exact same
connection string each time you make a
connection, and you will want to open the
connection explicitly, not implicitly. If you
don't do both, then connection pooling will not
work. The easiest way to ensure that you use the
same connection string each time is to store it
in an application variable in the registry of a
COM component, then call it from all your ASP
pages as needed. [6.5, 7.0, 2000]
*****
If you use collections in your VB 6 or ASP
code, consider instead using dictionaries in
order to boost the performance of your
application. While you are probably familiar
with what and how collections are used, you may
not be familiar with dictionaries. The
dictionary class (the Scripting.Dictionary
Object) is part of the Microsoft Scripting
Runtime library, which is delivered with the VB
6 and ASP development environments.
Like collections, dictionaries can hold any type
of data, and items can be retrieved by using a
key, or iterated using the "For Each" syntax.
But dictionaries are different in that offer
properties and methods not available for
collections. And the biggest difference is that
they are much faster, about twice as fast. If
you haven't learned about dictionaries yet, you
need to take the time now to learn about their
numerous advantages. [6.5, 7.0, 2000]
*****
(Part 1) Many ASP developers create their own
search engines that return back the results to
the web browser one page at a time, and
allowing the user to continue paging through the
results until they have found what they are
looking for. How this paging is implemented can
significantly affect the performance of your
website.
One common way to handle this paging is to use
client-side cursors. Coding is easy and this
method works fine. But the problem with
client-side cursors is that all the results of
the search, not just each single page, must be
transferred from SQL Server to the web server
(assuming your SQL Server and web server are on
separate boxes), which is acting as the client.
If the total search results include 1,000 rows,
that is a lot of data to move from the SQL
Server to the web server to be managed by a
client-side cursor. And if users are running
hundreds, if not thousands of searches each day,
this can cause a large performance hit on the
web server.
Another option is to use a server-side cursor
instead. While this will probably give better
performance than a client-side cursor in the
above example, service-side cursors aren't
always the best solution when performance is an
important issue. As discussed in other areas of
this website, cursors of any type are best
avoided.
A
more efficient way to handle paging is to use
SQL Server to perform most of the work, and only
send each page of data that is requested by the
user to the web server, not all potential pages
in one shot. This method can be implemented
using a SQL Server stored procedure, a SQL
Server temp table to store the results of the
user's query, and a sessions table to track each
user.
For example, when a user first connects to your
website, you can manage the user's session state
by storing it in a SQL Server table, and the
results of each user's query is inserted into a
separate temporary table that has an Identify
column. The Identify column is used to determine
how to divide up the result set into pages. Then
one page worth of rows (you determine the number
of rows) at a time is returned to the web
server. As part of your ASP code, you will have
to send the stored procedure the necessary
parameters so that it knows which page you want
to display each time, along with the necessary
state information. Once the session is ended,
the temp table can be deleted.
An
additional way to enhance the performance of
using a stored procedure and temp table is to
include the TOP operator as part of your SELECT
statement in the stored procedure. The TOP
operator allows you to limit how many rows are
to be returned. For example, if it is possible
to return thousands of rows, but you feel it is
unlikely that most users will not want to page
more than, say 10 pages of 20 rows each, then
you might want to limit the number of rows to
perhaps 200 rows. You will have to select the
ideal number of rows to use in the TOP operator
based on how you think your search engine will
be used. If you are bound and determined to use
client-side cursors, you might want to consider
using the TOP operator in your SELECT statement
to help reduce the number of rows sent to the
client (web server), instead of sending every
potential row.
[6.5, 7.0, 2000]
*****
(Part
2) Instead of creating a temporary table
with an identity column to create a numbered
column, as described in the above tip, you can
instead write the query using a correlated
subquery. (When I say numbered column, I mean
when you need a column in your recordset that is
a number from 1 to n, n being the number of
records returned from your query.)
So our task at hand is to write a query that
adds a numbered column to our recordset, and
only returns a predetermined number of records
for the given page. Here is the query:
DECLARE
@PAGESIZE INT
DECLARE @CURRENT_PAGE INT
SET @PAGESIZE = 5
SET @CURRENT_PAGE = 2
SET ROWCOUNT @PAGESIZE
SELECT AU_ID, AU_LNAME, AU_FNAME, PHONE,
(SELECT COUNT(*) FROM AUTHORS A2 WHERE
A2.AU_LNAME <= A.AU_LNAME AND AU_FNAME LIKE
'%A%') AS RowNumber
FROM AUTHORS A
WHERE AU_FNAME LIKE '%A%' AND (SELECT COUNT(*)
FROM AUTHORS A2 WHERE A2.AU_LNAME <= A.AU_LNAME
AND AU_FNAME LIKE '%A%') > (@PAGESIZE * @CURRENT_PAGE)
- @PAGESIZE
ORDER BY AU_LNAME
SET ROWCOUNT 0
I declared the variables so you could easily
turn this into a stored procedure. Also note
that you can remove the correlated subquery from
the list of columns to be selected. Even though
it shouldn’t hurt the query's performance, you
shouldn’t return a column unless you are going
to use it.
[6.5, 7.0, 2000]
*****
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]
*****
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]
*****
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] More
information from Microsoft
|
