When querying more than 4MB worth of data with the ODBC driver, 65MB temp files are created and never cleaned. With iSQL the files are not created.
Steps to Reproduce
- Install ODBC Driver.
- Configure the driver to connect to a ServiceNow instance with default settings.
- In ODBC Custom Properties, add gzip=false.
The problem is not dependent on this property but we do this to increase the response payload from the instance.
- Run the following query in the Interactive SQL:
select * from alm_asset;
- Watch temporary file created in C:\Users\user.name\AppData\Local\Temp.
Drive letter and/or location may vary based on your Windows version.
The temporary file feature prevents large result sets from taking up the computer memory and causing overall slowness. Any time the result set is larger than 4MB, it is saved on disk instead of memory.
There are two possible workarounds:
- Windows allows you to create a .bat file and then schedule it using Windows Task Scheduler. A .bat file can be created and scheduled to clean the files.
- Reduce the BatchSize property in ODBC Driver's Custom Properties. Default value is 2000. Reducing this value causes the driver to get the data from the instance in smaller (less than 4MB) chunks, causing more round trips.
Related Problem: PRB708941