How do I read all records from a large database fast?

[fkauffman]

Hi,

I'm prototyping a a three-layer MTS database application, that works marvelous, but..
When I read all records from my large database (300.000 records), it will take some time before the information will be presented to the user.
I would like to receive my information more batchlike, so the user can read the first database records while the last records still being retrieved from the database.
In a one layer application (using a datagrid on a form) I can use the adSyncFetch - option to do the trick, but how do I get this result with three-layers?

All ideas are welcome,

Fedor

[Ianpbaker]

Hi fkauffman

Ahhhh! The Questtion to Life, The universe and everthing.

This is a real swine and I have yet to find a solution to this that I am completly happy with, I've had the same problem myself and the only way Iv'e been able to get around it is to have the function grab the data in chunks. If you haven't already got one put in a auto number field into your table
.
Whithin the function see how may records there are by doing a SELECT MAX(ID) FROM table then grab the data in chunks specified by you. fire an event when it has got each part of the information and return that part of the data. Then in your top tier, trap the event and update the relevant part of your forms.
This way the class runs in the background whilst the user can do other things. I think this method is call A-Synchronous but don't hold me to it.

This is a real Bugger to get to work but As I said, I cant find ant other way of doing it.

I suspect there is a better way and if anyone else can do it I will be most interested.

I'm Off to hide in a dark room in fear that I never have to project like that again.

Ian

[fkauffman]

Thanks Ian, for your moral support,

Glad to read your answer, because I was getting very frustrated by this dilemma. I could not imagine I was the first one to bounce into this problem.
I already use an autonumber-field in my database and also thought of getting the data in parts, but I was (just like you are) not very keen on the solution.
But now, happily knowing someone else thought a lot about this problem without getting the result he hoped for, I will stop looking for the perfect thing and just solve the problem.

Ofcourse, when I find a solution I will keep you informed...

Thanks again,

Fedor

[janilane]

Hi guys, I have the same problem too. I'm using Sybase server and the table contents reached 2.4 million records. Like both of you , I would like to present chunks of data to the user while background selection is transparent to him/her. Please advise should you find a solution to this. It's quite a problem for me because all the user sees is the hourglass pointer, without any data being displayed. aaaaahhhhhhhhhhh!!!!

p.s. I'm using a datagrid too for display ...

;)

[Dave OToole]

Oh, my God but I am SO going to get shot down in flames here but here goes.....

What sort of an application is that needs the user to be presented with 300,000 records (let alone 2.4 million) all at once?

I think the problem is that you are thinking too much like a programmer and NOT like a user.

For example, the user wants to change the price on a product. What does he / she do?

a. Think. "I think I'll change the price on a product. Now, let me see what have we got? Scroll, scroll. Oh yes, there's a nice one. Click. Change. Click.

b. Has a DEFINITE product in mind and knows the product code/ Keys it in. Gets record displayed in edit mode. Makes change.

The user has a customer on the phone enquiring about his/her account balance.

see next post

[Dave OToole]

a. Can you just bear with me one moment?....the computers very slow this morning. Ahh there.... Sorry, what was the name again.
Smith!??? Just one moment. Scroll, scroll 40 times , pause, pause. We've several Smiths are you postcode (zipcode) NL6 5TS, Is that T. Smith? Hello. Hello. Hello?????

b. Keys in something unique. E.g. Account Number. If user does not have the account number (they won't 9 times out of ten) something else in combination like name and birthday or name and postcode. Gets presented half a dozen records, at most.

Systems that present all the records and allow you to choose one are great for us. If we want to test the system we don't know the product code, neither do we want to key it in. Were not going to choose Mr Zapata at the end of the table in preference to Mr Anderson three records down from the top.

Users are an odd breed they know the product codes and they key them in using that keypad type thingy at the end of the keyboard. (If you let them). Scrolling screens are fine for forums no good for 9 out of 10 database applications.

Best of luck.

Dave

;)

[oh1mie]

Make a time test without activeconnection.
(uses memory, but should be fast)


Set Db = New ADODB.Connection
Set ado = New ADODB.Recordset

Db.CursorLocation = adUseClient
Db.Open strConnectString

ado.Open _
"SELECT * FROM MyTable" _
, Db, adOpenStatic, adLockReadOnly

ado.ActiveConnection = Nothing

' Check query with filter
ado.Filter = "Name='" & strName & "'"

'Break hier and check querytime

[janilane]

thanks a lot guys, what i'll do with 2.4 million records is to select and display based on a monthly basis, or better yet on a particular code. Actually what I have in mind is to sort of displat all those records at once to the user, kinda browser or stuff like that. Anyways, performance would suffer so might as well present it in chunks ...


regards :p