Hi,



I've read that GetRows is faster and obviously keeps the time your

connection is open as small as possible, but I have a database results -

about 560 lines returning to the browser which is taking a little time. It's

no quicker when I converted the code to use GetRows.



It's loading from an Access Query which runs the same query in a second. So

why does it take ages for the web version?



Here is the few lines that open and call GetRows...





 objConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &

Server.Mappath("../../databases/Waybill.mdb")

 .

 .

 SQL String "......"

 .

 objRst.Open strSQL, objConn , 1, 1

 strDBData = objRst.GetRows()



Is this code the reason for the slow performace?





Cheers

Craig.





_____________________________________________________________________

This message has been checked for all known viruses by Star Internet

delivered through the MessageLabs Virus Scanning Service on behalf of Kingfield Heath Ltd. For further information visit
http://www.star.net.uk/stats.asp

Try



  objRst.Open strSQL, objConn



Instead of



  objRst.Open strSQL, objConn , 1, 1



-Larry



----- Original Message -----

From: "Craig Flannigan" <ckf@k...>

To: "ASP Databases" <asp_databases@p...>

Sent: Friday, February 15, 2002 2:25 AM

Subject: [asp_databases] GetRows - Is it really quicker?





>

> Hi,

>

> I've read that GetRows is faster and obviously keeps the time your

> connection is open as small as possible, but I have a database results -

> about 560 lines returning to the browser which is taking a little time.

It's

> no quicker when I converted the code to use GetRows.

>

> It's loading from an Access Query which runs the same query in a second.

So

> why does it take ages for the web version?

>

> Here is the few lines that open and call GetRows...

>

>

>  objConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &

> Server.Mappath("../../databases/Waybill.mdb")

>  .

>  .

>  SQL String "......"

>  .

>  objRst.Open strSQL, objConn , 1, 1

>  strDBData = objRst.GetRows()

>

> Is this code the reason for the slow performace?

>

>

> Cheers

> Craig.

>

>

> _____________________________________________________________________

> This message has been checked for all known viruses by Star Internet

> delivered through the MessageLabs Virus Scanning Service on behalf of

Kingfield Heath Ltd. For further information visit

http://www.star.net.uk/stats.asp

>




$subst('Email.Unsub').

>

Tried that, but I didn't notice any speed improvement.







-----Original Message-----

From: Larry Guayante [mailto:larryg@k...]

Sent: 15 February 2002 13:11

To: ASP Databases

Subject: [asp_databases] Re: GetRows - Is it really quicker?





Try



  objRst.Open strSQL, objConn



Instead of



  objRst.Open strSQL, objConn , 1, 1



-Larry



----- Original Message -----

From: "Craig Flannigan" <ckf@k...>

To: "ASP Databases" <asp_databases@p...>

Sent: Friday, February 15, 2002 2:25 AM

Subject: [asp_databases] GetRows - Is it really quicker?





>

> Hi,

>

> I've read that GetRows is faster and obviously keeps the time your

> connection is open as small as possible, but I have a database results -

> about 560 lines returning to the browser which is taking a little time.

It's

> no quicker when I converted the code to use GetRows.

>

> It's loading from an Access Query which runs the same query in a second.

So

> why does it take ages for the web version?

>

> Here is the few lines that open and call GetRows...

>

>

>  objConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &

> Server.Mappath("../../databases/Waybill.mdb")

>  .

>  .

>  SQL String "......"

>  .

>  objRst.Open strSQL, objConn , 1, 1

>  strDBData = objRst.GetRows()

>

> Is this code the reason for the slow performace?

>

>

> Cheers

> Craig.

>

>

> _____________________________________________________________________

> This message has been checked for all known viruses by Star Internet

> delivered through the MessageLabs Virus Scanning Service on behalf of

Kingfield Heath Ltd. For further information visit

http://www.star.net.uk/stats.asp

>




$subst('Email.Unsub').

>








$subst('Email.Unsub').



_____________________________________________________________________

This message has been checked for all known viruses by Star Internet

delivered through the MessageLabs Virus Scanning Service on behalf of

Kingfield Heath Ltd. For further information visit

