I have been watching with interest the discussion on the speed issue.
I also have a speed issue that I mentioned a month or so ago.
To rehash my problem, I have an edit form based on a variable radio
group and variable list view.
I implemented this hoping it would care for the speed problem but to no
avail.
The variable group has six fields they can choose from.
The On press field EEP is below.
GETPROPERTY Keyword TEXTVALUE vKeyword
IF vKeyword = '*' OR vKeyword = '%' THEN
-- Define Where Clause
SET VAR vWhereClause = ('Control# IS NOT NULL')
PROPERTY LV_Quotes REFRESHLIST 'TRUE'
PROPERTY LV_Quotes SET_FOCUS 'TRUE'
PROPERTY ExportResultsAs ENABLED 'TRUE'
GOTO Done
ENDIF
-- Pre-define variables
IF (SLEN (.vKeyword )) < 2 THEN
GOTO Done
ENDIF
SET VAR vQuotes = (CVAL('QUOTES'))
SET VAR vMany = (CVAL('MANY'))
SET VAR vSearchString TEXT = NULL
SET VAR vWhereClause TEXT = NULL
SET VAR vSearchString = (.vQuotes+.vKeyword+.vMany+.vQuotes)
-- Define Where Clause
SET VAR vWhereClause = (.vSearchWhat + ' CONTAINS' &.vSearchString )
PROPERTY LV_Quotes REFRESHLIST 'TRUE'
LABEL Done
CLEAR VAR iv%
RETURN
The var vKeyword is the var for the OKP field
The var vSearchwhat is the var for the variable radio group.
The Listview var is vControl (this references the PK in the table)
Upon many suggestions from the group I
Created my Primary key, Created a foreign key, added Indexes to the
fields.
I also used the Variable radio group along with the OKP w/ the variable
lookup listview.
The form is still VERY slow. 5 or more seconds between each keystroke.
Can anyone SEE a major problem?
Jim
________________________________
From: [email protected] [mailto:[email protected]] On Behalf Of Emmitt
Dove
Sent: Thursday, February 11, 2010 9:02 AM
To: RBASE-L Mailing List
Subject: [RBASE-L] - Re: Speed issues
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
