Very Slow Index, causing bad browser and repositioning performances

[MauditOstie]

Hello Ptalkers,

Well, after 1 day of testing, i was able to figure out some pretty weird things and found out the reason why i have a bad browser reposition! Seems an index is not behaving well!!

First, here's the indexes definition:

ADD INDEX "i-Primary" ON "emplr-status" 
  AREA "Schema Area"
  UNIQUE
  PRIMARY
  DESCRIPTION "Primary Index"
  INDEX-FIELD "emplr-code" ASCENDING 
  INDEX-FIELD "seq-no" ASCENDING 
  INDEX-FIELD "Effective-Date" ASCENDING

*** This index is a test i created this morning ****/
ADD INDEX "i-effective" ON "nss-emplr-status" 
  AREA "Schema Area"
  INDEX-FIELD "emplr-code" ASCENDING 
  INDEX-FIELD "Effective-Date" ASCENDING   

ADD INDEX "i-Status" ON "nss-emplr-status" 
  AREA "Schema Area"
  INDEX-FIELD "Status-Code" ASCENDING 
  INDEX-FIELD "Effective-Date" ASCENDING

Then, here are the testing i did:

DEFINE TEMP-TABLE tt LIKE nss-emplr-status
     FIELD rID    AS ROWID.

FOR EACH  nss-emplr-status NO-LOCK
   WHERE nss-emplr-status.status-code <= 4 
     AND nss-emplr-status.effective-date <= TODAY:

    FIND tt WHERE tt.emplr-code = nss-emplr-status.emplr-code NO-LOCK NO-ERROR.
    IF NOT AVAIL(tt) THEN 
       CREATE tt.
    ELSE IF tt.seq-no > nss-emplr-status.seq-no THEN NEXT.

    BUFFER-COPY nss-emplr-status TO tt.
    ASSIGN tt.rID = ROWID(nss-emplr-status).

END.

/***** This code takes arround 15 secondes to run *****/
/***** So this is 200 000 records in arround 15 secondes *****/
/***** If i then do: *****/

FOR EACH tt EXCLUSIVE-LOCK:
   DELETE tt.
END.

/***** This takes 5 seconds for 100 000 records ******/
/***** BUT! IF I DO THIS: *****/
FOR EACH tt EXCLUSIVE-LOCK:
   FIND LAST nss-emplr-status WHERE
             nss-emplr-status.emplr-code = tt.SSR AND
             nss-emplr-status.effective-date <= TODAY 
      NO-LOCK NO-ERROR.  
   IF AVAIL(nss-emplr-status)AND
      nss-emplr-status.status-code = 5 THEN
      DELETE tt.           
END.

/***** This takes 2min32 seconds on top of the 15 it took before!?!?!?!?!? ****/

I don't get it! Why is that index running so slowly if i can scan the whole table within 15sec?
This is also causing my REPOSITION command to take up to 3 minutes in my browser! Anyone have an idea as of why the index i-effective is so slow?

 

[GregTomkins]

My *guess* is that when Progress sees an index field defined like this:

INDEX-FIELD "Effective-Date" ASCENDING

... followed by a query that includes a clause like this ...

nss-emplr-status.effective-date <= TODAY

That is gets confused and reads all of the nss-emplr-status records (within emplr-code) to find the 'LAST' one. I didn't bother setting this up to check it out, but that's my guess. If I were you I would experiment with changing the <= to =, LAST to FIRST, etc. and see what happens.

 

[RealHeavyDude]

In order not to speculate I would compile the program with the LISTING option. There you will see what index the compiler picks and whether the whole index is scanned or not.

Since the whole schema definition for the table wasn't provided it's hard to predict whether the compiler has chosen the "right" index or not.

I've witnessed cases where there were two indexes on a table that satisfied all criteria for the index picking rules until it came down to picking the index by name ... viola - there you go, wrong index picked and the performance is not what you expect it to be.


Regards, RealHeavyDude.

 

[MauditOstie]

I fiddled around with FIRST, LAST and so on. It did not change a thing. Time was exactly the same (plus or minus 5 sec).

 

[MauditOstie]

I did a compile XREF and couldn't fine any WHOLE-INDEX searches.
I also forced indexes and it did not change a thing.

Here is the .df file of the table:

