Best way to do this without an idex?

D

dayv2005

Member
Ok i am loading a combo box based on all the different users that are in a table. Basically setting up 3 combo boxes for filters based on the info in the table. there will be multiple entries with the same user but once i get the first one i add it and move to the next user. Im doing this with user-id system-ID and fileFrom.

Right now performance with no index isn't bad because few records are in there. But this table will fill up quick.

Here's how im Loading it. I need a better way.

Code: 
DEFINE VARIABLE userName AS CHARACTER  NO-UNDO.

    ASSIGN cb-User:DELIMITER     = CHR(10).
           
    FOR EACH SystemTracker NO-LOCK BREAK BY SystemTracker.User-ID :
        IF FIRST-OF(SystemTracker.User-ID) THEN
        DO:
            FIND User-File NO-LOCK WHERE User-File.User-ID = SystemTracker.User-ID NO-ERROR.
            IF AVAILABLE User-File THEN
                ASSIGN userName = trim(User-File.FirstName) + " " + TRIM(User-File.LastName).

            cb-user:ADD-LAST(userName,SystemTracker.User-ID).
            cntUser = cntUser + 1.
            
        END.
    END.


I have no way of including and indexed field in the where clause though.

Here's my table.

Code: 
Table: SystemTracker

Order Field Name                      Data Type    Flags Format
----- ------------------------------- ------------ ----- -------------------
   10 TrackerID                       char         i     X(8)
   20 System-ID                       char         i     X(20)
   30 User-ID                         char         i     X(20)
   40 Created-Date                    date               99/99/99
   50 Created-Time                    char               X(12)
   60 Updated-Date                    date               99/99/99
   70 Updated-Time                    char               X(12)
   80 Counter                         inte               ->,>>>,>>9
   90 FileFrom                        char         i     X(40)
  100 Product                         char         i     X(8)

Here are my indexes

Code: 
Table: SystemTracker

Flags Index Name                    St Area Cnt Field Name
----- ----------------------------- ------- --- ------------------------------
      i-SystemUser                  7         2 + System-ID
                                                + User-ID

p     i-SysUserProdFile             7         4 + System-ID
                                                + User-ID
                                                + Product
                                                + FileFrom

      i-TrackerID                   7         1 + TrackerID
 
Hello,
As a quick reply , Break By is comparatively slow . Just to avoid that can't we change the code as follows.


FOR EACH User-file NO-LOCK:
FOR FIRST SystemTracker NO-LOCK WHERE SystemTracker.USER- ID = User-File.User-ID:
ASSIGN userName = trim(User-File.FirstName) + " " + TRIM(User-File.LastName).
cb-user:ADD-LAST(userName,SystemTracker.User-ID).
cntUser = cntUser + 1.
END.
END.

Regards
Philip
 
The issue im having with this is that the xref is picking this up as a slow point. And i cant think of any other way to load things from the table where i can't use the indexed fields into the where clause.
 
There is no way of avoiding the slowdown that populating your SystemTracker table will create. You can get some performance back by using a query that only brings back the user-ID from systemTracker table but it will be marginale compared to what you will experience once the SystemTracker fills up.

SystemTracker really need to have an Index on User-ID (has the first field) to get good performance that will not degrade over time.


Your only other option is to accept that filling up your combo box will get slower over time until you can add that index.
 
I would build a temp-table (which would contain a list of users) ahead of time by processing System-Tracker WITHOUT the BREAK BY.

Also, you may already be aware of this, but the i-SystemUser index is completely redundant/unnecessary since its only components are the leading components of the next index. :blush1:
 
If you have control over the database, I'd create a new table SystemUser

Code: 
Table: SystemUser
 
Order Field Name                      Data Type    Flags Format
----- ------------------------------- ------------ ----- -------------------
   10 System-ID                       char         i     X(20)
   20 User-ID                         char         i     X(20)
   30 Description                     char         i     X(20)
 
Flags Index Name                    St Area Cnt Field Name
----- ----------------------------- ------- --- ------------------------------
      i-SystemUser                  7         2 + System-ID
                                                + User-ID


I'd populate SystemUser when you create a SystemTracker record and also I'd run a one-off trawl through the SystemTracker table to populate SystemUser.

That way, you could do a FOR EACH on SystemUser and hit the SystemTracker records using the primary index.
 
You must log in or register to reply here.
Back
Top