Answered

Reading in over 200,000 rows of data from sharepoint list , delegation issue

(0) Share

Report

Posted on by JD_26

102

I have a form in a power app where activity is added and it is currently at 58,000 rows , this time next year it will be over 100,000 rows.

At present I am having to run this code every time a new row of activity is updated , so it shows live in a gallery.

Is this method sustainable , has anybody read data from sharepoint with more than 100,000 rows , is there a limit of how many sharepoin list rows can be read ? Are there any alternative solutions better suited to working with large datasets .

Any help would be much appreciated

If(
    FormSubmissionInProgress,
    // Step 2: Refresh the data source
    Refresh(Activity_List);
    // Step 3: Create a single collection with all required data
    Concurrent(
        ClearCollect( activity_00, Filter( Activity_List, ID_Code = 0 )),
        ClearCollect( activity_0, Filter( Activity_List, ID_Code >= 1 && ID_Code < 2000 )),
        ClearCollect( activity_1, Filter( Activity_List, ID_Code >= 2000 && ID_Code < 4000 )),
        ClearCollect( activity_2, Filter( Activity_List, ID_Code >= 4000 && ID_Code < 6000 )),
        ClearCollect( activity_3, Filter( Activity_List, ID_Code >= 6000 && ID_Code < 8000 )),
        ClearCollect( activity_4, Filter( Activity_List, ID_Code >= 8000 && ID_Code < 10000 )),
        ClearCollect( activity_5, Filter( Activity_List, ID_Code >= 10000 && ID_Code < 12000 )),
        ClearCollect( activity_6, Filter( Activity_List, ID_Code >= 12000 && ID_Code < 14000 )),
        ClearCollect( activity_7, Filter( Activity_List, ID_Code >= 14000 && ID_Code < 16000 )),
        ClearCollect( activity_8, Filter( Activity_List, ID_Code >= 16000 && ID_Code < 18000 )),
        ClearCollect( activity_9, Filter( Activity_List, ID_Code >= 18000 && ID_Code < 20000 )),
        ClearCollect( activity_10, Filter( Activity_List, ID_Code >= 20000 && ID_Code < 22000 )),
        ClearCollect( activity_11, Filter( Activity_List, ID_Code >= 22000 && ID_Code < 24000 )),
        ClearCollect( activity_12, Filter( Activity_List, ID_Code >= 24000 && ID_Code < 26000 )),
        ClearCollect( activity_13, Filter( Activity_List, ID_Code >= 26000 && ID_Code < 28000 )),
        ClearCollect( activity_14, Filter( Activity_List, ID_Code >= 28000 && ID_Code < 30000 )),
        ClearCollect( activity_15, Filter( Activity_List, ID_Code >= 30000 && ID_Code < 32000 )),
        ClearCollect( activity_16, Filter( Activity_List, ID_Code >= 32000 && ID_Code < 34000 )),
        ClearCollect( activity_17, Filter( Activity_List, ID_Code >= 34000 && ID_Code < 36000 )),
        ClearCollect( activity_18, Filter( Activity_List, ID_Code >= 36000 && ID_Code < 38000 )),
        ClearCollect( activity_19, Filter( Activity_List, ID_Code >= 38000 && ID_Code < 40000 )),
        ClearCollect( activity_20, Filter( Activity_List, ID_Code >= 40000 && ID_Code < 42000 )),
        ClearCollect( activity_21, Filter( Activity_List, ID_Code >= 42000 && ID_Code < 44000 )),
        ClearCollect( activity_22, Filter( Activity_List, ID_Code >= 44000 && ID_Code < 46000 )),
        ClearCollect( activity_23, Filter( Activity_List, ID_Code >= 46000 && ID_Code < 48000 )),
        ClearCollect( activity_24, Filter( Activity_List, ID_Code >= 48000 && ID_Code < 50000 )),
        ClearCollect( activity_25, Filter( Activity_List, ID_Code >= 50000 && ID_Code < 52000 )),
        ClearCollect( activity_26, Filter( Activity_List, ID_Code >= 52000 && ID_Code < 54000 )),
        ClearCollect( activity_27, Filter( Activity_List, ID_Code >= 54000 && ID_Code < 56000 )),
        ClearCollect( activity_28, Filter( Activity_List, ID_Code >= 56000 && ID_Code < 58000 )),
        ClearCollect( activity_29, Filter( Activity_List, ID_Code >= 58000 && ID_Code < 60000 )),
        ClearCollect( activity_30, Filter( Activity_List, ID_Code >= 60000 && ID_Code < 62000 ))
    );
      
    // Step 4: Notify the user of successful submission
    Notify("Edit request successfully submitted", NotificationType.Success, 100);
   ClearCollect(activity,activity_00, activity_0, activity_1, activity_2, activity_3, activity_4, activity_5, activity_6, activity_7, activity_8, activity_9, activity_10, activity_11, activity_12, activity_13, activity_14, activity_15, activity_16, activity_17, activity_18, activity_19, activity_20, activity_21, activity_22, activity_23, activity_24, activity_25, activity_26, activity_27, activity_28, activity_29, activity_30);
    // Step 5: Reset the form
    ResetForm(Form_Activity_Edit);
    // Step 6: Clear the form submission flag
    Set(FormSubmissionInProgress, false)
);