ADD TABLE "emplr-status"
  AREA "Schema Area"
  LABEL "Status"
  DESCRIPTION "Employer Status"
  DUMP-NAME "-emplyr-status"
  TABLE-TRIGGER "WRITE" NO-OVERRIDE PROCEDURE "emplrstatusw.p" CRC "?" 

ADD FIELD "emplr-code" OF "emplr-status" AS character 
  DESCRIPTION "Social Security Registration Number"
  FORMAT "X(9)"
  INITIAL ""
  LABEL "Code"
  LABEL-SA "R6"
  POSITION 2
  MAX-WIDTH 18
  COLUMN-LABEL "Code"
  COLUMN-LABEL-SA "L6"
  VALMSG-SA "T100"
  HELP "Enter the Code of the employer"
  HELP-SA "T67"
  ORDER 11
  MANDATORY

ADD FIELD "Status-Code" OF "emplr-status" AS integer 
  DESCRIPTION "Status of the employer"
  FORMAT ">>9"
  INITIAL "1"
  LABEL "Status"
  LABEL-SA "R15"
  POSITION 3
  MAX-WIDTH 4
  COLUMN-LABEL "Status"
  COLUMN-LABEL-SA "L15"
  VALMSG-SA "T100"
  HELP "Enter the status of employer."
  HELP-SA "63"
  ORDER 21
  MANDATORY

ADD FIELD "Effective-Date" OF "emplr-status" AS date 
  DESCRIPTION "Status Effective Date"
  FORMAT "99/99/9999"
  INITIAL ?
  LABEL "Effective Date"
  POSITION 4
  MAX-WIDTH 4
  COLUMN-LABEL "Effective"
  HELP "Please input the status' effective date"
  ORDER 31

ADD FIELD "Creation-Date" OF "emplr-status" AS date 
  DESCRIPTION "Status' Creation Date"
  FORMAT "99/99/9999"
  INITIAL ?
  LABEL "Created"
  POSITION 5
  MAX-WIDTH 4
  COLUMN-LABEL "Created"
  ORDER 41

ADD FIELD "Creation-User" OF "emplr-status" AS character 
  DESCRIPTION "Status's Creator"
  FORMAT "x(8)"
  INITIAL ""
  LABEL "Created by"
  POSITION 6
  MAX-WIDTH 16
  COLUMN-LABEL "By"
  ORDER 51

ADD FIELD "Creation-Time" OF "emplr-status" AS integer 
  DESCRIPTION "Statu's creation Time"
  FORMAT ">>>>>>>>>>9"
  INITIAL "0"
  LABEL "Created At"
  POSITION 7
  MAX-WIDTH 4
  COLUMN-LABEL "At"
  ORDER 61

ADD FIELD "seq-no" OF "emplr-status" AS integer 
  DESCRIPTION "Sequence identifier for Activity"
  FORMAT ">>9"
  INITIAL "0"
  LABEL "Seq-no"
  LABEL-SA "R6"
  POSITION 8
  MAX-WIDTH 4
  COLUMN-LABEL "Seq-no"
  COLUMN-LABEL-SA "L6"
  VALMSG-SA "T100"
  HELP "Enter the sequence of the employer activity"
  HELP-SA "T67"
  ORDER 71
  MANDATORY

ADD FIELD "cancelled" OF "emplr-status" AS logical 
  DESCRIPTION "Status cancelled"
  FORMAT "yes/no"
  INITIAL "no"
  POSITION 9
  MAX-WIDTH 1
  ORDER 81

ADD FIELD "Cancel-Date" OF "emplr-status" AS date 
  DESCRIPTION "Status' Cancellation Date"
  FORMAT "99/99/9999"
  INITIAL ?
  LABEL "Cancelled"
  POSITION 10
  MAX-WIDTH 4
  COLUMN-LABEL "Cancelled"
  ORDER 91

ADD FIELD "Cancel-User" OF "emplr-status" AS character 
  DESCRIPTION "Status's Cancellation user"
  FORMAT "x(8)"
  INITIAL ""
  LABEL "Cancelled by"
  POSITION 11
  MAX-WIDTH 16
  COLUMN-LABEL "By"
  ORDER 101

ADD FIELD "Cancel-Time" OF "emplr-status" AS integer 
  DESCRIPTION "Status' cancellation Time"
  FORMAT ">>>>>>>>>>9"
  INITIAL "0"
  LABEL "Cancelled At"
  POSITION 12
  MAX-WIDTH 4
  COLUMN-LABEL "At"
  ORDER 111

