Link to home
Start Free TrialLog in
Avatar of KarlisB
KarlisBFlag for Latvia

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!

 
Avatar of KarlisB
KarlisB
Flag of Latvia image

ASKER

http://edn.embarcadero.com/article/27790
might be one part of a solution.
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
Avatar of KarlisB

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
ASKER CERTIFIED SOLUTION
Avatar of KarlisB
KarlisB
Flag of Latvia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Yes of course...
You need to disable controls when dealing with large data...
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of KarlisB

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