SQL Server

Tips for using ASP.NET with SQL Server 2016

Tips for using ASP.NET with SQL Server 2016


Set the TrustServerCertificate to False in the SQL Azure connection string.

The TrustServerCertificate=False setting is recommended to help protect against

man-in-the-middle attacks.

Use the SQL Server .NET data provider to access SQL Server 2016 data using

ADO.NET.

Because using the SQL Server .NET data provider provides better performance

in comparison with other providers, you should use the SQL Server .NET data

provider whenever possible.


Try to use the same connection string each time.

Because the connection pooling only works if the connection string is the same,

you should use the same connection string each time. Using the connection pooling

sometimes can greatly improve the total ASP.NET performance.

When building a web page based on SQL Server queries, try to restrict the

queries result set by returning only the particular columns from the table,

not all table’s columns.

This can results in good performance benefits, because SQL Server will return

only particular columns, not all table’s columns. This can reduce network traffic

and boost the overall ASP.NET performance.

If you use ADO to access SQL Server data in your ASP.NET, try to avoid using

Refresh method when you call stored procedures from the ADO Command object.

This can improve performance of your ASP.NET because using Refresh method produces

extra network traffic. You should explicitly create the stored procedure parameters

using ADO code, instead of using the Refresh method to identify the parameters

of a stored procedure.

Consider using CLR stored procedures.

In SQL Server 2016 you can use stored procedures written in .NET Framework

languages, such as Visual Basic .NET and Visual C# .NET. You can use CLR stored

procedures because .NET languages support logic and features not available in the

Transact-SQL. For example, you can use user-defined types and aggregates that

written in .NET languages to build more complex data types than available in

Transact-SQL.

Avoid using application roles.

Using application roles prevent using of connection pooling. So, if you want to

take advantage of connection pooling, do not use application roles.

Consider using the CLR user-defined data types.

By using the CLR user-defined data types, you can create new database data types

in any of the languages supported by the .NET CLR. CLR user-defined data types

can be used in all the contexts where the built-in types are used. Keep in mind,

that the ability to execute CLR code is set to OFF by default. To execute CLR code

you should enable CLR by using the sp_configure stored procedure.

Specify the Initial Catalog setting for all connection strings if the database

will be used by the Entity Framework and your application targets the
.NET Framework 4.

The Initial Catalog setting specifies the name of the database in the SQL Server

instance catalog. By the way, you can omit this setting in applications that target

the .NET Framework 4.5.

If you use ADO to access SQL Server data in your ASP.NET, try to reuse the

ADO Command object instead of the creation a new Command object each time.

For example, if you call the same stored procedure over and over, reuse the

ADO Command object instead of the creation a new Command object each time.

This can boost the ASP.NET performance.

Consider using the SQLDataReader object if you will select data from a SQL Server

database for non-interactive display only on a web page.

You can select records from a database using a SQLCommand query and create a

SQLDataReader object that is returned from the SQLCommand object’s ExecuteReader method.

If you use ADO to access SQL Server data in your ASP.NET, always create

a Connection object explicitly, not implicitly.

This can reduce the server overhead and simplify the controlling of a connection.


When the web pages contain images, do not store these images in a SQL Server 2016

database using image data.

It is much faster to store images on the web server and store the URL to these images

in the SQL Server database.

Try to use early binding instead of late binding.

When you invoke the COM object, ASP.NET verifies that the object exists and that any

properties or methods used with the object are specified correctly. This verification

process is known as binding. There are two types of binding: late binding and early

binding. Late binding occurs at run time. Early binding occurs at compile time.

Late binding is much slower than early binding. So, you should use early binding,

whenever possible.

If you use ADO to return data from SQL Server 2016, try to use forward-only

and read-only cursors for an ADO Recordset.

ADO provides four cursors: forward-only, static, keyset, and dynamic. The forward-only

and read-only cursors sometimes called a firehose cursor. The firehose cursor does not

have to remember positioning information, and provides the fastest performance with

the least amount of overhead. So, you should use forward-only and read-only cursors

for an ADO Recordset to return data from SQL Server 2016 whenever possible.


When building a web page based on SQL Server queries, try to restrict the

queries result set by using the WHERE clause.

This can results in good performance benefits, because SQL Server will return

only the exact amount of data you need, not more. This can reduce network traffic

and boost the overall ASP performance.

Try to avoid using ADO methods to access SQL Server data in your ASP.

Use SQL Server 2016 stored procedures instead of ADO methods. By doing so, you

can reduce network traffic and boost overall ASP.NET performance.

If your data access code is large and is often reused in your ASP, consider

putting the database access code in compiled COM objects instead of in ASP code.

Using the COM objects instead of placing data access code in ASP can provide

better performance when the data access code is large and is often reused,

because COM objects are compiled. By using the COM objects, you can also improve

the scalability of you code.


Try to avoid using the DataGrid component to format and display the data

on a web page.

Because using the DataGrid component often reduce the performance of your

web-based application, you can manually format data retrieved from a SQL Server

database using the appropriate HTML code instead of using the DataGrid component.

If your ASP pages reuse the same static data over and over again, cache this

data on the web server instead of storing this data in your database and

retrieve it every time it is needed.

Internet Information Server (IIS) get the data from the web server much faster

than from the SQL Server database. So, you should store static data on a web

server instead of SQL Server database.

Do not forget to drop all temporary objects and close any user-defined

transactions before closing a connection.

All temporary objects and user-defined transactions that were created in

the connection should be dropped before closing the connection if you need

to release used memory.


If you use ADO to access SQL Server data in your ASP.NET, avoid creating

transactions using ADO methods.

Try to create transactions inside a stored procedure on the SQL Server. By doing

so, you can reduce network traffic and boost overall ASP.NET performance.

Consider setting the MultipleActiveResultSets option in the SQL Server

connection string.

This option (MultipleActiveResultSets – MARS) makes it possible to execute

multiple queries simultaneously.

Facebooktwittergoogle_plusredditpinterestlinkedinmail