What are some ADO & ASP.NET best practices

[Rashad Rivera]

What is the best practice for querying, inserting and deleting from a SQL
2000 database from high traffic ASP.NET web forms? Is it to have the
SqlConnection & SqlCommand object initialized and stored globally in the
Application object, or is it to dynamically create and destroy them with
every visit to the web forms? I am seeing a serious problem with
performance when I got to over 1000 users consecutively. I estimate a usage
of over 10,000.



If I am to store the SqlConnection and Command objects in the global
Application object, how will I deal with threading, or is IIS single
threaded?



How do sites like Google perform their queries? They must be in the million
hit range.



My next question is will transactions help to improve performance? And if
so, where can I find completed samples?



I know I have a lot of question and I thank you for your help in advance.



Sincerely,



- Rashad Rivera

 

[Scott Allen]

Hi Rashad:

Keeping connection objects around in global state will kill
scalability. Transactions are generally bad for performance, but
required for data integrity.

What is the serious problem you are encountering? The first step would
be to use tools like ACT, SQL Profiler, and Perfmon to identify where
botlenecks occur. Maybe it's a common query in your database not using
an index, or perhaps your ASP.NET has a problem and the database is
under utilized. There is a *wealth* of information in the following
document, including how to measure performance, with chapters
dedicated to ADO.NET and SQL Server performance tips:

Improving .NET Application Performance and Scalability
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnpag/html/scalenet.asp

HTH,

 

[Val Mazur]

Hi,

I do not think using of the global connection is a good idea. If you think
about performance impact during opening of the connection, then .NET
provides connection pooling, which improves performance. If your application
uses same connection string each time when it opens connection to the
database, then first time connection will take some time, but subsequent
calls should take almost no time, since connection will be taken from the
pool.
You should also think about using of the stored procedures to improve
performance. Using of the .NET Managed provider for SQL Server would also
allow to improve performance