Large RecordSet

[ARPRINCE]

I have a table that has 100K records and growing fast. I place the the records in a recordset and display it in a form.

Right now, it takes a few seconds before the form is displayed when called. I forsee that going forward, it will take a longer time to get the recordset and dump it into my form. I also see memory constraints on the user's PC.

(1) Is there a way I could read, let's say, 500 records at a time in my recordset? Just like paging it?

(2) Is there a way I could check how much memory my program is eating or how efficient it is?

Thank you.

[McGenius]

I'd rather look for a way to filter records so recorset won't get populated with 1000s of rows but with few dozens instead. However, if you do need them all at once then you may "split" them into arrays of UDT but this (large rst) may (and most definitely will) affect performance.

[techyspecy]

Also, if you cannot narrow the affected record count, try to use Server side cursor instead of Client side cursor. It should make some difference.

[Frans C]

If you use a server based database (eg sql server of oracle), you could benefit from using a serverside cursor, and specify a cache size for the recordset (assuming you use ADO).
For an Access database this won't help much, because the 'server side' runs on the client as well.

However, I would seriously reconsider if displaying that many records on a form is userfriendly.

[Simon_R]

We have this problem in the AS400, I have files of millions of records, try this

use a flexgrid

I believe you can know the toprow of this (ie where the user is in the recordset)

Load only 100 records, when the toprow reaches about 50 (trap the scroll) then load the next 100 and so on.

is this any use, if so I will try to code it for you

Simon

[techyspecy]

Originally posted by Simon_R
We have this problem in the AS400, I have files of millions of records, try this

use a flexgrid

I believe you can know the toprow of this (ie where the user is in the recordset)

Load only 100 records, when the toprow reaches about 50 (trap the scroll) then load the next 100 and so on.

is this any use, if so I will try to code it for you

Simon

You still have to download all the records in the recordset. I think time consumption in loading the records is one thing, the bigger problem is downloading the records in a recordset. As the no. of records increase the size of the recordset increases and thus it takes more system resources on client's machine. This is just one of those things that are associated with Client-Server World of VB. Web is better in this manner.

[Simon_R]

I could be missing something, but I was suggesting opening the recordset and only reading 100 (arbitary figure) and reading the next 100 when needed.
If the user wanted to scroll to the bottom then sure 100k otherwise you get away with only 100 records.

Simon

[ARPRINCE]

I won't be able to use a FLEXGRID since I need to show each record is a specified format.

I tried using a server side cursor but need tips about caching and record movement.

You see, I have "navigation buttons" (i.e. next, previous, first, last). When I cliked on the last record nav button, I got an error ROWSET DOESN'T SUPPORT FETCHING BACKWARD. I also lost my use of a bookmark.

Thanks

[Simon_R]

Seems you need a dynamic recordset, more resources there.

Are you using a listbox ?

Simon

ps.

no you could always do a select descending

I reckon you could still do it with a listbox, but I have never tried

[Wokawidget]

You could either do one of the following:

1) Split you app into an n-tier app and pass thev data to the UI in a buffer string using PropertyBags. this removes the need for a recordset, and may speed up the UI...

2) Don't bring back 100K records!

Why on earth do you want to bring that much data back anyways? Most of it will be pointless and of no interest to the user...

Woka

PS GOD I HATE xFACTORY!

[ARPRINCE]

Originally posted by Simon_R
Seems you need a dynamic recordset, more resources there.

Are you using a listbox ?

Simon

ps.

no you could always do a select descending

I reckon you could still do it with a listbox, but I have never tried

No. I have a design that drops each field of the record in the form.

'******************'

Private Sub FILL_SCREEN_INFO()
'THIS WILL PLACE DATA INTO THE FORM ENTRY UNDER TAB1
cmdDELETE.Enabled = True
Me.Caption = "STORES - Master Table " _
& " [" & Format(adoRS.RecordCount, "000000 Records") & "]"
With adoRS.Fields
sscFormCode = ![FormCode]
txtStoreCode = ![StoreCode]
cmbRankCode = ![RankCode]
sscDept = ![DEPTCODE]
txtStoreName = ![StoreName] & ""
txtAddress = ![Address] & ""
txtCity = ![City] & ""
sscState = ![StateCode] & ""
mskZIP = Format(![ZipCode], "00000")

