Pulling in large-ish SQL tables

[ericonline]

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
)

[TiagoFreire]

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

[RandyHayes]

@lostgical 

What kind of column is recordID ?

[DavesTechTips]

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.

[Anonymous]

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

Hi @Anonymous , 

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.

[Anonymous]

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

[Paulie78]

@Anonymous 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/ 

[Anonymous]

@Paulie78 

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

[Paulie78]

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

[Pulkit]

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