473,324 Members | 2,196 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

Connection pooling issue with pass through queries

I'm creating an MS Access application that connects to a SQL Server
2005 database using pass-through queries and ADO to call stored
procedures. I ran a SQL trace and started clicking through various
forms to look at how things were being handled on the database. I
noticed an alarming number of connections were opened as if there was
absolutely no connection pooling. After several clicks I had 20 new
connections. It appeared that a new connection was being opened by
each pass-through query even though the connection strings are exactly
the same in each (I set them globally in code at application
startup). I also noticed that if I set my connection string to use a
SQL Server login (username/password) rather than a trusted connection,
the problem went away and connections were properly reused. Note that
calls to the database made via ADO did not exhibit this problem.
Also, I'm not using an ODBC DSN.

Is there a reason why I'm seeing this behavior? Is there a setting
somewhere that I've neglected to change?

Bill E.
Hollywood, FL
Jun 27 '08 #1
12 3120
Bill E. wrote:
I'm creating an MS Access application that connects to a SQL Server
2005 database using pass-through queries and ADO to call stored
procedures. I ran a SQL trace and started clicking through various
forms to look at how things were being handled on the database. I
noticed an alarming number of connections were opened as if there was
absolutely no connection pooling. After several clicks I had 20 new
connections. It appeared that a new connection was being opened by
each pass-through query even though the connection strings are exactly
the same in each (I set them globally in code at application
startup). I also noticed that if I set my connection string to use a
SQL Server login (username/password) rather than a trusted connection,
the problem went away and connections were properly reused. Note that
calls to the database made via ADO did not exhibit this problem.
Also, I'm not using an ODBC DSN.

Is there a reason why I'm seeing this behavior? Is there a setting
somewhere that I've neglected to change?

Bill E.
Hollywood, FL
Why are you worried about it? Have you had a single problem that you would
have noticed had you NOT used SQL trace?

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com
Jun 27 '08 #2
Rick,

That's almost like asking why we should bother with database
connection pooling. It's a question of efficient use of resources and
avoiding problems that may occur in production. I was one single user
clicking through one single form and opened over 20 connections.
That's just plain sloppy and unacceptable. Who knows how many
connections would be opened with many users working on a complete
application in extended sessions. It wasn't long ago that I had to
fix someone else's web application because it opened hundreds of
connections and stopped all users in their tracks when the number of
allowable connections was exceeded.

The problem is eliminated when I set the connection string to use a
SQL Server login instead of a trusted connection. Until I understand
what's going on here, I'm going to stick with that. Any insight into
what may be causing this would be welcomed.

Thanks,

Bill E.
Hollywood, FL
Jun 27 '08 #3
Bill E. wrote:
Rick,

That's almost like asking why we should bother with database
connection pooling. It's a question of efficient use of resources and
avoiding problems that may occur in production. I was one single user
clicking through one single form and opened over 20 connections.
That's just plain sloppy and unacceptable. Who knows how many
connections would be opened with many users working on a complete
application in extended sessions. It wasn't long ago that I had to
fix someone else's web application because it opened hundreds of
connections and stopped all users in their tracks when the number of
allowable connections was exceeded.

The problem is eliminated when I set the connection string to use a
SQL Server login instead of a trusted connection. Until I understand
what's going on here, I'm going to stick with that. Any insight into
what may be causing this would be welcomed.
I can only tell you that I have been using ODBC with trusted connections
with around 200 users for over a decade and have never once had an issue
with it.

Connections ARE re-used.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com
Jun 27 '08 #4
In ADPs multiple connections cause problems when application roles are
used as per this quote from http://support.microsoft.com/kb/308312.

"Unlike with other database objects, Access does not always use the
same connection to retrieve the data source of a subform. Access
frequently (but not always) creates a new connection to SQL Server
just to handle the subform recordset, or to retrieve the linking field
data that connects the subform to the main form. Because this new
connection does not have the application role applied, a permissions
error may be generated if you do not have explicit permissions to the
database object. Unfortunately, this means that there is no reliable
way to use bound subforms when application roles are applied. The only
effective workaround is to have completely unbound subforms, with the
data manipulation handled programmatically. This is the most serious
limitation when using application roles in Access."

