Paging and getrows

[Rudolph]

Assuming the code below, whats the most efficient way to get the actual
record count as the full array count doesnt exist :-)
I want to keep the paging without the full recordset being opened.

Words of wisdom? tia

Set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open DSN
Set objRS = Server.CreateObject("ADODB.Recordset")
objRS.Open "qryEmployee", objConn,0,1
objRS.Move(iStart)
aResults = objRS.GetRows(iOffset)
objRS.Close
Set objRS = Nothing
objConn.Close
Set objConn = Nothing

[Bob Barrows]

Rudolph wrote:

> Assuming the code below, whats the most efficient way to get the
> actual record count as the full array count doesnt exist :-)
> I want to keep the paging without the full recordset being opened.
>
> Words of wisdom? tia
>
> Set objConn = Server.CreateObject("ADODB.Connection")
> objConn.Open DSN

Don't use a DSN. ODBC is being phased out. Instead, use the native OLEDB
provider for your database. Examples of connection strings can be found
here: [url]www.able-consulting.com/ado_conn.htm[/url]

This raises the question, what database are you using? The answer to your
question will depend on this. Due to "qryEmployee", I suspect Access, but
I'd rather know for sure.

> Set objRS = Server.CreateObject("ADODB.Recordset")
> objRS.Open "qryEmployee", objConn,0,1
> objRS.Move(iStart)
> aResults = objRS.GetRows(iOffset)
> objRS.Close
> Set objRS = Nothing
> objConn.Close
> Set objConn = Nothing

Bob Barrows

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[Rudolph]

Sorry, its badly named ;-)
Typically its access using OLEDB, but this example I used SQL and just
deleted the normal connection string.
Assuming Access (or both as I use both) whats the best answer?

Thanks Bob


"Bob Barrows" <reb01501@NOyahoo.SPAMcom> wrote in message
news:e#qQwAYwDHA.556@TK2MSFTNGP11.phx.gbl...

> Rudolph wrote:

> > Assuming the code below, whats the most efficient way to get the
> > actual record count as the full array count doesnt exist :-)
> > I want to keep the paging without the full recordset being opened.
> >
> > Words of wisdom? tia
> >
> > Set objConn = Server.CreateObject("ADODB.Connection")
> > objConn.Open DSN

>
> Don't use a DSN. ODBC is being phased out. Instead, use the native OLEDB
> provider for your database. Examples of connection strings can be found
> here: [url]www.able-consulting.com/ado_conn.htm[/url]
>
> This raises the question, what database are you using? The answer to your
> question will depend on this. Due to "qryEmployee", I suspect Access, but
> I'd rather know for sure.
>

> > Set objRS = Server.CreateObject("ADODB.Recordset")
> > objRS.Open "qryEmployee", objConn,0,1
> > objRS.Move(iStart)
> > aResults = objRS.GetRows(iOffset)
> > objRS.Close
> > Set objRS = Nothing
> > objConn.Close
> > Set objConn = Nothing

>
> Bob Barrows
>
> --
> Microsoft MVP - ASP/ASP.NET
> Please reply to the newsgroup. This email account is my spam trap so I
> don't check it very often. If you must reply off-line, then remove the
> "NO SPAM"
>
>

[Bob Barrows]

Rudolph wrote:

> Sorry, its badly named ;-)
> Typically its access using OLEDB, but this example I used SQL and just

SQL Server?
SQL is a language, not a database. I know it's a little picky, but we can
provide your answers much quicker if you leave no room for doubt.

> deleted the normal connection string.
> Assuming Access (or both as I use both) whats the best answer?
>

the best answer for SQL Server will not apply to Access, and vice versa. If
Access must be part of the process, then I would avoid a second trip to the
database by opening a client-side recordset so I can use the RecordCount
property.

dim recs
const adUseClient=3
const adOpenStatic=3
Const adCmdStoredProc = &H0004
Const adLockReadOnly = 1

Set objRS = Server.CreateObject("ADODB.Recordset")
objRS.Cursorlocation = adUseClient
objRS.Open "qryEmployee", objConn,adOpenStatic, _
adLockReadOnly,adCmdStoredProc
recs = objRS.RecordCount

Note: I specified the adOpenStatic cursor type. I did not have to. With a
client-side cursor, static is the only cursor type available. The following
statement would be equivalent:
objRS.Open "qryEmployee", objConn,, _
adLockReadOnly,adCmdText

Actually, this would also be equivalent:
objRS.LockType = adLockReadOnly
objConn.qryResults objRS


