Trying to Select 28M rows from an Oracle table using DRS stage with Oracle Connectivity.
The sql has joins, sub queries,sum , max.. blah blah blah. The sql runs for about 5-6 mins on DB for selecting. Same sql when run thru a DS job is processing at about 156 rps
![Evil or Very Mad :evil:](./images/smilies/icon_evil.gif)
Here is what I have tried so far:
The job has to be designed to read from a table and write to another table on Same DB. to identify the bottleneck, removed the Transformer and write stage. With Read and write, the processing speed was about 120-150 rps. Just selecting and dumping into a file(tried both hash as well as seq)processes at around 150-160 rps. There is no major CPU utilization. Also no major processes are running on DS or DB. not much wait activity too. reads and writes for all processes are happening almost evenly.
If the sql selects data in 5-6 mins on DB, should'nt the speed of DS job with array size 1 be more for selecting and wrting to a file?
Will try partitioning table on DB or parallel processing in server job. But before i go that route have i missed something
![Question :?:](./images/smilies/icon_question.gif)
![Rolling Eyes :roll:](./images/smilies/icon_rolleyes.gif)
TIA