http://www.star.net.uk/stats.asp





_____________________________________________________________________

This message has been checked for all known viruses by Star Internet

delivered through the MessageLabs Virus Scanning Service on behalf of Kingfield Heath Ltd. For further information visit
http://www.star.net.uk/stats.asp

Craig,



It may very well be that the delay is in sending all the data to the client,

rather than extracting all the data from the database.

That said, using .getRows allows you to close your DB connection, which

results in greater _scalability_ because each connection is held open for

less time. So, even if your page doesn't load faster, more users will be

able to browse your site:



<%

' Don't use "magic numbers" on the next line

' Use named constants

objRS.Open strSQL, objConn, adOpenForwardOnly, adLockReadOnly, adCmdText

If not objRS.EOF then

    arrResults = objRS.GetRows

End If



objRS.Close

Set objRS = nothing

objConn.Close

Set objConn = Nothing

'

'

If isArray(arrResults) then

    ' We have results

Else

    ' We do not have results

End If

%>



That said, manipulating large in-memory structures can be very slow is the

structure gets very large. So, if you're retrieving large memo fields from

your database, using a straight .GetRows call is not advised - instead you

should use chunking. Likewise you can minimise the size of the in-memory

array by only returning the fields you need, and avoiding SELECT *



For speed tests on .GetRows -vs- objRS.movenext in various scenarios see

http://www.davidpenton.com/testsite/datafetch/



One last point, .GetRows returns an array, not a string, so:

:  strDBData = objRst.GetRows()

looks a little weird to me.



Cheers

Ken



~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

From: "Craig Flannigan" <ckf@k...>

Subject: [asp_databases] GetRows - Is it really quicker?





:

: Hi,

:

: I've read that GetRows is faster and obviously keeps the time your

: connection is open as small as possible, but I have a database results -

: about 560 lines returning to the browser which is taking a little time.

It's

: no quicker when I converted the code to use GetRows.

:

: It's loading from an Access Query which runs the same query in a second.

So

: why does it take ages for the web version?

:

: Here is the few lines that open and call GetRows...

:

:

:  objConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &

: Server.Mappath("../../databases/Waybill.mdb")

:  .

:  .

:  SQL String "......"

:  .

:  objRst.Open strSQL, objConn , 1, 1

:  strDBData = objRst.GetRows()

:

: Is this code the reason for the slow performace?

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Thanks Ken.



the strDBData was just a bad name for the Array - it's not a string.



One other point I was always taught that you should loop through a recordset

with a For...Next rather than always checking to see if you've reached the

EOF marker.



But... I never see this in practice. Which is better?



For a=1 to objRST.Recordcount

	...

objRST.MoveNext

Next





OR



If not objRS.EOF then

    arrResults = objRS.GetRows

End If





Thanks,

Craig.





-----Original Message-----

From: Ken Schaefer [mailto:ken@a...]

Sent: 18 February 2002 00:23

To: ASP Databases

Subject: [asp_databases] Re: GetRows - Is it really quicker?





Craig,



It may very well be that the delay is in sending all the data to the client,

rather than extracting all the data from the database.

That said, using .getRows allows you to close your DB connection, which

results in greater _scalability_ because each connection is held open for

less time. So, even if your page doesn't load faster, more users will be

able to browse your site:



<%

' Don't use "magic numbers" on the next line

' Use named constants

objRS.Open strSQL, objConn, adOpenForwardOnly, adLockReadOnly, adCmdText

If not objRS.EOF then

    arrResults = objRS.GetRows

End If



objRS.Close

Set objRS = nothing

objConn.Close

Set objConn = Nothing

'

'

If isArray(arrResults) then

    ' We have results

Else

    ' We do not have results

End If

%>



That said, manipulating large in-memory structures can be very slow is the

structure gets very large. So, if you're retrieving large memo fields from

your database, using a straight .GetRows call is not advised - instead you

should use chunking. Likewise you can minimise the size of the in-memory

array by only returning the fields you need, and avoiding SELECT *



For speed tests on .GetRows -vs- objRS.movenext in various scenarios see

http://www.davidpenton.com/testsite/datafetch/