ADD INDEX "i-Primary" ON "emplr-status" 
  AREA "Schema Area"
  UNIQUE
  PRIMARY
  DESCRIPTION "Primary Index"
  INDEX-FIELD "emplr-code" ASCENDING 
  INDEX-FIELD "seq-no" ASCENDING 

ADD INDEX "i-effective" ON "emplr-status" 
  AREA "Schema Area"
  INDEX-FIELD "emplr-code" ASCENDING 
  INDEX-FIELD "Effective-Date" ASCENDING   

ADD INDEX "i-Status" ON "emplr-status" 
  AREA "Schema Area"
  INDEX-FIELD "Status-Code" ASCENDING 
  INDEX-FIELD "Effective-Date" ASCENDING 

.
PSC
cpstream=ISO8859-1
.
0000006392

 

[MauditOstie]

Here's the Compile Xref of my simple tests:

C:\testinquiryTT.p C:\testinquiryTT.p 1 COMPILE C:\testinquiryTT.p
C:\testinquiryTT.p C:\testinquiryTT.p 1 CPINTERNAL ISO8859-1
C:\testinquiryTT.p C:\testinquiryTT.p 1 CPSTREAM ISO8859-1
C:\testinquiryTT.p C:\testinquiryTT.p 1 STRING "tt" 2 NONE UNTRANSLATABLE 
C:\testinquiryTT.p C:\testinquiryTT.p 1 STRING "Status" 6 LEFT TRANSLATABLE 
C:\testinquiryTT.p C:\testinquiryTT.p 1 STRING "Code" 3 NONE UNTRANSLATABLE 
C:\testinquiryTT.p C:\testinquiryTT.p 1 STRING "Status-Code" 11 NONE UNTRANSLATABLE 
C:\testinquiryTT.p C:\testinquiryTT.p 1 STRING "Effective-Date" 14 NONE UNTRANSLATABLE 
C:\testinquiryTT.p C:\testinquiryTT.p 1 STRING "Creation-Date" 13 NONE UNTRANSLATABLE 
C:\testinquiryTT.p C:\testinquiryTT.p 1 STRING "Creation-User" 13 NONE UNTRANSLATABLE 
C:\testinquiryTT.p C:\testinquiryTT.p 1 STRING "Creation-Time" 13 NONE UNTRANSLATABLE 
C:\testinquiryTT.p C:\testinquiryTT.p 1 STRING "seq-no" 6 NONE UNTRANSLATABLE 
C:\testinquiryTT.p C:\testinquiryTT.p 1 STRING "cancelled" 9 NONE UNTRANSLATABLE 
C:\testinquiryTT.p C:\testinquiryTT.p 1 STRING "Cancel-Date" 11 NONE UNTRANSLATABLE 
C:\testinquiryTT.p C:\testinquiryTT.p 1 STRING "Cancel-User" 11 NONE UNTRANSLATABLE 
C:\testinquiryTT.p C:\testinquiryTT.p 1 STRING "Cancel-Time" 11 NONE UNTRANSLATABLE 
C:\testinquiryTT.p C:\testinquiryTT.p 1 STRING "rID" 3 NONE UNTRANSLATABLE 
C:\testinquiryTT.p C:\testinquiryTT.p 1 REFERENCE appl.emplr-status 
C:\testinquiryTT.p C:\testinquiryTT.p 5 STRING "i" 1 NONE UNTRANSLATABLE 
C:\testinquiryTT.p C:\testinquiryTT.p 6 STRING "j" 1 NONE UNTRANSLATABLE 
C:\testinquiryTT.p C:\testinquiryTT.p 9 STRING "bnes1" 5 NONE UNTRANSLATABLE 
C:\testinquiryTT.p C:\testinquiryTT.p 9 STRING "emplr-status" 16 NONE UNTRANSLATABLE 
C:\testinquiryTT.p C:\testinquiryTT.p 9 REFERENCE appl.emplr-status 
C:\testinquiryTT.p C:\testinquiryTT.p 10 STRING "bnes2" 5 NONE UNTRANSLATABLE 
C:\testinquiryTT.p C:\testinquiryTT.p 10 STRING "emplr-status" 16 NONE UNTRANSLATABLE 
C:\testinquiryTT.p C:\testinquiryTT.p 10 REFERENCE appl.emplr-status 
C:\testinquiryTT.p C:\testinquiryTT.p 12 STRING "v-OK" 4 NONE UNTRANSLATABLE 
C:\testinquiryTT.p C:\testinquiryTT.p 13 STRING "v-time" 6 NONE UNTRANSLATABLE 
C:\testinquiryTT.p C:\testinquiryTT.p 19 STRING "emplr-status" 16 NONE UNTRANSLATABLE 
C:\testinquiryTT.p C:\testinquiryTT.p 19 STRING "Status" 6 NONE TRANSLATABLE 
C:\testinquiryTT.p C:\testinquiryTT.p 19 ACCESS appl.emplr-status Status-Code 
C:\testinquiryTT.p C:\testinquiryTT.p 19 ACCESS appl.emplr-status Effective-Date 
C:\testinquiryTT.p C:\testinquiryTT.p 19 SEARCH appl.emplr-status i-Status
C:\testinquiryTT.p C:\testinquiryTT.p 22 ACCESS appl.emplr-status Emplr-Code 
C:\testinquiryTT.p C:\testinquiryTT.p 23 ACCESS appl.emplr-status seq-no 
C:\testinquiryTT.p C:\testinquiryTT.p 25 REFERENCE appl.emplr-status 
C:\testinquiryTT.p C:\testinquiryTT.p 25 ACCESS appl.emplr-status Cancel-Date 
C:\testinquiryTT.p C:\testinquiryTT.p 25 ACCESS appl.emplr-status Cancel-Time 
C:\testinquiryTT.p C:\testinquiryTT.p 25 ACCESS appl.emplr-status Cancel-User 
C:\testinquiryTT.p C:\testinquiryTT.p 25 ACCESS appl.emplr-status cancelled 
C:\testinquiryTT.p C:\testinquiryTT.p 25 ACCESS appl.emplr-status Creation-Date 
C:\testinquiryTT.p C:\testinquiryTT.p 25 ACCESS appl.emplr-status Creation-Time 
C:\testinquiryTT.p C:\testinquiryTT.p 25 ACCESS appl.emplr-status Creation-User 
C:\testinquiryTT.p C:\testinquiryTT.p 25 ACCESS appl.emplr-status Effective-Date 
C:\testinquiryTT.p C:\testinquiryTT.p 25 ACCESS appl.emplr-status seq-no 
C:\testinquiryTT.p C:\testinquiryTT.p 25 ACCESS appl.emplr-status Emplr-code 
C:\testinquiryTT.p C:\testinquiryTT.p 25 ACCESS appl.emplr-status Status-Code 
C:\testinquiryTT.p C:\testinquiryTT.p 26 REFERENCE appl.emplr-status 
C:\testinquiryTT.p C:\testinquiryTT.p 33 ACCESS appl.emplr-status Emplr-code 
C:\testinquiryTT.p C:\testinquiryTT.p 33 ACCESS appl.emplr-status Effective-Date 
C:\testinquiryTT.p C:\testinquiryTT.p 33 SEARCH appl.emplr-status i-Effective
C:\testinquiryTT.p C:\testinquiryTT.p 39 ACCESS appl.emplr-status Status-Code 
C:\testinquiryTT.p C:\testinquiryTT.p 39 REFERENCE appl.emplr-status 
C:\testinquiryTT.p C:\testinquiryTT.p 63 STRING "HH:MM:SS" 8 NONE TRANSLATABLE 
C:\testinquiryTT.p C:\testinquiryTT.p 63 STRING "i-Cancel" 8 NONE UNTRANSLATABLE 
C:\testinquiryTT.p C:\testinquiryTT.p 63 STRING "i-effective" 11 NONE UNTRANSLATABLE 
C:\testinquiryTT.p C:\testinquiryTT.p 63 STRING "i-Primary" 9 NONE UNTRANSLATABLE 
C:\testinquiryTT.p C:\testinquiryTT.p 63 STRING "i-Status" 8 NONE UNTRANSLATABLE 
C:\testinquiryTT.p C:\testinquiryTT.p 63 STRING "i-Status" 8 NONE UNTRANSLATABLE 
C:\testinquiryTT.p C:\testinquiryTT.p 63 STRING "i-Primary" 9 NONE UNTRANSLATABLE

 

