Re: [U2] Slow READ/WRITE with indexes

2010-09-10 Thread Ryan M

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

2010-09-10 Thread Ryan M

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


[U2] Slow READ/WRITE with indexes

2010-09-09 Thread Ryan M

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

2010-09-09 Thread 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
 
 
 -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

2010-09-09 Thread Ryan M

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