RE: UV secondary index
Two tricks to speed things up ... Firstly, would the NO.NULLS keyword speed things up? I'm guessing your fields can often be blank, and in this case you will have a single huge record knocking your index for six! Secondly, would the pause and resume indexing commands work? During the day, while the users are working, you pause index updates. At the end of the day (or whenever you need to run the reports) you resume indexing. Okay, it'll knacker the system while it sorts out the indices, but it'll be one short, concentrated hit. The you run your reports, disable indexing again, and let the users at it ... Cheers, Wol -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of [EMAIL PROTECTED] Sent: 09 February 2004 09:23 To: [EMAIL PROTECTED] Subject: UV secondary index Hello, I have distributed UV file with total size about 2.5Gb. I made secondary index CREATE. INDEX file field1 ... field6 BUILD.INDEX file field1 ... field6 After that, rows inserting takes very long time. (4000 rows. Server so busy, that other users can't do anything. If I removed index then inserting process is OK, but I need this index for reporting) Is it normal in UniVerse? How to avoid this problem? I use UniVerse 9.5.2 on AIX 5.2 Thanks, Margus Kandelin -- u2-users mailing list [EMAIL PROTECTED] http://www.oliver.com/mailman/listinfo/u2-users *** This transmission is intended for the named recipient only. It may contain private and confidential information. If this has come to you in error you must not act on anything disclosed in it, nor must you copy it, modify it, disseminate it in any way, or show it to anyone. Please e-mail the sender to inform us of the transmission error or telephone ECA International immediately and delete the e-mail from your information system. Telephone numbers for ECA International offices are: Sydney +61 (0)2 9911 7799, Hong Kong + 852 2121 2388, London +44 (0)20 7351 5000 and New York +1 212 582 2333. *** -- u2-users mailing list [EMAIL PROTECTED] http://www.oliver.com/mailman/listinfo/u2-users
UNCLASSIFIED RE: UV secondary index
Margus, -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of [EMAIL PROTECTED] Sent: Monday, 9 February 2004 10:23 p.m. To: [EMAIL PROTECTED] Subject: UV secondary index Hello, I have distributed UV file with total size about 2.5Gb. I made secondary index CREATE. INDEX file field1 ... field6 BUILD.INDEX file field1 ... field6 This syntax creates builds six separate secondary index files, so that inserting a row in the main file will create probably between six and eighteen additional I/O operations to the multiple indices. Is this what you intended? If you actually wanted to create a single secondary index based on a concatenation of the first six fields, you need to create an I-type which does the concatenation, then index on the I-type. If you do need six separate secondary indices, maybe you need to look at where the index files are placed (different disk channel from main file,etc.) Another possibility is that the data is very 'clumpy' on one or more index fields, so that a large proportion of the records share the same value for that index. When UV comes to add a value to the index, it has to scan through a huge set of identical values and then put the new occurrence at the end: this gets exponentially slower. If the shared value is null, then re-creating your index/indices with NO.NULLS will help. If it's not null (maybe it's a month number, so all records fall into only twelve distinct values?), then you need to find some way of creating a more-unique (e.g. month and day) indexable value. HTH Mike After that, rows inserting takes very long time. (4000 rows. Server so busy, that other users can't do anything. If I removed index then inserting process is OK, but I need this index for reporting) Is it normal in UniVerse? How to avoid this problem? I use UniVerse 9.5.2 on AIX 5.2 Thanks, Margus Kandelin The information contained in this Internet Email message is intended for the addressee only and may contain privileged information, but not necessarily the official views or opinions of the New Zealand Defence Force. If you are not the intended recipient you must not use, disclose, copy or distribute this message or the information in it. If you have received this message in error, please Email or telephone the sender immediately. -- u2-users mailing list [EMAIL PROTECTED] http://www.oliver.com/mailman/listinfo/u2-users