[TomBascom]

In your query it has to resolve 2 range matches. A range match immediately breaks bracketing. There is no index with as a leading component and thus there is no efficient way to resolve that particular aspect of the query. It isn't a WHOLE-INDEX search but it is pretty darned close to one and it is embedded inside another which is pretty close to one.

(Notice that in your "fast" code the leading component is an equality match, not a range match -- this allows the second component to be used in bracketing the index.)

I see two promising ways to potentially resolve this:

1) Add an index with effective-date as the leading component.

2) Change the code slightly. Instead of:

FOR EACH  nss-emplr-status NO-LOCK
   WHERE nss-emplr-status.status-code <= 4 
     AND nss-emplr-status.effective-date <= TODAY:

do i = 1 to 4: /* assuming that the valid values are 1, 2, 3, 4... */

  FOR EACH  nss-emplr-status NO-LOCK WHERE nss-emplr-status.status-code = i AND nss-emplr-status.effective-date <= TODAY:
    ...

Furthermore everything is in the schema area which gives you no possibility of having the hardware bail you out.

 

[MauditOstie]

Hi Tom,
Well, the for each you are asking me to change takes the fastest to run (14sec). And as weird as it sounds, the DO I is way longer then the current For Each
So i can create roughly 200 000 in my temp-table in a respectable 14 sec.

