›
Welcome to the SRP Forum! Please refer to the SRP Forum FAQ post if you have any questions regarding how the forum works.

Filter Performance

ColinRuleColinRule
in SRP EditTable Control
Hi, just looking for ideas here.
I have a large edit table, some 60k some 200k rows.
Population of the table is quick as it is indexed, and it just reads the index and feeds the ARRAY property.

I have a function where the user can select a filter, on up to 6 columns.
I do a loop/remove to process the list, so that in itself is quick.
Then I set the VISIBLE property to zero if the filter does not match, and at the end do a redraw.
This can take several minutes. I cant see anything I can do code wise to improve things.

Any thoughts if there are ways to make the table Visible row settings faster?

Code below

Thanks
Colin


SF_TABLE=Get_Property(FactorTable,"OLE.ARRAY")
SF_TABLE=SRP_ROTATE_ARRAY(SF_TABLE)
RowCount=DCOUNT(SF_TABLE,@FM)
SWAP @VM WITH "|" IN SF_TABLE
Position=1
TERM=0
TOTAL_ITEMS=RowCount

FOR ROW=1 TO RowCount until TERM
Remove Line From SF_TABLE At Position Setting flag
SWAP "|" WITH @FM IN Line
VISIBLE=1

IF SF_FILTER1 THEN
CELL1=Line<2>
IF CELL1#SF_FILTER1 THEN VISIBLE=0
END

IF SF_FILTER2 THEN
CELL2=Line<3>
IF CELL2#SF_FILTER2 THEN VISIBLE=0
END

IF SF_FILTER3 THEN
CELL3=Line<4>
IF CELL3#SF_FILTER3 THEN VISIBLE=0
END

IF SF_FILTER4 THEN
CELL4=Line<5>
IF CELL4#SF_FILTER4 THEN VISIBLE=0
END

IF SF_FILTER5 THEN
CELL5=Line<6>
IF CELL5#SF_FILTER5 THEN VISIBLE=0
END

IF SF_FILTER6 THEN
CELL6=Line<7>
IF CELL6#SF_FILTER6 THEN VISIBLE=0
END

IF VISIBLE=0 THEN
CALL Set_Property(FactorTable,"OLE.DataRow[":ROW:"]",@FM:VISIBLE)
END

ITEM_COUNT+=1
GOSUB UPDATE_STATUS
IF CANCEL THEN TERM=1
NEXT ROW

SF_TABLE=""
CALL END_DIALOG("STD_PROCESS")
CALL Set_Property(FactorTable,"OLE.Redraw",1)

Comments

  • KevinFournierKevinFournier
    OLE.Redraw is only useful if you set it to 0 somewhere at the top. Setting it to 0 tells the table to not refresh the view until it's set to 1 again. In the code above, each time you set a row's visibility, the table is redrawn.
  • ColinRuleColinRule
    Sorry, I should have included more code.
    All rows are set visible, and redraw is set to zero at the start.


    CALL Set_Property(FactorTable,"OLE.Redraw",0)
    CALL Set_Property(FactorTable,"OLE.DataRow[All]",@FM:1)
  • DonBakkeDonBakke
    We've handled this scenario before. In fact, one of our senior developers recently implemented a very similar design that you are describing. I'll let him respond with details and screenshots if he is able.

    The basic solution to get the best performance is to simply maintain two arrays (or lists). The original and the filtered. Just repopulate the SRP EditTable with the filtered array and then switch back to the original array when needed.
  • KevinFournierKevinFournier
    Okay, then the overhead has everything to do with the Set_Property calls. There's just too many of them. I would opt, instead, to create a new filtered dataset to pass into the ARRAY property. If you need to know row numbers, you could create a hidden column so that the original row numbers can be accessed. We've done filtering this away in several cases. The fewer OLE calls, the better.
  • DonBakkeDonBakke
    Colin - What Kevin said above. He added a little more guidance (i.e., the hidden column) to help with maintenance.

    Sorry...I didn't realize Kevin was responding at the same time I was also answering your question otherwise I would stayed silent and avoided potential noise and confusion.
  • ColinRuleColinRule
    Thanks, I was thinking along the same lines.
    My issue is that the user can update the table, and if they then apply a filter the updates wont be there.
    I will need to have a think if this is really an issue or not, maybe the user has to save before applying a filter.
    I will advise any solutions for info.
  • ColinRuleColinRule
    Oh, and another question.
    Suppose I want to create the filtered list, any suggestions as to the best way?
    The SRP_Array with JOIN is useful, but when there are multi-valued columns, it is not suitable.

    I am thinking I just find each row, match, if visible then append to the new filtered table.

    But... sort of hoping some SRP magic utility might prove useful.
  • KevinFournierKevinFournier
    I actually find that a generic search is the best filter: a single search field at the top that the user can type anything they want. Then I only have to loop through each row in an array and just do an IndexC on it. It's super fast and provides way more flexibility to the user than predefined filters and dropdowns.
  • ColinRuleColinRule
    Thanks Kevin, generally I would do as your suggestion, and have in some other screens.
    This one however does not fit with that model. See example screen below. Filters in the dropdowns at the top.
    In this case 220,000 rows. Reads the record and populates in about 1 or 2 seconds.

    Oh well, I just have to write the code, no simple solutions in some cases.


  • AusMarkBAusMarkB
    That many records in that few seconds is an impressive feat on its own I'd say.
  • ColinRuleColinRule
    Only one read, albeit a large one.
    Then it does a bunch of fast SRP array functions before populating.
    So, not 200k reads.

    But having said that 4m OI reads to build the structure works at about 20k per second.
    Well hashed record keys and small items are very quick.
Sign In or Register to comment.