473,543 Members | 2,565 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Webservice not releasing SQL connections

I have a webservice written in VB.net that opens a connection to SQL Server
2000 with a connection string. The connection remains open for the life of
the web service so the ADODB.connectio n is defined as public and used by a
single subroutine, SQLexecute. All recordset definitions are defined in the
individual subroutines and destroyed before exit of the subroutine. I would
expect only one connection to be made to the SQL Server but when I access
the webservice it appears that EVERY read or write to the database creates a
new connection and uses a new local port!

I used TCPview to look at the ports being used and sp_who to look at the SQL
spid's and there are hundreds of connections established after only a couple
of sessions with the web service. I counted the number of reads and writes
to the SQL Server and they are essentially the same number as the number of
open (established) ports to the SQL server. It's as if it creates a new
connection for every read or write and never closes them.

Can anyone shed any light on why these connections are being created and how
to stop this from happening?

Thanks for any direction you can provide.

Steve
Apr 3 '07 #1
6 4342
Hi Steve,

How does your connection string looks like? Does it change for each user
that connects to the database? Or are you using a dedicated account to
connect to SQL Server? Have you tried to open and close the connection with
each call to the database and look at the connections created? And why do you
need to keep a connection open for the lifetime of the web service? This
doesn't correspond to the ADO.NET disconnected architecture, right?

"Steve Ricketts" wrote:
I have a webservice written in VB.net that opens a connection to SQL Server
2000 with a connection string. The connection remains open for the life of
the web service so the ADODB.connectio n is defined as public and used by a
single subroutine, SQLexecute. All recordset definitions are defined in the
individual subroutines and destroyed before exit of the subroutine. I would
expect only one connection to be made to the SQL Server but when I access
the webservice it appears that EVERY read or write to the database creates a
new connection and uses a new local port!

I used TCPview to look at the ports being used and sp_who to look at the SQL
spid's and there are hundreds of connections established after only a couple
of sessions with the web service. I counted the number of reads and writes
to the SQL Server and they are essentially the same number as the number of
open (established) ports to the SQL server. It's as if it creates a new
connection for every read or write and never closes them.

Can anyone shed any light on why these connections are being created and how
to stop this from happening?

Thanks for any direction you can provide.

Steve
Apr 5 '07 #2
Thanks for the reply! The connection string looks like:

Provider=SQLOLE DB;Data Source=xx.xx.xx .xx; Initial Catalog=MyDB; User ID=sa;
Password=somepa ssword;

Just one connection string for all users. I haven't tried opening and
closing the connection but I will do so.

This is the run-time environment for a web based training application. Each
page of a training "course" may access the web service 10-30 times per page
and there are thousands of students that could be accessing it concurrently.
It doesn't seem to make sense to open and close the database connection when
everone that makes a request to the web service will want to read or write
into the database. The clients make a request to the service and receive a
reply. No state information is maintained so I couldn't say whether this is
a "disconnect ed" architecture or not. To the client it is, to the database
it's not.

I'll let you know what the open/close test shows.

Thanks again,

Steve

"Eugen" <Eu***@discussi ons.microsoft.c omwrote in message
news:F7******** *************** ***********@mic rosoft.com...
Hi Steve,

How does your connection string looks like? Does it change for each user
that connects to the database? Or are you using a dedicated account to
connect to SQL Server? Have you tried to open and close the connection
with
each call to the database and look at the connections created? And why do
you
need to keep a connection open for the lifetime of the web service? This
doesn't correspond to the ADO.NET disconnected architecture, right?

"Steve Ricketts" wrote:
>I have a webservice written in VB.net that opens a connection to SQL
Server
2000 with a connection string. The connection remains open for the life
of
the web service so the ADODB.connectio n is defined as public and used by
a
single subroutine, SQLexecute. All recordset definitions are defined in
the
individual subroutines and destroyed before exit of the subroutine. I
would
expect only one connection to be made to the SQL Server but when I access
the webservice it appears that EVERY read or write to the database
creates a
new connection and uses a new local port!

I used TCPview to look at the ports being used and sp_who to look at the
SQL
spid's and there are hundreds of connections established after only a
couple
of sessions with the web service. I counted the number of reads and
writes
to the SQL Server and they are essentially the same number as the number
of
open (established) ports to the SQL server. It's as if it creates a new
connection for every read or write and never closes them.

Can anyone shed any light on why these connections are being created and
how
to stop this from happening?