Categories:

Power Query

I have the same question (0)

All responses (3)

Answers (1)

Verified answer

WarrenBelz 153,079 Most Valuable Professional on at

Like (0)

Report
Hi @JD_26
The fundamental issue is that Power Apps has a Data Row Limit of 2,000 for good reason - the performance / capacity of the device to store and query data "locally". The size of the SharePoint list itself is generally not the issue as any Delegable filter will quickly and efficiently (although 200k may not be as fast) return the data you need. I suspect you are using non-Delegable filters in the Gallery, hence the enormous collection.

The other option if your current performance is acceptable is to also Patch the Collection when you update the SP List, so you do not need to re-collect every time. I would however expect a collection of 200k having query performance quite glacial.

This may not be helpful to you, but you can collect all records in a List like this
Clear(activity); With( { _Sets: AddColumns( RenameColumns( Sequence( RoundDown( First( Sort( Activity_List, ID_Code, SortOrder.Descending ) ).ID_Code / 2000, 0 ) + 1, 0, 2000 ), Value, LowID ), HighID, LowID + 2000 ) }, ForAll( _Sets As _MaxMin, Collect( activity, Filter( Activity_List, ID_Code > _MaxMin.LowID && ID_Code <= _MaxMin.HighID ) ) ) )

Please click Does this answer your question if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it a Like.
MVP (Business Applications) Visit my blog Practical Power Apps Buy me a coffee

Was this reply helpful? Yes No
JD_26 102 on at

Like (0)

Report

@WarrenBelz , I am interested in your idea about patching the data to the collection when adding or updating an activity . I am also thinking of archiving the activity sharepoint list.

I was looking to see if there was a way to make a copy of the list , and then remove everything with a created date < year 2023 , then another sharepoint list being the live version with date created >= year 2023 , any help would be much appreciated

Was this reply helpful? Yes No
WarrenBelz 153,079 Most Valuable Professional on at

Like (0)

Report
Hi @JD_26

Firstly patching to the Collection is no different to Patching to the data source (same syntax)
Patch( activity, LookUp( activity, ID = ThisItem.ID ), { FieldName: ThisItem.ControlName.Output, . . . . . } )

The Archive Flow would look something like this for records over a year old.

and then the delete after checking they had all copied.

Lastly, is there any part of your gallery filter that is Delegable and can bring your sample down under 2,000 records - this blog of mine may be helpful if so.

Please click Does this answer your question if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it a Like.
MVP (Business Applications) Visit my blog Practical Power Apps Buy me a coffee

Was this reply helpful? Yes No