It's the Find in the Table that causes me big problems:

FOR EACH tt EXCLUSIVE-LOCK:
   FIND LAST nss-emplr-status WHERE
             nss-emplr-status.emplr-code = tt.SSR AND
             nss-emplr-status.effective-date <= TODAY 
      NO-LOCK NO-ERROR.  
   IF AVAIL(nss-emplr-status)AND
      nss-emplr-status.status-code = 5 THEN
      DELETE tt.           
END.

/***** This takes 2min32 seconds on top of the 15 it took before!?!?!?!?!? ****/

Why is this part only, taking 2min32? Beats me!

 

[RealHeavyDude]

There's a rule in my book: Try to avoid using FIND in a loop - use FOR EACH or a QUERY instead because they are much faster.

I'm not 100% positive if that's the correct explanation, but: A FIND in a loop produces a round trip between the 4GL execution layer and the database engine with each iteration of the loop. That means, if it's not a self-service client connected directly via shared memory to the database, it also produces a net work round trip. FOR EACH or A QUERY produce much less such round trips between the 4GL execution layer and the database engine and, as remote client, much less network round trips. ( By the way - I've been told once by a wizard that that's the reason why the fill operation on ProDataSet with no callbacks are involved is much faster than anything else ... )

Regards, RealHeavyDude.

 

[TomBascom]

Because you have a range match on effective date. And you are using FIND which can only use a single index cursor to resolve a query.

As an experiment change "nss-emplr-status.effective-date <= TODAY" to "nss-emplr-status.effective-date = TODAY" (and make sure that it will find something) and run it again. If I am right it will be very fast.

Then you might be able to code that block as:

FOR EACH tt EXCLUSIVE-LOCK,
       LAST nss-emplr-status NO-LOCK WHERE
             nss-emplr-status.emplr-code = tt.SSR AND
             nss-emplr-status.status-code = 5 AND
             nss-emplr-status.effective-date <= TODAY:

   DELETE tt.           

END.

FOR EACH can use multiple index cursors. I'm not so sure about FOR LAST -- personally I never use it; but it would be worth a try. If it doesn't work then I suppose the next step would be to convert the FIND to an OPEN QUERY and a GET LAST.

 

[MauditOstie]

The result is in.
A query doesn't fix my problem... It is a bit faster (30sec) but no cigars:

DEFINE QUERY q-bnes FOR bnes FIELDS(emplr-code Effective-Date status-code).

FOR EACH  nss-emplr-status NO-LOCK
    WHERE nss-emplr-status.status-code <= 4 
      AND nss-emplr-status.effective-date <= TODAY:

    FIND tt WHERE tt.emplr-code = nss-emplr-status.emplr-code NO-LOCK NO-ERROR.
    
    IF NOT AVAIL(tt) THEN 
       CREATE tt.
    ELSE IF tt.seq-no > nss-emplr-status.seq-no THEN NEXT.

    BUFFER-COPY nss-emplr-status TO tt.
    ASSIGN tt.rID = ROWID(nss-emplr-status).
END.

