Slice Large SQL Table By Multiple Parameters

[ericonline]

Hello,

I have a SQL Table with 6.5M records. I'd like the user to enter 3 "slicers" to pare this data set down to one value. I'll then look up this value in another table to apply a Lat/Long, and append the Lat/Long to a Launch() command or a custom connector API query.

What is the best practice (performance-wise) for "slicing" this data set?

Example:

- Three combo boxes in the application (Value1, Value2, Value3)

    - Value2, Value 3 are disabled until user searches and selects a value in Value1 combobox

- User enters "21" in Value1 combobox

    - System Searches/Filters/etc the Value1 column of the 6.5M record SQL table

    - Value2 combobox becomes editable

- User enters "14.45" in Value2 combobox

    - System Searches/Filters/etc the Value2 column of the now 1.3M record SQL table (its already been filtered based on Value1)

    - Value3 combobox becomes editable

- User enters "x" in Value3 combobox

    - System Searches/Filters/etc the Value 3 column of the now 12 record SQL table (its already been filtered based on Value1 and Value2)

    - Results are displayed in a Gallery control.

What do I set the Items Property of the Value1-3 comboboxes?

So far I've tried:

Value1 combobox:

Distinct(
    '[dbo].[6MRecordSQLTable]',
    Value1
)

Value2 combobox:

Sort(
    Distinct(
        Filter(
            '[dbo].[6MRecordSQLTable]',
            Value1 = Value1Combobox.Selected.Value
        ), Value2
    ),Result,Ascending
)

Value3 combobox:

Distinct(
    Filter(
        '[dbo].[6MRecordSQLTable]',
        And(
            Value1 = Value1Combobox.Selected.Value,
            Value2 = Value2Combobox.Selected.Value
        )
    ), Value3
)

Hope this is clear. Its a challenging thing to communicate.

Thank you!

[Drrickryp]

Hi @ericonline,

I wanted to play around with this problem and set up a datasource from USA zipcodes.  It consists of 52,000 entries, not nearly as big as your datasource.  I tried to reproduce your issue by using State, City and ZipCode in the Comboboxes.   I immediately ran into problems with delegation for the Distinct() function as only the first 2k records were processed and not all of the states were represented in the results. Groupby()  wouldn't work either since it isn't delegatable.   I'm not sure how to get around this except by coming up with separate tables of unique values at the server level for the first two fields.

Addendum:  Interestingly, I was able to capture all of the States by sorting the table by City and then using Distinct on the sorted list by State.  By sorting by city first, all of the states were represented in the first 2000 rows.  

[jared_simmons]

Correct, I've experienced similar bumps, I resulted in harcoding a few dozen selections into a combo (my tables were 10k-15k), but it would be nice to know officially how to handle this... I want to say PowerApps recommends bringing everything down and storing it into a Collection and then doing something, can't say for sure though.

[Drrickryp]

Hi @jared_simmons and @ericonline,

When the delegation limit was 500 items, @mr-dang came up with a very cool way to build a huge collection.  It may still be possible to try that but it would put a strain on the resources available to some devices. 

[Drrickryp]

This was the article. https://powerusers.microsoft.com/t5/General-Discussion/Solution-Overcome-Delegation-for-Aggregate-Fu...

[mr-dang]

If those are the only 3 scenarios you're working with, I would create a SQL view for each of them. Show the desired gallery with the SQL view depending on what fields they interact with.

Let me get ahead and say you do not want to bring 6.5 Million records into PowerApps 🙂

[ericonline]

Hello @mr-dang,

I was able to create the following SQL View. Works well for the Items Property of Value1 dropdown. Took about 20 seconds to populate.

• CREATE VIEW [distinctValue1] AS
  SELECT DISTINCT value1
  FROM giantSQLTable

What would the WHERE clause of a SQL View for the Value2 and Value3 dropdowns look like? I feel like I want to create a view like:

• CREATE VIEW [filteredValue2] AS
  SELECT value2
  FROM distinctValue1 //Previously created SQL View
  WHERE value2 = value1.Selected.Value // <--HA!

[mr-dang]

Ah, I am sorry I did not look closely at your filters. The connector to SQL does not support a way for a View to interact with a parameter in PowerApps.

I am thinking a solution for you would be to run the desired query through flow and pass it parameters from PowerApps. Here's a video on how to declare parameters:

https://www.youtube.com/watch?v=Tz67Deb_3RA

Is this more in line with what you're looking for?

[ericonline]

Well, I got something to work. The performance will not pass UAT, but it does work. 

OnStart/OnVisible Property:

//Grab all distinct Value1's from the SQL View. Results in ~1050 records
ClearCollect(
    colAllValue1,
        '[dbo].[distinctValue1]'
)

OnChange Property of Value1 dropdown:

//Create a collection of distinct Value2 based on Value1 dropdown selection.
ClearCollect(
    colValue2,
    Distinct(
        Filter(
            '[dbo].[giantSQLTable]',
            value1Column = Value1.Selected.Value
        ), mp
    )
)

Items Property of Value2 dropdown: colValue2

Repeat this logic for Value2 dropdown OnChange Property and Value3 dropdown Items Property.

To perform the final lookup I need:

//Use the user selected values to lookup lat/long
Concatenate(
    LookUp(
        '[dbo].[giantSQLTable]',
        And(
            value1Column = Value1.Selected.Value,
            value2Column = Value2.Selected.Value,
            value3Column = Value3.Selected.Value
        ), latitude
    ), ",",
    LookUp(
        '[dbo].[giantSQLTable]',
        And(
            value1Column = Value1.Selected.Value,
            value2Column = Value2.Selected.Value,
            value3Column = Value3.Selected.Value
        ), longitude
    )
)

[ericonline]

WHOA!

So... I decided to go into Azure Portal and beef up the Pricing Tier from S0: 10DTU's to S1: 20DTU's. The speeds are approaching managable!

• FROM 1:00-1:30 per "filter" outlined above
• TO 00:08-00:10 per "filter" outlined above

Not bad for $25/mo!