Macro/script help; runs out of memory

[#1]

Hello everyone!

I've got a bit of a problem with my project and I'd really hope someone here can help me.

What I'm doing is load data from the SQL database (about 15 different tables) then join most of them into one temporary table and start a loop loading and transforming each day's data (the final result in one single table). I'm doing incremental load so most of the data (all except the current month and the month before) is already stored in a QVD file. So if this is set to be automatically updated every day I basically only need loading data from the last 3 months (one gets stored into the QVD file and the other two just loaded into a virtual table). The loop I have is technically two loops - first is monthly and the second - daily. But what I'm doing inside them wastes a lot of memory even though I drop all temporary tables after I'm done with one day's data. I've been reading around and it seems like the function group by I use in there uses up too much memory so that after loading about 1.5 months the script stops running and gives me the "Type D internal incostistency" error. I already tried modifying the script but none of the many modifications worked so I decided to drop that idea. The variant of "grouping by" in SQL instead of QV also won't work because I aggregate data by fields from different tables. So I was thinking of doing something like loading one month worth of data, storing it into a temporary file then killing the process (so that the memory is freed up) and so on. But I'm not sure where to even start. Do I need a macro for that? Would a macro help this situation? Or just a javascript or something which runs the QV script the needed amount of times (the amount of months) on a server? If so can anyone please at least outline what I should write in such a script/macro so that the thing I want to accomplish could actually work. That is unless there is another more simple solution to this problem of course. Any help would be really appreciated.

Thank you.

[Peter_Cammaert]

SQL allows you to perform unions and joins in a single statement. If those 15 tables originate in the same DB, you could combine all memory-hungry logic together with those GROUP BYs into a a few SQL statements and let the DB engine perform most of the filtering/aggregation.

In my experience, RDBMS are quite usable for some data-intensive tasks. The tricky thing is that you may need to tune those SQL statement in order to get optimal performance. And all of this depends on the type of RDBMS you are using.

My 20cts.

Peter