Unanswered

Best way to store and manipulate very large lists of data.

(0) Share

Report

Posted on by martinav

3,340

I have had some experience now with some moderate sized databases. I have a situation where I have a very large list. Currently 80,000 rows. This will grow every year by 10,000. I want this app to be able to do basic searches on the entire list. Nothing ultra complex, but simple individual IDs, date range, by match in one column or progressive column matches.

I have used SQL (through on-prem), and sharepoint online. To me, it seems that SQL is much more suited for large lists. However, the on-prem can be a challenge. But also isnt SQL more flexible with regards to delegation?

I'm curious on some suggestions.

Thank you!

Categories:

Building Power Apps

I have the same question (0)

All responses (15)

Answers (0)

RandyHayes 76,297 Super User 2024 Season 1 on at

Like (2)

Report

@martinav

Delegation is going to be pretty much the same between SQL and SharePoint. There are minor differences, but not significant.

What is more significant with SQL is that you can create filtered views of your data and then use that as a datasource.

If your criteria for your filters works against delegable columns - and your result set is less than 2000 (same for any datasource), then SharePoint can work fine if you are trying to avoid the on-prem scenario.

Keep in mind that you have to contend with record limits and delegation. These are two different things. All app are constrained by 2000 record limits. You can never bring back more than 2000. However, if you have the ability to pre-filter via delegable criteria that will result in less than 2000, then you can post filter with non-delegable criteria against that record set.

So, the above is relatively generic and applies to both SharePoint and SQL.

I hope this is helpful for you.

Was this reply helpful? Yes No
martinav 3,340 on at

Like (0)

Report

@RandyHayes ,

Actually, those details I did know. What I'm more interested in is which one would be better performance. My gut tells me (as well as I'm operating within the rules you state above) that SQL has the potential of a quicker result. Filtered views is definitely an advantage. In the future, if I decide to archive, I can add a single row to the master table, and make it 0 or 1. Then I can make a view of current vs archived, etc. Just a simple example.

As far as ultimate record limits of SP and SQL.... where do we stand with this?

Was this reply helpful? Yes No
RandyHayes 76,297 Super User 2024 Season 1 on at

Like (0)

Report

@martinav

I actually find that SQL seems a little slower in many of our apps. Just a casual observation though, no definitive proof.

As for the archive aspect, you could achieve this in either SharePoint or SQL as well.

You can have up to 30 million records in a SharePoint list. SQL will be more dependent on the version of SQL you are using, but it too is more than ample for your needs.

Was this reply helpful? Yes No
martinav 3,340 on at

Like (0)

Report

@RandyHayes ,

How about initial data load? Or adding large amounts at once? I find using SSMS to add tons of data at once is pretty easy. IN sp.. It seems very slow. I know there is this sort of table edit mode where you can paste in data, but it seems that only works with a limited number of rows at a time, and it becomes quickly resource saturated.

I'm going to have several spreadsheets. I know with either tool, I can ingest the excel file into a new table. I suppose I should do all of my data handling in excel, then ingest it for the initial load.

Thoughts?

Was this reply helpful? Yes No
RandyHayes 76,297 Super User 2024 Season 1 on at

Like (0)

Report

@martinav

Depends on the size of that initial load. SQL is going to be far more superior in data management and manipulation.

But, if there is an Excel file as the start of this initial load, you could also consider a PowerAutomate flow to get the data in as needed. It would still be slow for processing if there are a lot, but at least it is not a user waiting for it to complete.

Was this reply helpful? Yes No
martinav 3,340 on at

Like (1)

Report

@RandyHayes ,

If I remember right, there is a ingest function in SP for an Excel file to create a new list. I'll play with each one and see how they respond. I do have 80,000 rows now that I can play with to get some practical tests completed.

I'll post here for others to enjoy.

Rod

Was this reply helpful? Yes No
RandyHayes 76,297 Super User 2024 Season 1 on at

Like (0)

Report

@martinav

That would be valuable information to share with all!!

Was this reply helpful? Yes No
martinav 3,340 on at

Like (0)

Report

New Table -> From Excel in SP seems to struggle here. I'm sure SP can handle more rows... maybe the import tool cannot?

Was this reply helpful? Yes No
RandyHayes 76,297 Super User 2024 Season 1 on at

Like (0)

Report

@martinav

I would say that is a limitation of the tool...not SharePoint.

Was this reply helpful? Yes No
martinav 3,340 on at

Like (0)

Report

@RandyHayes ,

Yes! I was sure there was a way. I found the 'old' tool. I ingested 87,000 lines in one shot from the excel spreadsheet. It didnt take but a couple of hours. No errors or interruptions! I'll post a how-to here in a bit.

Was this reply helpful? Yes No