ADO parameters queries and stored procedures - and Connections

[Darryl Kerkeslager]

I use the following code to retrieve to recordsets into two local tables,
from an mdb file over a WAN. Works fine, but I'm trying to tweak the speed,
and I have some questions:

1. I tried both an ADOX-created stored procedure and a regular Access
parameter query. After several tests, I came to the conclusion that there
was no statistcal difference between the two. Does this make sense, or
could I do something different to speed it up (the two queries are just
standard three-table joined Select queries with one parameter)?

2. When I wrap the two calls below in a third recordset, opened on the user
table, this speeds things up - in other words, when the recordset 'rs' is
closed and re-opened, the connection to the WAN mdb is lost, and has to be
re-established - which is noticeable over the WAN. Is there any other way
to keep the connection open ... and why does closing the recordset close it?

3. Any other suggestions?
-----------------------------------------------------------
Dim cnxn As ADODB.Connectio n
Dim rs As ADODB.Recordset
Dim rs2 As ADODB.Recordset
Dim prm1 As ADODB.Parameter
Dim cmd1 As ADODB.Command
Set rs = New ADODB.Recordset
Set rs2 = New ADODB.Recordset
Set cnxn = New ADODB.Connectio n
Set cmd1 = New ADODB.Command

cnxn.Provider = "Microsoft.Jet. OLEDB.4.0"
cnxn.Open "Data Source=" & GetServer()

Set prm1 = cmd1.CreatePara meter("PO", adVarChar, adParamInput,
Len(GetSelected PO()))
prm1.Value = GetSelectedPO()
With cmd1
.ActiveConnecti on = cnxn
.CommandText = "HomeInvestigat ionPQuery"
.CommandType = adCmdStoredProc
.Parameters.App end prm1
End With

rs2.Open "HomeInvestigat ionTable", CurrentProject. Connection, adOpenKeyset,
adLockOptimisti c, adCmdTable
With rs
.Open cmd1, , adOpenForwardOn ly, adLockReadOnly, adCmdStoredProc
Do While Not .EOF
rs2.AddNew
For f = 0 To 5
rs2.Fields(f) = .Fields(f)
Next f
rs2.Update
.MoveNext
Loop
.Close
rs2.Close

rs2.Open "HomeCaseloadTa ble", CurrentProject. Connection,
adOpenKeyset, adLockOptimisti c, adCmdTable
cmd1.CommandTex t = "HomeCaseloadPQ uery"
.Open cmd1, , adOpenForwardOn ly, adLockReadOnly, adCmdStoredProc
Do While Not .EOF
rs2.AddNew
For f = 0 To 2
rs2.Fields(f) = .Fields(f)
Next f
rs2.Update
.MoveNext
Loop
.Close
End With
rs2.Close
cnxn.Close
Set cmd1 = Nothing
Set prm1 = Nothing
--
Darryl Kerkeslager

Power corrupts.
Absolute power corrupts absolutely.
Knowledge is power.
See www.adcritic.com/interactive/view.php?id=5927

[lylefair]

Do you really have to use recordsets? Can you just use two Update
Queries?

If recordsets then ...
Quite often when dealing with ADODB recordsets it's more efficient and
faster to open them, disconnect them (Set .ActiveConnecti on = Nothing),
do our work, reconnect them (Set .ActiveConnecti on =
YourConnectionO bject) and do a batch update. TTBOMK the batch update is
asynchronous, so we can go about doing something else while it does its
work.

It's unnecessary to close and release ADO object pointers.

[Steve Jorgensen]

On 25 Jul 2005 19:09:51 -0700, "lylefair" <ly******@yahoo .ca> wrote:

Do you really have to use recordsets? Can you just use two Update
Queries?

If recordsets then ...
Quite often when dealing with ADODB recordsets it's more efficient and
faster to open them, disconnect them (Set .ActiveConnecti on = Nothing),
do our work, reconnect them (Set .ActiveConnecti on =
YourConnection Object) and do a batch update. TTBOMK the batch update is
asynchronous , so we can go about doing something else while it does its
work.

It's unnecessary to close and release ADO object pointers.

Actually, if you open the recordset as static, batch optimistic, there's no
benefit to disconnecting it. It's not going to talk to the database again
until you do the batch update, disconnected or not. The only reason to
disconnect is if you really do want to close the connection to conserver
resources.

[Darryl Kerkeslager]

Lyle Fairfield wrote:

Do you really have to use recordsets? Can you just use two Update
Queries?
Good question. I would be updating the Front End mdb from the Back End mdb,
which I have never done before. How would I do this? Wouldn't the BE have
to know where the FE is?
If recordsets then ...
Quite often when dealing with ADODB recordsets it's more efficient and
faster to open them, disconnect them (Set .ActiveConnecti on = Nothing),
do our work, reconnect them (Set .ActiveConnecti on =
YourConnectionO bject) and do a batch update.
So change this ...

rs2.Open "HomeInvestigat ionTable", CurrentProject. Connection, adOpenKeyset,
adLockOptimisti c, adCmdTable

