Creating a Collection of > 2000 items of SQL Table

[martinav]

I have a app that searches an SQL table of over 10,000 rows.  The lookups are very slow.  I want to pull that table into a collection so it speeds it up a bit.  However, the table is only 2,000 rows with clearcollect().  I thought that an SQL table was not like a sharepoint table.  That you could have more rows than 2,000.  We figured out how to get 4,000 rows from a SP table via this post:

https://powerusers.microsoft.com/t5/General-Discussion/Collections-maxes-out-at-2000-when-populating...

I was hoping to get a larger SQL table.  Perhaps I can make an index, and grab them 2,000 at a time using some GT/LT scenarios with the index.

More later... unless someone has a better idea.

[timl · Accepted solution]

Hi @martinav

If your table has a numeric id column, you can use @mr-dangs technique here.

https://powerusers.microsoft.com/t5/General-Discussion/500-item-limit-in-CDM-entity-search-filter-ne...

This uses the method that you describe, which is to grab the records in batches inside a ForAll loop.

Out of interest, why do need to collect such a large number of records into your app? If you want to join records from multiple SQL tables for display purposes, could you not use a View instead?

[martinav]

Yes!  That is what I was thinking about.  Nice to have the work all done, with a nice autmatic iteration counter.  Schweet!

Many thanks.