Thanks for any direction you can provide.

Steve

Apr 5 '07 #3
Tip 3—Connection Pooling

Setting up the TCP connection between your Web application and SQL Server™
can be an expensive operation. Developers at Microsoft have been able to take
advantage of connection pooling for some time now, allowing them to reuse
connections to the database. Rather than setting up a new TCP connection on
each request, a new connection is set up only when one is not available in
the connection pool. When the connection is closed, it is returned to the
pool where it remains connected to the database, as opposed to completely
tearing down that TCP connection.

Of course you need to watch out for leaking connections. Always close your
connections when you're finished with them. I repeat: no matter what anyone
says about garbage collection within the Microsoft® .NET Framework, always
call Close or Dispose explicitly on your connection when you are finished
with it. Do not trust the common language runtime (CLR) to clean up and close
your connection for you at a predetermined time. The CLR will eventually
destroy the class and force the connection closed, but you have no guarantee
when the garbage collection on the object will actually happen.

To use connection pooling optimally, there are a couple of rules to live by.
First, open the connection, do the work, and then close the connection. It's
okay to open and close the connection multiple times on each request if you
have to (optimally you apply Tip 1) rather than keeping the connection open
and passing it around through different methods. Second, use the same
connection string (and the same thread identity if you're using integrated
authentication) . If you don't use the same connection string, for example
customizing the connection string based on the logged-in user, you won't get
the same optimization value provided by connection pooling. And if you use
integrated authentication while impersonating a large set of users, your
pooling will also be much less effective. The .NET CLR data performance
counters can be very useful when attempting to track down any performance
issues that are related to connection pooling.

Whenever your application is connecting to a resource, such as a database,
running in another process, you should optimize by focusing on the time spent
connecting to the resource, the time spent sending or retrieving data, and
the number of round-trips. Optimizing any kind of process hop in your
application is the first place to start to achieve better performance.

The application tier contains the logic that connects to your data layer and
transforms data into meaningful class instances and business processes. For
example, in Community Server, this is where you populate a Forums or Threads
collection, and apply business rules such as permissions; most importantly it
is where the Caching logic is performed.
From http://msdn.microsoft.com/msdnmag/is...erformance/#S4
"Steve Ricketts" wrote:
Thanks for the reply! The connection string looks like:

Provider=SQLOLE DB;Data Source=xx.xx.xx .xx; Initial Catalog=MyDB; User ID=sa;
Password=somepa ssword;

Just one connection string for all users. I haven't tried opening and
closing the connection but I will do so.

This is the run-time environment for a web based training application. Each
page of a training "course" may access the web service 10-30 times per page
and there are thousands of students that could be accessing it concurrently.
It doesn't seem to make sense to open and close the database connection when
everone that makes a request to the web service will want to read or write
into the database. The clients make a request to the service and receive a
reply. No state information is maintained so I couldn't say whether this is
a "disconnect ed" architecture or not. To the client it is, to the database
it's not.

I'll let you know what the open/close test shows.

Thanks again,

Steve

"Eugen" <Eu***@discussi ons.microsoft.c omwrote in message
news:F7******** *************** ***********@mic rosoft.com...
Hi Steve,

How does your connection string looks like? Does it change for each user
that connects to the database? Or are you using a dedicated account to
connect to SQL Server? Have you tried to open and close the connection
with
each call to the database and look at the connections created? And why do
you
need to keep a connection open for the lifetime of the web service? This
doesn't correspond to the ADO.NET disconnected architecture, right?

"Steve Ricketts" wrote:
I have a webservice written in VB.net that opens a connection to SQL
Server
2000 with a connection string. The connection remains open for the life
of
the web service so the ADODB.connectio n is defined as public and used by
a
single subroutine, SQLexecute. All recordset definitions are defined in
the
individual subroutines and destroyed before exit of the subroutine. I
would
expect only one connection to be made to the SQL Server but when I access
the webservice it appears that EVERY read or write to the database
creates a
new connection and uses a new local port!

I used TCPview to look at the ports being used and sp_who to look at the
SQL
spid's and there are hundreds of connections established after only a
couple
of sessions with the web service. I counted the number of reads and
writes
to the SQL Server and they are essentially the same number as the number
of
open (established) ports to the SQL server. It's as if it creates a new
connection for every read or write and never closes them.

Can anyone shed any light on why these connections are being created and
how
to stop this from happening?

Thanks for any direction you can provide.

Steve


