What I found in the past is rules will work good until a table reaches a large number of rows. The the number of rows grow the time for the rule to run grows. Dan Goldberg
_____ From: [email protected] [mailto:[email protected]] On Behalf Of Dan Sent: Thursday, February 11, 2010 6:43 AM To: RBASE-L Mailing List Subject: [RBASE-L] - Re: Speed issues Ok, All I had done (probably wrong) was to tell R:Base that Invno must be unique. It created the code. So, instead of making a rule that it needs to be unique, I should turn that off, and make that index a Unique index or Primary Key? I will try that. Stuff like this bugs me... it has been this way for 10 years, now it is wrong. Thanks. At 09:31 AM 2/11/2010, you wrote: << 3 indexes Custno, Invno, IDTrans (all unique numbers) >> Is there a reason you need three separate unique indexes in one table? Reducing the number might help optimize. << 1 rule: Ardetail.INVNO IS NOT NULL AND NOT Ardetail.INVNO IN ( SELECT INVNO FROM Ardetail #T1 WHERE #T1.INVNO = Ardetail.INVNO ) >> I think this might be your problem, or part of it. Every insert or update of InvNo is causing a SELECT of all the InvNos already in the table. You can eliminate this by: 1. Recreating the index on InvNo as either a UNIQUE index, or a PRIMARY KEY constraint and getting rid of the rule. 2. Alternatively (still slow, but probably not SO slow), rewriting the rule to use NOT EXISTS instead of InvNo NOT IN (and definitely not NOT InvNo IN). -- Larry