I believe that "Access frequently (but not always) creates a new
connection to SQL Server just to handle the subform recordset" is not
the whole story. In my experience Access frequently (but not always)
creates a new connection to SQL Server to handle any implicit Select,
Insert, Update or Delete procedure, including record-based combo-boxes
and list-boxes. It would be helpful if "not always" could be
documented as to when. I have found that "not always" is
unpredictable, varying from day-to-day and server to server.

I have never used application roles with ODBC and can't comment on
that combination.

A different kind of problem caused by multiple connections is the
effect they may have on the application's credibility. When a DBA/SA
sees one hundred five connections from the application to his server
when she knows only seventeen users have the application open he may
be alarmed. And she may communicate that alarm and concern for
efficiency, merited or not, to the people who are making decisions
about buying Access applications.

On May 31, 10:00*pm, "Rick Brandt" <rickbran...@hotmail.comwrote:
Why are you worried about it? *Have you had a single problem that you would
have noticed had you NOT used SQL trace?
Jun 27 '08 #5
Lyle,

Thanks for the information. I'm not using an ADP and I'm not using
application roles, although I was considering it. I agree that it
would be helpful if it were documented somewhere how Access is
handling connections to SQL Server.

I found something else that was strange. It appeared that pass-
through queries were being called twice for some reason. That is to
say that my trace was showing two calls in a row to the same stored
procedure every time. However, when I ran a pass-through query
directly by clicking on the query and running it, the trace showed
only one call to the proc as expected. I thought perhaps the problem
was due to poor form design on my part. To test this, I created a
form with nothing but a single combobox and bound the combobox to the
pass through query. When I opened the form, I looked at my trace and
voila--two calls to the same proc on SQL Server! Do you have any idea
why this would be happening?

Bill
Jun 27 '08 #6
"Bill E." <bi********@netscape.netwrote in news:0ebc7349-14be-49e2-b40f-
f7**********@x1g2000prh.googlegroups.com:
Lyle,

Thanks for the information. I'm not using an ADP and I'm not using
application roles, although I was considering it. I agree that it
would be helpful if it were documented somewhere how Access is
handling connections to SQL Server.

I found something else that was strange. It appeared that pass-
through queries were being called twice for some reason. That is to
say that my trace was showing two calls in a row to the same stored
procedure every time. However, when I ran a pass-through query
directly by clicking on the query and running it, the trace showed
only one call to the proc as expected. I thought perhaps the problem
was due to poor form design on my part. To test this, I created a
form with nothing but a single combobox and bound the combobox to the
pass through query. When I opened the form, I looked at my trace and
voila--two calls to the same proc on SQL Server! Do you have any idea
why this would be happening?

Bill
Not really! All I can think is that ODBC can translate a JET/VBA
expression, say, TRIM(x), to its T-SQL equivalent RTRIM(LTRIM(x)) [this is
not a good example] and perhaps, to do so it must visit the SQL Server
twice, once to assess and plan, and a second time to do the work. But I
think it doesn't attempt to do that with Pass Through Queries, it just
[passes them through].

[OT] I often create and maintain an independent ADO connection (as below)
when dealing with SQL Server and use it rather than
CurrentProject.Connection where ever possible. Depending on the version of
Access, one can use an ADO recordset for bound forms, and bypass the normal
methods of binding.

Dim mDefaultConnection As ADODB.Connection

Private Sub OpenConnection(ByRef Connection As ADODB.Connection)
Set Connection = New ADODB.Connection
With Connection
.CursorLocation = adUseClient
.Provider = "sqloledb.1"
With .Properties
.Item("Data Source") = "Place_Holder"
.Item("Initial Catalog") = "Place_Holder"
-----
.Item("PassWord") = "Place_Holder"
.Item("User ID") = "Place_Holder"
-----
OR
-----
..Item("Integrated Security") = "SSPI"
-----
End With
.Open
End With
End Sub

Public Function DefaultConnection() As ADODB.Connection
If mDefaultConnection Is Nothing Then _
OpenConnection mDefaultConnection
Set DefaultConnection = mDefaultConnection
End Function

Public Function NewConnection() As ADODB.Connection
OpenConnection NewConnection
End Function