FOR EACH tt EXCLUSIVE:
    OPEN QUERY q-bnes FOR EACH bnes WHERE bnes.emplr-code = tt.emplr-code AND
                                      bnes.Effective-date <= TODAY NO-LOCK.
    GET LAST q-bnes.
    IF bnes.status-code = 5 THEN
        DELETE tt.         
END.

/**** TIME : 1min 58 *****/

 

[MauditOstie]

I was able to drop the time to 1min20 with this:

FOR EACH  nss-emplr-status NO-LOCK WHERE 
          nss-emplr-status.status-code <= 4 
      AND nss-emplr-status.effective-date <= TODAY:
    FIND LAST bnes WHERE 
               bnes.status-code = 5 AND
               bnes.SSR = nss-emplr-status.SSR AND
               bnes.seq-no > nss-emplr-status.seq-no 
         NO-LOCK NO-ERROR.
    IF AVAIL bnes AND bnes.effective-date <= TODAY THEN NEXT.
    FIND tt WHERE tt.SSR = nss-emplr-status.SSR NO-LOCK NO-ERROR.
    IF NOT AVAIL(tt) THEN
       CREATE tt.
    ELSE IF tt.seq-no > nss-emplr-status.seq-no THEN NEXT.
    
    BUFFER-COPY nss-emplr-status TO tt.
    ASSIGN tt.rID = ROWID(nss-emplr-status).
END.

 

[TomBascom]

Did you try the "=" test?

 

[MauditOstie]

Yes i did Tom, gain was minimal (average 4 to 5 sec).

 

[MauditOstie]

We just did a re-index and checked everything so we didn't have corrupted data..
Everything is OK and this didn't fix a thing

 

[MauditOstie]

I'll be darned!!!!!

12 Seconds now to load my temp-table!!! O - M - F - G!!!!!

FOR EACH  nss-emplr-status NO-LOCK WHERE 
          nss-emplr-status.SSR > "" AND
          nss-emplr-status.seq-no > 0 AND
          nss-emplr-status.effective-date <= TODAY
       BREAK BY SSR BY seq-no:
    IF LAST-OF(nss-emplr-status.SSR) AND
       nss-emplr-status.status-code <= 4 THEN DO:
        FIND tt WHERE tt.SSR = nss-emplr-status.SSR NO-LOCK NO-ERROR.
        IF NOT AVAIL(tt) THEN
           CREATE tt.
        ELSE IF tt.seq-no > nss-emplr-status.seq-no THEN NEXT.
        BUFFER-COPY nss-emplr-status TO tt.
    END. 
END.

WOW!!!
So now, can anyone help me improve it?

 

[TomBascom]

You're cheating You introduced SEQ-NO...

 

[MauditOstie]

lol yeah i know!
But...
I still have a huge problem! I need some information on a linked table (the nss-employer table).
Thing is, my query takes 12 secondes BUT as soon as i add a Find in it... Then BOOM... 1min45 to 2 min!

FIND FIRST nss-control 
    NO-LOCK NO-ERROR.
FOR EACH  nss-emplr-status NO-LOCK WHERE 
          nss-emplr-status.SSR GT "" AND
          nss-emplr-status.seq-no GT 0 AND
          nss-emplr-status.effective-date <= TODAY
    BREAK BY nss-emplr-status.SSR BY nss-emplr-status.seq-no:
    IF LAST-OF(nss-emplr-status.SSR) AND
       nss-emplr-status.status-code <= 4 THEN DO:
        FIND FIRST nss-employer WHERE nss-employer.entity-code = nss-control.nss-entity AND
                   nss-employer.SSR = nss-emplr-status.SSR 
           NO-LOCK NO-ERROR. 
        IF NOT AVAIL nss-employer THEN NEXT.
        FIND ttnes WHERE ttnes.SSR = nss-emplr-status.SSR NO-LOCK NO-ERROR.
        IF NOT AVAIL(ttnes) THEN
           CREATE ttnes.
        ELSE IF ttnes.seq-no > nss-emplr-status.seq-no THEN NEXT.
        BUFFER-COPY nss-emplr-status TO ttnes.
        ASSIGN ttnes.Legal-Name = nss-employer.Legal-Name
               ttnes.Trade-Name = nss-employer.Trade-Name. 
    END. 
END.

 

[Cringer]

Try changing the FIND FIRST to a FOR FIRST...

 

[LarryD]

even more important... what indexes do you have on the nss-employer table. you are no doubt not using an index with the find first (or for first)