How to get 7 values out of more than 2'000'000 documents

HCL Domino Domino Forum
1

I have an agent which is accessing a db with more than 2’000’000 documents. Those documents contain a field user and a field category. The documents contain log data.What I need to do is to find all categories from one user. A user has about seven different categories. I do not really want to go over a view because there are tousands of users.Maybe there is a a way to create a dynamic view.

I tried also to work with a view contain the user’s name as a part of the selection formula. The problem is there that update of the view index.

So if anybody has nice idea or tip I really would appreciate because I am stuck a little bit.

Thanks in advance

2

Subject: How to get 7 values out of more than 2’000’000 documents

Instead of creating a new view (which will adversely impact performance) you may consider creating a full text index. Then you can write a script to search out your 7 values on the fly. The values can be printed into a form designed as a report and displayed in the UI but never saved.

If you do go with the view, be sure to turn on the db property “Optimize document table map.”

3

Subject: RE: How to get 7 values out of more than 2’000’000 documents

I want to disagree with this response – and I can! :slight_smile:

Adding one view sorted by user ID will not affect performance all that much. How many views does this database need? Maybe two in all? Does it even need another besides this one?

With a full-text index, it depends on what they’re using for usernames, but the search will not necessarily return only the documents belonging to one user. E.g. if the username is “William Hoople”, then the search [username] = “William Hoople” will also return records belonging to “James William Hoople”, so the records require some post-processing to weed out the false matches.

The view also gives you the opportunity to do some extra sorting of the records (e.g. by date) so that you don’t have to sort them in your program – full-text results, on the other hand, you don’t have so much control over what order you get them in.

4

Subject: RE: How to get 7 values out of more than 2’000’000 documents

There does come a point when a Notes database is too bloated and server performance and diskspace limitations preclude adding more views. 100 hours of processing time and 16GB of new indexes would certainly cross the line for some shops. In contrast, full text indexing does not affect performance at all and at least the index is stored as separate files. Isn’t it great that there are so many ways to approach a problem in Domino?

5

Subject: RE: How to get 7 values out of more than 2’000’000 documents

Searching through millions of documents takes time no matter how you do it. If you create a view on the fly, it’s going to take a long time to construct the initial index, because even if the selection formula will result in just 6 documents, the server must still examine each document to see whether it meets the selection criteria.

The most efficient way is to keep a permanent view sorted by user – then you can use GetAllDocumentsByKey to pull in a collection of all the user’s documents at once – or even use @DbLookup to retrieve the result you’re asking for with a single line of macro code. It takes a while to create the view index initially, but it’s comparatively quick – and fully automatic – to update the index with any changed documents since the last time the view was used.

A reflection about this design: from the little you’ve said, it sounds like your application is overly relational in structure. Your application would operate far more efficiently if you had one document for each user, which listed all their categories in a multivalue field. You can still create categorized or sorted views that sort users by category, etc. if you do this.

A full-text index is also reasonably efficient to use, but not as precise as a view key search.

6

Subject: RE: How to get 7 values out of more than 2’000’000 documents

So I am a little bit further now.

To clear the situation a little bit:

The documents in the log database contain a field ‘Username’. This field is simple made up of a String like ‘Christian Schuster’ and is used to identify to whom the log document belongs.

For each user exists two documents. One is in the the public address book for athentication. The other one is in another database. This document contains specific information about the user which is needed to run the web app.

Until now there was no need for this category as said, thus, not any IT who was developing this app has thought about this before. In a way a little bit sad because it would not have hurt at all to store the categories, a user is using, in his specific document.

Anyway, I created a view with two columns the first is the user name and the second the category. The second column is categorized. To build the view index took about three hours which is even not so terrible.

Then I wrote an agent which gets this view and gets all documents by key which was quite fast. This way I have already all values which I need. Those values I save then in the users specific document.

This way the agent needs just 25 mins to process 1000 users. So, at the end I will need around 1h15min to process one database including the build time of the view around 4h15min for a database. By around 20dbs I will need about 100h. With this solution I can live, although the size of the view is huge(800MB). But at the end I can delete this view again.

A final word:

I appreciated all the nice help here which gave me some really good tips how to do it. I also try as far as I can to help other people. But I for sure NEVER NEVER NEVER write that something is not possible. This is just soooo stupid. We are here to help each other, thus, just constructive comments are welcome!!!

Also, we are using now domino and we cannot just move the whole web app into another system which would need much more time to move and set up than the solution now!!!

Regards,

7

Subject: RE: How to get 7 values out of more than 2’000’000 documents

Try this…

Since you’ve got the view in the log database that shows the log items categorized by the user name, find some place in the interface (user preferences?) to put a subform in that has an embedded view. Point the view control to the new categorized view. Set the control to “show only selected category.” Specify the formula as the user’s name (@Name([CN]; @UserName).

Fire this up in the browser and see how it looks. What’s the response time? You could do quite a lot with this style of interface.

8

Subject: RE: How to get 7 values out of more than 2’000’000 documents

Thanks a lot Andre for your helpful answer.

I will try your sugestions with a view sorted by user. This can be the trick.

I was also thinking about the putting the categories in the users document. The only thing is here that there is a log db for each year. So, if I want to know all the categories of a user I have to run over all dbs and there are 20 of them and all have more than 2’000’000 documents and this for several tousand users. Even if we have quite good hardware this could take for ever.

Well, here is also the question whether it needs to be for all years or just from now on. But this is not my decision due I am the programer.

I will try to explain the problem a little bit better.

In our web app the users can search for information. Each search is done with a specified categories specified by the user. So, some user’s have 100 categories and others just two or three. The information the searched is charged and thus logged in this database which is FT indexed.

Now, there was the request of some users to see the charges of them online. They also would like to see the cost split by the categories, month and search type(there are four different information systems)

So I need to get all charge documents of a user grouped by category and then grouped by date and at the end also grouped by search type.

I will do some test now with your suggestions and see what is the fastest way. For sure any solution will need time to get the information.

It would be a nice feature to use some basic SQL statements on a view or documents in a database or collection, would not be?

9

Subject: RE: How to get 7 values out of more than 2’000’000 documents

Anything that deals with a document set of 2,000,000 must be done outside Notes.

I think your last statement confirms my view.

It is unlikely taht you will get reasonable response time dealing only in Notes.

10

Subject: RE: How to get 7 values out of more than 2’000’000 documents

Nonsense. Notes has been able to handle this kind of volume for some time. Ten times that much and you might run into problems. But 2 million documents are easily supported in a well-designed application. When we take into account the fact that these databases are logs, and therefore the growth rate is known and prior information is not subject to change, there’s no reason not to do it this way. 2 million docs over a year is less than 10000 per day, which (assuming a 16 hour usage period) is about 625 per hour, which is about 10 a minute. Well-within the capacity of the Domino indexer, particularly if you bother to schedule an UPDALL at night to ensure up-to-date indexes in current logs.

That being said, Andre is quite right in his remark that this would be better done by not creating an individual document for each search log, and instead storing global line item documents that record search information in list structures within single documents. Undoubtedly this system already has profile documents for users of some kind, and any individual user is not likely to have performed more than 1000 searches or so in a given year. So recording the logs in that user profile should be easy, and would make searching for a given user pretty much instantaneous – since you’re only resorting list structures within a single document context, instead of sorting millions of documents.

But it’s not necessary. If your log databases have an appropriate structure to them, and you build two or three views with intelligent category structures, this should all be very easy through embedded views and “show single category.”