|
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.
|
|
|
|
|
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.
|
|
|
|
|
That is what i am doing when before opening the connection in the function.
|
|
|
|
|
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
|
|
|
|
|
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.
"On two occasions, I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able to rightly apprehend the kind of confusion of ideas that could provoke such a question."
--Charles Babbage (1791-1871)
My: Website | Blog
|
|
|
|
|
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 --
|
|
|
|
|
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.
|
|
|
|
|
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.
|
|
|
|
|
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
|
|
|
|
|
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%'
|
|
|
|
|
what u get as result? be more specific.
|
|
|
|
|
this query takes a lot of time .How do i optimize this
|
|
|
|
|
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.
"On two occasions, I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able to rightly apprehend the kind of confusion of ideas that could provoke such a question."
--Charles Babbage (1791-1871)
My: Website | Blog
|
|
|
|
|
i have to keep it at the start as it is required
|
|
|
|
|
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]
|
|
|
|
|
Don't bother; it's a waste of time. You're doing an arbitrary query on text in the middle of a bunch of strings. Short of rearranging your data, the only significant optimization you're going to get is by throwing more hardware at it.
What does the schema or the rest of the data in the data set look like? Maybe there's some optimization based on the sequences your're looking for vs. the sequences in the data set. It's unlikely, though.
|
|
|
|
|
Halo,
Which tables in SQL server i need to update to create users for database and assign them to a groups? any articles to share.
planning to use this sql script from vb.net to create users 
thanks
Stephen
---------------------
www.islasolutions.net
|
|
|
|
|
miceisland wrote: Which tables in SQL server i need to update to create users for database and assign them to a groups?
You don't update any tables.
You should use the stored procedures:
* sp_grantdbaccess[^]
* sp_addrolemember[^]
"On two occasions, I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able to rightly apprehend the kind of confusion of ideas that could provoke such a question."
--Charles Babbage (1791-1871)
My: Website | Blog
|
|
|
|
|
many thanks. i will try this. 
---------------------
www.islasolutions.net
|
|
|
|
|
Also see: sp_addlogin[^]
----------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters
|
|
|
|
|
In my project, I need write data to Server 2000, I use the following code in different places, in most places it works, but in one class, whenever it runs to "writeroRs.Fields...", I get System.Runtime.InteropServices.COMException. Is it locked? Please help, thank you in advance.
Dim writerconnection As ADODB.Connection
Dim writertable As ADODB.Recordset
Dim arecord As addressrecord
writerconnection = New ADODB.Connection
writerconnection.ConnectionTimeout = 300
While writerconnection.State = ADODB.ObjectStateEnum.adStateClosed
Try
writerconnection.Open(connectionstring)
Catch ex As Exception
End Try
End While
Dim writeroRs As ADODB.Recordset
writeroRs = New ADODB.Recordset
writeroRs.Open("test.address", writerconnection, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockOptimistic, ADODB.CommandTypeEnum.adCmdTable)
writeroRs.AddNew()
writeroRs.Fields("Address1").Value = address1
writeroRs.Fields("Price").Value = price
|
|
|
|
|
my_btr wrote: In my project, I need write data to Server 2000, I use the following code in different places, in most places it works, but in one class, whenever it runs to "writeroRs.Fields...", I get System.Runtime.InteropServices.COMException. Is it locked? Please help, thank you in advance.
Looks like your trying to use VB.NET and ADO to connect to SQL Server. Read up on ADO.NET it has been completely remodled for the .NET languages.
I'd love to help, but unfortunatley I have prior commitments monitoring the length of my grass. :Andrew Bleakley:
|
|
|
|
|
i want to make datagrid read from datareader but i don't know to join between them
ma_refay
|
|
|
|
|
....
IDataReader reader = cmd.ExecuteReader();
DataGrid1.DataSource = reader;
DataGrid1.DataBind();
...
The code is something like that or at least very close. This solution only works over ASP.NET, Windows Grids can't read data from a DataReader
Greetings,
|
|
|
|
|
Hi Friends, let's straight to question:
This is the table I have, with 2 fields.
Product | Supplier
_________________________
Stationary | A
Stationary | A
Stationary | A
Stationary | B
Stationary | C
Furniture | A
Furniture | A
Furniture | B
Furniture | B
Furniture | B
Books | A
Books | C
Books | C
.......
.....
...
.
=====================================
Below is the COUNTING RESULT I expected after perform a sql query
Supplier | Stationary | Furniture | Books
---------------------------------------------------------
A | 3 | 2 | 1
B | 1 | 3 | 0
C | 1 | 0 | 2
D.......
E.....
F...
...
This the query I worked so far.
-----------------------------------------------------------
SELECT distinct (
SELECT count (*)
FROM Table1 where Product = 'stationary' and Supplier = 'A'
) AS no_of_Stationary, (
SELECT count (*)
FROM Table1 where Product = 'Furniture' and Supplier = 'A'
) AS no_of_Furniture, (
SELECT count (*)
FROM Table1 where Product = 'Books' and Supplier = 'A'
) AS no_of_Books
FROM Table1
UNION
SELECT distinct (
SELECT count (*)
FROM Table1 where Product = 'stationary' and Supplier = 'B'
) AS no_of_Stationary, (
SELECT count (*)
FROM Table1 where Product = 'Furniture' and Supplier = 'B'
) AS no_of_Furniture, (
SELECT count (*)
FROM Table1 where Product = 'Books' and Supplier = 'B'
) AS no_of_Books
FROM Table1
UNION
...... 'C'
..... 'D'
.... 'E'
...
..
. 'Z'
;
----------------------------------------------------------
Result:
no_of_Stationary | no_of_Furniture | no_of_Books
----------------------------------------------------
3 | 2 | 1
1 | 3 | 0
1 | 0 | 2
....
..
.
-----------------------------------------------------------
I don't know how to make another column for the Supplier appear,
and my SQL query is very tedious. Have to repeat for every supplier after each union.
In future, every time a new supplier added, have to create new query again for it.
Someone please help me to figure out the most simplified statement to perform
the expect table result above.
Thanks.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