Jun 27 '08 #7
Here are some resources you can try -- It might help to set up ODBC
Trace to troubleshoot from that side. The optimizing paper listed
first explains how Jet works with SQL Server. Make sure to turn off
trace when you're done - it can really chew up resources. HTH.

Optimizing Microsoft Office Access Applications Linked to SQL Server
http://msdn.microsoft.com/en-us/library/bb188204.aspx

Data Access Tracing
http://msdn.microsoft.com/en-us/library/aa964124.aspx

How To Generate an ODBC Trace
http://support.microsoft.com/kb/274551

--Mary

On Sat, 31 May 2008 13:55:06 -0700 (PDT), "Bill E."
<bi********@netscape.netwrote:
>I'm creating an MS Access application that connects to a SQL Server
2005 database using pass-through queries and ADO to call stored
procedures. I ran a SQL trace and started clicking through various
forms to look at how things were being handled on the database. I
noticed an alarming number of connections were opened as if there was
absolutely no connection pooling. After several clicks I had 20 new
connections. It appeared that a new connection was being opened by
each pass-through query even though the connection strings are exactly
the same in each (I set them globally in code at application
startup). I also noticed that if I set my connection string to use a
SQL Server login (username/password) rather than a trusted connection,
the problem went away and connections were properly reused. Note that
calls to the database made via ADO did not exhibit this problem.
Also, I'm not using an ODBC DSN.

Is there a reason why I'm seeing this behavior? Is there a setting
somewhere that I've neglected to change?

Bill E.
Hollywood, FL
Jun 27 '08 #8
Bill E. (bi********@netscape.net) writes:
That's almost like asking why we should bother with database
connection pooling. It's a question of efficient use of resources and
avoiding problems that may occur in production. I was one single user
clicking through one single form and opened over 20 connections.
That's just plain sloppy and unacceptable. Who knows how many
connections would be opened with many users working on a complete
application in extended sessions. It wasn't long ago that I had to
fix someone else's web application because it opened hundreds of
connections and stopped all users in their tracks when the number of
allowable connections was exceeded.

The problem is eliminated when I set the connection string to use a
SQL Server login instead of a trusted connection. Until I understand
what's going on here, I'm going to stick with that. Any insight into
what may be causing this would be welcomed.
I have no idea of what might be going on. I can understand that
connections are not being resued - the typical reason is that they
are not closed properly. But why it would work with SQL authentication
and not Windows authentication, I don't know.

My guess it that when you changed the authentication, you changed something
more thar you did not tell us about.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Jun 27 '08 #9
Thanks for your comments Lyle, Mary and Erland.

Lyle -- I'm not sure how much Jet is involved in this because I have
no queries that need to be translated. Everything is a pass through
to SQL Server. There are no linked tables in my application. I've
seen that technique you mentioned for the ADO connection, but I'm not
having any problems with ADO. It's the pass-through queries that are
the problem.

Mary--Thanks for the resources. The first article deals primarily
with querying SQL Server through linked tables so does not apply to my
case. I'm interested in seeing what the ODBC trace looks like and I'm
hoping that it will not simply be an academic exercise but will lead
me to some kind of corrective action.

Erland -- I changed the connection string only, nothing else.

Bill
Jun 27 '08 #10
On Sun, 1 Jun 2008 06:52:51 -0700 (PDT), "Bill E."
<bi********@netscape.netwrote:
>When I opened the form, I looked at my trace and
voila--two calls to the same proc on SQL Server! Do you have any idea
why this would be happening?
For forms and reports bound to passthrough queries, Access runs the
query *before* the Open event, then again during it's normal time. I
think this is because Access must get the column definitions "on the
fly" and must run the query to do so.

To mitigate this performance hit, we often add change the Where clause
of the passthrough query to WHERE 1=0 when the form or report
*closes*. This leaves it lurking until next time the object is used,
and results in a very fast query execution. Then in our Open event,
we change the Where clause to something more useful, often based on
user input. Using this technique we've seen performance improve from
several seconds down to instantaneous.

I've seen a variation on this technique where the lurking Where clause
is something like WHERE CustomerID = -1, which will result in zero
records. This isn't as quick, because SQL must still perform the
search. Using WHERE 1=0 is determined mathematically to be false, so
SQL immediately returns an empty recordset.

