cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
martinav
Super User
Super User

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

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!

13 REPLIES 13
RandyHayes
Super User
Super User

@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.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

@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?

RandyHayes
Super User
Super User

@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.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!
martinav
Super User
Super User

@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?

RandyHayes
Super User
Super User

@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.

 

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

@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

RandyHayes
Super User
Super User

@martinav 

That would be valuable information to share with all!!

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!
martinav
Super User
Super User

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

 

martinav_0-1629391754854.png

 

RandyHayes
Super User
Super User

@martinav 

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

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

Helpful resources

Announcements
PA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

This training provides practical hands-on experience in creating Power Apps solutions in a full-day of instructor-led App creation workshop.

Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Top Solution Authors
Top Kudoed Authors
Users online (1,290)