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

Pulling in large-ish SQL tables

Hello,

Is this how everyone is pulling in large-ish SQL data sets (~35k records)? Or do you use another method?

  1. Set Advanced Settings max to `2000`
  2.  
Concurrent(
    ClearCollect(col1, Filter('[dbo].[bigSqltable]', recordID >= 1 && recordID <= 2000)),
    ClearCollect(col2, Filter('[dbo].[bigSqltable]', recordID >= 2001 && recordID <= 4000)),
    ClearCollect(col3, Filter('[dbo].[bigSqltable]', recordID >= 4001 && recordID <= 6000)),
    ClearCollect(col4, Filter('[dbo].[bigSqltable]', recordID >= 6001 && recordID <= 8000)),
    ClearCollect(col5, Filter('[dbo].[bigSqltable]', recordID >= 8001 && recordID <= 10000)),
    ClearCollect(col6, Filter('[dbo].[bigSqltable]', recordID >= 10001 && recordID <= 12000)),
    ClearCollect(col7, Filter('[dbo].[bigSqltable]', recordID >= 12001 && recordID <= 14000)),
    ClearCollect(col8, Filter('[dbo].[bigSqltable]', recordID >= 14001 && recordID <= 16000)),
    ClearCollect(col9, Filter('[dbo].[bigSqltable]', recordID >= 16001 && recordID <= 18000)),
    ClearCollect(col10, Filter('[dbo].[bigSqltable]', recordID >= 18001 && recordID <= 20000)),
    ClearCollect(col11, Filter('[dbo].[bigSqltable]', recordID >= 20001 && recordID <= 22000)),
    ClearCollect(col12, Filter('[dbo].[bigSqltable]', recordID >= 22001 && recordID <= 24000)),
    ClearCollect(col13, Filter('[dbo].[bigSqltable]', recordID >= 24001 && recordID <= 26000)),
    ClearCollect(col14, Filter('[dbo].[bigSqltable]', recordID >= 26001 && recordID <= 28000)),
    ClearCollect(col15, Filter('[dbo].[bigSqltable]', recordID >= 28001 && recordID <= 30000)),
    ClearCollect(col16, Filter('[dbo].[bigSqltable]', recordID >= 30001 && recordID <= 32000)),
    ClearCollect(col17, Filter('[dbo].[bigSqltable]', recordID >= 32001 && recordID <= 34000)),
    ClearCollect(col18, Filter('[dbo].[bigSqltable]', recordID >= 34001 && recordID <= 35000))
);
ClearCollect(colCombined, 
    col1, col2, col3, col4, col5, col6, col7, col8, col9, col10, col11, col12, col13, col14, col15, col16, col17, col18
)
33 REPLIES 33
Super User
Super User

Re: Pulling in large-ish SQL tables

@ericonline  Don't know about everyone else, but plus 1 here.  Have used that method for SharePoint lists that are large - with the exception that I'm typically using a text field and startswith to get the same results (as the < and > are not delegable on SharePoint)

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
Super User
Super User

Re: Pulling in large-ish SQL tables

Awesome. Thanks for sharing! Curious what others are doing as well. 

Super User
Super User

Re: Pulling in large-ish SQL tables

@ericonline  I really see no other alternative if you need to pull the whole list/table.  I do try to avoid that as much as possible, but there are times...

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
Super User
Super User

Re: Pulling in large-ish SQL tables

Hi @ericonline 

Out of interest, why do you need to pull in such a large table? Is it because you want to run some non-delegable query against the data, or perhaps you want to work offline with this data?

The reason I ask is because if there's a specific requirement you have, maybe someone can suggest a solution that doesn't require you to pull in 35k rows.

Super User
Super User

Re: Pulling in large-ish SQL tables

Right on @timl. Yeah, I'd like to take advantage of the recent "SaveData/LoadData Unleashed" announcement for local caching.
Idea:

  • Pull down all 35k records in an intial "Sync" action (using Azure SQL, this is ~6 seconds!)
  • SaveData to device
  • LoadData on subsequent app starts
  • Have lightning fast Lookups, Filters and Searches using Collections
  • Once a week or so, have user "Sync" again to pull latest data down
  • Rinse, Repeat.