Apr 5 '07 #4
Steve, I have the feeling that your web service leak connections and that
actually you are creating a new connection with each call.

Read the article from below. I hope it will help you.

http://msdn2.microsoft.com/en-us/lib...3(SQL.80).aspx

"Eugen" wrote:
Tip 3—Connection Pooling

Setting up the TCP connection between your Web application and SQL Server™
can be an expensive operation. Developers at Microsoft have been able to take
advantage of connection pooling for some time now, allowing them to reuse
connections to the database. Rather than setting up a new TCP connection on
each request, a new connection is set up only when one is not available in
the connection pool. When the connection is closed, it is returned to the
pool where it remains connected to the database, as opposed to completely
tearing down that TCP connection.

Of course you need to watch out for leaking connections. Always close your
connections when you're finished with them. I repeat: no matter what anyone
says about garbage collection within the Microsoft® .NET Framework, always
call Close or Dispose explicitly on your connection when you are finished
with it. Do not trust the common language runtime (CLR) to clean up and close
your connection for you at a predetermined time. The CLR will eventually
destroy the class and force the connection closed, but you have no guarantee
when the garbage collection on the object will actually happen.

To use connection pooling optimally, there are a couple of rules to live by.
First, open the connection, do the work, and then close the connection. It's
okay to open and close the connection multiple times on each request if you
have to (optimally you apply Tip 1) rather than keeping the connection open
and passing it around through different methods. Second, use the same
connection string (and the same thread identity if you're using integrated
authentication) . If you don't use the same connection string, for example
customizing the connection string based on the logged-in user, you won't get
the same optimization value provided by connection pooling. And if you use
integrated authentication while impersonating a large set of users, your
pooling will also be much less effective. The .NET CLR data performance
counters can be very useful when attempting to track down any performance
issues that are related to connection pooling.

Whenever your application is connecting to a resource, such as a database,
running in another process, you should optimize by focusing on the time spent
connecting to the resource, the time spent sending or retrieving data, and
the number of round-trips. Optimizing any kind of process hop in your
application is the first place to start to achieve better performance.

The application tier contains the logic that connects to your data layer and
transforms data into meaningful class instances and business processes. For
example, in Community Server, this is where you populate a Forums or Threads
collection, and apply business rules such as permissions; most importantly it
is where the Caching logic is performed.
From http://msdn.microsoft.com/msdnmag/is...erformance/#S4
"Steve Ricketts" wrote:
Thanks for the reply! The connection string looks like:

Provider=SQLOLE DB;Data Source=xx.xx.xx .xx; Initial Catalog=MyDB; User ID=sa;
Password=somepa ssword;

Just one connection string for all users. I haven't tried opening and
closing the connection but I will do so.

This is the run-time environment for a web based training application. Each
page of a training "course" may access the web service 10-30 times per page
and there are thousands of students that could be accessing it concurrently.
It doesn't seem to make sense to open and close the database connection when
everone that makes a request to the web service will want to read or write
into the database. The clients make a request to the service and receive a
reply. No state information is maintained so I couldn't say whether this is
a "disconnect ed" architecture or not. To the client it is, to the database
it's not.

I'll let you know what the open/close test shows.

Thanks again,

Steve

"Eugen" <Eu***@discussi ons.microsoft.c omwrote in message
news:F7******** *************** ***********@mic rosoft.com...
Hi Steve,
>
How does your connection string looks like? Does it change for each user
that connects to the database? Or are you using a dedicated account to
connect to SQL Server? Have you tried to open and close the connection
with
each call to the database and look at the connections created? And why do
you
need to keep a connection open for the lifetime of the web service? This
doesn't correspond to the ADO.NET disconnected architecture, right?
>
"Steve Ricketts" wrote:
>
>I have a webservice written in VB.net that opens a connection to SQL
>Server
>2000 with a connection string. The connection remains open for the life
>of
>the web service so the ADODB.connectio n is defined as public and used by
>a
>single subroutine, SQLexecute. All recordset definitions are defined in
>the
>individual subroutines and destroyed before exit of the subroutine. I
>would
>expect only one connection to be made to the SQL Server but when I access
>the webservice it appears that EVERY read or write to the database
>creates a
>new connection and uses a new local port!
>>
>I used TCPview to look at the ports being used and sp_who to look at the
>SQL
>spid's and there are hundreds of connections established after only a
>couple
>of sessions with the web service. I counted the number of reads and
>writes
>to the SQL Server and they are essentially the same number as the number
>of
>open (established) ports to the SQL server. It's as if it creates a new
>connection for every read or write and never closes them.
>>
>Can anyone shed any light on why these connections are being created and
>how
>to stop this from happening?
>>
>Thanks for any direction you can provide.
>>
>Steve
>>
>>
>>
Apr 5 '07 #5
I suspect you're right... not sure why it happens, but that's what it seems
to be doing. I'll take a look at the article in more detail, it looks
interesting.

