Returning Large dataset from Oracle to SAS
Posted on 2013-06-21
We have a query which runs on a SAS server and connects to an Oracle database. The query is using sql-pass through and returns 20M rows. Query ran for 10 hours and never returned anything.
create temptbl as
select * from sales where taxable = 'Y';
If we limit the rows, ie inobs=1000000, then we get a return dataset in 15 min.
Same query connecting to the same database but run on another, identical server returns dataset in 3 minutes. Note: we need all 20M rows.
We have validated server configuration, network traffic, nic card setting, database utilization are all the same between the two servers. Two servers are on the same backbone and mounted on the same rack. Evaluated each component in the stack (session, transport, network, etc) and have narrowed it down to the data after returning to the SAS server.
Any suggestions where to look?