I'm trying to export a moderately complex query to a CSV file. I've been doing this manually via SQL Developer during development, but now I'm setting up an automated process so I switched to using SQL*PLUS to export. For some reason the SQL*PLUS version is meaningfully slower than the manual SQL Developer export! How can this be? I thought SQL*PLUS was made to be faster.
Here are the headings I'm using:
set termout off;
set verify off;
set flush off;
set pagesize 0;
column tm new_value file_name noprint
select to_char(sysdate, 'YYYYMMDD') || '_filename.csv' tm from dual ;
SET markup csv on;
set echo off;
set feedback off
-- set rowprefetch 1000
-- set arraysize 100
SPOOL &file_name
SELECT ....*querygoeshere*
spool off;
exit
I'm calling this query from a windows machine using a batch script sqlplus -l -F -M "CSV ON" username/pass@server @script.sql
I have tried turning the arraysize on and setting it anywhere from 100 to 10,000, tried rowprefetch to 1000 or off, and tried removing both the -F and -M flags in my calling script. My line lengths vary a LOT so I likely couldn't set the linesize less than 7500 so I haven't done that, but no other article I find has other ideas that seem to help.
The bizarre thing is I can see the file loading slower. If I just refresh on the file itself in explorer an export from SQL Developer simply grows at a much faster rate than one from SQL*PLUS. The SQL Developer export in progress jumps up over 100k/s as I hit refresh; the SQL*PLUS export in progress generally needs a few seconds at least per 100k. These are both happening on the same machine though; what's the difference?? What am I missing?
ctrl-Oto turn off the output, if you're on a system that supports that, and see if that resolves in a speed increaseset termout off;stopped that; not so? If not then good to know about the piping. What isctrl-0?