The best answer for SQL Server, IMO, is to use a stored procedure with an
output parameter with which to pass the record count. This requires the use
of an explicit Command object.

Modify the following to fit your database and table, then run it in Query
Analyzer:
CREATE PROCEDURE GetEmployees (
@empcount int output) AS
SET NOCOUNT ON
Select <column list> FROM Employees
SET @empcount = @@ROWCOUNT

In asp:
dim cmd, params, recs
Const adCmdStoredProc = &H0004
Const adInteger = 3
Const adParamOutput = &H0002
Const adParamReturnValue = &H0004

Set cmd=server.createobject("adodb.command")
With cmd
.CommandType = adCmdStoredProc
.CommandText = "GetEmployees"
.ActiveConnection = objConn
set params = .Parameters
params.append .CreateParameter("RETURN",adInteger, _
adParamReturnValue)
params.append .CreateParameter("@empcount,adInteger, _
adParamOutput)
Set objRS = .Execute
End With
if not objRS.EOF then
objRS.Move iStart
aResults=objRS.GetRows(iOffset)
end if
objRS.close
recs = params(1).value

Note, the value of the output parameter is not available until either:
1. the last record in the resultset has been brought into the recordset
2. the recordset is closed

HTH,
Bob Barrows
PS. See here for a way to avoid the Const statements:
[url]http://www.aspfaq.com/show.asp?id=2112[/url]

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[Rudolph]

Thanks Bob!
Not too picky, but its 3am here so the grey matter is a bit fuzzy ;-)

"Bob Barrows" <reb01501@NOyahoo.SPAMcom> wrote in message
news:eQ5eqpYwDHA.3116@tk2msftngp13.phx.gbl...

> Rudolph wrote:

> > Sorry, its badly named ;-)
> > Typically its access using OLEDB, but this example I used SQL and just

>
> SQL Server?
> SQL is a language, not a database. I know it's a little picky, but we can
> provide your answers much quicker if you leave no room for doubt.
>

> > deleted the normal connection string.
> > Assuming Access (or both as I use both) whats the best answer?
> >

>
> the best answer for SQL Server will not apply to Access, and vice versa.

If

> Access must be part of the process, then I would avoid a second trip to

the

> database by opening a client-side recordset so I can use the RecordCount
> property.
>
> dim recs
> const adUseClient=3
> const adOpenStatic=3
> Const adCmdStoredProc = &H0004
> Const adLockReadOnly = 1
>
> Set objRS = Server.CreateObject("ADODB.Recordset")
> objRS.Cursorlocation = adUseClient
> objRS.Open "qryEmployee", objConn,adOpenStatic, _
> adLockReadOnly,adCmdStoredProc
> recs = objRS.RecordCount
>
> Note: I specified the adOpenStatic cursor type. I did not have to. With a
> client-side cursor, static is the only cursor type available. The

following

> statement would be equivalent:
> objRS.Open "qryEmployee", objConn,, _
> adLockReadOnly,adCmdText
>
> Actually, this would also be equivalent:
> objRS.LockType = adLockReadOnly
> objConn.qryResults objRS
>
>
> The best answer for SQL Server, IMO, is to use a stored procedure with an
> output parameter with which to pass the record count. This requires the

use

> of an explicit Command object.
>
> Modify the following to fit your database and table, then run it in Query
> Analyzer:
> CREATE PROCEDURE GetEmployees (
> @empcount int output) AS
> SET NOCOUNT ON
> Select <column list> FROM Employees
> SET @empcount = @@ROWCOUNT
>
> In asp:
> dim cmd, params, recs
> Const adCmdStoredProc = &H0004
> Const adInteger = 3
> Const adParamOutput = &H0002
> Const adParamReturnValue = &H0004
>
> Set cmd=server.createobject("adodb.command")
> With cmd
> .CommandType = adCmdStoredProc
> .CommandText = "GetEmployees"
> .ActiveConnection = objConn
> set params = .Parameters
> params.append .CreateParameter("RETURN",adInteger, _
> adParamReturnValue)
> params.append .CreateParameter("@empcount,adInteger, _
> adParamOutput)
> Set objRS = .Execute
> End With
> if not objRS.EOF then
> objRS.Move iStart
> aResults=objRS.GetRows(iOffset)
> end if
> objRS.close
> recs = params(1).value
>
> Note, the value of the output parameter is not available until either:
> 1. the last record in the resultset has been brought into the recordset
> 2. the recordset is closed
>
> HTH,
> Bob Barrows
> PS. See here for a way to avoid the Const statements:
> [url]http://www.aspfaq.com/show.asp?id=2112[/url]
>
> --
> Microsoft MVP - ASP/ASP.NET
> Please reply to the newsgroup. This email account is my spam trap so I
> don't check it very often. If you must reply off-line, then remove the
> "NO SPAM"
>
>