Many thanks,

Steve

"Eugen" <Eu***@discussi ons.microsoft.c omwrote in message
news:7E******** *************** ***********@mic rosoft.com...
Steve, I have the feeling that your web service leak connections and that
actually you are creating a new connection with each call.

Read the article from below. I hope it will help you.

http://msdn2.microsoft.com/en-us/lib...3(SQL.80).aspx

"Eugen" wrote:
>Tip 3-Connection Pooling

Setting up the TCP connection between your Web application and SQL
ServerT
can be an expensive operation. Developers at Microsoft have been able to
take
advantage of connection pooling for some time now, allowing them to reuse
connections to the database. Rather than setting up a new TCP connection
on
each request, a new connection is set up only when one is not available
in
the connection pool. When the connection is closed, it is returned to the
pool where it remains connected to the database, as opposed to completely
tearing down that TCP connection.

Of course you need to watch out for leaking connections. Always close
your
connections when you're finished with them. I repeat: no matter what
anyone
says about garbage collection within the Microsoft .NET Framework,
always
call Close or Dispose explicitly on your connection when you are finished
with it. Do not trust the common language runtime (CLR) to clean up and
close
your connection for you at a predetermined time. The CLR will eventually
destroy the class and force the connection closed, but you have no
guarantee
when the garbage collection on the object will actually happen.

To use connection pooling optimally, there are a couple of rules to live
by.
First, open the connection, do the work, and then close the connection.
It's
okay to open and close the connection multiple times on each request if
you
have to (optimally you apply Tip 1) rather than keeping the connection
open
and passing it around through different methods. Second, use the same
connection string (and the same thread identity if you're using
integrated
authentication ). If you don't use the same connection string, for example
customizing the connection string based on the logged-in user, you won't
get
the same optimization value provided by connection pooling. And if you
use
integrated authentication while impersonating a large set of users, your
pooling will also be much less effective. The .NET CLR data performance
counters can be very useful when attempting to track down any performance
issues that are related to connection pooling.

Whenever your application is connecting to a resource, such as a
database,
running in another process, you should optimize by focusing on the time
spent
connecting to the resource, the time spent sending or retrieving data,
and
the number of round-trips. Optimizing any kind of process hop in your
application is the first place to start to achieve better performance.

The application tier contains the logic that connects to your data layer
and
transforms data into meaningful class instances and business processes.
For
example, in Community Server, this is where you populate a Forums or
Threads
collection, and apply business rules such as permissions; most
importantly it
is where the Caching logic is performed.
From http://msdn.microsoft.com/msdnmag/is...erformance/#S4
"Steve Ricketts" wrote:
Thanks for the reply! The connection string looks like:

Provider=SQLOLE DB;Data Source=xx.xx.xx .xx; Initial Catalog=MyDB; User
ID=sa;
Password=somepa ssword;

Just one connection string for all users. I haven't tried opening and
closing the connection but I will do so.

This is the run-time environment for a web based training application.
Each
page of a training "course" may access the web service 10-30 times per
page
and there are thousands of students that could be accessing it
concurrently.
It doesn't seem to make sense to open and close the database connection
when
everone that makes a request to the web service will want to read or
write
into the database. The clients make a request to the service and
receive a
reply. No state information is maintained so I couldn't say whether
this is
a "disconnect ed" architecture or not. To the client it is, to the
database
it's not.

I'll let you know what the open/close test shows.

Thanks again,

Steve

"Eugen" <Eu***@discussi ons.microsoft.c omwrote in message
news:F7******** *************** ***********@mic rosoft.com...
Hi Steve,

How does your connection string looks like? Does it change for each
user
that connects to the database? Or are you using a dedicated account
to
connect to SQL Server? Have you tried to open and close the
connection
with
each call to the database and look at the connections created? And
why do
you
need to keep a connection open for the lifetime of the web service?
This
doesn't correspond to the ADO.NET disconnected architecture, right?

