Problem Note 71170: Performance issues occur when using SAS/ACCESS® Interface to Google BigQuery and OBS=
 |  |  |  |  |
SAS has identified several issues with SAS/ACCESS Interface to Google BigQuery. As a result, you might experience a performance issue, or SAS/ACCESS Interface to Google BigQuery might stop working. These issues commonly occur when you do as follows:
- Access very large BigQuery tables, or access views where the underlying tables are very large. For example, this issue occurred with tables that contained 10 million rows and 145 columns.
- In the SAS code, you use the following:
- the OBS= SAS Data Set option
- INOBS= from the SQL procedure
- In SAS® Enterprise Guide®, you navigate to Tools ► Options ► Data ► Performance ► Limit number of rows to: to effectively set OBS= for opening a table in the data grid
Exact symptoms of this issue vary depending on the situation. However, below are a few examples and workarounds when applicable:
1. In SAS Enterprise Guide, if you open a table, the table might fail to open in the expected time frame. In this scenario, your only options are to do either of the following:
- disconnect from the server
- (in some circumstances) use Windows Task Manager to end the SAS Enterprise Guide process
2. In SAS Enterprise Guide, if the table in point 1 does open, it might fail to close. In this scenario, you must use Windows Task Manager to end the SAS Enterprise Guide process.
3. SAS Enterprise Guide might take a long time to respond when you select the properties of a table. For example, one reported instance took up to 23 minutes. This issue also occurs when running the CONTENTS procedure in the SAS code.
4. Using the SAS data set OPTION OBS=n might cause the software to stop responding.
5. Using PROC SQL with INOBS= might cause the software to stop responding. Although SAS does not recommend using INOBS= with PROC SQL, it is not expected that the software would stop responding as a result. To work around this issue, do either of the following:
- use the SAS data set OPTION OBS=
- use the undocumented LIBNAME option NUM_STREAMS=10
Note that in only this scenario, the following warning occurs in the SAS log:
Some of these performance issues might occur only when accessing the table on the first attempt, which might be because Google BigQuery uses previously cached results. There is no guarantee that the cached results will be used the next time.
Click the Hot Fix tab in this note to access the hot fix for this issue.
Operating System and Release Information
| Product Family | Product | System | Product Release | SAS Release | ||
| Reported | Fixed* | Reported | Fixed* | |||
| SAS System | SAS/ACCESS Interface to Google BigQuery | Linux for x64 | 9.42 | 9.4 TS1M7 | ||