ClearCollect only collects data to one column

[trjackson77]

For performance reasons, I collect my product table from the SQL source.  The collection is correctly created with all fields.  However, only one field (RowID) is populated.

(The source table has data)

Here is the code:

Set(varLoadingSpinner,true);

ClearCollect(colPoSProducts,Filter(tjPoSProducts,RowID<=2000));
Collect(colPoSProducts,Filter(tjPoSProducts,RowID>2000,RowID<=4000));
Collect(colPoSProducts,Filter(tjPoSProducts,RowID>4000,RowID<=6000));
Collect(colPoSProducts,Filter(tjPoSProducts,RowID>6000,RowID<=8000));

Set(varLoadingSpinner,false);

I thought the issue may be to do with the loads running concurrently.  Though when I comment out the Collect statements the problem persists.

Has anyone come across this behaviour before?  Is there a fix/workaround? 

[timl]

Hi @trjackson77 

The cause of this problem will most likely be the "explicit column selection" feature of Power Apps, which itself is a performance optimisation.

The answer to this is problem is to explicitly specify the columns that you want to collect by calling the

ShowColumns function.


Set(varLoadingSpinner,true);

ClearCollect(colPoSProducts,ShowColumns(Filter(tjPoSProducts,RowID<=2000), "RowID", "YourCol1", "YourCol2"));
Collect(colPoSProducts,ShowColumns(Filter(tjPoSProducts,RowID>2000,RowID<=4000), "RowID", "YourCol1", "YourCol2"));
Collect(colPoSProducts,ShowColumns(Filter(tjPoSProducts,RowID>4000,RowID<=6000), "RowID", "YourCol1", "YourCol2"));
Collect(colPoSProducts,ShowColumns(Filter(tjPoSProducts,RowID>6000,RowID<=8000), "RowID", "YourCol1", "YourCol2"));

Set(varLoadingSpinner,false);

The additional comment I would make about this general strategy of collecting 8,000 records upfront is that delegation support with SQL Server is very good, and that caching records in this way can very often impact performance negatively.

[trjackson77]

Hi @timl , thanks for your response.  I also thought I'd need to be explicit with column selection (although I was unaware there was a specific performance measure to restrict implicit columns)

However, the result yields an empty but formed collection see an example result of three explicit columns as per your suggested code:

On the wider point of using SQL on the data source, I tend to agree.  However, as it's a PoS application I need it to return results immediately and regardless of the internet connection.  I also see that the large number of calls to the server from the app was impacting performance elsewhere in the business.

[timl]

Hi @trjackson77 

Thanks for clarifying your collection methodology, which I understand.

It's strange how Collect isn't collecting data when combined with ShowColumns.

One other technique you could try is to trick Power Apps into returning data for specific columns by referencing those columns in the filter condition like so...

ClearCollect(colPoSProducts,
             Filter(tjPoSProducts,
                    RowID<=2000 && 
                    ProductCode=ProductCode && 
                    ProductDescription=ProductDescription
              )
);

[trjackson77 · Accepted solution]

@timl ,

      Thanks again.  Your questions pointed me in the right direction.  Particularly the query around "explicit column selection" and the performance optimisation.

The reason for the problem was (and I admit, this will sound odd) I wasn't using the collection in the canvas app at all.  As soon as I started using the collection columns in fields the collection started populating.

I feel the "not using the collection in the app" requires some explanation.  My methodology was to get the app working, connect to the source if needs be.  From there, refactor and optimise for speed.  I tend to work in baby steps, so the first step was to bring in the collection using the code and view the collection.  I also wanted to understand the 'startup time' for the app if I was to preload the product table into a collection.  Once I had this secured I'd connect the collection to the gallery.

Thanks again for your help Tim.  

Regards,

Tim.

[StalinPonnusamy]

Hi @trjackson77 

Along with other answer, use concurrent to improve the performance and this is what I implemented.

Concurrent(
    ClearCollect(
    colMyRequests,
    Filter(
        DimCustomer,
        CustomerKey <= 2000
    )
),
ClearCollect(
    colMyRequests2000,
    Filter(
        DimCustomer,
        CustomerKey > 2000 && CustomerKey <= 4000
    )
)
,ClearCollect(
    colMyRequests4000,
    Filter(
        DimCustomer,
        CustomerKey > 4000 && CustomerKey <= 6000
    )
)
);

Collect(colMyRequests,colMyRequests2000,colMyRequests4000);

Your final collection will be colMyRequests

And Clear all other collection(s)

[Anonymous]

Hi @timl , I am trying using your code with showcolumns, but I still get blank values. I am also working with a SQL server.