static async Task queryAirportsPaginated()
{
// SELECT COUNT(1) FROM c <-- 1459
// SELECT COUNT(1) FROM c where c.timezone_num = "-5" <-- 522
// SELECT c.pk FROM c where c.timezone_num = '-5' order by c.pk offset 140 limit 20
// https://docs.microsoft.com/en-us/azure/cosmos-db/sql-query-offset-limit
// https://www.postgresql.org/docs/current/queries-limit.html
// https://github.com/azure/azure-documentdb-datamigrationtool
//
// "The rows skipped by an OFFSET clause still have to be computed inside the server;
// therefore a large OFFSET can be inefficient."
log("queryAirportsPaginated");
CosmosUtil cu = new CosmosUtil();
string dbname = dbNameEnvVar();
string cname = "airports";
await cu.setCurrentDatabase(dbname);
await cu.setCurrentContainer(cname);
string predicate = "where c.timezone_num = '-5'";
int itemCount = await cu.count(predicate);
int offset = 0;
int itemsPerPage = 20;
log("itemCount: " + itemCount);
log("itemsPerPage: " + itemsPerPage);
double pagesDouble = ((double)itemCount / (double)itemsPerPage);
double pagesCount = Math.Ceiling(pagesDouble);
log("pagesCount: " + pagesCount);
for (int p = 0; p < pagesCount; p++)
{
offset = offset + itemsPerPage;
string sql = $"SELECT c.pk FROM c {predicate} order by c.pk offset {offset} limit {itemsPerPage}";
log("===");
log($"page: {p} sql: {sql}");
List <dynamic> items = await cu.queryDocuments(sql, itemsPerPage, true);
for (int i = 0; i < items.Count; i++)
{
Console.WriteLine(items[i]);
}
}
return;
}
