Google BigQuery unable to process larger result set getting "Response too large to return" or "Resources exceeded during query execution" -

i working large table (~105m records) in c# application.

1. when query table 'order by' or 'order each by' clause, getting "resources exceeded during query execution" error.

2. if remove 'order by' or 'order each by' clause, getting response large return error.

here sample query 2 scenarios (i using wikipedia public table)

1. select id,title,count(*) [publicdata:samples.wikipedia] group each id, title order id, title desc

2. select id,title,count(*) [publicdata:samples.wikipedia] group each id, title

here questions have

1. what maximum size of big query response?
2. how select records in query request not in 'export method'?

1. maximum size of big query response?

as it's mentioned on quota-policy queries maximum response size: 128 mb compressed (unlimited when returning large query results)

2. how select records in query request not in 'export method'?

if plan run query might return larger results, can set allowlargeresults true in job configuration.

queries return large results take longer execute, if result set small, , subject additional limitations:

• you must specify destination table.
• you can't specify top-level order by, top or limit clause. doing negates benefit of using allowlargeresults, because query output can no longer computed in parallel.
• window functions can return large query results if used in conjunction partition clause.

read more how paginate results here , read bigquery analytics book, pages start page 200, explained how jobs::getqueryresults working maxresults parameter , int's blocking mode.

update:

query result size limitations - sometimes, hard know 128 mb of compressed data means.

when run normal query in bigquery, response size limited 128 mb of compressed data. sometimes, hard know 128 mb of compressed data means. compressed 2x? 10x? results compressed within respective columns, means compression ratio tends good. example, if have 1 column name of country, there few different values. when have few distinct values, means there isn’t lot of unique information, , column compress well. if return encrypted blobs of data, not compress because random. (this explained on book linked above on page 220)