If IsNull(![Phone]) = True Or ![Phone] = "" Then
mskPhone = "(___)___-____"
Else
mskPhone = Format(![Phone], "(000)000-0000")
End If

If IsNull(![Fax]) = True Or ![Fax] = "" Then
mskFax = "(___)___-____"
Else
mskFax = Format(![Fax], "(000)000-0000")
End If

sscSSN = !EmployeeID

If ![permanent] = "P" Then
chkPermanent.Value = 1
Else
chkPermanent.Value = 0
End If

If ![Status] = "True" Then
chkStatus.Value = 1
lblActive.Visible = True
Else
chkStatus.Value = 0
lblActive.Visible = False
End If
txtComments = ![Comments] & ""
txtUSER = ![LMSAccount] & " " & _
Format(Month(![CurrentDate]), "00") & _
Format(Day(![CurrentDate]), "00") & _
Right(Year(![CurrentDate]), 2)
End With
cmdEDIT.Enabled = True
End Sub

'*************

[ARPRINCE]

Originally posted by Wokawidget
You could either do one of the following:

1) Split you app into an n-tier app and pass thev data to the UI in a buffer string using PropertyBags. this removes the need for a recordset, and may speed up the UI...

2) Don't bring back 100K records!

Why on earth do you want to bring that much data back anyways? Most of it will be pointless and of no interest to the user...

Woka

PS GOD I HATE xFACTORY!

(2) Yes. I see the point of uselessness bringing back 100K records. Needs to redeisgn.

Thanks

[techyspecy]

Originally posted by Wokawidget
You could either do one of the following:

1) Split you app into an n-tier app and pass thev data to the UI in a buffer string using PropertyBags. this removes the need for a recordset, and may speed up the UI...

2) Don't bring back 100K records!

Why on earth do you want to bring that much data back anyways? Most of it will be pointless and of no interest to the user...

Woka

PS GOD I HATE xFACTORY!

WOKA - Lets say I have a search screen where user can search for some Item details from a transaction table. And the search results in 100K records. What do you think I outta do ? Mind you, its not a Web app, its your typical VB app. God i hate VB now.

[newace]

why not make it like a page flipping back and forth? like eg, the 1st page displays the 1st 100 records and the 2nd page displays the 101 to 200 records.

you can use "SELECT TOP 100 * FROM TABLE_TO_SEARCH" query to list the 1st 100 records and catch the cursor's position when the query is finished.

hope that helps.

[techyspecy]

Originally posted by newace
why not make it like a page flipping back and forth? like eg, the 1st page displays the 1st 100 records and the 2nd page displays the 101 to 200 records.

you can use "SELECT TOP 100 * FROM TABLE_TO_SEARCH" query to list the 1st 100 records and catch the cursor's position when the query is finished.

hope that helps.

In VB ????? Easier said than done.

[Wokawidget]

Well considering bringing 100k records is a bit of overkill, and there is never any need for this amount of records (that I can think of) I would do one of a few things...

1) The server would send a message back to the client saying "Search Results too Big...Please narrow search down" etc and I would maybe pass 200 records back as a taster...you know what mean...kazaa does that.

2) have some options in the UI specifying the MAX amount of records a user wants to bring back. If they specify the MAX value as 100k then fair enough...they will have to wait for the processing. Obviously this may impact on the server so I would hard code a MAX MAX value so they can't enter some really stupid number...

3) create some funky buffer so the user can click on GET NEXT 1000 records...something like that

4) max the search screen and functionality so it does things by dates, ie JAn, Feb so that roughly you know that the max search they can do will bring back 1000 records tops...

5) Leave it as it is and when they compain take a shovel and a picture of Maggie Thatcher round to their desk, make then eat the picture then lamp them with the shovel

