Data collection

[piopio]

Hello,

I have a question on the data collection.

If I run the following statements:

PGQuery1.SQL:="SELECT * FROM Customers"
PGQuery2.SQL:="SELECT name FROM Customers"
PGQuery3.SQL:="SELECT Address FROM Customers where..."

I understand the first statement collects data from the DB but what about the following statements ?
As this data is already in the pc memory I suppose data is collected not from the DB but the computer RAM. Is that correct ?

If not, is there a way to improve the performance ? Is it possible to write something like:
PGQuery2.SQL:="SELECT name FROM PGQuery1" ?


Many thanks


PioPio

[AlexP]

hello,

After query running

Code: Select all

SELECT * FROM Customers

provided that the option PGQuery1.fetchAll is set to True, all data from the table is read out into memory, however, you can't apply SQL constructions to this data. Instead, you can work with this data with the help of methods of the DataSet, such as: Filter - filtering data (similar to the WHERE condition in the query), Locate - move to the record, etc.
Please describe your task in more detail and we will try to give a more complete answer

[piopio]

Hi AlexP,

I will give you the classical example: the table “customers” and the table “invoices”. These two tables can be linked via one slq query.
Let say in a Delphi TForm I have to display data taken from customer table and the relevant data from the table invoices. Let also say I need to display in two TLabels the breakdown of the invoices according to their date in the following way:
- Total invoices with date < date1
- Total invoices with date >date1
If I understand correctly your explanation, in order to maximize the performance, once the query is ran I should apply a filter to select one customer first, then another filter to the date to select only a few invoices and then do the calculation of the total by scrolling sql rows, am I right ?

What about linking the two table via master-detail ? In this case I can apply filters only to the invoice date and do the total calculation manually. To apply one filter instead of two should improve the performance. Is this the best way or there is a better one ?

Many thanks

[AlexP]

hello,

In your case it is really better to use master-detail DataSet, but anyway to get the summarized data you will have to run over all of the rows and sum the values falling within the required range.