Hope this helps,

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

Jun 27 '08 #11
I also you another technique - I clear form's recordsource property and set
it in form's Open event
Sometimes even WHERE 1=0 takes some time run

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com
"Armen Stein" <Ar********@R3m00v3Th1s.gmail.comwrote in message
news:lo********************************@4ax.com...
On Sun, 1 Jun 2008 06:52:51 -0700 (PDT), "Bill E."
<bi********@netscape.netwrote:
>>When I opened the form, I looked at my trace and
voila--two calls to the same proc on SQL Server! Do you have any idea
why this would be happening?

For forms and reports bound to passthrough queries, Access runs the
query *before* the Open event, then again during it's normal time. I
think this is because Access must get the column definitions "on the
fly" and must run the query to do so.

To mitigate this performance hit, we often add change the Where clause
of the passthrough query to WHERE 1=0 when the form or report
*closes*. This leaves it lurking until next time the object is used,
and results in a very fast query execution. Then in our Open event,
we change the Where clause to something more useful, often based on
user input. Using this technique we've seen performance improve from
several seconds down to instantaneous.

I've seen a variation on this technique where the lurking Where clause
is something like WHERE CustomerID = -1, which will result in zero
records. This isn't as quick, because SQL must still perform the
search. Using WHERE 1=0 is determined mathematically to be false, so
SQL immediately returns an empty recordset.

Hope this helps,

Armen Stein
Microsoft Access MVP
www.JStreetTech.com
Jun 27 '08 #12
On Fri, 13 Jun 2008 09:49:22 +0400, "Alex Dybenko"
<al*****@PLEASE.cemi.NO.rssi.SPAM.ruwrote:
>I also you another technique - I clear form's recordsource property and set
it in form's Open event
Sometimes even WHERE 1=0 takes some time run
Hi Alex,

Yes, that will work too. But it's a hassle for the developer to have
a missing recordsource when it's time to make changes to the form.

WHERE 1=0 takes *some* time, but we've seen it to be almost instant.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

Jun 27 '08 #13

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

Similar topics

3
by: Harry | last post by:
Using Oracle 8i enterprise on win 2000 (sp3) Installed the standard configuration & whenever I make a connection it takes about 10 secs. It's running on a P1900 with 1gb Ram so no reason there...
0
by: JWM | last post by:
I am trying to implement Oracle connection pooling for the following code, which was written by someone else. Here is my main question -- this java file creates code that is executed every hour,...
1
by: Lenny Shprekher | last post by:
Hi, I am getting issues that Oracle collecting opened sessions (connections) from my webservice using regular System.Data.OleDb.OleDbConnection object. I am guessing that this is connection...
7
by: Mrinal Kamboj | last post by:
Hi , I am using OracleConnection object from Oracle ODP.net provider and following is the behaviour which i am finding bit strange : To start with my argument is based on followings facts : ...
16
by: crbd98 | last post by:
Hello All, Some time ago, I implemented a data access layer that included a simple connectin pool. At the time, I did it all by myself: I created N connections, each connection associated with...
20
by: fniles | last post by:
I am using VB.NET 2003, SQL 2000, and SqlDataReader. As I read data from tblA, I want to populate tblB. I use SQLDataReader for both tables. I do not use thread. When I ExecuteReader on tblB, I...
20
by: fniles | last post by:
I am using VS2003 and connecting to MS Access database. When using a connection pooling (every time I open the OLEDBCONNECTION I use the exact matching connection string), 1. how can I know how...
3
by: Hahn, Thomas | last post by:
Hallo, I have an ASP.NET application with masterpages, skins and diffrent themes. The application works fine, but the performance is not realy good. If I load an ASPX file, which has no database...
9
by: Bill E. | last post by:
I'm creating an MS Access application that connects to a SQL Server 2005 database using pass-through queries and ADO to call stored procedures. I ran a SQL trace and started clicking through...
4
by: Bill E. | last post by:
Lyle, Thanks for the information. I'm not using an ADP and I'm not using application roles, although I was considering it. I agree that it would be helpful if it were documented somewhere how...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 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 former...

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.