I would, if I was ever in that situation, go for option 1...or option 3, however, I feel option 1 is the more sensible, and option 5 is the most fun

All valid points me thinks...but I still stand by what I said earlier that 100K records...NO NEED!

Woka

[Wokawidget]

Originally posted by techyspecy
In VB ????? Easier said than done.

Easy...create some class that returns the ID's from the search. So you have 100k ID's loaded into a class...then, depending on how many you want to retrieve at any one time, pass say 100 ID's back to the server and load all the display info for them. Click next and it then passes the next 100 ID's and so on...you can easily go back and forth by storing which set of ID's you are currently viewing...

Actually it's NOT that easy, would require intermediate level VB programming skills, but it's very, very possible. You would have to write extra coding to handle all this...that's how I would do it anyways...

Still prefer option 1, lot less processing time etc
Although it does all depend on what your user wants



Woka

[techyspecy]

Woka,

First of all you can return 200 records but what about other. What if user wants to see next 200 now ???????

I do ask my users to narrow the search As much as possible. In the end "you can only ask".

There is no way (i do not know at least) that you can do paging in VB returning first 200 and then next 200 and then next 200 ( unless you download the whole god damn recordset on client machine and then simulate this ... user is ****ed anyway ) and so on ...

You cannot create no funky buffer like that ( without bringing the entire recordset ) ... if you can i'd love to see this happening in VB.

And i will only adapt your method 5 if i do not want to work anymore .....

[techyspecy]

Originally posted by Wokawidget
Easy...create some class that returns the ID's from the search. So you have 100k ID's loaded into a class...then, depending on how many you want to retrieve at any one time, pass say 100 ID's back to the server and load all the display info for them. Click next and it then passes the next 100 ID's and so on...you can easily go back and forth by storing which set of ID's you are currently viewing...

Actually it's NOT that easy, would require intermediate level VB programming skills, but it's very, very possible. You would have to write extra coding to handle all this...that's how I would do it anyways...

Still prefer option 1, lot less processing time etc
Although it does all depend on what your user wants



Woka

Exactly - you still have to download the data on the client pal.
Evne in ASP pages you can do paging but you still have to carry the data to the client.

[techyspecy]

Originally posted by Wokawidget
Easy...create some class that returns the ID's from the search. So you have 100k ID's loaded into a class...then, depending on how many you want to retrieve at any one time, pass say 100 ID's back to the server and load all the display info for them. Click next and it then passes the next 100 ID's and so on...you can easily go back and forth by storing which set of ID's you are currently viewing...

Actually it's NOT that easy, would require intermediate level VB programming skills, but it's very, very possible. You would have to write extra coding to handle all this...that's how I would do it anyways...

Still prefer option 1, lot less processing time etc
Although it does all depend on what your user wants



Woka

Displaying part is easy WOKA. But the ****in problem is, to display even 100 records of data you still have to bring the entire recordset which may contain 100k records. That kills the memory on client machine. You cannot directly request 100 and next 100 and next 100 and so on from the database.

[frigginjerk]

Newace has the right idea.

Creating a page of next 100 records would be simple as long as your records were ordered in some fashion when you retrieve them. So, your following queries would become something like:

"SELECT TOP 100 * FROM TABLE_TO_SEARCH WHERE StoreCode > ?"

Then just replace the '?' with the last record's StoreCode. I don't see why this would be difficult.

[techyspecy]

Originally posted by frigginjerk
Newace has the right idea.

Creating a page of next 100 records would be simple as long as your records were ordered in some fashion when you retrieve them. So, your following queries would become something like:

"SELECT TOP 100 * FROM TABLE_TO_SEARCH WHERE StoreCode > ?"

Then just replace the '?' with the last record's StoreCode. I don't see why this would be difficult.

Thats not a full proof solution either. It will not work for uniqueidentifiers which we use to place keys in our tables. Or in some tables where there is no key at all.

[Wokawidget]

techY, sure you can...

