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. 

[v-bofeng-msft]

Hi @martinav :

If the number of records in "VIEW_ALL_PDM_LATEST" is less than 2000,you could try this solution:
1\Set the data row limit to 2000

2\Try this formula:

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

Save all records in the SQL table to a collection with one call, so that subsequent operations only need to call the data in the collection without sending a request to the SQL table.

Best Regards,

Bof

[martinav]

@v-bofeng-msft ,

I'm not sure I explained my issue very well.  Its not a matter of not getting data back.  Its a matter of making too many individual queries to SQL on the back side.  Lets look at an example.  If the list of part numbers in the ForAll() statement is this:

'99996-2'

'204232'

'1Y3221'

The ForAll() statement would result in three queries to SQL.  As such (This is actual from SQL Profiler):

select top 1
    [_].[DocumentID],
    [_].[DwgRevision],
    [_].[Description],
    [_].[ProjectName],
    [_].[PartNumber],
    [_].[CurrentStatusID],
    [_].[Pending],
    [_].[LatestRevisionNo],
    [_].[Filename],
    [_].[Version],
    [_].[Material],
    [_].[RvTbl_Description],
    [_].[RvTbl_DwgDate],
    [_].[RvTbl_Approved],
    [_].[RvTbl_Revision],
    [_].[TransitionID],
    [_].[ProjectID]
from [dbo].[VIEW_ALL_PDM_LATEST] as [_]
where [_].[PartNumber] = '999996-2' and [_].[PartNumber] is not null

select top 1
    [_].[DocumentID],
    [_].[DwgRevision],
    [_].[Description],
    [_].[ProjectName],
    [_].[PartNumber],
    [_].[CurrentStatusID],
    [_].[Pending],
    [_].[LatestRevisionNo],
    [_].[Filename],
    [_].[Version],
    [_].[Material],
    [_].[RvTbl_Description],
    [_].[RvTbl_DwgDate],
    [_].[RvTbl_Approved],
    [_].[RvTbl_Revision],
    [_].[TransitionID],
    [_].[ProjectID]
from [dbo].[VIEW_ALL_PDM_LATEST] as [_]
where [_].[PartNumber] = '204232' and [_].[PartNumber] is not null

select top 1
    [_].[DocumentID],
    [_].[DwgRevision],
    [_].[Description],
    [_].[ProjectName],
    [_].[PartNumber],
    [_].[CurrentStatusID],
    [_].[Pending],
    [_].[LatestRevisionNo],
    [_].[Filename],
    [_].[Version],
    [_].[Material],
    [_].[RvTbl_Description],
    [_].[RvTbl_DwgDate],
    [_].[RvTbl_Approved],
    [_].[RvTbl_Revision],
    [_].[TransitionID],
    [_].[ProjectID]
from [dbo].[VIEW_ALL_PDM_LATEST] as [_]
where [_].[PartNumber] = '1Y3221' and [_].[PartNumber] is not null

These three calls to SQL create multiple tasks, and quickly stack up, and consume CPU resources.  

What I want it to do is this:

select top 3
    [_].[DocumentID],
    [_].[DwgRevision],
    [_].[Description],
    [_].[ProjectName],
    [_].[PartNumber],
    [_].[CurrentStatusID],
    [_].[Pending],
    [_].[LatestRevisionNo],
    [_].[Filename],
    [_].[Version],
    [_].[Material],
    [_].[RvTbl_Description],
    [_].[RvTbl_DwgDate],
    [_].[RvTbl_Approved],
    [_].[RvTbl_Revision],
    [_].[TransitionID],
    [_].[ProjectID]
from [dbo].[VIEW_ALL_PDM_LATEST] as [_]
where ([_].[PartNumber] = '999996-2' or [_].[PartNumber] = '204232' or [_].[PartNumber] = '1Y3221') and [_].[PartNumber] is not null

This would reduce the resource burden tremendously.

Sometimes, the lists are quite long, and if I have several users, this compounds things.  Even with a single user online, this can become unusably slow, and PowerApps has even timed out waiting.  

Please advise.

[v-bofeng-msft]

Hi @martinav :

Please try:

Filter(SQLRows,PartNumber in Gallery1.AllItems.Part_x0020_Number)

Best Regards,

Bof

[StalinPonnusamy]

Hi @martinav

Each gallery item calls on-prem will be an expensive one.  And your point to combined multiple parts and send as one query is the best option. My opinion is to use lookup only on detail and edit screen if possible.

[martinav]

@v-bofeng-msft ,

This is a great idea.  However, its delegated.  This will perhaps limit results possible from SQLRows, right?  If I have more than 2000 lines in SQLRows, its quite possible I dont get all of the results.  Or, am I ok if the Gallery1 has fewer than 2000??  I'm not always certain how delegation works.

[StalinPonnusamy · Accepted solution]

Good thought @v-bofeng-msft .

@martinav , Please try this to avoid delegation. sData is a local temp variable name

ClearCollect(
    colParts,
    With(
        {sData: VIEW_ALL_PDM_LATEST},
        Filter(
            sData,
            PartNumber in Gallery1.AllItems.'Part Number'
        )
    )
)

• Yes, the Filter should work as long as the result is < 2000.
• I have my on-premises SQL has 80k and works fine.

[martinav]

@StalinPonnusamy ,

@v-bofeng-msft ,

Great thoughts both of you!

The first method did work.  Provided expected results, however, delegated.  

I changed it to the second method.  No delegation, and no errors, but no data returns.  It runs the right amount of time, the column names are there, but no data comes back into colParts.  Any ideas?

Thanks.

[martinav]

@StalinPonnusamy ,

@v-bofeng-msft ,

Actually, I had to exit and reenter the app.  Then it populated the collection properly.  However, there is something goin on with delegation.  There should be 78 lines in my result.  I get varying results and 19-21 lines.  So, it seems like its delegating something.  Despite the fact my SQL table is only 1575 lines, and row limit is set to 2000 in my app.  All results are valid, but I only get 19-21, and the lines are different ones each time when I recycle the statement.

[StalinPonnusamy]

I have only 10 records and works. 😀 Let me create more records in my environment and test this. I will update you both.