One last point, .GetRows returns an array, not a string, so:

:  strDBData = objRst.GetRows()

looks a little weird to me.



Cheers

Ken



~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

From: "Craig Flannigan" <ckf@k...>

Subject: [asp_databases] GetRows - Is it really quicker?





:

: Hi,

:

: I've read that GetRows is faster and obviously keeps the time your

: connection is open as small as possible, but I have a database results -

: about 560 lines returning to the browser which is taking a little time.

It's

: no quicker when I converted the code to use GetRows.

:

: It's loading from an Access Query which runs the same query in a second.

So

: why does it take ages for the web version?

:

: Here is the few lines that open and call GetRows...

:

:

:  objConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &

: Server.Mappath("../../databases/Waybill.mdb")

:  .

:  .

:  SQL String "......"

:  .

:  objRst.Open strSQL, objConn , 1, 1

:  strDBData = objRst.GetRows()

:

: Is this code the reason for the slow performace?

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~








$subst('Email.Unsub').



_____________________________________________________________________

This message has been checked for all known viruses by Star Internet

delivered through the MessageLabs Virus Scanning Service on behalf of

Kingfield Heath Ltd. For further information visit

http://www.star.net.uk/stats.asp





_____________________________________________________________________

This message has been checked for all known viruses by Star Internet

delivered through the MessageLabs Virus Scanning Service on behalf of Kingfield Heath Ltd. For further information visit
http://www.star.net.uk/stats.asp

The EOF approach is necessary when the cursor type - database combination 

you are using does not support the Recordcount property.



I can say from experience that a forward-only cursor with Access always 

yields 0 for Recordcount, even when there are plenty of records.



> One other point I was always taught that you should loop through a 

recordset

> with a For...Next rather than always checking to see if you've reached 

the

> EOF marker.

> 

> But... I never see this in practice. Which is better?

> 

> For a=1 to objRST.Recordcount

> 	...

> objRST.MoveNext

> Next

> 

> 

> OR

> 

> If not objRS.EOF then

>     arrResults = objRS.GetRows

> End If

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

From: "Craig Flannigan" <ckf@k...>

Subject: [asp_databases] Re: GetRows - Is it really quicker?





: One other point I was always taught that you should loop through a

recordset

: with a For...Next rather than always checking to see if you've reached the

: EOF marker.

:

: But... I never see this in practice. Which is better?

:

: For a=1 to objRST.Recordcount

: ...

: objRST.MoveNext

: Next

:

: OR

:

: If not objRS.EOF then

:     arrResults = objRS.GetRows

: End If

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~



First:

.Recordcount is only supported when you use expensive cursors that are aware

of the number of records in the recordset (eg adOpenStatic): see

www.adopenstatic.com/faq/recordcounterror.asp



Cheap recordsets like adOpenForwardOnly will yield -1 for the .RecordCount

property.





Second:

Checking .EOF works in a more generic way than doing a For loop. Suppose we

have a generic routine that it not aware of where we are in the recordset.

We could be at the beginning, or we could be in the middle, or even at the

end. If we try to do:

<% For i = 1 to objRS.RecordCount%>



we could generate a nasty error, whereas if we do:



<%

If not objRS.EOF then

    Do While Not objRS.EOF



    Loop

End If

%>



then it doesn't matter where we are in the recordset - the code will only

iterate what's left of the recordset (if any).



That said, the code you have above does two different things. The first bit

of code loops through a recordset. The second bit of code checks to see if

there are any remaining records, and if there are, puts them into an array.

I very much prefer the latter code because it helps you more cleanly write

your ASP pages: by helping to separate presentation and business logic. My

prefered way to write an ASP page is like this:



<% @Declarations here %>

<% Option Explicit %>

<!-- #include files here -->

<%

Dim all variables here



Do all Business logic here

Assign values to variables

%>



<html>

    <head></head>

    <body>

        All presentation logic here. For example we'd have all the elements

for a  <select> list in an array. We'd just call a routine to write out the

array:

        <%=WriteFormSelectList("cboTest", "", "", arrResults, 1, "", 1)%>

    </body>

<html>



Cheers

Ken