You have a class with a function called GetIDs or something like that. You pass the search criteria to the server, and all the server does is return 100k ID's, in a string, no recordsets. That's won't take that much resources up.
In your class you have a collection of another class, which stores x many ID's. Add x amount of ID's to one of these classes then add it to the collection with a key something like "1x100", "2x100"...This class you have added to the collection has a function GetData. this function passes the 100 or so ID's back to the server from which you return 100 or so "records". You then display them...you can use the SQL statement something like (by the way I canny remember the exact syntax for this select statement so don't pull me up on it )

"SELECT * FROM blah WHERE ID IS IN(" & strMyCommaSeperatedIDs & ")"

You can easily write a movenext and moveprevious sub in the main class and have a pointer so you know which "group" of ID's you are looking at, and loads them on a need by need bases, destroying the previous 100 as you go...

No recordsets involved, only buffer strings or property bag byte arrays...This way you will hardly use any resources and you can easily load 100 at a time and move back and forth between them...

Regarding my Option 1 in my last post, well loads of apps and web pages do this...Kazaa for one, think the limit is 200 for Kazaa.

All the above is off the top of my head, apart from the Loading 100k ID's As I bench tested this the other day for some completely different reason to what we are discussing now...took something silly like 0.1 to 0.2 seconds to request, retrieve and populate a collection of classes with 100,000 IDs, AND DESCRIPTIONS (30 chars long) Can't remember the EXACT time. Although I think it was closer to 0.1s. Now, personally, I feel that is quick

Try it out for yourself. NOT USING RECORDSETS, as empty recordsets come with there own overhead which on it's own is FAR greater than 100k ID's in a byte array.

So, why wouldn't the above work?
Where are the cons with this method?

Woka

[Wokawidget]

Originally posted by frigginjerk
Newace has the right idea.

Creating a page of next 100 records would be simple as long as your records were ordered in some fashion when you retrieve them. So, your following queries would become something like:

"SELECT TOP 100 * FROM TABLE_TO_SEARCH WHERE StoreCode > ?"

Then just replace the '?' with the last record's StoreCode. I don't see why this would be difficult.

There's another way to do it, however you are limited as your are restricted by bringing back the data in a certain order. But it's still a perfectly good way to do it...

Imagineering is what I do best

Woka

[techyspecy]

Originally posted by Wokawidget
techY, sure you can...

You have a class with a function called GetIDs or something like that. You pass the search criteria to the server, and all the server does is return 100k ID's, in a string, no recordsets. That's won't take that much resources up.
In your class you have a collection of another class, which stores x many ID's. Add x amount of ID's to one of these classes then add it to the collection with a key something like "1x100", "2x100"...This class you have added to the collection has a function GetData. this function passes the 100 or so ID's back to the server from which you return 100 or so "records". You then display them...you can use the SQL statement something like (by the way I canny remember the exact syntax for this select statement so don't pull me up on it )

"SELECT * FROM blah WHERE ID IS IN(" & strMyCommaSeperatedIDs & ")"

You can easily write a movenext and moveprevious sub in the main class and have a pointer so you know which "group" of ID's you are looking at, and loads them on a need by need bases, destroying the previous 100 as you go...

No recordsets involved, only buffer strings or property bag byte arrays...This way you will hardly use any resources and you can easily load 100 at a time and move back and forth between them...

Regarding my Option 1 in my last post, well loads of apps and web pages do this...Kazaa for one, think the limit is 200 for Kazaa.

All the above is off the top of my head, apart from the Loading 100k ID's As I bench tested this the other day for some completely different reason to what we are discussing now...took something silly like 0.1 to 0.2 seconds to request, retrieve and populate a collection of classes with 100,000 IDs, AND DESCRIPTIONS (30 chars long) Can't remember the EXACT time. Although I think it was closer to 0.1s. Now, personally, I feel that is quick

Try it out for yourself. NOT USING RECORDSETS, as empty recordsets come with there own overhead which on it's own is FAR greater than 100k ID's in a byte array.

So, why wouldn't the above work?
Where are the cons with this method?

Woka

