Re: Connection pooling issue with pass through queries

[Bill E.]

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

[lyle fairfield]

Re: Connection pooling issue with pass through queries

"Bill E." <billmiami2@net scape.netwrote in news:0ebc7349-14be-49e2-b40f-
f7176fd69c84@x1 g2000prh.google groups.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 mDefaultConnect ion As ADODB.Connectio n

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

Public Function DefaultConnecti on() As ADODB.Connectio n
If mDefaultConnect ion Is Nothing Then _
OpenConnection mDefaultConnect ion
Set DefaultConnecti on = mDefaultConnect ion
End Function

Public Function NewConnection() As ADODB.Connectio n
OpenConnection NewConnection
End Function

[Armen Stein]

Re: Connection pooling issue with pass through queries

On Sun, 1 Jun 2008 06:52:51 -0700 (PDT), "Bill E."
<billmiami2@net scape.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

https://www.JStreetTech.com

[Alex Dybenko]

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" <ArmenStein@R3m 00v3Th1s.gmail. comwrote in message
news:loc3545m1d u3041ji9getlo25 m9iv3ou50@4ax.c om...

On Sun, 1 Jun 2008 06:52:51 -0700 (PDT), "Bill E."
<billmiami2@net scape.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

https://www.JStreetTech.com

>

[Armen Stein]

Re: Connection pooling issue with pass through queries

On Fri, 13 Jun 2008 09:49:22 +0400, "Alex Dybenko"
<alexdyb@PLEASE .cemi.NO.rssi.S PAM.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

https://www.JStreetTech.com