Database

8-Jun-06 1:42

I have a function which is interacting with SQL.
In the function i am opening the connection and closing it in the finally block.

When i execute that function it opens a connection with SQL that's fine.

Now if i call that functuion say 10 times 10 connections will be opened to SQL which is correct, but that should not be. Soon the SQL pool size will be filled and application(APS.Net) will get timeout exception.

That function will be used a lot of time in the application.

Is there a way that i can use a single connection to SQL for that application

Cause the problem that we are facing is that pool gets filled in a couple of hours and why the connection is not destroyed once i close it in FINALLY block.

Any help will be welcomed.

Paul Brower8-Jun-06 1:48

Paul Brower

8-Jun-06 1:48

Use a SqlConnection object at the class level. Create a property accessor for the SqlConnection object, and in the 'get' portion of the property, only create a new connection if the existing SqlConnection object is null.

nitin_ion8-Jun-06 1:52

8-Jun-06 1:52

That is what i am doing when before opening the connection in the function.

SeMartens8-Jun-06 1:50

SeMartens

8-Jun-06 1:50

Hi,

I think the easiest way to use a single connection for the whole application is to maintain the connection within the Global.asax.

There are several methods which will be called if your application starts or stops. Myself, I used this method several times when facing the same problem like you, and it worked fine for me.

I hope this helps a bit (if not, don't hesitate to ask again)

Regards
Sebastian

Re: SQL Connection [modified]

Colin Angus Mackay8-Jun-06 4:33

Colin Angus Mackay

8-Jun-06 4:33

The best practices for ADO.NET suggest you aquire late and release early. You should create a connection only when you need it and release it as soon as you are done with it. You should not hold it for the life of the application.

Rob Graham8-Jun-06 5:03

Rob Graham

8-Jun-06 5:03

I believe you need to Dispose() and set null in the finally block to insure it is returned to the pool. Close() is not enough.
As Colin pointed out, best practices recommend createing the connection as late as possible, and destroying it as soon as possible. You only need to keep it around across multiple operations if you are executing a multi-step transaction.

Last modified: Thursday, June 08, 2006 10:01:36 AM --

Re: SQL Connection [modified]

nitin_ion8-Jun-06 17:36

8-Jun-06 17:36

I m connecting to SQL only when needed and closing and disposing ASAP.
When i connect to database the pool size goes to 2 irrespective of how many times i call the function and whether i open and close the connection.

But as soon as i closes the application the pool goes to 0

I have tried with different options (Closing and keeping the connection open)

Dim scon As New SqlConnection(ConnectionString)
Dim da As New SqlDataAdapter("select * from Table", scon)
Dim ds As New DataSet

Try
da.Fill(ds)
Return ds
Catch ex As Exception
Finally
'da.Dispose()
'scon.Close()
'scon.Dispose()
End Try

what can be the reason.

Rob Graham9-Jun-06 5:23

Rob Graham

9-Jun-06 5:23

nitin_ion wrote:
When i connect to database the pool size goes to 2 irrespective of how many times i call the function and whether i open and close the connection
But as soon as i closes the application the pool goes to 0

Looks like its working correctly to me. On the first use, the connection pool is initialized to the minimum (2) and stays there (since you are only using one connection. The pool is not destroyed until your app exits... How are you determining the pool's connection count?
You can cause connections not in use to be removed by setting the "Connection Lifetime" parameter in your connection string to the number of seconds after which the connection should be reclaimed (NET2.0)
It defaults to 0 which meens infinite lifetime.

nitin_ion9-Jun-06 17:53

9-Jun-06 17:53

Ok, just 1 Q
as i am using asp.net and i am not using Global.asaz for connectionstring and also i am using web services which interacts with the same database.

So if multiple users are using the web pages and at the same time webservice is also quering database then how many connections should be open.
In oor case if 5-7 users are using and web services are working the the connections to Db goes upto 100+ and

at the login page it starts 11+ connections and we are just checking userid and password that's it.

What can be the reason

optimize this query

smita_roy8-Jun-06 0:18

smita_roy

8-Jun-06 0:18

select * from premium where Reg_Number_Vehicle like '%PB-1-AD-511%' or Reg_Number_Vehicle like '%PB-1-AU-396%' or Reg_Number_Vehicle like '%PCL-6888 %' or Reg_Number_Vehicle like '%PB-1-AP-535%' or Reg_Number_Vehicle like '%PB-1-AV-223%' or Reg_Number_Vehicle like '%PIM-224 %' or Reg_Number_Vehicle like '%CHE-5851 %' or Reg_Number_Vehicle like '%PCS-5569 %' or Reg_Number_Vehicle like '%PJL-161 %' or Reg_Number_Vehicle like '%CH-3-B-99%' or Reg_Number_Vehicle like '%PB-1-AC-198%' or Reg_Number_Vehicle like '%PB-1-AL-87%'

albCode8-Jun-06 0:50

albCode

8-Jun-06 0:50

what u get as result? be more specific.

smita_roy8-Jun-06 0:51

smita_roy

8-Jun-06 0:51

this query takes a lot of time .How do i optimize this

Colin Angus Mackay8-Jun-06 1:02

Colin Angus Mackay

8-Jun-06 1:02

Using LIKE, especially with wild cards at the start of a string, basically mean that the database can't use indexes (or can't use them efficiently) so it will naturally take a long time.

smita_roy8-Jun-06 1:25

smita_roy

8-Jun-06 1:25

i have to keep it at the start as it is required

Chris Meech8-Jun-06 3:43

Chris Meech

8-Jun-06 3:43

I wouldn't optimize it! I'd change the data design. You should create another table called Reg_Number_Vehicle_Class that is used to store all of the the different classes you are using. Then create an insert trigger on your premium table to ensure the appropriate class id is assigned to each row. Create a non-unique index on that column and your query should run faster.

Chris Meech

I am Canadian. [heard in a local bar]

The America I believe in has always understood that natural harmony is only one meal away from monkey burgers. [Stan Shannon]

GOOD DAY FOR: Bean counters, as the Australian Taxation Office said that prostitutes and strippers could claim tax deductions for adult toys and sexy lingerie. [Associated Press]