WOKA - What ID's are you talking about ? Are you talking about Primary Keys of the table ? If yes, then we have tables with keys, no keys, secondary keys, composite keys consisting of 5 fields, you name it. We have every possible situation here. You cannot handle each and every possibility. Primary key might work in some tables but not everywhere. Specially in those tables where there is no key in the first place.

[Wokawidget]

Originally posted by techyspecy
Thats not a full proof solution either. It will not work for uniqueidentifiers which we use to place keys in our tables. Or in some tables where there is no key at all.

Have to agree with you there, but it's only a small obstical to get round. Just add a PK_ID identity field to your database and then both of the above methods are perfectly feasable...

You can't just design your app around a DB. The DB must, to a certain extent, be also designed around your app...

Woka

[Wokawidget]

Adding an identity field to a DB table, when you are already using a UniqueIdentity field, is going to increase the DB size by a neglegable amount, so no worries there, espescially if the table has 10, 20 or even as many as 30 fields...if you had a description field which was say 100 chars and the user said we want it to be 110, you wouldn't bat an eyelid in the slightest at the extra overhead...

Woka

[techyspecy]

Originally posted by Wokawidget
Have to agree with you there, but it's only a small obstical to get round. Just add a PK_ID identity field to your database and then both of the above methods are perfectly feasable...

You can't just design your app around a DB. The DB must, to a certain extent, be also designed around your app...

Woka

I wish it was that simple as you said 'Just add a PK_ID identity field to your database'. In our new systems we make sure the database and system are perfectly done. We do not design our database first. System gets designed first all entities, classes, objects etc. Database is always a secondary thing. You cannot create application of longer lifetime and less maintainability if you depend on your DB's. However, just like anybody else we do have applications that are done by some bunch of stupid morons and we are suppose to maintain them. Its just not that easy WOKA.

We have AS/400 system tables with millions of business data with no keys. If i were to go and tell my business people that i wanna make a small change to those tables, they'd rather get rid of me than allowing me to touch them.

[Wokawidget]

Adding an identity field to a DB is dead easy. It should not affect your code or require ANY changes...UNLESS the code is like:

adoRec.Field(1).Value = "Fish"

In which case you will get an error as Field 1 is the Identity. Same goes for inserts and updates...
It may be a bigger pain if you want to implement it late on in the projects design and you will have to rewrite alot of existing code to accomondate these changes, but you could knock up a few classes that loaded 100k's or records 100 by 100, in say an afternoon....no problem, so the fucntionality isn't hard, what is is implementing as I know it would be a bugger to add that type of functionality into our apps. But yes I agree with you that implementing into a large system may cause a few problems, but from scratch, there is no problems what so ever.

Another good discussion...
****! Am late for the pub!!!
Boooooooooooooooooooooo

Woka

[techyspecy]

Originally posted by Wokawidget
Adding an identity field to a DB is dead easy. It should not affect your code or require ANY changes...UNLESS the code is like:

adoRec.Field(1).Value = "Fish"

In which case you will get an error as Field 1 is the Identity. Same goes for inserts and updates...
It may be a bigger pain if you want to implement it late on in the projects design and you will have to rewrite alot of existing code to accomondate these changes, but you could knock up a few classes that loaded 100k's or records 100 by 100, in say an afternoon....no problem, so the fucntionality isn't hard, what is is implementing as I know it would be a bugger to add that type of functionality into our apps. But yes I agree with you that implementing into a large system may cause a few problems, but from scratch, there is no problems what so ever.

Another good discussion...
****! Am late for the pub!!!
Boooooooooooooooooooooo

Woka

Well - Most of our New systems (infact all - no vb dev.) are web based systems so these problems are not as big now. Its just something that i struggled with about 3-4 years ago.

Anyway - Good Discussion.

[Wokawidget]

Everyone struggles with certain parts of VB at some point in time...I can remember when I got seriously confused about listviews Hahahahaha

ARPRINCE, have we answered your question???

Hope this thread helps.

Woka

[ARPRINCE]

Midway through this long thread, I decided to redo my form and not to dump 100K records back to it to be displayed one at a time. I do think that's the most efficient way.

Thanks to all for the ideas.