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