Multiple recordsets continued

[Randy Harris]

I was inspired by the recent discussion of returning multiple recordsets to
ADO from a stored procedure. (Amazed is probably more accurate). I asked
about how to accomplish same with Oracle and got a nudge in the right
direction from Mr. Kreft. I promised to provide details once working, so
here it is.

The code is shown below. My next step is to build this technique into my
application. I'm hoping for substantial performance gain.

This is totally new to me, I'd welcome any constructive criticism. By the
same token, I got it working, so I'll be happy to attempt to answer any
questions anyone might have.

Randy Harris
In Oracle I created this package and stored procedure:

/* A PL/SQL book said that the cursor object
must be declard in a package */
CREATE OR REPLACE PACKAGE OraOLEDB AS
TYPE m_refcur IS REF CURSOR;
END OraOLEDB;
/

/* =================================== */
/* This procedure will return two REF cursors */
/* I added a couple of IN parameters to prove I could */
CREATE OR REPLACE PROCEDURE getrecs(
firstweek IN NUMBER,
lastweek IN NUMBER,
firstrs OUT OraOLEDB.m_refcur,
secondrs OUT OraOLEDB.m_refcur)
IS

BEGIN
/* Define the first cursor that will become a recordset */
OPEN firstrs FOR
SELECT weekid, weekstart
FROM weeks_tbl
WHERE weekid BETWEEN firstweek AND lastweek ;

/* And now a second */
OPEN secondrs FOR
SELECT tablename, updateseq
FROM concurrence ;

END getrecs;
/
Here's the VBA procedure that calls the SP:

Private Sub MultiRecs()

Dim conn As New ADODB.Connection
Dim rst1 As New ADODB.Recordset
Dim rst2 As New ADODB.Recordset

Dim cmd As New ADODB.Command

With conn
.Provider = "OraOLEDB.Oracle"
.Open "data source=" & OracleDB & ";", dbUser, dbPass
End With

cmd.ActiveConnection = conn
cmd.Properties("PLSQLRSet") = True
cmd.CommandType = adCmdText
cmd.CommandText = "{Call getrecs (420, 430) }"

' this calls the procedure and retrieves the cursors
' the first recordset is created
Set rst1 = cmd.Execute

While Not rst1.EOF
Debug.Print rst1(0), rst1(1)
rst1.MoveNext
Wend

' Now call the NextRecordset Method
Set rst2 = rst1.NextRecordset

While Not rst2.EOF
Debug.Print rst2(0), rst2(1)
rst2.MoveNext
Wend

rst1.Close
rst2.Close
End Sub

[Lyle Fairfield]

Randy Harris wrote:

The code is shown below. My next step is to build this technique into my
application. I'm hoping for substantial performance gain.

Why would getting one recordset composed of two recordsets result in a
substantial performance gain as compared with just getting two
recordsets?

1. It's true that you will send one command instead of two, but will
you not get back the same data in the same form (form the noun not form
the Access Object) and is the transport of this data not likely to be
the chief time-consuming activity of the whole procedure;

2. In MS-SQL we can create and use the cursor in the stored procedure
and get only the second recordset (assuming the first is used only as
fodder for the second); can this be done in Oracle;

3. How often will we write Procedures that will return multiple
recordsets, which might be rewritten as Joins to return the required
data;

4. How efficient is it to hold two recordsets in memory if we are using
them consecutively, that is, if we use the first, then use the second
and never return to the first;

4. In cases where field structure is the same will Unions or multiple
recordsets be more efficient?

These are idle thoughts of my own that I am pondering; I'm currently at
this stage:

1. I don't have anything running so slowly or inefficiently that I'm
going to rewrite it using multiple recordsets;
2. I'll keep multiples in mind when I program ADO recordsets in the
future;
3. I already use Unions when I have two recordsets with identical
fields that I want to use, using an absolute field defined something
like "A" as WhichGroup for the first and "B" as WhichGroup for the
second; this troubles me and maybe I'll replace it with multiples
recordsets;
4. With SQL 2000 and 2005 VBA is almost redundant; there is little I
can do in a VBA module that I can't do in a SPROC; but do I really
wanna have the server do all the work sharing it's CPU time with a
bunch of clients, rather than having the client machine do all the
work, sharing its CPU time with nothing?