[Chris Hohmann]

"Rudolph" <nospam@spam.com> wrote in message
news:uINKW8XwDHA.2092@TK2MSFTNGP09.phx.gbl...

> Assuming the code below, whats the most efficient way to get the

actual

> record count as the full array count doesnt exist :-)
> I want to keep the paging without the full recordset being opened.
>
> Words of wisdom? tia
>
> Set objConn = Server.CreateObject("ADODB.Connection")
> objConn.Open DSN
> Set objRS = Server.CreateObject("ADODB.Recordset")
> objRS.Open "qryEmployee", objConn,0,1
> objRS.Move(iStart)
> aResults = objRS.GetRows(iOffset)
> objRS.Close
> Set objRS = Nothing
> objConn.Close
> Set objConn = Nothing

What about this?

iStart + UBound(aResults,2) + 1

Also, have you considered using a stored procedure. It seems a shame to
retrieve all the records on each paging request when you are only
concerned with let say 50 of them. Aaron has a nice proof-of-concept on
his site:

[url]http://aspfaq.com/2120[/url]

HTH
-Chris Hohmann

[Rudolph]

"It seems a shame to retrieve all the records on each paging request "

I was under the impression I wasnt doing that by .move to the iStart record
and only getting an iOffset chunk?
Thats why the question, if the offset is only "100" thats the only records
it knows about isnt it?

Cheers


"Chris Hohmann" <nospam@thankyou.com> wrote in message
news:O$09bjbwDHA.2304@TK2MSFTNGP12.phx.gbl...

> "Rudolph" <nospam@spam.com> wrote in message
> news:uINKW8XwDHA.2092@TK2MSFTNGP09.phx.gbl...

> > Assuming the code below, whats the most efficient way to get the

> actual

> > record count as the full array count doesnt exist :-)
> > I want to keep the paging without the full recordset being opened.
> >
> > Words of wisdom? tia
> >
> > Set objConn = Server.CreateObject("ADODB.Connection")
> > objConn.Open DSN
> > Set objRS = Server.CreateObject("ADODB.Recordset")
> > objRS.Open "qryEmployee", objConn,0,1
> > objRS.Move(iStart)
> > aResults = objRS.GetRows(iOffset)
> > objRS.Close
> > Set objRS = Nothing
> > objConn.Close
> > Set objConn = Nothing

>
> What about this?
>
> iStart + UBound(aResults,2) + 1
>
> Also, have you considered using a stored procedure. It seems a shame to
> retrieve all the records on each paging request when you are only
> concerned with let say 50 of them. Aaron has a nice proof-of-concept on
> his site:
>
> [url]http://aspfaq.com/2120[/url]
>
> HTH
> -Chris Hohmann
>
>

[Bob Barrows]

Rudolph wrote:

> "It seems a shame to retrieve all the records on each paging request "
>
> I was under the impression I wasnt doing that by .move to the iStart
> record and only getting an iOffset chunk?
> Thats why the question, if the offset is only "100" thats the only
> records it knows about isnt it?
>
>

It depends.

With a forward-only cursor, only enough records to fill the cache are
retrieved from the database. The default cache size is 1, so usually only 1
record at a time will be retrieved. You can increase the cache size to allow
some limited scrollability with even a forward-only cursor, but this is
rarely done.

With a static cursor, however, all the records returned by the query are
retrieved (this is why RecordCount works with static cursor). So Chris
definitely has a point: if you take my advice to use a static cursor, you
may be retrieving many more records than intended.

Bottom line: my advice was not that great. I should have at least advised
you to use a server-side static cursor, which will decrease the network
traffic. However, with a large number of records, you may still be killing
your application's performance, so you should also test the difference in
performance between doing this:

Set objRS = Server.CreateObject("ADODB.Recordset")
objRS.Cursorlocation = adUseServer
objRS.Open "qryEmployee", objConn,adOpenStatic, _
adLockReadOnly,adCmdStoredProc
recs = objRS.RecordCount

and doing this:

In Access, create a saved query called qryGetRecordCount that simply returns
the number of records in the table:
Select Count(*) FROM tablename