"Steve Ricketts" wrote:

I have a webservice written in VB.net that opens a connection to SQL
Server
2000 with a connection string. The connection remains open for the
life
of
the web service so the ADODB.connectio n is defined as public and
used by
a
single subroutine, SQLexecute. All recordset definitions are
defined in
the
individual subroutines and destroyed before exit of the subroutine.
I
would
expect only one connection to be made to the SQL Server but when I
access
the webservice it appears that EVERY read or write to the database
creates a
new connection and uses a new local port!

I used TCPview to look at the ports being used and sp_who to look at
the
SQL
spid's and there are hundreds of connections established after only
a
couple
of sessions with the web service. I counted the number of reads and
writes
to the SQL Server and they are essentially the same number as the
number
of
open (established) ports to the SQL server. It's as if it creates a
new
connection for every read or write and never closes them.

Can anyone shed any light on why these connections are being created
and
how
to stop this from happening?

Thanks for any direction you can provide.

Steve


Apr 5 '07 #6
Hi Steve
I had a couple of suggestions. Hope you find these helpful.
-You mentioned Adodb, recordset..in your post. How come you are not
using ADO.NET? Using Com objects from a dot net application will give
you poor performance.
-Even if the connection object is declared public, why would you think
that would be reused by all invocations of the web methods? Each
invocation would create a new instance of the public member. You need
to make the member static. The singleton design pattern meets this
requirement perfectly.
Having mentioned this, as Eugen has suggested above, you should be
reopening a connection for each request rather than trying to keep the
same connection alive.

Latish

Apr 7 '07 #7

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
2591
by: Tommy | last post by:
hello, i tryd to use an webservice like Global Weather http://www.webservicex.net/globalweather.asmx?WSDL i have add a reference to my asp.net-website and write the follwing code(button1_click) Dim ProxyWeather As New Weather.GlobalWeather()
5
1410
by: Nikolay Petrov | last post by:
1. Can I use SSL for my web services? 2. How can implement some kind of security to my web services? My idea is to implement some kind of licensing or user/password authentication for an web service. 3. Do I have to make my web service multi threaded or IIS takes care for simultaneous requests? (I mean not multi threaded for accepting...
3
1643
by: Johan Johansson (Sweden) | last post by:
I wonder why it is that no more than 10 clients can access a webservice at a time, and how to increase that number. The eleventh client get a Http 403.9 (Access forbidden : To many users are connected) error message. Simply explanied; if I create the simple HelloWorld WebService, open 11 Internet Explorer's and give them the HelloWorld...
8
9306
by: kenneth fleckenstein nielsen | last post by:
hi guru's I want to restart my webservice when ever it throws an exception that isn't cought. can i do that by web.config or iss or how ??
0
1409
by: perschrotti | last post by:
I have simple .NET webservice coded in C# running on IIS 5 windows 2000 sp4 with .NET framework 1.1. I have a client coded in java with a webservice client stub generated by wsdl2java (axis). After doing some monitoring on the server box I noticed that connections were not closed but stacked up with the status TIME_WAIT and were not closed...
1
1180
by: Goose14 | last post by:
I have a client application and a webservice. Both are in the same solution. If I reference the local webservice and run, and step into the webservice no problem. My question is, when I want to publish the webservice, do i have to keep changing the reference from the local copy to the remote copy(on server) ? My ultimate goal is to be able...
0
1002
by: UnglueD | last post by:
Hello. I have a webservice located on a machine within our network. I have developed and tested this webservice internally without having to worry about credentials. Now I wish for web applications located on a server outside the network to be able to consume this webservice. I do not want to put the webservice outside the network, and...
2
3469
by: =?Utf-8?B?TGFycnlLdXBlcm1hbg==?= | last post by:
Our WebDev team seems to have found a problem that exposes a bug in .NET 2.0. This problem can be shown when trying to access a WebService using SSL and through a proxy server after using the HttpWebRequest object. Under normal circumstances I am able to use the webservice without any problems. But after using an HttpWebRequest object to...
7
2149
by: Amirallia | last post by:
Hi, I consume my webservice with the IE browser and all is ok when I call it with one browser. But when I call it a the same time with two browsers, the IE page tells me that the apllication server is not available!! Any idea ?
0
7341
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
7729
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
0
7677
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
0
5880
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development projectplanning, coding, testing, and deploymentwithout human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
1
5258
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupr who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
4884
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3386
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
1809
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
0
626
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.