As an aside T-SQL can even act as an adjunct to VBA. Don't want the
hassle of API functions to get Zulu time? Have access to an MS-SQL
sever? Use its functions then:
CurrentProject.Connection.Execute("SELECT GETUTCDATE()").Collect(0)
2005-12-10 03:24:26

If one extends this to UDFs then one could do everything in the server
and UDFs would be the repository for ones code.
And of course, this doesn't have to have anything at all to do with
your data or your type of database Your data could be an mdb or oracle
or whatever and you could access functions from some central business
rules/ function ms-sql code repository on the other side of the earth,
or on your local hard drive. That is we could all share the same
functions and have access to instant updates and additions.

I'm digressing, sorry.

Lyle

[Randy Harris]

"Lyle Fairfield" <ly***********@aim.com> wrote in message
news:11**********************@g43g2000cwa.googlegr oups.com...

Randy Harris wrote:

The code is shown below. My next step is to build this technique into my
application. I'm hoping for substantial performance gain.

Why would getting one recordset composed of two recordsets result in a
substantial performance gain as compared with just getting two
recordsets?

1. It's true that you will send one command instead of two, but will
you not get back the same data in the same form (form the noun not form
the Access Object) and is the transport of this data not likely to be
the chief time-consuming activity of the whole procedure;

2. In MS-SQL we can create and use the cursor in the stored procedure
and get only the second recordset (assuming the first is used only as
fodder for the second); can this be done in Oracle;

3. How often will we write Procedures that will return multiple
recordsets, which might be rewritten as Joins to return the required
data;

4. How efficient is it to hold two recordsets in memory if we are using
them consecutively, that is, if we use the first, then use the second
and never return to the first;

4. In cases where field structure is the same will Unions or multiple
recordsets be more efficient?

These are idle thoughts of my own that I am pondering; I'm currently at
this stage:

1. I don't have anything running so slowly or inefficiently that I'm
going to rewrite it using multiple recordsets;
2. I'll keep multiples in mind when I program ADO recordsets in the
future;
3. I already use Unions when I have two recordsets with identical
fields that I want to use, using an absolute field defined something
like "A" as WhichGroup for the first and "B" as WhichGroup for the
second; this troubles me and maybe I'll replace it with multiples
recordsets;
4. With SQL 2000 and 2005 VBA is almost redundant; there is little I
can do in a VBA module that I can't do in a SPROC; but do I really
wanna have the server do all the work sharing it's CPU time with a
bunch of clients, rather than having the client machine do all the
work, sharing its CPU time with nothing?

As an aside T-SQL can even act as an adjunct to VBA. Don't want the
hassle of API functions to get Zulu time? Have access to an MS-SQL
sever? Use its functions then:
CurrentProject.Connection.Execute("SELECT GETUTCDATE()").Collect(0)
2005-12-10 03:24:26

If one extends this to UDFs then one could do everything in the server
and UDFs would be the repository for ones code.
And of course, this doesn't have to have anything at all to do with
your data or your type of database Your data could be an mdb or oracle
or whatever and you could access functions from some central business
rules/ function ms-sql code repository on the other side of the earth,
or on your local hard drive. That is we could all share the same
functions and have access to instant updates and additions.

I'm digressing, sorry.

Lyle

Good questions, and food for thought. Perhaps my judgment might be clouded
by my enthusiasm for the novelty of this approach.

Regarding question #1: The particular areas where I'm hoping this might be
beneficial involve consecutively passing multiple SQL statements to Oracle
then retrieving or updating recordset data, then closing. In several places
there are 4 to 6 operations. My thinking is that there is "handshake" time
involved in each of these operations. Perhaps the "stack up" of that time
can be reduced by use of the single SP. The quantity of data passed should
be unchanged from the technique used currently.

