How to send a combined query to SQL from Powerapps

[martinav]

I have a desparate need to reduce calls to SQL through the on-prem gateway due to some problem with the SQL install.  More than just a few concurrent calls, and the physical server CPUs max out, and response is nearly null.  I need to call for multiple rows of a SQL table with given IndexIDs.  It cannot be filtered, but a list of IDs and get the complete rows of data.  This command "works", but it results in a separate Query for each item in the list.  How can I do this with an "or" in my SQL query?

ClearCollect(Rows_Test,
    ForAll(Gallery1.AllItems,
        LookUp(VIEW_ALL_PDM_LATEST,
               PartNumber=Part_x0020_Number)
    )
)

My apps are currently down because the server is useless when you try to run them.  Even a single user, and SQL is brought down.

What changed?

Only this:  OS upgarde from MS Server 2014 Enterprise to MS Server 2019 Std, and SQL 2014 Std to SQL 2019 Std SP1 CU12.  Also the on-prem gateway was upgdated from May 2021 to July 2021.

Please advise!  I am desparate here. 

[martinav]

For some reason, i disconnected and reconnected to the SQL View... and it worked.   Thanks!!!

[martinav]

@StalinPonnusamy , @v-bofeng-msft ,

**URGENT**

Well... #$%#.  I missed your point that the filter is <2000.  I had been harping on delegation the entire thread.  Well, I just found this out the hard way.  I need a way to do this for a table >2000.  I was **bleep** near done, then found this out.  No warning in PA regarding delegation.  Very dissappointing!  I made promises about this being done very shortly, just to find this out.

ClearCollect(ProjectID_ActiveECN, 
             With({sData3: '[dbo].[DocumentsInProjects]'}, 
             Filter(sData3,DocumentID in MS_VIEW_ALL_PDM_LATEST.DocumentID)))

[StalinPonnusamy]

Hi @martinav

Our SQL table may have more than 2000 rows. No issues. But the result after the filter should be less than 2000. Your gallery should have less than 2000 records. so getting records should not be an issue.

Need to split the query into 2 or 3 and joins together If the result is more than 2000. First query has half of the gallery items and another has another half.

Here is an example if the result is more than 2000.

Concurrent(
    ClearCollect(
        colParts1,
        With(
            {sData: DimProduct},
            Filter(
                sData,
                ProductAlternateKey in Gallery1.AllItems.ProductAlternateKey
                && ProductKey <= 1500
            )
        )
    ),
    ClearCollect(
        colParts2,
        With(
            {sData: DimProduct},
            Filter(
                sData,
                ProductAlternateKey in Gallery1.AllItems.ProductAlternateKey
                && ProductKey > 1500
            )
        )
    )
);
Collect(
    colParts1,
    colParts2
);

[martinav]

@StalinPonnusamy ,

Hold on... I got that wrong.  I panicked a bit.  My filter result is only 4 records.  It should be around 70.  But, then I find in the monitor, that it is prefetching 2000 rows from my SQL table.  Hence, delegated.  Is there something I need to do differently?

[StalinPonnusamy]

This request assumes you are requesting 2000 records since your PowerApps default settings to pull 2000 records. We should be fine as long as the result is less than 2000 records.

[martinav]

@StalinPonnusamy ,

Yes, that that is the point of delegation.  There are well over 2000 rows.  Its an incomplete pull.  Whats the difference?  Still doesnt get all the results I need.  Only 4, and should be over 70.  You mention you have one with over 80k rows.  How to you manage that with only a pull of 2000?

[StalinPonnusamy]

Testing with more data. Let you know soon.

[martinav]

@StalinPonnusamy ,

Thanks much!  I'm ok for the time-being.  I disabled the feature that required that data.  I at least have my app back up and running.  BUt, i'm getting very close to 2000 rows for two other databases.  I'll need this solution sooner than later!!

Rod

[StalinPonnusamy]

Hi @martinav

I have 2500 records in my on-premises SQL. And Gallery has 100 records. It pulled all 100 based on my gallery

ClearCollect(
    colProductAlternateKey,
    Gallery1.AllItems.ProductAlternateKey
);
ClearCollect(
    colParts1,
    With(
        {sData: DimProduct},
        Filter(
            sData,
            ProductAlternateKey in Gallery1.AllItems.ProductAlternateKey
        )
    )
);

Note: initially it says 0 and I closed the app and reopened and worked fine.

[martinav]

@StalinPonnusamy ,

Oh, sure.  Your Gallery does a pre-filter on the sql server, so your are golden.  Mine is not that way.  My filter is a list of somewhat random ID's.  Not something I can filter first on the server, then pull into PA.  If I had some characteristic I could filter for first, that would be desired.  The best I may be able to do is to sort by creation date, newest first.  That should cover 99.9% of all situations.  But, there may be that rare outlyer.  If it happens, no one will remember why, and it will make people crazy.