How many records fit in one keyset?

Application Note for QuNect ODBC for QuickBase

Sometimes QuNect ODBC for QuickBase retrieves records from QuickBase using a keyset. In its first request for records from Quickbase, QuNect ODBC for QuickBase asks for 1000 records. If less than 1000 records are returned, then QuNect ODBC for QuickBase does not need a keyset as all records were returned in the first request. If 1000 records are returned, then QuNect ODBC for QuickBase must assume there could be more records and needs to use a keyset to retrieve the rest of the records. A keyset is a set of record IDs (the Quickbase built-in field with a field identifer of 3 and named by default Record ID#) that are retrieved from QuickBase in one request. This is necessary because QuickBase has a limit of 25 megabytes per request. As a result, the number of records that can be retrieved in one request is limited. So QuNect ODBC for QuickBase first retrieves the keyset to lock down which records will be presented to the ODBC client application. Then it proceeds to retrieve the actual records in the keyset in batches. Using the size of the first request in bytes it divides this by 1000 to get the number of bytes per record on average. Then it divides the number of bytes per record into the 25 megabyte limit to get the number of records to request in the next batch. It actually only asks for 80% of that number to allow for variations in the size of the records. This is done to avoid exceeding the 25 megabyte limit and causing a report too large error. Since the records are retrieved in batches, records could be added, deleted or modified in QuickBase while QuNect ODBC for QuickBase is retrieving the records. As a result some records may appear in two batches or not at all. The keyset ensures that overlooked records are retrieved and no duplicates are presented. The 25 megabyte limit is a QuickBase limit and not a QuNect ODBC for QuickBase limit. This limit applies to the keyset as well as the batches of records. QuNect ODBC for QuickBase uses the most byte efficent API (API_GenResultsTable) to retrieve the records. If you have never deleted records from your QuickBase table, then the number of records that can be retrieved in one keyset is just over 2.9 million records. You can see this by recognizing that the record IDs from 1 million to 2.9 million take up 7 bytes each plus two more bytes each for the carriage return and line feed separation characters. So 1.9 million records take up 1.9 million times 9 bytes or 17,100,000 bytes. Add on the records IDs from 100,000 to 1 million (900,000 times 8 bytes equaling 7,200,000) and and you get close to the Quickbase limit of 25,000,000 bytes.

However, if you have deleted records from your QuickBase table, then the number of records that can be retrieved in one keyset could be less than 2.9 million records. For example if you deleted the first 1 million records from your QuickBase table, then all the remaining record IDs will take up at least 9 bytes each. So you would only be able to at best retrieve approximately 2.7 million records in one keyset. If your record IDs were all over a billion because your deleted all records with a record ID less than 1 billion, then all the remaining record IDs would take up 12 bytes each including the carriage return line feed separators. Then you would only be able to retrieve approximately 2.1 million records in one keyset. You can use a WHERE clause to limit the number of records that are retrieved in one keyset. For example, if you only want to retrieve records with a record ID less than 1 million, then you can use the WHERE clause Record_ID_ < 1000000. If you are using SSMS make sure to use the OpenQuery syntax. Or another approach is to use the dbid~qid feature to access Quickbase table reports. Rather than using two separate SQL statements you can combine them in a single UNION ALL statement.