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]

"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

[Armen Stein]

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

[Alex Dybenko]

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

[Armen Stein]

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