My first attempt at this goal, so putting feelers out!

Super User
Super User

Re: Pulling in large-ish SQL tables

@timl  I can't speak for @ericonline needs, but I can explain our most recent.

We had a SharePoint list of contacts that was around 5000 contacts.  The customer wanted an app to be able to pull up any contact through name or company name.  We needed to search at least 2 fields for the information they wanted. It was a simple request, but took some doing on the PowerApps side.  The only solution was to pull down all of the items in a series of Filters using StartsWith and every letter of the alphabet on each column.  Then combine them all together and use that to do all the rest of the requirements in the app.  

The bigger trick was that since we were collecting twice, once for name and once for company, we would get duplicates in the collection, so the final step was to Filter them out on the final collect.

We made use of the Concurrent function to reduce the amount of time for loading.  Also, we had a separate collection (alpha) that had all the letters of the Alphabet in and a status field for if the names were loaded and the companies were loaded for that letter.  This would be updated after each complete of a "grab" from the SharePoint. And, this was used then on a Gallery of all the Alphabet letters to change their appearance as they were loaded. (Kind of gave the user a sense that things were happening as they were).

 

Anybody interested???  Here is the formula:

 

//Build the alpha collection for status and feedback to user.
ClearCollect(alpha, {disp:"A",nameLoad:false, compLoad:false}, {disp:"B",nameLoad:false, compLoad:false}, {disp:"C",nameLoad:false, compLoad:false}, {disp:"D",nameLoad:false, compLoad:false}, {disp:"E",nameLoad:false, compLoad:false}, {disp:"F",nameLoad:false, compLoad:false}, {disp:"G",nameLoad:false, compLoad:false}, {disp:"H",nameLoad:false, compLoad:false}, {disp:"I",nameLoad:false, compLoad:false}, {disp:"J",nameLoad:false, compLoad:false}, {disp:"K",nameLoad:false, compLoad:false}, {disp:"L",nameLoad:false, compLoad:false}, {disp:"M",nameLoad:false, compLoad:false}, {disp:"N",nameLoad:false, compLoad:false}, {disp:"O",nameLoad:false, compLoad:false}, {disp:"P",nameLoad:false, compLoad:false}, {disp:"Q",nameLoad:false, compLoad:false}, {disp:"R",nameLoad:false, compLoad:false}, {disp:"S",nameLoad:false, compLoad:false}, {disp:"T",nameLoad:false, compLoad:false}, {disp:"U",nameLoad:false, compLoad:false}, {disp:"V",nameLoad:false, compLoad:false}, {disp:"W",nameLoad:false, compLoad:false}, {disp:"X",nameLoad:false, compLoad:false}, {disp:"Y",nameLoad:false, compLoad:false}, {disp:"Z",nameLoad:false, compLoad:false}); UpdateContext({loadMsg:"Loading Names"});
//Concurrently filter all letters of the alphabet on the Title column, update status as you go.
Concurrent( ClearCollect(local_A, SortByColumns(Filter(Contacts, StartsWith(Title, "A")), "Title")); Patch(alpha, LookUp(alpha, disp="A"), {nameLoad:true}), ClearCollect(local_B, SortByColumns(Filter(Contacts, StartsWith(Title, "B")), "Title")); Patch(alpha, LookUp(alpha, disp="B"), {nameLoad:true}), ClearCollect(local_C, SortByColumns(Filter(Contacts, StartsWith(Title, "C")), "Title")); Patch(alpha, LookUp(alpha, disp="C"), {nameLoad:true}), ClearCollect(local_D, SortByColumns(Filter(Contacts, StartsWith(Title, "D")), "Title")); Patch(alpha, LookUp(alpha, disp="D"), {nameLoad:true}), ClearCollect(local_E, SortByColumns(Filter(Contacts, StartsWith(Title, "E")), "Title")); Patch(alpha, LookUp(alpha, disp="E"), {nameLoad:true}), ClearCollect(local_F, SortByColumns(Filter(Contacts, StartsWith(Title, "F")), "Title")); Patch(alpha, LookUp(alpha, disp="F"), {nameLoad:true}), ClearCollect(local_G, SortByColumns(Filter(Contacts, StartsWith(Title, "G")), "Title")); Patch(alpha, LookUp(alpha, disp="G"), {nameLoad:true}), ClearCollect(local_H, SortByColumns(Filter(Contacts, StartsWith(Title, "H")), "Title")); Patch(alpha, LookUp(alpha, disp="H"), {nameLoad:true}), ClearCollect(local_I, SortByColumns(Filter(Contacts, StartsWith(Title, "I")), "Title")); Patch(alpha, LookUp(alpha, disp="I"), {nameLoad:true}), ClearCollect(local_J, SortByColumns(Filter(Contacts, StartsWith(Title, "J")), "Title")); Patch(alpha, LookUp(alpha, disp="J"), {nameLoad:true}), ClearCollect(local_K, SortByColumns(Filter(Contacts, StartsWith(Title, "K")), "Title")); Patch(alpha, LookUp(alpha, disp="K"), {nameLoad:true}), ClearCollect(local_L, SortByColumns(Filter(Contacts, StartsWith(Title, "L")), "Title")); Patch(alpha, LookUp(alpha, disp="L"), {nameLoad:true}), ClearCollect(local_M, SortByColumns(Filter(Contacts, StartsWith(Title, "M")), "Title")); Patch(alpha, LookUp(alpha, disp="M"), {nameLoad:true}), ClearCollect(local_N, SortByColumns(Filter(Contacts, StartsWith(Title, "N")), "Title")); Patch(alpha, LookUp(alpha, disp="N"), {nameLoad:true}), ClearCollect(local_O, SortByColumns(Filter(Contacts, StartsWith(Title, "O")), "Title")); Patch(alpha, LookUp(alpha, disp="O"), {nameLoad:true}), ClearCollect(local_P, SortByColumns(Filter(Contacts, StartsWith(Title, "P")), "Title")); Patch(alpha, LookUp(alpha, disp="P"), {nameLoad:true}), ClearCollect(local_Q, SortByColumns(Filter(Contacts, StartsWith(Title, "Q")), "Title")); Patch(alpha, LookUp(alpha, disp="Q"), {nameLoad:true}), ClearCollect(local_R, SortByColumns(Filter(Contacts, StartsWith(Title, "R")), "Title")); Patch(alpha, LookUp(alpha, disp="R"), {nameLoad:true}), ClearCollect(local_S, SortByColumns(Filter(Contacts, StartsWith(Title, "S")), "Title")); Patch(alpha, LookUp(alpha, disp="S"), {nameLoad:true}), ClearCollect(local_T, SortByColumns(Filter(Contacts, StartsWith(Title, "T")), "Title")); Patch(alpha, LookUp(alpha, disp="T"), {nameLoad:true}), ClearCollect(local_U, SortByColumns(Filter(Contacts, StartsWith(Title, "U")), "Title")); Patch(alpha, LookUp(alpha, disp="U"), {nameLoad:true}), ClearCollect(local_V, SortByColumns(Filter(Contacts, StartsWith(Title, "V")), "Title")); Patch(alpha, LookUp(alpha, disp="V"), {nameLoad:true}), ClearCollect(local_W, SortByColumns(Filter(Contacts, StartsWith(Title, "W")), "Title")); Patch(alpha, LookUp(alpha, disp="W"), {nameLoad:true}), ClearCollect(local_X, SortByColumns(Filter(Contacts, StartsWith(Title, "X")), "Title")); Patch(alpha, LookUp(alpha, disp="X"), {nameLoad:true}), ClearCollect(local_Y, SortByColumns(Filter(Contacts, StartsWith(Title, "Y")), "Title")); Patch(alpha, LookUp(alpha, disp="Y"), {nameLoad:true}), ClearCollect(local_Z, SortByColumns(Filter(Contacts, StartsWith(Title, "Z")), "Title")); Patch(alpha, LookUp(alpha, disp="Z"), {nameLoad:true}) ); UpdateContext({loadMsg:"Consolidating Names"});
//Combine the results of all collections into one.
ClearCollect(local, local_A, local_B, local_C, local_D, local_E, local_F, local_G, local_H, local_I, local_J, local_K, local_L, local_M, local_N, local_O, local_P, local_Q, local_R, local_S, local_T, local_U, local_V, local_W, local_X, local_Y, local_Z); UpdateContext({loadMsg:"Loading Companies"}); //Concurrently filter all the letters of the alphabet on Company column, update status as you go.
Concurrent( ClearCollect(local_A, SortByColumns(Filter(Contacts, StartsWith(Company, "A")), "Title")); Patch(alpha, LookUp(alpha, disp="A"), {compLoad:true}), ClearCollect(local_B, SortByColumns(Filter(Contacts, StartsWith(Company, "B")), "Title")); Patch(alpha, LookUp(alpha, disp="B"), {compLoad:true}), ClearCollect(local_C, SortByColumns(Filter(Contacts, StartsWith(Company, "C")), "Title")); Patch(alpha, LookUp(alpha, disp="C"), {compLoad:true}), ClearCollect(local_D, SortByColumns(Filter(Contacts, StartsWith(Company, "D")), "Title")); Patch(alpha, LookUp(alpha, disp="D"), {compLoad:true}), ClearCollect(local_E, SortByColumns(Filter(Contacts, StartsWith(Company, "E")), "Title")); Patch(alpha, LookUp(alpha, disp="E"), {compLoad:true}), ClearCollect(local_F, SortByColumns(Filter(Contacts, StartsWith(Company, "F")), "Title")); Patch(alpha, LookUp(alpha, disp="F"), {compLoad:true}), ClearCollect(local_G, SortByColumns(Filter(Contacts, StartsWith(Company, "G")), "Title")); Patch(alpha, LookUp(alpha, disp="G"), {compLoad:true}), ClearCollect(local_H, SortByColumns(Filter(Contacts, StartsWith(Company, "H")), "Title")); Patch(alpha, LookUp(alpha, disp="H"), {compLoad:true}), ClearCollect(local_I, SortByColumns(Filter(Contacts, StartsWith(Company, "I")), "Title")); Patch(alpha, LookUp(alpha, disp="I"), {compLoad:true}), ClearCollect(local_J, SortByColumns(Filter(Contacts, StartsWith(Company, "J")), "Title")); Patch(alpha, LookUp(alpha, disp="J"), {compLoad:true}), ClearCollect(local_K, SortByColumns(Filter(Contacts, StartsWith(Company, "K")), "Title")); Patch(alpha, LookUp(alpha, disp="K"), {compLoad:true}), ClearCollect(local_L, SortByColumns(Filter(Contacts, StartsWith(Company, "L")), "Title")); Patch(alpha, LookUp(alpha, disp="L"), {compLoad:true}), ClearCollect(local_M, SortByColumns(Filter(Contacts, StartsWith(Company, "M")), "Title")); Patch(alpha, LookUp(alpha, disp="M"), {compLoad:true}), ClearCollect(local_N, SortByColumns(Filter(Contacts, StartsWith(Company, "N")), "Title")); Patch(alpha, LookUp(alpha, disp="N"), {compLoad:true}), ClearCollect(local_O, SortByColumns(Filter(Contacts, StartsWith(Company, "O")), "Title")); Patch(alpha, LookUp(alpha, disp="O"), {compLoad:true}), ClearCollect(local_P, SortByColumns(Filter(Contacts, StartsWith(Company, "P")), "Title")); Patch(alpha, LookUp(alpha, disp="P"), {compLoad:true}), ClearCollect(local_Q, SortByColumns(Filter(Contacts, StartsWith(Company, "Q")), "Title")); Patch(alpha, LookUp(alpha, disp="Q"), {compLoad:true}), ClearCollect(local_R, SortByColumns(Filter(Contacts, StartsWith(Company, "R")), "Title")); Patch(alpha, LookUp(alpha, disp="R"), {compLoad:true}), ClearCollect(local_S, SortByColumns(Filter(Contacts, StartsWith(Company, "S")), "Title")); Patch(alpha, LookUp(alpha, disp="S"), {compLoad:true}), ClearCollect(local_T, SortByColumns(Filter(Contacts, StartsWith(Company, "T")), "Title")); Patch(alpha, LookUp(alpha, disp="T"), {compLoad:true}), ClearCollect(local_U, SortByColumns(Filter(Contacts, StartsWith(Company, "U")), "Title")); Patch(alpha, LookUp(alpha, disp="U"), {compLoad:true}), ClearCollect(local_V, SortByColumns(Filter(Contacts, StartsWith(Company, "V")), "Title")); Patch(alpha, LookUp(alpha, disp="V"), {compLoad:true}), ClearCollect(local_W, SortByColumns(Filter(Contacts, StartsWith(Company, "W")), "Title")); Patch(alpha, LookUp(alpha, disp="W"), {compLoad:true}), ClearCollect(local_X, SortByColumns(Filter(Contacts, StartsWith(Company, "X")), "Title")); Patch(alpha, LookUp(alpha, disp="X"), {compLoad:true}), ClearCollect(local_Y, SortByColumns(Filter(Contacts, StartsWith(Company, "Y")), "Title")); Patch(alpha, LookUp(alpha, disp="Y"), {compLoad:true}), ClearCollect(local_Z, SortByColumns(Filter(Contacts, StartsWith(Company, "Z")), "Title")); Patch(alpha, LookUp(alpha, disp="Z"), {compLoad:true}) ); UpdateContext({distinctIDs: Distinct(local, ID)}); //Pull out any duplicate records by ID (which is unique) UpdateContext({loadMsg:"Consolidating Companies A"}); Collect(local, Filter(local_A, !(ID in distinctIDs))); UpdateContext({loadMsg:"Consolidating Companies B"}); Collect(local, Filter(local_B, !(ID in distinctIDs))); UpdateContext({loadMsg:"Consolidating Companies C"}); Collect(local, Filter(local_C, !(ID in distinctIDs))); UpdateContext({loadMsg:"Consolidating Companies D"}); Collect(local, Filter(local_D, !(ID in distinctIDs))); UpdateContext({loadMsg:"Consolidating Companies E"}); Collect(local, Filter(local_E, !(ID in distinctIDs))); UpdateContext({loadMsg:"Consolidating Companies F"}); Collect(local, Filter(local_F, !(ID in distinctIDs))); UpdateContext({loadMsg:"Consolidating Companies G"}); Collect(local, Filter(local_G, !(ID in distinctIDs))); UpdateContext({loadMsg:"Consolidating Companies H"}); Collect(local, Filter(local_H, !(ID in distinctIDs))); UpdateContext({loadMsg:"Consolidating Companies I"}); Collect(local, Filter(local_I, !(ID in distinctIDs))); UpdateContext({loadMsg:"Consolidating Companies J"}); Collect(local, Filter(local_J, !(ID in distinctIDs))); UpdateContext({loadMsg:"Consolidating Companies K"}); Collect(local, Filter(local_K, !(ID in distinctIDs))); UpdateContext({loadMsg:"Consolidating Companies L"}); Collect(local, Filter(local_L, !(ID in distinctIDs))); UpdateContext({loadMsg:"Consolidating Companies M"}); Collect(local, Filter(local_M, !(ID in distinctIDs))); UpdateContext({loadMsg:"Consolidating Companies N"}); Collect(local, Filter(local_N, !(ID in distinctIDs))); UpdateContext({loadMsg:"Consolidating Companies O"}); Collect(local, Filter(local_O, !(ID in distinctIDs))); UpdateContext({loadMsg:"Consolidating Companies P"}); Collect(local, Filter(local_P, !(ID in distinctIDs))); UpdateContext({loadMsg:"Consolidating Companies Q"}); Collect(local, Filter(local_Q, !(ID in distinctIDs))); UpdateContext({loadMsg:"Consolidating Companies R"}); Collect(local, Filter(local_R, !(ID in distinctIDs))); UpdateContext({loadMsg:"Consolidating Companies S"}); Collect(local, Filter(local_S, !(ID in distinctIDs))); UpdateContext({loadMsg:"Consolidating Companies T"}); Collect(local, Filter(local_T, !(ID in distinctIDs))); UpdateContext({loadMsg:"Consolidating Companies U"}); Collect(local, Filter(local_U, !(ID in distinctIDs))); UpdateContext({loadMsg:"Consolidating Companies V"}); Collect(local, Filter(local_V, !(ID in distinctIDs))); UpdateContext({loadMsg:"Consolidating Companies W"}); Collect(local, Filter(local_W, !(ID in distinctIDs))); UpdateContext({loadMsg:"Consolidating Companies X"}); Collect(local, Filter(local_X, !(ID in distinctIDs))); UpdateContext({loadMsg:"Consolidating Companies Y"}); Collect(local, Filter(local_Y, !(ID in distinctIDs))); UpdateContext({loadMsg:"Consolidating Companies Z"}); Collect(local, Filter(local_Z, !(ID in distinctIDs))); //Just because it seems cleaning up is nice to do... Clear(local_A); Clear(local_B); Clear(local_C); Clear(local_D); Clear(local_E); Clear(local_F); Clear(local_G); Clear(local_H); Clear(local_I); Clear(local_J); Clear(local_K); Clear(local_L); Clear(local_M); Clear(local_N); Clear(local_O); Clear(local_P); Clear(local_Q); Clear(local_R); Clear(local_S); Clear(local_T); Clear(local_U); Clear(local_V); Clear(local_W); Clear(local_X); Clear(local_Y); Clear(local_Z); UpdateContext({loadMsg:""}); UpdateContext({loadData:false, _initedContacts:true})

 

