Solved

Huge Amount Of Data, Paging

Posted on 2004-08-30
14
1,642 Views
Last Modified: 2008-02-01
As you know, It causes problems to show huge amount of data (some millions records) on DBGrid. Especially, when users want to go to the last record, All of records are loading into the client machine. It seems to use stored procedures to handle with this situation! Maybe paging with stored procedure! I'm currently using SQL Server 2000. And of course there are some problems more.. e.g. Users want to find any record! Can you show me your approaches about these!?
0
Comment
Question by:wishy73
  • 6
  • 5
  • 3
14 Comments
 
LVL 27

Expert Comment

by:kretzschmar
Comment Utility
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 ;-)
0
 
LVL 17

Expert Comment

by:Wim ten Brink
Comment Utility
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...
0
 
LVL 27

Expert Comment

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

Author Comment

by:wishy73
Comment Utility
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?
0
 
LVL 27

Expert Comment

by:kretzschmar
Comment Utility
>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 ;-)
0
 
LVL 17

Accepted Solution

by:
Wim ten Brink earned 125 total points
Comment Utility
wishy73, the point that you're missing here is that no sane user will be looking at 3000 invoices or more just to look for a single invoice. Users are either interested in totals, statistics or in just a subselection of the latest information. Just assume a user needs to search for an invoice to customer X in city Y then you could display one million records but the user will never find it. No, the user would prefer to enter X and Y in some filter and then get a subset. If the subset is still to big, then the user has to see how to filter it a bit more.

In general, your grid will be showing 20 to 50 records onscreen, depending on the screen and font size. A user looking for a single record in 3000 records would have to scroll quite a long time to find whatever he's looking for. Compare it to Google. If you search for a word like "Pascal" then the resultset is about 3,360,000 records. And Google will just display you about 10 pages. You can't go to the last in any way. And users who will just walk through the million of pages generated by Google will be busy for a few weeks, so most users will add a second keyword if the first results generates too many hits.

The same is true with database applications with a huge amount of records in the back-end. No SANE user would like to see all those records. Also, looking at the "last" record is just arbitrary thing. Any record could become the last record in the list. Unless you sort it, the last record is unpredictable. But if you sort it, you're probably are already interested in a list of records within a certain range.

And trust me on this. I've done some research in this, talking with customers about their wishes and no one minded that the complete overview was truncated to a maximum amount. They all agreed that showing more than 1000 records would just be overkill. And if the user wants to see the last record, then just sort the list in reverse order, but still select the top-1000. It will then be the first record in the list...

No user will be browsing through millions of records manually. They might go 10 pages down, or to the end and 10 pages up. So they see about 10x2x50 lines, or 1000 records. So it's an ideal amount. If an user wants some specific information, like the biggest invoice or an invoice by customer X then filters would provide this information. And I know, filters are a real pain to write but no user wants to *really* look at millions of records...
0
 
LVL 27

Expert Comment

by:kretzschmar
Comment Utility
well spoken(written), alex :-))
0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 17

Expert Comment

by:Wim ten Brink
Comment Utility
Thanks, Meikl. You're not that bad yourself either. ;-)
0
 

Author Comment

by:wishy73
Comment Utility
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
0
 
LVL 17

Expert Comment

by:Wim ten Brink
Comment Utility
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...
0
 
LVL 27

Expert Comment

by:kretzschmar
Comment Utility
pu, no grade for me :-(
0
 

Author Comment

by:wishy73
Comment Utility
-> 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!
0
 
LVL 27

Expert Comment

by:kretzschmar
Comment Utility
>I did still not know.

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

>Sorry! Next time!
yep ;-)
0
 
LVL 17

Expert Comment

by:Wim ten Brink
Comment Utility
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.
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Introduction The parallel port is a very commonly known port, it was widely used to connect a printer to the PC, if you look at the back of your computer, for those who don't have newer computers, there will be a port with 25 pins and a small print…
In this tutorial I will show you how to use the Windows Speech API in Delphi. I will only cover basic functions such as text to speech and controlling the speed of the speech. SAPI Installation First you need to install the SAPI type library, th…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now