Dan,
Don't be frustrated that something 10 years old seems broken now. One thing that has always been true is that R:BASE lets us get by with sub-optimal stuff until RBTI tightens the code. That then reveals the flaws in our work. Bill earlier made note of the query optimizer changes forcing him to rewrite some code. In a related circumstance, one of our facilities has been experiencing terribly slow response in some areas of the application. While getting our infrastructure guys on the task of figuring out why something that executed in 6 seconds on the file server console could take 10 minutes or more under Citrix, we revisited some key queries and rewrote them. Now they are much more efficient. One example was turning a three-table SELECT into a SELECT with a two-table SUB-SELECT using IN. Huge improvement. Finally, the infrastructure guys replaced two old DELL switches with new Ciscos and made sure they had all the servers properly wired to the same switch. Now, the task is almost too quick to time on the FS console and takes about 8-10 seconds under Citrix. So the sub-optimal infrastructure forced us to revisit code and make it more efficient. Likewise, as RBTI refines R:BASE and improves its internal workings, matters in our code surface that need attention. Emmitt Dove Manager, Converting Applications Development Evergreen Packaging, Inc. [email protected] (203) 214-5683 m (203) 643-8022 o (203) 643-8086 f [email protected] From: [email protected] [mailto:[email protected]] On Behalf Of Dan Sent: Thursday, February 11, 2010 09:43 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