Oh how I longed for a good old For...Next!!

All of this on a Toggle so that we could call a Refresh easily if needed.

 

Enjoy!!

 

(Yikes!  Sorry for the big Formula dump)

 

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
Super User
Super User

Re: Pulling in large-ish SQL tables

Very clever @ericonline! I'm impressed with the very fast load time from Azure SQL. I'm sure it'll be an amazing app once you get it working.

Like @RandyHayes, I don't think there's any other practical way to do this. You probably know this but if there are columns in [bigSqltable] that you don't need, you could use a SQL View to exclude these and to avoid pulling in any additional data that you don't need.

Good luck!

Super User
Super User

Re: Pulling in large-ish SQL tables

@RandyHayes ,
How about:

OnStart of App:

Concurrent(
ClearCollect(col1, Filter(sharepointList, recordID >= 1 && recordID <= 2000)),
ClearCollect(col2, Filter(sharepointList, recordID >= 2001 && recordID <= 4000)),
ClearCollect(col3, Filter(sharepointList, recordID >= 4001 && recordID <= 6000))
);
ClearCollect(colCombined,col1,col2,col3)

Set Gallery Control Items Property to:

Filter(
    colCombined,
    searchBar.Text in firstName ||
    searchBar.Text in lastName ||
    searchBar.Text in companyName ||
    searchBar.Text in anyDarnColumnYouWantName
)

Prosper!!

Super User
Super User

Re: Pulling in large-ish SQL tables

Good point @timl. I had not thought about dropping columns like that to optimize even further. Thank you senor!

Helpful resources

Announcements
firstImage

PowerApps Monthly Community Call!

Join us next Wednesday for our Demo Extravaganza, October 16, 2019 8am PDT.

firstImage

Microsoft Business Applications Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

firstImage

Watch Sessions On Demand!

Continue your learning in our online communities.

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

FirstImage

Power Platform World Tour

Coming to a city near you

thirdimage

PowerApps Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

FourthImage

Join PowerApps User Group!!

Connect, share, and learn with your peers year-round

SecondImage

Power Platform Summit North America

Register by September 5 to save $200

Top Kudoed Authors
Users Online
Currently online: 416 members 5,037 guests
Please welcome our newest community members: