First of all, i apologise form my terrible english.
I am stuck on a problem.
I have like table with 50 million records on a postgres server.
Have perfectly tuned server, set up indexes, primary keys using 3 columns based on nature of where statements mostly used.
It comes to that I need to compare 5 million records to an auto generated table.
its not a big issue, nevertheless there is 3 table join. ok query takes like 2 min. but in this situation i cant do nothing much to speed things up, have already spent days of analysing best query and database performance for my situation.
Query is called by my program, and query returns like 100 000 to all 5 million of records
Thats should be enough for the background.
there are two problems I have encountered.
First one, i do fetch all data on client side machine. it uses up lot of memory, as it should be.
but i am thinking if there is a way to reduce amount of data stored into RAM, and the speed its loaded into it.
Is there something to tune this up, like disabling ado querys, datasets features, properties (data loging or something like that)
second, actually the main problem is adoquery, and its dataset.
when data is fetched, i do while loop and run trough all records.
at start it runs perfect and fast, but after a while application is starting to consume more and more cpu performance till it hits all 100% and stating to slow down the speed of operations app is processing. I am suspecting that ado query and its dataset is at fault since it was not designed at first for large datasets.
I am not an expert in programming, might not know various things which for others might be simple.
I am using:
two ado query's (one for retrieving large amount of data and an other-one for insert operations.
altho,i am using there are a couple of interbase components for retrieving data, but they are frequently recreated and destroyed while looping for certain reasons, and it shold not be cause of increasing performance loss.
Ah, and yes, i am using threads, calling simultaneously many similar requests. but even if i run one of them only, same problem still persists, so that wont be cause aswell