Re: [U2] Slow READ/WRITE with indexes (Nick Gettino)
Hundreds of characters.
My example concatenates a bunch of attributes like name and address together to
see if there are any matching records already on file, before creating a new
record. I set the alternate key length to 160 for this one index, and nothing
bad happened (so far).
I use that with the setindex, readfwd, etc unibasic commands in a subroutine to
return the matching record id, or nothing if not found.
Haven't had a need to go any bigger than that.
-Original Message-
From: u2-users-boun...@listserver.u2ug.org
[mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Wally Terhune
Sent: Friday, September 10, 2010 4:04 PM
To: U2 Users List
Subject: Re: [U2] Slow READ/WRITE with indexes (Nick Gettino)
How long of a data string to you want to index?
This mostly has a bearing on the block-size of the index file (the key length
setting)
Wally Terhune
U2 Support Architect
Rocket Software
4600 South Ulster Street, Suite 1100 **Denver, CO 80237 **USA
Tel: +1.720.475.8055
Email: wterh...@rs.com
Web: www.rocketsoftware.com/u2
-Original Message-
From: u2-users-boun...@listserver.u2ug.org
[mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Dave Davis
Sent: Friday, September 10, 2010 1:24 PM
To: U2 Users List
Subject: Re: [U2] Slow READ/WRITE with indexes (Nick Gettino)
Does building the biggest first really matter, or is it more important to just
put the right value in the key size prompt you get asked for on the first
index you create?
Also, does anyone know in UniData how big a key size you can give on an index?
I know it's bigger than the 120-something you are limited to for the file key,
but I've never seen a documented limit.
-Original Message-
From: u2-users-boun...@listserver.u2ug.org
[mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Nick Gettino
Sent: Friday, September 10, 2010 3:19 PM
To: u2-users@listserver.u2ug.org
Subject: [U2] Slow READ/WRITE with indexes (Nick Gettino)
On Unidata you can do a LIST.INDEX 'FILENAME' ALL STATS
This will give you each index the number of keys how many are in overflow and a
couple of other things.
If you can't tell from that command use
LIST.INDEX 'FILE.NAME' ALL DETAILS
This will show you how many records per key and whether the index item is in
overflow.
As mentioned use the NO.NULLS and or NO.DUPS to build the index.
And this has been a killer for us. You have to ALWAYS build the biggest index
first.
By biggest I mean the dictionary length being used.
Example. If you have city, state zip, order# and date. 40L, 15R, 8R
respectively that is how they should be built.
Nicholas M Gettino - Director of Support Professional Services | EnRoute
Emergency Systems an Infor Company, 401 E Jackson Street, Suite 1500, Tampa
Florida 33602 | Office 813-207-6998 |Fax 678-393-5389
|nick.gett...@enroute911.com / www.enroute911.com
Register Now- Early Bird Discount Available |
enroute911.com/user-conference-2010 | EnRoute User Conference | Disney's Yacht
Club Resort, Lake Buena Vista, FL | September 28- October 1, 2010
-Original Message-
From: u2-users-boun...@listserver.u2ug.org
[mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of
u2-users-requ...@listserver.u2ug.org
Sent: 09/10/2010 3:00 PM
To: u2-users@listserver.u2ug.org
Subject: U2-Users Digest, Vol 17, Issue 8
Send U2-Users mailing list submissions to
u2-users@listserver.u2ug.org
To subscribe or unsubscribe via the World Wide Web, visit
http://listserver.u2ug.org/mailman/listinfo/u2-users
or, via email, send a message with subject or body 'help' to
u2-users-requ...@listserver.u2ug.org
You can reach the person managing the list at
u2-users-ow...@listserver.u2ug.org
When replying, please edit your Subject line so it is more specific
than Re: Contents of U2-Users digest...
Today's Topics:
1. Re: Sequential Files Question (Colin Alfke)
2. Re: Sequential Files Question (Bill Haskett)
3. Re: Slow READ/WRITE with indexes (Ryan M)
4. Re: Slow READ/WRITE with indexes (Ryan M)
5. Re: Slow READ/WRITE with indexes {Unclassified}
(HENDERSON MIKE, MR)
6. Re: Slow READ/WRITE with indexes (Colin Alfke)
7. Re: Sequential Files Question (Colin Alfke)
8. Re: Sequential Files Question (Bill Haskett)
9. Re: Slow READ/WRITE with indexes {Unclassified} (Wols Lists)
10. Re: Slow READ/WRITE with indexes {Unclassified} (Dan McGrath)
11. Re: Slow READ/WRITE with indexes (Boydell, Stuart)
12. Re: Slow READ/WRITE with indexes (Ryan M)
13. Re: Slow READ/WRITE with indexes {Unclassified} (Ryan M)
14. Re: Slow READ/WRITE with indexes (Ryan M)
15. UD - XDOMLocate problem - solved (Edward Brown)
16. Re: Slow READ/WRITE with indexes {Unclassified} (Wols Lists)
--
Message: 1
Date: Thu, 9 Sep 2010 13:02:10 -0600
From: Colin Alfke alfke...@hotmail.com
To: 'U2 Users List' u2-users@listserver.u2ug.org
Subject
Re: [U2] Slow READ/WRITE with indexes
Ryan, You've gotten some excellent suggestions here. Hopefully one or more will help. I've faced this same situation in EOM processing. What I ended up doing was copying the items from the main file (indexed) to a work file (not indexed) without deleting. I then fired off a phantom which copied the items from the work file to the history file (indexed), then deleted them from the main file. EOM was able to continue on after the items were first copied out. The index didn't interfere (in Jbase, at least), because no index updating was happening. As long as the items were out of the main file and into the history file before they were needed, all was OK. This required that I move the process toward the beginning of EOM, though. As always, your mileage may vary. Regards, Charlie Noah The views and opinions expressed herein are my own (Charlie Noah) and do not necessarily reflect the views, positions or policies of any of my former, current or future employers, employees, clients, friends, enemies or anyone else who might take exception to them. On 09-09-2010 12:28 PM, Larry Hiscock wrote: Are any of your indices based on virtual fields? I haven't worked with UV in a while, but UD has the DISABLE.INDEX, ENABLE.INDEX and UPDATE.INDEX commands. If UV also has them, you could disable the indexes prior to the archival and re-enable and update them at the end. I'm not sure if it would be any faster, but certainly worth an attempt. Larry Hiscock Western Computer Services -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Ryan M Sent: Thursday, September 09, 2010 10:07 AM To: u2-users@listserver.u2ug.org Subject: [U2] Slow READ/WRITE with indexes I am hoping I can find some help here. I am running into a serious performance issue with indexes on our UV system (UV 10.2, on AIX). An example of this is our sales order files, SO (current/active) and SOH (history) files. We archive sales orders from SO to SOH on a daily basis, this is moving approx 15,000 records from one file to the other. If I remove all indexes from both files, the process flies by, hundreds of transactions per second. But, with indexes on, we are luck to get one per second. Basically what happens with the code that does the archiving is it reads the SO record, does some quick checks to make sure we can move it, then writes the record to SOH, then deletes the SO. There are 7 indexes on the SO file and 5 on the SOH. I've tried removing one index from a file and running the process, but see no performance gain until all indexes are gone. The SO file is approx 7GB with 280k records, and the SOH file is 11GB, with 8,900,000 records I have check the files sizes in UV and they are correct. Can anyone provide some pointers on ways to setup indexes so they will run faster? Some of the indexes are 'ORDER.DATE', 'COUNTRY', 'MEMBER ID' ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Slow READ/WRITE with indexes
Thanks, this what I'm finding too, and it's really bad news. Our database needs to be available 24/7 due to being in the international market. When disabling the index on the SO, then enabling and updating, the update causes locks the file - not good as our company runs off the SO file. Locking that file means we have scheduled downtime where we do no make money, the Powers That Be won't have any of that. Boydell, Stuart wrote: I have found that deletes are the hardest on index updating, slower than inserts. If you can, I'd specifically disable the delete from the SO file to see what change that makes. Cheers Stuart -Original Message- Basically what happens with the code that does the archiving is it reads the SO record, does some quick checks to make sure we can move it, then writes the record to SOH, then deletes the SO. There are 7 indexes on the SO file and 5 on the SOH. ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users -- View this message in context: http://old.nabble.com/Slow-READ-WRITE-with-indexes-tp29653705p29676533.html Sent from the U2 - Users mailing list archive at Nabble.com. ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Slow READ/WRITE with indexes
Not sure what a vitural field is, is that like a foreign key? There is one index that is setup weird. Let me see if I can explain. We have multiple accounts of UV, one of them is a sql account, we'll call that uvsql. The SO table that is there has a dictionary/index of MEM_ID. That dictionary does not exist in the main account, uvmain. In the uvsql account an index was created called MEM_ID. There is no dictionary with that name but rather the dictionary is MEM.ID and SQL has problems with the . in the name. Would it be better to copy the dictionary MEM.ID to MEM_ID then re-create the index? Larry Hiscock wrote: Are any of your indices based on virtual fields? I haven't worked with UV in a while, but UD has the DISABLE.INDEX, ENABLE.INDEX and UPDATE.INDEX commands. If UV also has them, you could disable the indexes prior to the archival and re-enable and update them at the end. I'm not sure if it would be any faster, but certainly worth an attempt. Larry Hiscock Western Computer Services -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Ryan M Sent: Thursday, September 09, 2010 10:07 AM To: u2-users@listserver.u2ug.org Subject: [U2] Slow READ/WRITE with indexes I am hoping I can find some help here. I am running into a serious performance issue with indexes on our UV system (UV 10.2, on AIX). An example of this is our sales order files, SO (current/active) and SOH (history) files. We archive sales orders from SO to SOH on a daily basis, this is moving approx 15,000 records from one file to the other. If I remove all indexes from both files, the process flies by, hundreds of transactions per second. But, with indexes on, we are luck to get one per second. Basically what happens with the code that does the archiving is it reads the SO record, does some quick checks to make sure we can move it, then writes the record to SOH, then deletes the SO. There are 7 indexes on the SO file and 5 on the SOH. I've tried removing one index from a file and running the process, but see no performance gain until all indexes are gone. The SO file is approx 7GB with 280k records, and the SOH file is 11GB, with 8,900,000 records I have check the files sizes in UV and they are correct. Can anyone provide some pointers on ways to setup indexes so they will run faster? Some of the indexes are 'ORDER.DATE', 'COUNTRY', 'MEMBER ID' -- View this message in context: http://old.nabble.com/Slow-READ-WRITE-with-indexes-tp29653705p29653705.html Sent from the U2 - Users mailing list archive at Nabble.com. ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users -- View this message in context: http://old.nabble.com/Slow-READ-WRITE-with-indexes-tp29653705p29676602.html Sent from the U2 - Users mailing list archive at Nabble.com. ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Slow READ/WRITE with indexes {Unclassified}
On 10/09/10 13:58, Ryan M wrote: So should the indexes be created with the NO.NULLS options? I can rebuild the indexes on our dev system with the NO.NULLs option as I'm pretty sure they are not using it. If any of the fields don't have any data in them they will index to null. This often results in a massive index record (and as others have said, massive index records clobber performance). NO.NULLS just stops any null records from being indexed. So if the index fields should always contain data of some sort, then the NO.NULLS option shouldn't make any difference. If the fields are often left blank, then yes it's a good idea to use the NO.NULLS option. Be aware, though, that one of the side effects is if you select for that field being blank, the index won't be used... If a blank entry is an error but sometimes happens and you want to fix them, then you don't want to use NO.NULLS because it makes finding the errors easier. Cheers, Wol Wols Lists wrote: On 09/09/10 21:29, HENDERSON MIKE, MR wrote: Ryan, You said you had indexes like 'ORDER.DATE', 'COUNTRY', 'MEMBER ID'. If those are really the full index data items, you could have thousands, tens of thousands, or even hundreds of thousands, of records with the identical index data item value in the SOH file. If that's the case, updates (inserts) may well be s-l-o-w as the system churns through many, many, many linked buffers of identical valued keys looking for an insert point. That was my thinking too. The other, related point. Does UD have NO.NULLS? And are you using it? If you haven't used it that's the most common performance-killer with indices out there ... Cheers, Wol ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
[U2] Slow READ/WRITE with indexes (Nick Gettino)
all about pipe didn't it used to be | ? btw, even though I'm the admin on my laptop, it will not allow me to even use notepad to save to c:\buddy.txt Must be a new windows 7 thing. ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users -- Message: 3 Date: Thu, 9 Sep 2010 13:10:54 -0700 (PDT) From: Ryan M rmcmul...@nsai.com To: u2-users@listserver.u2ug.org Subject: Re: [U2] Slow READ/WRITE with indexes Message-ID: 29670902.p...@talk.nabble.com Content-Type: text/plain; charset=us-ascii I'm trying this now, thanks. Larry Hiscock wrote: Are any of your indices based on virtual fields? I haven't worked with UV in a while, but UD has the DISABLE.INDEX, ENABLE.INDEX and UPDATE.INDEX commands. If UV also has them, you could disable the indexes prior to the archival and re-enable and update them at the end. I'm not sure if it would be any faster, but certainly worth an attempt. Larry Hiscock Western Computer Services -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Ryan M Sent: Thursday, September 09, 2010 10:07 AM To: u2-users@listserver.u2ug.org Subject: [U2] Slow READ/WRITE with indexes I am hoping I can find some help here. I am running into a serious performance issue with indexes on our UV system (UV 10.2, on AIX). An example of this is our sales order files, SO (current/active) and SOH (history) files. We archive sales orders from SO to SOH on a daily basis, this is moving approx 15,000 records from one file to the other. If I remove all indexes from both files, the process flies by, hundreds of transactions per second. But, with indexes on, we are luck to get one per second. Basically what happens with the code that does the archiving is it reads the SO record, does some quick checks to make sure we can move it, then writes the record to SOH, then deletes the SO. There are 7 indexes on the SO file and 5 on the SOH. I've tried removing one index from a file and running the process, but see no performance gain until all indexes are gone. The SO file is approx 7GB with 280k records, and the SOH file is 11GB, with 8,900,000 records I have check the files sizes in UV and they are correct. Can anyone provide some pointers on ways to setup indexes so they will run faster? Some of the indexes are 'ORDER.DATE', 'COUNTRY', 'MEMBER ID' -- View this message in context: http://old.nabble.com/Slow-READ-WRITE-with-indexes-tp29653705p29653705.html Sent from the U2 - Users mailing list archive at Nabble.com. ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users -- View this message in context: http://old.nabble.com/Slow-READ-WRITE-with-indexes-tp29653705p29670902.html Sent from the U2 - Users mailing list archive at Nabble.com. -- Message: 4 Date: Thu, 9 Sep 2010 13:12:43 -0700 (PDT) From: Ryan M rmcmul...@nsai.com To: u2-users@listserver.u2ug.org Subject: Re: [U2] Slow READ/WRITE with indexes Message-ID: 29670926.p...@talk.nabble.com Content-Type: text/plain; charset=us-ascii The indexes have been in place for some time now (1+ years) I'm guessing 10k to 15k new records per day is fairly high volume (this does not include changes to existing records). bradley.schrag wrote: How long have these indexes been in place? If they're new, that's one thing. If they've been in place for a while and this is a change in behavior we may need to look in different areas. FYI, on ud I've had performance issues when going above five indexes on a given file when I have a high volume of transactions. Brad. U.S. BANCORP made the following annotations - Electronic Privacy Notice. This e-mail, and any attachments, contains information that is, or may be, covered by electronic communications privacy laws, and is also confidential and proprietary in nature. If you are not the intended recipient, please be advised that you are legally prohibited from retaining, using, copying, distributing, or otherwise disclosing this information in any manner. Instead, please reply to the sender that you have received this communication in error, and then immediately delete it. Thank you in advance for your cooperation. - ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users -- View this message in context: http
Re: [U2] Slow READ/WRITE with indexes (Nick Gettino)
Does building the biggest first really matter, or is it more important to just
put the right value in the key size prompt you get asked for on the first
index you create?
Also, does anyone know in UniData how big a key size you can give on an index?
I know it's bigger than the 120-something you are limited to for the file key,
but I've never seen a documented limit.
-Original Message-
From: u2-users-boun...@listserver.u2ug.org
[mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Nick Gettino
Sent: Friday, September 10, 2010 3:19 PM
To: u2-users@listserver.u2ug.org
Subject: [U2] Slow READ/WRITE with indexes (Nick Gettino)
On Unidata you can do a LIST.INDEX 'FILENAME' ALL STATS
This will give you each index the number of keys how many are in overflow and a
couple of other things.
If you can't tell from that command use
LIST.INDEX 'FILE.NAME' ALL DETAILS
This will show you how many records per key and whether the index item is in
overflow.
As mentioned use the NO.NULLS and or NO.DUPS to build the index.
And this has been a killer for us. You have to ALWAYS build the biggest index
first.
By biggest I mean the dictionary length being used.
Example. If you have city, state zip, order# and date. 40L, 15R, 8R
respectively that is how they should be built.
Nicholas M Gettino - Director of Support Professional Services | EnRoute
Emergency Systems an Infor Company, 401 E Jackson Street, Suite 1500, Tampa
Florida 33602 | Office 813-207-6998 |Fax 678-393-5389
|nick.gett...@enroute911.com / www.enroute911.com
Register Now- Early Bird Discount Available |
enroute911.com/user-conference-2010 | EnRoute User Conference | Disney's Yacht
Club Resort, Lake Buena Vista, FL | September 28- October 1, 2010
-Original Message-
From: u2-users-boun...@listserver.u2ug.org
[mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of
u2-users-requ...@listserver.u2ug.org
Sent: 09/10/2010 3:00 PM
To: u2-users@listserver.u2ug.org
Subject: U2-Users Digest, Vol 17, Issue 8
Send U2-Users mailing list submissions to
u2-users@listserver.u2ug.org
To subscribe or unsubscribe via the World Wide Web, visit
http://listserver.u2ug.org/mailman/listinfo/u2-users
or, via email, send a message with subject or body 'help' to
u2-users-requ...@listserver.u2ug.org
You can reach the person managing the list at
u2-users-ow...@listserver.u2ug.org
When replying, please edit your Subject line so it is more specific
than Re: Contents of U2-Users digest...
Today's Topics:
1. Re: Sequential Files Question (Colin Alfke)
2. Re: Sequential Files Question (Bill Haskett)
3. Re: Slow READ/WRITE with indexes (Ryan M)
4. Re: Slow READ/WRITE with indexes (Ryan M)
5. Re: Slow READ/WRITE with indexes {Unclassified}
(HENDERSON MIKE, MR)
6. Re: Slow READ/WRITE with indexes (Colin Alfke)
7. Re: Sequential Files Question (Colin Alfke)
8. Re: Sequential Files Question (Bill Haskett)
9. Re: Slow READ/WRITE with indexes {Unclassified} (Wols Lists)
10. Re: Slow READ/WRITE with indexes {Unclassified} (Dan McGrath)
11. Re: Slow READ/WRITE with indexes (Boydell, Stuart)
12. Re: Slow READ/WRITE with indexes (Ryan M)
13. Re: Slow READ/WRITE with indexes {Unclassified} (Ryan M)
14. Re: Slow READ/WRITE with indexes (Ryan M)
15. UD - XDOMLocate problem - solved (Edward Brown)
16. Re: Slow READ/WRITE with indexes {Unclassified} (Wols Lists)
--
Message: 1
Date: Thu, 9 Sep 2010 13:02:10 -0600
From: Colin Alfke alfke...@hotmail.com
To: 'U2 Users List' u2-users@listserver.u2ug.org
Subject: Re: [U2] Sequential Files Question
Message-ID: snt139-ds5e54ede19399d72b0201898...@phx.gbl
Content-Type: text/plain; charset=us-ascii
The pipe is different. I use it to send output as input to other commands:
!LISTUSER | MORE or
!LISTUSER | FIND COLIN /I
But then I've only been really using DOS since 3.11
I've noticed Win 7 hides the root of C:, but I didn't realize that it
wouldn't let you create a file.
Colin
-Original Message-
From: Allen Elwood
I've been using DOS since 1.0 - forgot all about pipe didn't it
used to be | ?
btw, even though I'm the admin on my laptop, it will not allow me to
even use notepad to save to c:\buddy.txt
Must be a new windows 7 thing.
--
Message: 2
Date: Thu, 09 Sep 2010 12:33:19 -0700
From: Bill Haskett wphask...@advantos.net
To: U2 Users List u2-users@listserver.u2ug.org
Subject: Re: [U2] Sequential Files Question
Message-ID: 4c89367f.6020...@advantos.net
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Colin:
I wonder if Windows 7 complains about any writes to the C:\ drive.
This would be a good thing, but still, some applications install into
C:\Program Files (x86), so that directory must allow writes along with
C:\ProgramData (notice how they seem to have removed their heads from
that dark space
Re: [U2] Slow READ/WRITE with indexes (Nick Gettino)
How long of a data string to you want to index?
This mostly has a bearing on the block-size of the index file (the key length
setting)
Wally Terhune
U2 Support Architect
Rocket Software
4600 South Ulster Street, Suite 1100 **Denver, CO 80237 **USA
Tel: +1.720.475.8055
Email: wterh...@rs.com
Web: www.rocketsoftware.com/u2
-Original Message-
From: u2-users-boun...@listserver.u2ug.org
[mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Dave Davis
Sent: Friday, September 10, 2010 1:24 PM
To: U2 Users List
Subject: Re: [U2] Slow READ/WRITE with indexes (Nick Gettino)
Does building the biggest first really matter, or is it more important to just
put the right value in the key size prompt you get asked for on the first
index you create?
Also, does anyone know in UniData how big a key size you can give on an index?
I know it's bigger than the 120-something you are limited to for the file key,
but I've never seen a documented limit.
-Original Message-
From: u2-users-boun...@listserver.u2ug.org
[mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Nick Gettino
Sent: Friday, September 10, 2010 3:19 PM
To: u2-users@listserver.u2ug.org
Subject: [U2] Slow READ/WRITE with indexes (Nick Gettino)
On Unidata you can do a LIST.INDEX 'FILENAME' ALL STATS
This will give you each index the number of keys how many are in overflow and a
couple of other things.
If you can't tell from that command use
LIST.INDEX 'FILE.NAME' ALL DETAILS
This will show you how many records per key and whether the index item is in
overflow.
As mentioned use the NO.NULLS and or NO.DUPS to build the index.
And this has been a killer for us. You have to ALWAYS build the biggest index
first.
By biggest I mean the dictionary length being used.
Example. If you have city, state zip, order# and date. 40L, 15R, 8R
respectively that is how they should be built.
Nicholas M Gettino - Director of Support Professional Services | EnRoute
Emergency Systems an Infor Company, 401 E Jackson Street, Suite 1500, Tampa
Florida 33602 | Office 813-207-6998 |Fax 678-393-5389
|nick.gett...@enroute911.com / www.enroute911.com
Register Now- Early Bird Discount Available |
enroute911.com/user-conference-2010 | EnRoute User Conference | Disney's Yacht
Club Resort, Lake Buena Vista, FL | September 28- October 1, 2010
-Original Message-
From: u2-users-boun...@listserver.u2ug.org
[mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of
u2-users-requ...@listserver.u2ug.org
Sent: 09/10/2010 3:00 PM
To: u2-users@listserver.u2ug.org
Subject: U2-Users Digest, Vol 17, Issue 8
Send U2-Users mailing list submissions to
u2-users@listserver.u2ug.org
To subscribe or unsubscribe via the World Wide Web, visit
http://listserver.u2ug.org/mailman/listinfo/u2-users
or, via email, send a message with subject or body 'help' to
u2-users-requ...@listserver.u2ug.org
You can reach the person managing the list at
u2-users-ow...@listserver.u2ug.org
When replying, please edit your Subject line so it is more specific
than Re: Contents of U2-Users digest...
Today's Topics:
1. Re: Sequential Files Question (Colin Alfke)
2. Re: Sequential Files Question (Bill Haskett)
3. Re: Slow READ/WRITE with indexes (Ryan M)
4. Re: Slow READ/WRITE with indexes (Ryan M)
5. Re: Slow READ/WRITE with indexes {Unclassified}
(HENDERSON MIKE, MR)
6. Re: Slow READ/WRITE with indexes (Colin Alfke)
7. Re: Sequential Files Question (Colin Alfke)
8. Re: Sequential Files Question (Bill Haskett)
9. Re: Slow READ/WRITE with indexes {Unclassified} (Wols Lists)
10. Re: Slow READ/WRITE with indexes {Unclassified} (Dan McGrath)
11. Re: Slow READ/WRITE with indexes (Boydell, Stuart)
12. Re: Slow READ/WRITE with indexes (Ryan M)
13. Re: Slow READ/WRITE with indexes {Unclassified} (Ryan M)
14. Re: Slow READ/WRITE with indexes (Ryan M)
15. UD - XDOMLocate problem - solved (Edward Brown)
16. Re: Slow READ/WRITE with indexes {Unclassified} (Wols Lists)
--
Message: 1
Date: Thu, 9 Sep 2010 13:02:10 -0600
From: Colin Alfke alfke...@hotmail.com
To: 'U2 Users List' u2-users@listserver.u2ug.org
Subject: Re: [U2] Sequential Files Question
Message-ID: snt139-ds5e54ede19399d72b0201898...@phx.gbl
Content-Type: text/plain; charset=us-ascii
The pipe is different. I use it to send output as input to other commands:
!LISTUSER | MORE or
!LISTUSER | FIND COLIN /I
But then I've only been really using DOS since 3.11
I've noticed Win 7 hides the root of C:, but I didn't realize that it
wouldn't let you create a file.
Colin
-Original Message-
From: Allen Elwood
I've been using DOS since 1.0 - forgot all about pipe didn't it
used to be | ?
btw, even though I'm the admin on my laptop, it will not allow me to
even use notepad to save to c:\buddy.txt
Must be a new windows 7 thing
Re: [U2] Slow READ/WRITE with indexes
Key Size Block Size Key Size Block Size 1-13 2,048 137-177 10,240 14-54 4,096 178-218 12,288 55-95 6,144 219-259 14,336 96-136 8,192 260-300 16,384 Wally Terhune U2 Support Architect Rocket Software 4600 South Ulster Street, Suite 1100 **Denver, CO 80237 **USA Tel: +1.720.475.8055 Email: wterh...@rs.com Web: www.rocketsoftware.com/u2 -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Wally Terhune Sent: Friday, September 10, 2010 2:04 PM To: U2 Users List Subject: Re: [U2] Slow READ/WRITE with indexes (Nick Gettino) How long of a data string to you want to index? This mostly has a bearing on the block-size of the index file (the key length setting) ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Slow READ/WRITE with indexes
Hmm - table didn't get handled well by MS mail Wally Terhune U2 Support Architect Rocket Software 4600 South Ulster Street, Suite 1100 **Denver, CO 80237 **USA Tel: +1.720.475.8055 Email: wterh...@rs.com Web: www.rocketsoftware.com/u2 -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Wally Terhune Sent: Friday, September 10, 2010 2:09 PM To: U2 Users List Subject: Re: [U2] Slow READ/WRITE with indexes Key Size Block Size Key Size Block Size 1-13 2,048 137-177 10,240 14-54 4,096 178-218 12,288 55-95 6,144 219-259 14,336 96-136 8,192 260-300 16,384 Wally Terhune U2 Support Architect Rocket Software 4600 South Ulster Street, Suite 1100 **Denver, CO 80237 **USA Tel: +1.720.475.8055 Email: wterh...@rs.com Web: www.rocketsoftware.com/u2 -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Wally Terhune Sent: Friday, September 10, 2010 2:04 PM To: U2 Users List Subject: Re: [U2] Slow READ/WRITE with indexes (Nick Gettino) How long of a data string to you want to index? This mostly has a bearing on the block-size of the index file (the key length setting) ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Slow READ/WRITE with indexes
I get your point though - it matches my result - 10K block size for 160 key size. It does what I need it to do though. -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Wally Terhune Sent: Friday, September 10, 2010 4:12 PM To: U2 Users List Subject: Re: [U2] Slow READ/WRITE with indexes Hmm - table didn't get handled well by MS mail Wally Terhune U2 Support Architect Rocket Software 4600 South Ulster Street, Suite 1100 **Denver, CO 80237 **USA Tel: +1.720.475.8055 Email: wterh...@rs.com Web: www.rocketsoftware.com/u2 -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Wally Terhune Sent: Friday, September 10, 2010 2:09 PM To: U2 Users List Subject: Re: [U2] Slow READ/WRITE with indexes Key Size Block Size Key Size Block Size 1-13 2,048 137-177 10,240 14-54 4,096 178-218 12,288 55-95 6,144 219-259 14,336 96-136 8,192 260-300 16,384 Wally Terhune U2 Support Architect Rocket Software 4600 South Ulster Street, Suite 1100 **Denver, CO 80237 **USA Tel: +1.720.475.8055 Email: wterh...@rs.com Web: www.rocketsoftware.com/u2 -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Wally Terhune Sent: Friday, September 10, 2010 2:04 PM To: U2 Users List Subject: Re: [U2] Slow READ/WRITE with indexes (Nick Gettino) How long of a data string to you want to index? This mostly has a bearing on the block-size of the index file (the key length setting) ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users html body Dave Davis Team Lead, Ramp;D P: 614-875-4910 x108 F: 614-875-4088 E: dda...@harriscomputer.com [http://www.harriscomputer.com/images/signatures/HarrisSchools.gif] [http://www.harriscomputer.com/images/signatures/DivisionofHarris.gif] 6110 Enterprise Parkway Grove City, OH 43123 www.harris-schoolsolutions.com This message is intended exclusively for the individual or entity to which it is addressed. This communication may contain information that is proprietary, privileged or confidential or otherwise legally exempt from disclosure. If you are not the named addressee, you are not authorized to read, print, retain, copy or disseminate this message or any part of it. If you have received this message in error, please notify the sender immediately by e-mail and delete all copies of the message. /body /html ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
[U2] Slow READ/WRITE with indexes
I am hoping I can find some help here. I am running into a serious performance issue with indexes on our UV system (UV 10.2, on AIX). An example of this is our sales order files, SO (current/active) and SOH (history) files. We archive sales orders from SO to SOH on a daily basis, this is moving approx 15,000 records from one file to the other. If I remove all indexes from both files, the process flies by, hundreds of transactions per second. But, with indexes on, we are luck to get one per second. Basically what happens with the code that does the archiving is it reads the SO record, does some quick checks to make sure we can move it, then writes the record to SOH, then deletes the SO. There are 7 indexes on the SO file and 5 on the SOH. I've tried removing one index from a file and running the process, but see no performance gain until all indexes are gone. The SO file is approx 7GB with 280k records, and the SOH file is 11GB, with 8,900,000 records I have check the files sizes in UV and they are correct. Can anyone provide some pointers on ways to setup indexes so they will run faster? Some of the indexes are 'ORDER.DATE', 'COUNTRY', 'MEMBER ID' -- View this message in context: http://old.nabble.com/Slow-READ-WRITE-with-indexes-tp29653705p29653705.html Sent from the U2 - Users mailing list archive at Nabble.com. ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Slow READ/WRITE with indexes
Are any of your indices based on virtual fields? I haven't worked with UV in a while, but UD has the DISABLE.INDEX, ENABLE.INDEX and UPDATE.INDEX commands. If UV also has them, you could disable the indexes prior to the archival and re-enable and update them at the end. I'm not sure if it would be any faster, but certainly worth an attempt. Larry Hiscock Western Computer Services -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Ryan M Sent: Thursday, September 09, 2010 10:07 AM To: u2-users@listserver.u2ug.org Subject: [U2] Slow READ/WRITE with indexes I am hoping I can find some help here. I am running into a serious performance issue with indexes on our UV system (UV 10.2, on AIX). An example of this is our sales order files, SO (current/active) and SOH (history) files. We archive sales orders from SO to SOH on a daily basis, this is moving approx 15,000 records from one file to the other. If I remove all indexes from both files, the process flies by, hundreds of transactions per second. But, with indexes on, we are luck to get one per second. Basically what happens with the code that does the archiving is it reads the SO record, does some quick checks to make sure we can move it, then writes the record to SOH, then deletes the SO. There are 7 indexes on the SO file and 5 on the SOH. I've tried removing one index from a file and running the process, but see no performance gain until all indexes are gone. The SO file is approx 7GB with 280k records, and the SOH file is 11GB, with 8,900,000 records I have check the files sizes in UV and they are correct. Can anyone provide some pointers on ways to setup indexes so they will run faster? Some of the indexes are 'ORDER.DATE', 'COUNTRY', 'MEMBER ID' -- View this message in context: http://old.nabble.com/Slow-READ-WRITE-with-indexes-tp29653705p29653705.html Sent from the U2 - Users mailing list archive at Nabble.com. ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Slow READ/WRITE with indexes
How long have these indexes been in place? If they're new, that's one thing. If they've been in place for a while and this is a change in behavior we may need to look in different areas. FYI, on ud I've had performance issues when going above five indexes on a given file when I have a high volume of transactions. Brad. U.S. BANCORP made the following annotations - Electronic Privacy Notice. This e-mail, and any attachments, contains information that is, or may be, covered by electronic communications privacy laws, and is also confidential and proprietary in nature. If you are not the intended recipient, please be advised that you are legally prohibited from retaining, using, copying, distributing, or otherwise disclosing this information in any manner. Instead, please reply to the sender that you have received this communication in error, and then immediately delete it. Thank you in advance for your cooperation. - ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Slow READ/WRITE with indexes
what sofware package are you running there at usbank ? --- On Thu, 9/9/10, bradley.sch...@usbank.com bradley.sch...@usbank.com wrote: From: bradley.sch...@usbank.com bradley.sch...@usbank.com Subject: Re: [U2] Slow READ/WRITE with indexes To: U2 Users List u2-users@listserver.u2ug.org Date: Thursday, September 9, 2010, 2:39 PM How long have these indexes been in place? If they're new, that's one thing. If they've been in place for a while and this is a change in behavior we may need to look in different areas. FYI, on ud I've had performance issues when going above five indexes on a given file when I have a high volume of transactions. Brad. U.S. BANCORP made the following annotations - Electronic Privacy Notice. This e-mail, and any attachments, contains information that is, or may be, covered by electronic communications privacy laws, and is also confidential and proprietary in nature. If you are not the intended recipient, please be advised that you are legally prohibited from retaining, using, copying, distributing, or otherwise disclosing this information in any manner. Instead, please reply to the sender that you have received this communication in error, and then immediately delete it. Thank you in advance for your cooperation. - ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Slow READ/WRITE with indexes
I'm trying this now, thanks. Larry Hiscock wrote: Are any of your indices based on virtual fields? I haven't worked with UV in a while, but UD has the DISABLE.INDEX, ENABLE.INDEX and UPDATE.INDEX commands. If UV also has them, you could disable the indexes prior to the archival and re-enable and update them at the end. I'm not sure if it would be any faster, but certainly worth an attempt. Larry Hiscock Western Computer Services -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Ryan M Sent: Thursday, September 09, 2010 10:07 AM To: u2-users@listserver.u2ug.org Subject: [U2] Slow READ/WRITE with indexes I am hoping I can find some help here. I am running into a serious performance issue with indexes on our UV system (UV 10.2, on AIX). An example of this is our sales order files, SO (current/active) and SOH (history) files. We archive sales orders from SO to SOH on a daily basis, this is moving approx 15,000 records from one file to the other. If I remove all indexes from both files, the process flies by, hundreds of transactions per second. But, with indexes on, we are luck to get one per second. Basically what happens with the code that does the archiving is it reads the SO record, does some quick checks to make sure we can move it, then writes the record to SOH, then deletes the SO. There are 7 indexes on the SO file and 5 on the SOH. I've tried removing one index from a file and running the process, but see no performance gain until all indexes are gone. The SO file is approx 7GB with 280k records, and the SOH file is 11GB, with 8,900,000 records I have check the files sizes in UV and they are correct. Can anyone provide some pointers on ways to setup indexes so they will run faster? Some of the indexes are 'ORDER.DATE', 'COUNTRY', 'MEMBER ID' -- View this message in context: http://old.nabble.com/Slow-READ-WRITE-with-indexes-tp29653705p29653705.html Sent from the U2 - Users mailing list archive at Nabble.com. ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users -- View this message in context: http://old.nabble.com/Slow-READ-WRITE-with-indexes-tp29653705p29670902.html Sent from the U2 - Users mailing list archive at Nabble.com. ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Slow READ/WRITE with indexes
The indexes have been in place for some time now (1+ years) I'm guessing 10k to 15k new records per day is fairly high volume (this does not include changes to existing records). bradley.schrag wrote: How long have these indexes been in place? If they're new, that's one thing. If they've been in place for a while and this is a change in behavior we may need to look in different areas. FYI, on ud I've had performance issues when going above five indexes on a given file when I have a high volume of transactions. Brad. U.S. BANCORP made the following annotations - Electronic Privacy Notice. This e-mail, and any attachments, contains information that is, or may be, covered by electronic communications privacy laws, and is also confidential and proprietary in nature. If you are not the intended recipient, please be advised that you are legally prohibited from retaining, using, copying, distributing, or otherwise disclosing this information in any manner. Instead, please reply to the sender that you have received this communication in error, and then immediately delete it. Thank you in advance for your cooperation. - ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users -- View this message in context: http://old.nabble.com/Slow-READ-WRITE-with-indexes-tp29653705p29670926.html Sent from the U2 - Users mailing list archive at Nabble.com. ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Slow READ/WRITE with indexes {Unclassified}
Ryan, You said you had indexes like 'ORDER.DATE', 'COUNTRY', 'MEMBER ID'. If those are really the full index data items, you could have thousands, tens of thousands, or even hundreds of thousands, of records with the identical index data item value in the SOH file. If that's the case, updates (inserts) may well be s-l-o-w as the system churns through many, many, many linked buffers of identical valued keys looking for an insert point. I'd try to isolate if the problem is with deletes from the SO file or inserts to the SOH file, by disabling indexing first on one, then the other, then both, to see where the problem lies. If it's inserts to the SOH file that's the problem - which I suspect is the case - then you have two choices: make the disabling of indexes during this process a 'feature'; or restructure your SOH file and its indexes to avoid the clumping. The restructuring could involve re-defining your indexed fields to give fewer duplicated values (but that might mean considerable application program changes, depending how they're written) or you could consider turning SOH into a UV Distributed File set, maybe partitioning on values of ORDER.DATE. Good hunting Mike -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Ryan M Sent: Friday, 10 September 2010 8:13 a.m. To: u2-users@listserver.u2ug.org Subject: Re: [U2] Slow READ/WRITE with indexes The indexes have been in place for some time now (1+ years) I'm guessing 10k to 15k new records per day is fairly high volume (this does not include changes to existing records). 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 U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Slow READ/WRITE with indexes
Make sure you do some testing first. Under UniData I found that disabling the index made things much worse. It created a log file and put a copy of each record (appears to be the entire record - and not optimized for just the required index info) into it. The bad part was we were building a reporting cube so each record was written many times - which were all in the log file. This created a HUGE log file and was actually slower than leaving the index disabled. Plus it had to apply each of these records once we re-enabled/updated the index. It was actually much faster to remove the index, build the file, and then rebuild the index. Maybe it's better now - or maybe it just wasn't designed for what we were doing Hth Colin Alfke Calgary, Canada -Original Message- From: Ryan M I'm trying this now, thanks. Larry Hiscock wrote: Are any of your indices based on virtual fields? I haven't worked with UV in a while, but UD has the DISABLE.INDEX, ENABLE.INDEX and UPDATE.INDEX commands. If UV also has them, you could disable the indexes prior to the archival and re-enable and update them at the end. I'm not sure if it would be any faster, but certainly worth an attempt. Larry Hiscock Western Computer Services ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Slow READ/WRITE with indexes {Unclassified}
On 09/09/10 21:29, HENDERSON MIKE, MR wrote: Ryan, You said you had indexes like 'ORDER.DATE', 'COUNTRY', 'MEMBER ID'. If those are really the full index data items, you could have thousands, tens of thousands, or even hundreds of thousands, of records with the identical index data item value in the SOH file. If that's the case, updates (inserts) may well be s-l-o-w as the system churns through many, many, many linked buffers of identical valued keys looking for an insert point. That was my thinking too. The other, related point. Does UD have NO.NULLS? And are you using it? If you haven't used it that's the most common performance-killer with indices out there ... Cheers, Wol ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Slow READ/WRITE with indexes
I have found that deletes are the hardest on index updating, slower than inserts. If you can, I'd specifically disable the delete from the SO file to see what change that makes. Cheers Stuart -Original Message- Basically what happens with the code that does the archiving is it reads the SO record, does some quick checks to make sure we can move it, then writes the record to SOH, then deletes the SO. There are 7 indexes on the SO file and 5 on the SOH. ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