With rs
.Open cmd1, , adOpenForwardOn ly, adLockReadOnly, adCmdStoredProc

Do While Not .EOF
rs2.AddNew
For f = 0 To 5
rs2.Fields(f) = .Fields(f)
Next f
rs2.Update
.MoveNext
Loop

.... to this? ..

rs2.Open "HomeInvestigat ionTable", CurrentProject. Connection, adOpenKeyset,
adLockBatchOpti mistic, adCmdTable

With rs
.Open cmd1, , adOpenForwardOn ly, adLockReadOnly, adCmdStoredProc

Set .ActiveConnecti on = Nothing
Do While Not .EOF
rs2.AddNew
For f = 0 To 5
rs2.Fields(f) = .Fields(f)
Next f
.MoveNext
Loop
Set .ActiveConnecti on = cnxn
rs2.UpdateBatch

It's unnecessary to close and release ADO object pointers.

I've stopped doing it for recordsets, but I wasn't sure the same applied for
command and parameter objects.


Darryl Kerkeslager

Power corrupts.
Absolute power corrupts absolutely.
Knowledge is power.
See www.adcritic.com/interactive/view.php?id=5927

[Darryl Kerkeslager]

Steve Jorgensen wrote:

The only reason to
disconnect is if you really do want to close the connection to conserver
resources.

I'm doing this over a WAN (The Powers said I couldn't use their SQL Server).
All I want to accomplish is make it faster, any way I can. The WAN is good
and fast, and the PCs and server involved are first rate. I just want to:

1. Not open and close the connection if preventable - this alone takes
anywhere from 1-2 seconds, so if I open and close a recordset 4 times
instead of 1 in one Save action, that's an extra 3-6 seconds I add.

2. Not send any more data TO the server than I need to to GET data.
--
Darryl Kerkeslager

Power corrupts.
Absolute power corrupts absolutely.
Knowledge is power.
See www.adcritic.com/interactive/view.php?id=5927

[Steve Jorgensen]

On Mon, 25 Jul 2005 22:58:02 -0400, "Darryl Kerkeslager"
<ke*********@co mcast.net> wrote:

Steve Jorgensen wrote:

The only reason to
disconnect is if you really do want to close the connection to conserve
resources.

I'm doing this over a WAN (The Powers said I couldn't use their SQL Server).
All I want to accomplish is make it faster, any way I can. The WAN is good
and fast, and the PCs and server involved are first rate. I just want to:

1. Not open and close the connection if preventable - this alone takes
anywhere from 1-2 seconds, so if I open and close a recordset 4 times
instead of 1 in one Save action, that's an extra 3-6 seconds I add.

2. Not send any more data TO the server than I need to to GET data.

I didn't realize you were using an Access MDB over a WAN. That's generally
both slow and unreliable. Can you get set up to remote control your PC at
work via Terminal Services? Alternatively, if there are only a handfull of
simultaneous users, could you use MSDE on your computer, and access that over
the WAN?

If none of those is an option, perhaps, you should look into replication.
David Fenton may have some advice on this point.

[lylefair]

A few more things

Steve points out that it's unnecessary to close the connection, if one
changes the cursor type to adOpenStatic.

You should still be able to use
Dim FieldArray As Variant
FieldArray = Array(0, 1, 2, 3, 4, 5)
rs2.Open "HomeInvestigat ionTable", CurrentProject. Connection,
adOpenStatic,
adLockBatchOpti mistic, adCmdTable
With rs
.Open cmd1, , adOpenForwardOn ly, adLockReadOnly, adCmdStoredProc
Do While Not .EOF
rs2.AddNew FieldArray, Array(Fields(0) , Fields(1), Fields(2),
Fields(3), Fields(4), Fields(5))
.MoveNext
Loop
End With
rs2.UpdateBatch
(with lines inserted in the correct areas)
To avoid iterating f

CurrentProject. Connection can be 100s of times slower than a simple ADO
connection. You might try creating an independent connection (naming
the provider, data source, etc.)

Finally, running Access code can sometimes be very slow. In your
routine there is nothing that I can see that requires Access. I'm going
to guess that running this as VB Script, or Compiled VB, or JScript in
the Wsh environment might be faster.

Finally, finally, if the connection can find these tables in the front
end and backend, then why not SQL? In SQL we can reference a table or
query in an mdb with the path to the table and the dot and the name of
the object, perhaps all enclosed with [...].
eg
With DBEngine(0)(0)
.Execute "DELETE * FROM [C:\Documents and Settings\Lyle
Fairfield\My Documents\Acces s\terrawaretabl es.mdb].[transactions]"
.Execute "INSERT INTO " _
& "[C:\Documents and Settings\Lyle Fairfield\My
Documents\Acces s\terrawaretabl es2.mdb].[transactions] " _
& "SELECT * FROM [C:\Documents and Settings\Lyle Fairfield\My
Documents\Acces s\terrawaretabl es.mdb].[transactions] "
End With

[David W. Fenton]

Steve Jorgensen <no****@nospam. nospam> wrote in
news:to******** *************** *********@4ax.c om:

I didn't realize you were using an Access MDB over a WAN. That's
generally both slow and unreliable. Can you get set up to remote
control your PC at work via Terminal Services? Alternatively, if
there are only a handfull of simultaneous users, could you use
MSDE on your computer, and access that over the WAN?

If none of those is an option, perhaps, you should look into
replication. David Fenton may have some advice on this point.

Well, there's WANs and then there's WANs. One client of mine, a
school district in New Jersey, has a WAN connecting all the schools,
but it runs at Gigabit speeds, so it's faster than most LANs, and
thus perfectly appropriate for using Access MDBs. And, indeed, the
first app I redesigned for them is indeed being run with the data
MDB on the high school server, and the end users both at the high
school and out in the grade schools and middle schools, on the other
side of the WAN.

So, the real problem with WANs is when they are running at under
10BaseT speed.

Oh, also, wireless is unacceptable, too.

Replication is not for the faint of heart, and I would say not at
all preferable to WTS except where WTS can't be done.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[Darryl Kerkeslager]

> I didn't realize you were using an Access MDB over a WAN. That's

generally
both slow and unreliable.
Slow, yes, unreliable, no.
Can you get set up to remote control your PC at
work via Terminal Services? Alternatively, if there are only a handful of
simultaneous users, could you use MSDE on your computer, and access that
over
the WAN?
80 users - ?? simultaneous (perhaps 20) - so MSDE is out. But MSDE,
Terminal Services, even scripting are all out for the same reason - I'm not
IT staff, and nothing will be put in without the approval of IT staff -
which will not come. I lost my automatic backups when IT nixed scripts run
from anywhere but the servers, and I was forced to put this on the WAN when
they eliminated all file sharing. As of now, I can't even share out my
Outlook calendar.

With either IBM or Northrop Grumman getting the IT outsourcing contract
soon, there will be no positive changes for me. I'm resigned to this
solution for the next 18 months until the enterprise .Net application is
installed, making my app obsolete.
If none of those is an option, perhaps, you should look into replication.
David Fenton may have some advice on this point.

Well, my subject line contains the text "ADO", so David has doubtless
blocked the thread ;), but I will read the chapter on replication in the ADH
and ask as needed.
--
Darryl Kerkeslager

Power corrupts.
Absolute power corrupts absolutely.
Knowledge is power.

See www.adcritic.com/interactive/view.php?id=5927
Steve Jorgensen wrote:

[Darryl Kerkeslager]

Lyle Fairfield wrote:

A few more things

Steve points out that it's unnecessary to close the connection, if one
changes the cursor type to adOpenStatic.

You should still be able to use
Dim FieldArray As Variant
FieldArray = Array(0, 1, 2, 3, 4, 5)
rs2.Open "HomeInvestigat ionTable", CurrentProject. Connection,
adOpenStatic,
adLockBatchOpti mistic, adCmdTable
With rs
.Open cmd1, , adOpenForwardOn ly, adLockReadOnly, adCmdStoredProc
Do While Not .EOF
rs2.AddNew FieldArray, Array(Fields(0) , Fields(1), Fields(2),
Fields(3), Fields(4), Fields(5))
.MoveNext
Loop
End With
rs2.UpdateBatch
(with lines inserted in the correct areas)
To avoid iterating f
Thank you - I couldn't figure out a method to avoid that, even though I knew
it would be a drag.

CurrentProject. Connection can be 100s of times slower than a simple ADO
connection. You might try creating an independent connection (naming
the provider, data source, etc.)
I only use the CurrentProject. Connection for the local mdb, and specify the
path for the WAN mdb. But I'll try specifying both, as I really need to
wring out every drop of performance.

Finally, running Access code can sometimes be very slow. In your
routine there is nothing that I can see that requires Access. I'm going
to guess that running this as VB Script, or Compiled VB, or JScript in
the Wsh environment might be faster.
Unfortunately scripting has been disabled from local PCs along with other
security features.
Finally, finally, if the connection can find these tables in the front
end and backend, then why not SQL? In SQL we can reference a table or
query in an mdb with the path to the table and the dot and the name of
the object, perhaps all enclosed with [...]. .Execute "DELETE * FROM [C:\Documents and Settings\Lyle
Fairfield\My Documents\Acces s\terrawaretabl es.mdb].[transactions]"
.Execute "INSERT INTO " _
& "[C:\Documents and Settings\Lyle Fairfield\My
Documents\Acces s\terrawaretabl es2.mdb].[transactions] " _
& "SELECT * FROM [C:\Documents and Settings\Lyle Fairfield\My
Documents\Acces s\terrawaretabl es.mdb].[transactions] "
End With

Ahhh! I really didn't know that you could do that (probably should have,
but didn't). Yes, I would love it if I could stick with a(n) SQL solution.

Thank you both, Steve and Lyle.

Now to test out my newfound nowledge ...
--
Darryl Kerkeslager

Power corrupts.
Absolute power corrupts absolutely.
Knowledge is power.

See www.adcritic.com/interactive/view.php?id=5927