Why Experts Exchange?
Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.
Jim Murphy
Programmer at Smart IT Solutions
When asked, what has been your best career decision?
Deciding to stick with EE.
Mohamed Asif
Technical Department Head
Being involved with EE helped me to grow personally and professionally.
Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question
Connect with Certified Experts to gain insight and support on specific technology challenges including:
Troubleshooting
Research
Professional Opinions
Ask a QuestionDid You Know?
We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE
asked on
Greetings expert's
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:
ado-connection,
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.
Any advices?
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
Thank you!
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:
ado-connection,
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.
Any advices?
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
Thank you!
Our community of experts have been thoroughly vetted for their expertise and industry experience.