#3: I think there are some places where properly defined joins could reduce
the number of retrieval recordsets.

#5: The tables involved have dissimilar structure, but unions would be more
efficient if usable.

Perhaps wishful thinking. I don't know. I can think of only one way to find
out for certain.

Randy

[Terry Kreft]

Lyle,
Some answers.

1) But there is more to it than ths, when you send a command to a server
database (SQL anyway) there is time spent compiling that command and doing
things like resolving execution plans. Sending one command to retrieve
multiple recordsets will impact on these and should show a performance
increase in certain cases.

Of course (as with anything db) this is not an absolute and would need to be
tested for a particular situation to see if a performance increase occurs.

2) outside my area of expertise so I'll leave this to Randy.

3) I would use this technique for situations where I wanted either disparate
recordsets or ones where I need to navigate through the recordsets
independently. In situations where previously I would have used joins I
tend to use shaped recordsets as these can show a performance improvement
over standard joined recordsets.

4) This is one of theose situation where you would have to try it and see.

BTW, I know that Randy's example shows him consuming the recordsets in
sequence you don't have to do this though. You can do this

Function xxxx()
Dim loCon As ADODB.Connection
Dim rs1 As ADODB.Recordset
Dim rs2 As ADODB.Recordset
Dim rs3 As ADODB.Recordset

Set rs1 = New ADODB.Recordset

Const CONN_STRING = "File Name=c:\a.udl"

