cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ericonline
Community Champion
Community Champion

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
)
39 REPLIES 39
TiagoFreire
Continued Contributor
Continued Contributor

Unrolled loops of ClearCollects would not scale as data grows. 
There is this little piece of dark magic that should work, though I have not tried it first hand.
It was created in the context of the user's CDM, but should work on Canvas apps I believe.
Nice thing is, it should work without preconceived knowledge of what's your maximum number of rows. 

Worth a look:
https://powerusers.microsoft.com/t5/General-Discussion/500-item-limit-in-CDM-entity-search-filter-ne...

 

@lostgical 

What kind of column is recordID ?

_____________________________________________________________________________________
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.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

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

Hello everyone.

 

We have also used the ClearCollect method in a ForAll loop, to batch import the records from SQL in pages of 2000. Pulling the data from a view helps a lot, but there are some instances where nothing beats using SQL queries or stored procedures.

 

Below are two videos dealing with advanced queries to SQL from PowerApps (using Flow).
For SQL Azure you can use direct (native) queries:
https://youtu.be/DII10gK715I

 

For on-premesis SQL servers, native queries are not supported from Flow (you will get an error: operation Execute Native Sql is currently not supported using an on-prem gateway connection), so we have to use stored procedures to accomplish the same:
https://youtu.be/BAGBzI4zdww

 

Please let me know if you don't come right.

Dawid van Heerden
Follow on Twitter: @davestechtips
Subscribe to YouTube: https://www.youtube.com/davestechtips?sub_confirmation=1
**If you found this reply helpful, please mark this as the answer to close the topic and make it easier to find for other people with similar questions.

@RandyHayes 

This is great mate! Wish I'd found this earlier in the year. I need large SharePoint dataset filter options and I should be able to work with this process - unless you have developed something better since then 🙂

 

Cheers

ericonline
Community Champion
Community Champion

Hi @Eelman , 

I've been using Flow to query large Sharepoint lists recently. There is an action called Send an HTTP Request to Sharepoint that will allow you to interact with the API. It can be temperamental, but I've successfully used it on lists > 20k records and views > 5k records. 

Holler if you want to chat further.

@ericonline 

Thanks mate. I just had a read over the docs for Send an HTTP Request to Sharepoint and it's a little above my understanding, currently. I would love to see how you use this flow, if you are able to show me, because I'm all for learning something new.  

@Eelman I've written up a guide and provided sample flows for the action Send an HTTP Request to SharePoint, which I was able to use to get 17,000 rows of data into a PowerApps:

 

Use SharePoint REST API from Power Automate to get large SharePoint lists 

 

You can use this method, which is much faster than the standard "Get Items" method, or if you want to get the data into your app really quickly use the methods I described here (which are a little more complicated):

 

https://www.tachytelic.net/2020/04/many-ways-get-sharepoint-items-power-automate/ 

@Paulie78 

Thanks mate, I will definitely be giving this a try. Might have to hit you up if I get stuck though 🙂 

@Eelman  No worries - please do, bit of feedback will make it easier for me to know if and where I went wrong on the blog posts/video.

Is there any way of pulling all data through ID? I am new at Powerapps.

Helpful resources

Announcements
PA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

secondImage

Power Apps Community Call

Please join us on Wednesday, October 20th, at 8a PDT. Come and learn from our amazing speakers!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Top Solution Authors
Users online (2,347)