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

Reuse of Recursive Queries

I have a recursive query which I use to retrieve a set of identifiers. Something like:

WITH Set (Id)
AS (/* recursive code */)
SELECT Item FROM MyTable T
WHERE T.ItemId = ANY (SELECT Id FROM Set)

What I would like to achieve is to be able to reuse the code in bold in another procedure without duplicating the code (ie. reuse the query building up the set).

What I've done is to create a user-defined function which returns a TABLE parameter. So now, I have something like:

SELECT Item FROM MyTable T
WHERE T.ItemId = ANY(SELECT Id FROM MyFunction())

My question: is this the righ way of doing it? Does the use of a function incurs any relevant performance cost?

Thanks for advice.TVF would be fine. Another option is to convert it into a view.|||If you are making it as a TVF, then make sure it is inline otherwise you will have performance issues.

Returning the entered date

My question might be stupid but How to retrieve records for a date that I would like to determine?

Means I would like to have a parameter where I can enter the date I want to retrieve my records.

At the beginning I had it set up as: Between [Beginning Date] And [Ending Date]. Now I just want One parameter

Thanks for your helpHi
I am not sure if I fully understand your question.
you could do something like

select orderid, ordername from ordertable where dt_ordered = to_date('DATE_REQUIRED','YYYY/MM/DD');

Make sure DATE REQUIRED is of the format 'YYYY/MM/DD'.
Thanx and Regards
Aruneesh|||I want SQL to retrieve all the records to the corresponding date that I'll enter.

Means I'll have one pop up parameter where I enter the date I want.

I have a already one pop up parameter in my SQL statement for country which is: Like [Country:].
When I run my query a parameter pop up and I return the country I want.

I would like to do the same thing with the Date. Having a parameter where I enter the date I want.

Thanks|||I wanted to get some clarification on the scenario.
I dont under what popup you are mentioning here.

Are you anyway using VB to link it to some DB.
Please clarify the popup portion.
Thanx and Regards
Aruneesh|||The pop portion is just a parameter that pops up when you run your query.

In access your query can be presented in 3 ways: Design View, Table View and SQL view.

I'm working out of the Design view and when you specify a parameter in the criteria part of a field, it will pop up as a parameter when you run your Table view.

Anyway I found the solution. For the date and to return the date that you want on your parameter you just set the criteria as this:
Like [Date:] in your date field. In SQL it's translated like this:
WHERE Table.OrderDate = [Date:]

And now I bumped into another issue:
How to return the text that I want in my parameter. Means I want to look for a word in the whole title and the query will run the companies that contain that word.
I don't want to specify the word in my SQL statement I want to leave it open like I've done above for the date. (I'm working out of Access Database)

Thanks for the help