Set loCon = New ADODB.Connection
With loCon
.CursorLocation = adUseClient
.ConnectionString = CONN_STRING
.Open
Set rs1 = .Execute("SELECT * FROM scheme.plsuppm SELECT * FROM
scheme.slcustm")
End With

' NOTE .clone
Set rs2 = rs1.Clone
' Now .NextRecordset
Set rs3 = rs1.NextRecordset

' At this point
' rs2 contains the first recordset
' rs3 contains the second recordset
End Function

--
Terry Kreft

"Lyle Fairfield" <ly***********@aim.com> wrote in message
news:11**********************@g43g2000cwa.googlegr oups.com...

Randy Harris wrote:

The code is shown below. My next step is to build this technique into my
application. I'm hoping for substantial performance gain.

Why would getting one recordset composed of two recordsets result in a
substantial performance gain as compared with just getting two
recordsets?

1. It's true that you will send one command instead of two, but will
you not get back the same data in the same form (form the noun not form
the Access Object) and is the transport of this data not likely to be
the chief time-consuming activity of the whole procedure;

2. In MS-SQL we can create and use the cursor in the stored procedure
and get only the second recordset (assuming the first is used only as
fodder for the second); can this be done in Oracle;

3. How often will we write Procedures that will return multiple
recordsets, which might be rewritten as Joins to return the required
data;

4. How efficient is it to hold two recordsets in memory if we are using
them consecutively, that is, if we use the first, then use the second
and never return to the first;

4. In cases where field structure is the same will Unions or multiple
recordsets be more efficient?

These are idle thoughts of my own that I am pondering; I'm currently at
this stage:

1. I don't have anything running so slowly or inefficiently that I'm
going to rewrite it using multiple recordsets;
2. I'll keep multiples in mind when I program ADO recordsets in the
future;
3. I already use Unions when I have two recordsets with identical
fields that I want to use, using an absolute field defined something
like "A" as WhichGroup for the first and "B" as WhichGroup for the
second; this troubles me and maybe I'll replace it with multiples
recordsets;
4. With SQL 2000 and 2005 VBA is almost redundant; there is little I
can do in a VBA module that I can't do in a SPROC; but do I really
wanna have the server do all the work sharing it's CPU time with a
bunch of clients, rather than having the client machine do all the
work, sharing its CPU time with nothing?

As an aside T-SQL can even act as an adjunct to VBA. Don't want the
hassle of API functions to get Zulu time? Have access to an MS-SQL
sever? Use its functions then:
CurrentProject.Connection.Execute("SELECT GETUTCDATE()").Collect(0)
2005-12-10 03:24:26

If one extends this to UDFs then one could do everything in the server
and UDFs would be the repository for ones code.
And of course, this doesn't have to have anything at all to do with
your data or your type of database Your data could be an mdb or oracle
or whatever and you could access functions from some central business
rules/ function ms-sql code repository on the other side of the earth,
or on your local hard drive. That is we could all share the same
functions and have access to instant updates and additions.

I'm digressing, sorry.

Lyle

[Lyle Fairfield]

I had to test. If you see something I didn't optimize correctly,
please, let me know.
The current connection is to an internet enabled MS-SQL server in
California (I am near Toronto about 4000 km away). I am using a cable
modem (Your download speed : 3133 kbps or 391.6 KB/sec.Your upload
speed : 597 kbps or 74.6 KB/sec.).
FFDBATransactions has 296 rows; 4060148Transactions has 867 rows.
I interpret the results to show that multiple recordset retrieval has
about a 0.01 second advantage over the retrieval of two separate
recordsets under these conditions.

- code is left aligned to try to prevent news client mangling.

Private Declare Function GetTickCount Lib "kernel32" () As Long
Private Const Iterations As Long = 10
Private Const SQL1 As String = "SELECT * FROM FFDBATransactions"
Private Const SQL2 As String = "SELECT * FROM [4060148Transactions]"

Private Sub TwoRecordSets()
Dim r(2) As ADODB.Recordset
Dim t As Long
Dim z As Long
t = GetTickCount()
For z = 1 To Iterations
Set r(0) = CurrentProject.AccessConnection.Execute(SQL1)
With r(0)
While .State <> adStateOpen
Wend
..Close
End With
Set r(1) = CurrentProject.AccessConnection.Execute(SQL2)
With r(1)
While .State <> adStateOpen
Wend
..Close
End With
Next z
Debug.Print GetTickCount - t '8201,8162,8131,8171
End Sub

Private Sub OneMultipleRecordSetofTwoRecordsets()
Dim r(2) As ADODB.Recordset
Dim s As String
Dim t As Long
Dim z As Long
s = "SET NOCOUNT ON;" & SQL1 & ";" & SQL2 & ";SET NOCOUNT OFF"
t = GetTickCount()
For z = 1 To Iterations
Set r(0) = CurrentProject.Connection.Execute(s)
With r(0)
While .State <> adStateOpen
Wend
Set r(1) = .NextRecordset
..Close
r(1).Close
End With
Next z
Debug.Print GetTickCount - t '8101,8071,8042,8062
End Sub

[Randy Harris]

You've deflated my enthusiasm for this... somewhat.

I think there might still be the possibility of some advantage, despite the
negligible difference you've demonstrated. As Terry pointed out, a
significant portion of the time involved would be the compile and plan time
at the server. Perhaps the server has cached these as you submit the same
SQL repeatedly? If you were to set Iterations to 1 and run the same test,
might the results reflect less time actually passing data across the network
and a higher percentage of "overhead" time?

The other reason that I still hold out some hope that this technique might
have promise is this. When dynamic SQL is passed to the server it must
always be parsed and compiled at run time. By using a stored procedure, I'm
hoping that the SQL will be kept precompiled.

Randy
"Lyle Fairfield" <ly***********@aim.com> wrote in message
news:11**********************@g49g2000cwa.googlegr oups.com...

I had to test. If you see something I didn't optimize correctly,
please, let me know.
The current connection is to an internet enabled MS-SQL server in
California (I am near Toronto about 4000 km away). I am using a cable
modem (Your download speed : 3133 kbps or 391.6 KB/sec.Your upload
speed : 597 kbps or 74.6 KB/sec.).
FFDBATransactions has 296 rows; 4060148Transactions has 867 rows.
I interpret the results to show that multiple recordset retrieval has
about a 0.01 second advantage over the retrieval of two separate
recordsets under these conditions.

- code is left aligned to try to prevent news client mangling.

Private Declare Function GetTickCount Lib "kernel32" () As Long
Private Const Iterations As Long = 10
Private Const SQL1 As String = "SELECT * FROM FFDBATransactions"
Private Const SQL2 As String = "SELECT * FROM [4060148Transactions]"

Private Sub TwoRecordSets()
Dim r(2) As ADODB.Recordset
Dim t As Long
Dim z As Long
t = GetTickCount()
For z = 1 To Iterations
Set r(0) = CurrentProject.AccessConnection.Execute(SQL1)
With r(0)
While .State <> adStateOpen
Wend
.Close
End With
Set r(1) = CurrentProject.AccessConnection.Execute(SQL2)
With r(1)
While .State <> adStateOpen
Wend
.Close
End With
Next z
Debug.Print GetTickCount - t '8201,8162,8131,8171
End Sub

Private Sub OneMultipleRecordSetofTwoRecordsets()
Dim r(2) As ADODB.Recordset
Dim s As String
Dim t As Long
Dim z As Long
s = "SET NOCOUNT ON;" & SQL1 & ";" & SQL2 & ";SET NOCOUNT OFF"
t = GetTickCount()
For z = 1 To Iterations
Set r(0) = CurrentProject.Connection.Execute(s)
With r(0)
While .State <> adStateOpen
Wend
Set r(1) = .NextRecordset
.Close
r(1).Close
End With
Next z
Debug.Print GetTickCount - t '8101,8071,8042,8062
End Sub

[Lyle Fairfield]

Running them 6 times each but not repeatedly I get:

TwoRecordSets
0901 0831 1001 0901 0821 0821

OneMultipleRecordSetofTwoRecordsets
0801 0821 0802 0801 0801 0812

[Lyle Fairfield]

BTW, older people like me may not get over being astonished at getting
2 recordsets and more than 1000 records from another computer situated
more than 4000 km away in less than one second using a $1000 computer
and a $130 program and a $48 monthly (all amounts CAD) internet
connection.
If a teacher had demonstrated this in my high school days (the early
fifties) he/she would probably have been checked out for witchcraft!

[David W. Fenton]

"Randy Harris" <ra***@SpamFree.com> wrote in
news:9O*******************@newssvr29.news.prodigy. net:

The other reason that I still hold out some hope that this
technique might have promise is this. When dynamic SQL is passed
to the server it must always be parsed and compiled at run time.
By using a stored procedure, I'm hoping that the SQL will be kept
precompiled.

That's not true for MS SQL, if I'm not mistaken. If it's sent the
same SQL a second time, it uses a cached query plan for it. I don't
know how much flexibility there is there, e.g., if all that's
changed is the *value* searched for in a WHERE clause, but the
query
is otherwise identical, the query plan might be cached. You'd have
to look at the documentation for your particular db engine, though.

It seems to me that this technique would be most useful to reduce
load on a server and to control timeing. If, for instance, you have
a process that needs to retrieve both recordsets before it can make
the UI available to the user, it might make more sense to retrieve
those two recordsets in a single request, since the user can't
doing
anything with the first one by itself. If you send two requests,
another request could end up tying up the server before it has a
chance to get to the second request, leading to lag time in your
application as it waits for the second result set.

With multiple recordsets returned, if the server queues the queries
them together (as opposed to treating them as independent and
perhaps interleaving someone else's requests between them), then
you
potentially improve the performance of your application.

You're also consuming fewer resources on the server, so you're
allowing others to work better.

But I'd think these issues would be significant only in an app that
runs against a server that is under very heavy load. On the other
hand, if there's potential for an app to grow in that direction,
you
might want to implement this approach at the beginning to insure
maximum scalability.

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

[Randy Harris]

"Lyle Fairfield" <ly***********@aim.com> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...

BTW, older people like me may not get over being astonished at getting
2 recordsets and more than 1000 records from another computer situated
more than 4000 km away in less than one second using a $1000 computer
and a $130 program and a $48 monthly (all amounts CAD) internet
connection.
If a teacher had demonstrated this in my high school days (the early
fifties) he/she would probably have been checked out for witchcraft!

LOL! Technology is a wonder. Imagine what it will likely be 50 years from
now. No reason to think there will be less change in the next 50 years than
there were in the last 50. You and I are unlikely to witness it, but it's
still something to ponder.

[Lyle Fairfield]

Randy Harris wrote:

You've deflated my enthusiasm for this... somewhat.
My experience is that if you can use a ServerSide recordset
(Connection.CursorLocation = adUseServer or
Recordset.CursorLocation=adUseServer) then you can reduce time to get
recordset by about 37.5 per cent; times of 8100 I quited before were
reduced to 4600.

This works for two discrete recordsets.

With minimal tries, I have not succeeded in using Multiple Recordsets
with Server Side; that is .NextRecordSet seems to work only with Client
Side.
From time to time I find CurrentProject.Connection can be very slow.

Michka explained why this was once but I've lost the reference. So
generally I roll my own ADO Connection. I just a have a public function
that uses a string I create creates and returns the connection object.

There are other attributes of the recordset that may affect time. For
big recordsets I experiment trying to find the minimal (and I hope
fastst) configuration that meets my needs.

Which returns us to doing things on the Server with Sprocs and UDFs. If
one doesn't need the results immediately and one does the work on the
server then the time spent on the client will be zero or almost zero,
just enough to send our command.

[Randy Harris]

"Lyle Fairfield" <ly***********@aim.com> wrote in message
news:11**********************@z14g2000cwz.googlegr oups.com...

Randy Harris wrote:

You've deflated my enthusiasm for this... somewhat.
My experience is that if you can use a ServerSide recordset
(Connection.CursorLocation = adUseServer or
Recordset.CursorLocation=adUseServer) then you can reduce time to get
recordset by about 37.5 per cent; times of 8100 I quited before were
reduced to 4600.

Ah jeez Lyle, you're making this even more complicated. ;- {)

I haven't even tried using server side cursors. 37.5% is pretty dramatic.
Guess I've got more testing to do. Would you happen to know, can the
CursorLocation be changed after the connection has been created, or only
when the connection is made? In other words, can I switch an active
connection back and forth?

Seriously, thanks for the tip.

Randy

This works for two discrete recordsets.

With minimal tries, I have not succeeded in using Multiple Recordsets
with Server Side; that is .NextRecordSet seems to work only with Client
Side.

From time to time I find CurrentProject.Connection can be very slow.

Michka explained why this was once but I've lost the reference. So
generally I roll my own ADO Connection. I just a have a public function
that uses a string I create creates and returns the connection object.

There are other attributes of the recordset that may affect time. For
big recordsets I experiment trying to find the minimal (and I hope
fastst) configuration that meets my needs.

Which returns us to doing things on the Server with Sprocs and UDFs. If
one doesn't need the results immediately and one does the work on the
server then the time spent on the client will be zero or almost zero,
just enough to send our command.

[Lyle Fairfield]

One of these cursor locations is good for some things and the other is
good for other things; I don't want to lead you into a lot of testing
and then have you find that can't use the faster anyway:

from help files

*******
adUseClient 3
Uses client-side cursors supplied by a local cursor library. Local
cursor services often will allow many features that driver-supplied
cursors may not, so using this setting may provide an advantage with
respect to features that will be enabled

adUseServer 2
(Default)
Uses data-provider or driver-supplied cursors. These cursors are
sometimes very flexible and allow for additional sensitivity to changes
others make to the data source. However, some features of the Microsoft
Cursor Service for OLE DB (such as disassociated Recordset objects)
cannot be simulated with server-side cursors and these features will be
unavailable with this setting.

Cursors returned by the Execute method inherit this setting. Recordset
objects will automatically inherit this setting from their associated
connections.
*******
I think the documentation isn't complete here; they inherit if not set
themselves:
that is we can have
ADOConnection.CursorLocation = CursorLocationEnum
Recordsets using ADOConnection will inherit .CursorLocation =
CursorLocationEnum
BUT this property can be changed with
ADORecordset.CursorLocation = OtherCursorLocationEnum
before opening ADORecordset

******

[Lyle Fairfield]

I think one can go on with this forever (and I'm going to try to stop
now!) BUT:

Upon further review the speed of "getting" the recordset seems not to
be influenced by the cursor location for the smaller recordset (296
records). That is ten opens of the smaller took about 200 milleseconds
with adUseClient and about 200 milliseconds with adUseServer.

But the speed for the larger recordset (867 records) is reduced not by
37.5 per cent as I perviously reported but by 66.6 per cent with
adUseServer. That is ten opens of the larger took about 600
milleseconds for adUseClient and about 200 milliseconds for
asUseServer.

BTW, the structure, indexes, relationships of the two tables are
identical.

[David W. Fenton]

"Randy Harris" <ra***@SpamFree.com> wrote in
news:fS*******************@newssvr11.news.prodigy. com:

"Lyle Fairfield" <ly***********@aim.com> wrote in message
news:11**********************@z14g2000cwz.googlegr oups.com...

Randy Harris wrote:

> You've deflated my enthusiasm for this... somewhat.

My experience is that if you can use a ServerSide recordset
(Connection.CursorLocation = adUseServer or
Recordset.CursorLocation=adUseServer) then you can reduce time
to get recordset by about 37.5 per cent; times of 8100 I quited
before were reduced to 4600.

Ah jeez Lyle, you're making this even more complicated. ;- {)

I haven't even tried using server side cursors. 37.5% is pretty
dramatic. Guess I've got more testing to do.

It's not necessarily dramatic -- only if 100% is significant to
begin with.

If 100% is 10 milliseconds, then a user is going to notice no
difference at all.

If 100% is 60 seconds, the difference will be noticeable.

Anywhere in between is going to require a consideration of how hard
it is to re-implement existing code to use the new method. If
you're writing new code, though, the cost will be lower, unless the
amount of additional code for server-side cursors is significant
and not re-usable.

I think people often take percentages as proof of the superior
performance of different methods, and ignore the real-world
implications of it, especially when there are outside dependencies
over which you have no control at all (such as sending data to a
printer or retrieving data across the Internet or a slow WAN).

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

[Randy Harris]

"David W. Fenton" <dX********@bway.net.invalid> wrote in message
news:Xn**********************************@216.196. 97.142...

"Randy Harris" <ra***@SpamFree.com> wrote in
news:fS*******************@newssvr11.news.prodigy. com:

"Lyle Fairfield" <ly***********@aim.com> wrote in message
news:11**********************@z14g2000cwz.googlegr oups.com...

Randy Harris wrote:
> You've deflated my enthusiasm for this... somewhat.

My experience is that if you can use a ServerSide recordset
(Connection.CursorLocation = adUseServer or
Recordset.CursorLocation=adUseServer) then you can reduce time
to get recordset by about 37.5 per cent; times of 8100 I quited
before were reduced to 4600.

Ah jeez Lyle, you're making this even more complicated. ;- {)

I haven't even tried using server side cursors. 37.5% is pretty
dramatic. Guess I've got more testing to do.

It's not necessarily dramatic -- only if 100% is significant to
begin with.

If 100% is 10 milliseconds, then a user is going to notice no
difference at all.

If 100% is 60 seconds, the difference will be noticeable.

Anywhere in between is going to require a consideration of how hard
it is to re-implement existing code to use the new method. If
you're writing new code, though, the cost will be lower, unless the
amount of additional code for server-side cursors is significant
and not re-usable.

I think people often take percentages as proof of the superior
performance of different methods, and ignore the real-world
implications of it, especially when there are outside dependencies
over which you have no control at all (such as sending data to a
printer or retrieving data across the Internet or a slow WAN).

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

I understand what you are saying and agree.

My objective here has two parts. I'm hoping to address some current
performance issues, but also to develop optimal coding practices for future
use. The old, ounce of prevention strategy.

Randy Harris

[Lyle Fairfield]

Knowledge is like redheads; it requires no raison d'être.