|
|
|
date: Sat, 31 May 2008 13:55:06 -0700 (PDT),
group: microsoft.public.access.odbcclientsvr
back
Re: Connection pooling issue with pass through queries
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" wrote:
> Why are you worried about it? Have you had a single problem that you would
> have noticed had you NOT used SQL trace?
date: Sun, 1 Jun 2008 03:26:14 -0700 (PDT)
author: lyle fairfield
Re: Connection pooling issue with pass through queries
"Bill E." wrote in news:0ebc7349-14be-49e2-b40f-
f7176fd69c84@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
date: Sun, 01 Jun 2008 14:52:17 GMT
author: lyle fairfield
Re: Connection pooling issue with pass through queries
On Sun, 1 Jun 2008 06:52:51 -0700 (PDT), "Bill E."
wrote:
>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
date: Thu, 12 Jun 2008 16:45:57 -0700
author: Armen Stein
Re: Connection pooling issue with pass through queries
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" wrote in message
news:loc3545m1du3041ji9getlo25m9iv3ou50@4ax.com...
> On Sun, 1 Jun 2008 06:52:51 -0700 (PDT), "Bill E."
> wrote:
>
>>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
>
date: Fri, 13 Jun 2008 09:49:22 +0400
author: Alex Dybenko
|
|
