Avatar of KarlisB
KarlisB
Flag 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!

 
DelphiPostgreSQL

Avatar of undefined
Last Comment
KarlisB

8/22/2022 - Mon
KarlisB

ASKER
http://edn.embarcadero.com/article/27790
might be one part of a solution.
rfwoolf

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
KarlisB

ASKER
I am already doing comparison on the server, ado query is returning set of results.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
jplevene

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
KarlisB

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.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
senad

Yes of course...
You need to disable controls when dealing with large data...
SOLUTION
senad

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.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
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
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.