asked on Tadoquery dataset perfomance
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!
DelphiPostgreSQL
Last Comment
KarlisB
Can you not perform the 'comparison' on the server using a stored procedure on the server?
You could put the result set into a view, or you could even return a result set to your ADOQuery
You could put the result set into a view, or you could even return a result set to your ADOQuery
ASKER
I am already doing comparison on the server, ado query is returning set of results.
If your database is Interbase, why use ADO at all, it will just slow things down. ADO, BDE, etc. is a way of using common instructions for databases and translates them to your database type. Skip the translation and just go direct unless your app needs the ability to use different types of databases.
Better to use the Interbase components directly (TIBDataSet, TIBQuery, etc.), or even faster is IBObjects (http://www.ibobjects.com/), which is free for personal use.
Justin
Better to use the Interbase components directly (TIBDataSet, TIBQuery, etc.), or even faster is IBObjects (http://www.ibobjects.com/), which is free for personal use.
Justin
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Yes of course...
You need to disable controls when dealing with large data...
You need to disable controls when dealing with large data...
SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Senad, you didnt said anything new to me.
its same as in this article i found hour after asking question. sorry.
http://edn.embarcadero.com/article/27790
ok, you might know something, so i'll try to shot a question at ya.
is there a way to call a single select to the server, but fetch or load half of result into dataset and when program runs trough first part of data till Eof, it clears dataset and fetches(loads) remaining result without calling again select statement.? hope you understand.
thanks
its same as in this article i found hour after asking question. sorry.
http://edn.embarcadero.com/article/27790
ok, you might know something, so i'll try to shot a question at ya.
is there a way to call a single select to the server, but fetch or load half of result into dataset and when program runs trough first part of data till Eof, it clears dataset and fetches(loads) remaining result without calling again select statement.? hope you understand.
thanks
might be one part of a solution.