One Star

SQL script returns more data than I can store in RAM

Hi! 
I use a large SQL script in tPostgresqlInput, which returns more than 100 GB of data while I am using a server with RAM 16 GB.
There is no way to cache all the data in memory, so setting xms/xmx is not a solution. Could you please give me a piece of advice what  I can do in such a situation? Maybe I can fetch the data from PostgreSQL partitially (but remember that the SQL script is rather complex, it builds a data mart table) or swap selected data to the server hdd?
Thanks!
5 REPLIES
Moderator

Re: SQL script returns more data than I can store in RAM

Hi,
What's your target output? Do you want to extract 100 GB of data from Postgresql and load it into other DB?
Best regards
Sabrina
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
One Star

Re: SQL script returns more data than I can store in RAM

Hi Sabrina!
Yes, it is another DB (PostgreSQL) on another server. There is no direct connection between source and target databases but both of them are connected to ETL-server.
One Star

Re: SQL script returns more data than I can store in RAM

Hi,
Why don't you simply push selected data on one or more CSV files, then reuse these files to load data into the target DB?
Regards,
TRF
Fifteen Stars

Re: SQL script returns more data than I can store in RAM

As TRF said, you need to break this problem down. Even if your SQL query is really complicated, it can be filtered with a WHERE clause. A nice way to try to solve this problem could be as simple as iterating over multiple calls of this query and changing the WHERE clause each time. This would break the memory load into manageable chunks while not having the overhead of creating a load a files. You will need to think about what you will filter on and whether it is suitable to break the data into the chunk sizes you can work with though.
Rilhia Solutions
One Star

Re: SQL script returns more data than I can store in RAM

TRF, rhall_2.0
thanks for your answers. I thought there could be a way to fetch data from source RDBMS server by batches without any additional rework of my jobs.