Then in ASP:
Set objRS = Server.CreateObject("ADODB.Recordset")
objConn.qryGetRecordCount objRS
recs = objRS(0).value
objRS.close
Set objRS = Server.CreateObject("ADODB.Recordset")
objRS.LockType=adLockReadOnly
objConn.qryEmployee objRS

etc.

The result of your tests (make sure the contents of the database are
realistic) will tell you which approach to use.

HTH,
Bob Barrows

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[Ken Schaefer]

"Bob Barrows" <reb01501@NOyahoo.SPAMcom> wrote in message
news:eQ5eqpYwDHA.3116@tk2msftngp13.phx.gbl...
: Rudolph wrote:


: the best answer for SQL Server will not apply to Access, and vice versa.
If
: Access must be part of the process, then I would avoid a second trip to
the
: database by opening a client-side recordset so I can use the RecordCount
: property.

I disagree.

Try running this -vs- using an adOpenForwardOnly cursor/GetRows like the
original code snippet that the OP posted. The OP's code, for a reasonable
number of records (say, getting 20 records out of 1000) will run in about
half the time as using a static cursor.

Since OLEDB Provider for Jet is single threaded, getting in, and out again,
in the quickest possible time is quite important.

If the total number of records is also required, then execute another query
to return all the records (SELECT COUNT())
-or-
Use GetRows/ForwardOnly cursor to retrieve all the records into an array
(similar to using a Static/ClientSide cursor), however the forwardonly
cursor is much faster than the static cursor in returning all records, and
the recordcount can be obtained by evaluating UBound(arrResults) + 1

Cheers
Ken

[Rudolph]

Thanks Ken,

Using a basic browser JScript time trial, it seems that is the best method
for the records tried (3000)
Ive got another with 200000+ record and Ill try it on that too.

Test # 1 - approx 60ms (.record count)
Test # 2 - approx 25ms (2 queries - chunked records + count())
Test # 3 - approx 65ms (all records, ubound array)


Test # 1 - code

Set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open DSN
Set objRS = Server.CreateObject("ADODB.Recordset")
With objRS
.Cursorlocation = 3
.Open "select reportid from reports", objConn,0,1
recs = .RecordCount
.Move(iStart)
aResults = .GetRows(iOffset)
.Close
End With
Set objRS = Nothing
objConn.Close
Set objConn = Nothing

Test # 2 code

Set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open DSN
Set objRS = Server.CreateObject("ADODB.Recordset")
With objRS
.Open "select reportid from reports", objConn,0,1
.Move(iStart)
aResults = .GetRows(iOffset)
.Close
End With
Set objRS = Nothing
Set objRS2 = objConn.Execute("Select Count(ReportID) from Reports")
recs = objRS2(0)
objRS2.close
Set objRS2 = Nothing
objConn.Close
Set objConn = Nothing

Test #3 code

Set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open DSN
Set objRS = Server.CreateObject("ADODB.Recordset")
With objRS
.Open "select reportid from reports", objConn,0,1
aResults = .GetRows()
.Close
End With
Set objRS = Nothing
objConn.Close
Set objConn = Nothing
recs=ubound(aResults,2) +1




"Ken Schaefer" <kenREMOVE@THISadOpenStatic.com> wrote in message
news:uqI0hWtwDHA.2556@TK2MSFTNGP10.phx.gbl...

>
> "Bob Barrows" <reb01501@NOyahoo.SPAMcom> wrote in message
> news:eQ5eqpYwDHA.3116@tk2msftngp13.phx.gbl...
> : Rudolph wrote:
>
>
> : the best answer for SQL Server will not apply to Access, and vice versa.
> If
> : Access must be part of the process, then I would avoid a second trip to
> the
> : database by opening a client-side recordset so I can use the RecordCount
> : property.
>
> I disagree.
>
> Try running this -vs- using an adOpenForwardOnly cursor/GetRows like the
> original code snippet that the OP posted. The OP's code, for a reasonable
> number of records (say, getting 20 records out of 1000) will run in about
> half the time as using a static cursor.
>
> Since OLEDB Provider for Jet is single threaded, getting in, and out

again,

> in the quickest possible time is quite important.
>
> If the total number of records is also required, then execute another

query

> to return all the records (SELECT COUNT())
> -or-
> Use GetRows/ForwardOnly cursor to retrieve all the records into an array
> (similar to using a Static/ClientSide cursor), however the forwardonly
> cursor is much faster than the static cursor in returning all records, and
> the recordcount can be obtained by evaluating UBound(arrResults) + 1
>
> Cheers
> Ken
>
>