Challenge - Great code for paging but... 2

[avivit]

Too much to ask, I know. But trying anyway:

I've just seen a great code for paging records in:

http://www.learnasp.com/learn/dbtablepaged.asp

It suppose to save memory, but no where I see that the connection is closed.
Can anyone pls help???
(Or get me another code that really saves memory/time connections)?

I use getRows, and now when traffic is high, I often get errors regarding not enough temporary disk space.
So i'd like to show the records in pages, and not show 700 records in one page if 700 records r found by making a search.
And I'd like to use no memory until a page is accessed (by pressing "next" for e.g.), whixh is what tis code promisses.

Thanks to anyone that takes this challenge

 

[GabeC]

The connection is handled by the recordset object (rttemp).

rstemp.close
set rstemp = nothing

A connection object is not explicitly created so the recordset object handles this itself.

I have not used this technique but I would think the page size should be set before opening the recordset. I have not researched this or tested it. Its just what sounds logical to me.

Thanks,

Gabe

 

[avivit]

Really?
Isn't it a must to explicitly open a connection?
Anyway, I opened a connection in my way (using DSN etc.), I guess it's one of the classic ways.

So in my case, I should close it after the rs is set to nothing. Right?

Btw - I can see that the code saves memory use, but does it look to you that it is also saves connection time, rather than using getRows?
For I understand that using loops, moveNext etc. takes a lot of time and if used by too many users, it will be too much for the "system" to handle.Right?
Is there any reason why not use getRows (with limit for number of rows) in that code?
Won't it be faster?Will it takes more memory?

Thanks a LOT for your help

 

[GabeC]

Yes, the way you do it, you should close the connection object and set it to nothing. With DAO and RDO you do need to create a connection object to use with a recordset. With ADO, if you pass in a connection string, a recordset can handle the connection to the database itself. If you are concerned with amount of time a connection is opened to the database, let the recordset open and close the connection. It isn't much time but you don't have to create and open a connection object and then do the same with a recordset.

If you don't think you would ever need data rows from any page other than the one you currently are on, getRows may work for you. Then destroy the recordset. I really don't know if the array created from getRows would use less memory than the ADO recordset.

It would most likely cut down on the number of active connections to your database if this is an active site. As for speed, put it to the test. Write some sample code that does the exact same thing, one using your getRows and one using the MoveNext and such on a recordset.



Thanks,

Gabe

 

[schase]

you can also find information on getstring over at

http://www.aspfaqs.com/aspfaqs/

- no i'm not plugging that site - it is good info for the method.
"Damn the torpedoes, full speed ahead!"

-Adm. James Farragut

Stuart

 

[avivit]

GabeC,
"Getrows would most likely cut down on the number of active connections to your database if this is an active site".
On the other hand u say:
"I really don't know if the array created from getRows would use less memory than the ADO recordset."

So why r the active conection limited with getRows, if (maybe) not using more memory?
(Actually that is probably why I get an error regarding not enough temp disk space, when using getRows. Would like to understand why).

"If you don't think you would ever need data rows from any page other than the one you currently are on, getRows may work for you"
What do u mean? I have many users querying from the same page, thus using getRows, and also have other pages that use getRows, and can be accessed at the same time.
For there r many users at the same time.
Does it matter if many users query from the same page, or from different pages at the same time?
Pls explain.

Sorry for re-asking. I's really like to know.
Thanks very much.

schase, thanks. I'll check it out.

 

[GabeC]

avivit:So why r the active conection limited with getRows, if (maybe) not using more memory?

Once you call the getRows method and copy the data into an array you can close and destroy the recordset object. Thus while you are looping through the array there is no connection to the database open. If you loop through the recordset, the connection to the database is open the entire time you are working with the data.

avivit: What do u mean?( "If you don't think you would ever need data rows from any page other than the one you currently are on, getRows may work for you&quot

I understand you will eventually need data from every part of the database table, but do you here? Each web page that gets data from the database will be creating its own recordset and thus has no relationship to the recordset you are working with here.

Here is an example of what I mean by if I don't need data on any other page than the current page. I have a search function on a web page. The results web page shows 20 records per page. The database results to the search the user conducted contains 500 records. If my recordset page size contains only 20 records and I just want to send the first 20 records back to the browser, I have no need for the recordset after getting these first 20 records from the first recordset page, even though my database query returned 500 rows of data.

By “page”, I meant recordset page and not web page.

Thanks,

Gabe

 

[schase]

just a little side info -

I did a "real world" test of getrows versus normal paging through a recordset.

The normal looping through recordset returned all records and made the page in something like 4,672 milliseconds.

While the getrows returned the same page in 148 milliseconds. "Damn the torpedoes, full speed ahead!"

-Adm. James Farragut

Stuart