Huge Amount Of Data, Paging

usual to find the last record all records must be fetched from the server,
this means not, that all records may be stored  on clientside.

- to make it faster provide a whereclause with your dataset
- to avoid that a user retrieves all records, disallow fetch without a whereclause-condition
- supply the fields, on which are searches performed with indexes

meikl ;-)

Alas, meikl is right... I've had to deal with the same problem. The only solution would be to divide the resultset by using a "WHERE" clause. But keep in mind that no sane user would like to see a list of 2 million records in his screen anyway. No sane person will be looking through all this just to find whatever he needs.

My solution was a bit easier... I limited the resultset to a maximum of 1000 records, to reduce the size of the resultset. (select top 1000 ...) And if you allow wildcards in the filter, the user should have no problems doing a more exact search. No use of presenting the user to see more than 1000 records because users search a lot slower than computers...

>select top 1000 ...
well, thats a good idea, not available for all database-systems,
but as it is sql server 2000, it should work

Those are not enough solutions for me! Of course I can limit the result set with select top 1000 .... or filter properties or even not without using what I have mentioned.

Think that view for a while!

There are many invoices for a day (e.g. apprx 1500 per a day) and I limited dataset to 3000 records (2 days). A user billed a new one belonged to 4 days ago ;) We can't show that invoice on the grid!

I need to get records page by page with stored procedure. That's the only way I can imagine for now. Then, it is not important how many records there are in DB ;-) Anyone uses that kind of behaviour(page by page logic) on DBGrid?

>There are many invoices for a day (e.g. apprx 1500 per a day) and I limited >dataset to 3000 records (2 days). A user billed a new one belonged to 4 days >ago ;) We can't show that invoice on the grid!

don't think, that a top limit avoids a view to older (in your case) invoices,
it just says fetch the first 1000 records of my select. so to have older invoices,
just include a whereclause like (simplified, because of syntax not in mind)

select top 1000 from my invoicesTable where InvoiceDate = now - 2 order by InvoiceDate desc

will fetch you the first 1000 records from before two days

another possibility would be to use a datetimepicker where the user can select a date, and you can based on this selection limit the resultset to this selected date.

just use a select like

select * from InvoicesTable where InvoiceDate = :SelectedDate

just adjust the parameter SelectedDate in the onChange-Event of the DateTimePicker like

begin
  adoquery1.close;
  adoquery1.params[0].Value := DateToStr(DateTimePicker1.Date);
  adoquery1.open;
end;

code above may have errors (just from had)
just as suggestion

meikl ;-)

well spoken(written), alex :-))

Thanks, Meikl. You're not that bad yourself either. ;-)

Alex, Completely agree with you! But you're missing something too! If you put a DBGrid on your form, it has to be worked the way how it has to be worked! If you can't browse all records, why do you put one!? If you query records and show them on a Grid, I understand! Because you selected some records! Even under this circumstance, there are some problems if there are 20000 records queried! Meanwhile, after querying, you do how they have to be shown.

I am going to continue to work on parametrized stored procedure + grid to show all records page by page as I have wanted to do!

Thank you very much for letting me know your approaches!

Regards,

Wishy

In general people use grids to see just a small list. With the filters you just create a small selection of what you want to see, then the grid can be used to display all related records to this selection. If the list is still too big, no user will even bother looking through the list. They'd just add more to the filter. So say you want to see all invoices of today. The system then displays 1000 records and warns you there are more than 1000 records available. (Easy to do with a "select count(*) from whatever".) Thus the user knows he has to be more specific. So, he filters on location, selecting all invoices from cities starting with an "A". (where cityname like "A%") and then the list of 20000 records might be reduced to 3000. Still too much for the user to browse through correctly. So perhaps two letters for the city. Like "AM". And now the list might be reduced to 400 records, which is still a lot but it's controllable. If the user finishes to type the city name "AMSTERDAM" then the list might be limited to 20 invoices, which fits nicely on one page. And now the user has a usable list!

If you're using ADO, and you're probably are, then the biggest problem you face is the lack of paging support in the Delphi ADO components so step one would be using the ADO COM components directly from code. You can still connect them to a TADODataset if needed, though. The next problem would be dealing with those pages and if my memory is correct, then you cannot browse backwards through those ADO pages. Thus, the user can go to the end, but never return to the top again without executing the query again.

Of course, you could use multiple small queries to get the next pages. Still selecting the top-1000 only but the table is sorted on some ID field. And the next page are all top-1000 records where the ID is bigger than the highest ID on the previous page.

And your biggest problem will always be knowing where the last page is...

pu, no grade for me :-(

-> Workshop_Alex

Some days ago, I decompiled a program written with Delphi. It was using an interesting method to call records into DBGrid. Stored procedures.. It is an Enterprise Application. It is working quite well!!

I wondered if there were some ppl work on this way. Surely, it is so nice way to call records page by page (seperating 50 or 100 records in one time) via procedures. It is faster than getting 1000 records. Besides that, you can navigate among all records easily and you can fetch records very fast into client! There will be no performance problem.

Think another way... Maybe you query some records of a customer and then bring them onto DBGrid and then you wanted to see the first operation date of that customer! You can order backward and then get the first operation date! or you can use another query! Anyway... There are of course many ways to do that. But moving on a DBGrid is the most preferred way by me! Because I don't want to limit users as navigation! Or that example can make many.. Believe me! What I wanted will be necessary for you too!

In the meantime, sometimes, ppl are strange! They can want different things! Some people can want to see the first record like me! :))

-> kretzschmar

I'm a newbie on this site! So I couldn't give you any point! Because I didn't know how to share those points between you and alex. I did still not know. Sorry! Next time!

>I did still not know.

read this instruction
https://www.experts-exchange.com/Programming/Programming_Languages/Delphi/help.jsp#hi19

>Sorry! Next time!
yep ;-)

Wishy,

As I said, there is a way to get a huge recordset into pages but the trick is to get still a limited set of records all the time. Say to have a table of article ID's and names. So the first time you select the first 100 records, with ArticleID's 1 to 100. When the user goes to the next page, you select article ID's 101 to 200. Or if article's 140 to 150 are missing then you'd select ArticleID's 101 to 210. So in general you take the highest ID on your current page and select the top-100 records with an ID bigger than this ID. If the user goes in the other direction, you select the bottom-100 records where the ID is LOWER than the lowest ID on your page. In pseudocode, something like this:

if PageUp then
  select top-100 * from Table where ID > MaxID
elseif PAgeDown then
  select bottom-100 * from table where ID < MinID

MaxID and MinID are the biggest and lowest ID's on your current page...

It can be done this way, indeed. But in general this is quite a lot of work for functionality that no one is really interested in. Sure, there could be persons who want to count if there are really 2.954.776 records in your table thus they go to the top page and scroll down to the end. But most users... No need to repeat myself, do I? ;-)

Now, another problem would be navigation speed... With 100 records per page you'd have 10,000 pages to view. Means that a user who'se quickly walking through those pages would execute your query 10,000 times at least to view everything. With one second per page that user would be spending 2 hours and 45 minutes to see everything. An employee at my companee would quickly end up unemployed that way. It's not efficient.