Right way to retrieve data from sql-server

Last night I face performace problems in web site. I got like 100 000 users and site fails because of sql-connections.

Currently I'm making a new connection for every query, and my code looks like this:

Public Shared Function executeQueryReturnDataset(ByVal strsqlAs String)As DataSetDim sqlConnectionAs New SqlConnection(Connstr)Dim sqlCommandAs New SqlCommand(strsql, sqlConnection)Dim sqlDataSetAs New DataSetTry sqlConnection.Open()Dim myDataAdapterAs New SqlDataAdapter myDataAdapter.SelectCommand = sqlCommand myDataAdapter.Fill(sqlDataSet)Catch eAs Exception Console.Write(e.ToString())Finally sqlConnection.Close()End Try Return sqlDataSetEnd Function

I was wondering would it be better to save an instance from connection object to memory and use that same connection for all querys?

No. SQL connection pooling is automatic.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconConnectionPoolingForSQLServerNETDataProvider.asp

Which version of SQL are you running?

As a by note, a SQLAdapter will open its connection if it is closed, and if it did open it, close it immediately after it finishes.

The only real improvement above would be the use of a SQLDataReader if applicable, and if not, the instantiation of the SQLDataAdapter, and assignment of its command, prior to opening the connection.

|||

I do not think that saving connection will help you to much because you use connection pooling anyway
try to modify your code to look like this:

Public Shared Function executeQueryReturnDataset(ByVal strsqlAs String)As DataSetDim sqlConnectionAs New SqlConnection(Connstr)Dim sqlCommandAs New SqlCommand(strsql, sqlConnection)

Dim myDataAdapterAs New SqlDataAdapter myDataAdapter.SelectCommand = sqlCommandDim sqlDataSetAs New DataSetTry sqlConnection.Open() myDataAdapter.Fill(sqlDataSet)Catch eAs Exception Console.Write(e.ToString())Finally sqlConnection.Close()End Try Return sqlDataSetEnd Function

So now your connection should be use for a little shorter period of time and it should allow other web user to access data,
you can also try to optimize your query to be processed in shortest amount of time. Another question is:

 Do you really need to return dataset? maybe returning table will be more efficient (less resources to use)?

Public Shared Function executeQueryReturnDataset(ByVal strsqlAs String)As DataTableDim sqlConnectionAs New SqlConnection(Connstr)Dim sqlCommandAs New SqlCommand(strsql, sqlConnection)
Dim myDataAdapterAs New SqlDataAdapter myDataAdapter.SelectCommand = sqlCommandDim ResultTableAs New DataTableTry sqlConnection.Open() myDataAdapter.Fill(ResultTable)Catch eAs Exception Console.Write(e.ToString())Finally sqlConnection.Close()End Try Return ResultTableEnd Function
 
You can also try to use different way for reading your data DataReader?
Thanks

|||

But sometimes is safe to open and close connection by hands in case you would like to use the same connection object for multiple database access, because adapter sometimes does not close connection and reader inside it if it fails so you can have problem if you would like to use the same connection object after adapter. But if you use connection object for one call you can allow adapter to take care about opening/closing connection.

Thanks

|||

Thanks you!

Automatic pooling was new to me, I'm such a newbie :-)

Thanks