1

Topic: Why different speed of request from IBExpert and from the program?

Kind time of days, help to understand?
[spoiler Here such plain request]

select tc. ID;
(with N as (
select NAME
from
TFIOS t inner join SPFIOS s on s. ID=t. IDFIO
where t. IDCAT = tc. ID and t.idkind = 1
order by NAME)
select list (NAME, ', ')
from N) as FIOS
from TCATALOG tc order by tc. ID

[/spoiler]
From IBExpert it is fulfilled perfectly, all data for some seconds.
[spoiler Sbatsal all it in a flow in Delfi, here so]

procedure TFillUpFIOsThread. Execute;
var D: TpFIBDataset;
RecC: Integer;
begin
inherited;
try
LaDB: = TFIBDatabase. Create (nil);
LaTRN: = TFIBTransaction. Create (nil);
LaDB.DefaultTransaction: = LaTRN;
D: = TpFIBDataSet. Create (LaDB);
D.Database: = LaDB;
D.Transaction: = LaTRN;
LaDB.LibraryName: = DM.FIBDB.LibraryName;
LaDB.UseLoginPrompt: = False;
LaDB.DatabaseName: = DM.FIBDB.DatabaseName;
LaDB.DBParams: = DM.FIBDB.DBParams;
LaDB.SQLDialect: = DM.FIBDB.SQLDialect;
LaDB.DefaultTransaction: = LaTRN;
LaTRN.DefaultDatabase: = LaDB;
LaTRN.TRParams: = DM.TRNRead. TRParams;
LaDB.Connected: = True;
with D do
begin
SelectSQL.Text: = ' select count (ID) from TCATALOG ';
Open;
RecC: = Fields [0].AsInteger;
Close; SelectSQL.Clear;
//SelectSQL.Text: = ' select c. ID, (SELECT FIOS FROM CONSTRUCTFIOSLIST (c. ID)) as AUTHLIST from TCATALOG c order by c. ID ';
SelectSQL.Add (' select c. ID, ');
SelectSQL.Add (' (with N as (');
SelectSQL.Add (' select NAME ');
SelectSQL.Add (' from ');
SelectSQL.Add (' TFIOS t inner join SPFIOS s on s. ID=t. IDFIO ');
SelectSQL.Add (' where t. IDCAT = c. ID and t.idkind = 1 ');
SelectSQL.Add (' order by NAME) ');
SelectSQL.Add (' select list (NAME ",") ');
SelectSQL.Add (' from N) as AUTHLIST ');
SelectSQL.Add (' from TCATALOG c ');//order by c. ID
Open;
while not EOF do
begin
if Terminated then Exit;
//mutex? ahz
MainForm. FIOsList. Add (Fields [0].AsInteger, Fields [1].AsString);
if RecNo mod 1000 = 0 then
begin
PBPos: = Round (RecNo / RecC * 100);
Synchronize (MainForm. DrawPBPos);
end;
Next;
end;
Close;
end;
finally
FreeAndNil (D);
FreeAndNil (LaTRN);
FreeAndNil (LaDB);
Synchronize (MainForm. HidePB);
if not Terminated then
FreeAndNil (MainForm. FillUpFIOsThread);
end;
end;

[/spoiler]
Speed on pair orders more slowly, than from IBE, why?
There is a procedure doing almost same ( a line). If to preempt through it, speed normal and from Delfi. But it would be desirable to understand, why the request from the program so slowly works?
Thanks!

2

Re: Why different speed of request from IBExpert and from the program?

Devillio;
In IBExpert button FetchAll tried to push?

3

Re: Why different speed of request from IBExpert and from the program?

Simonov Denis, it is finite))

4

Re: Why different speed of request from IBExpert and from the program?

Devillio, can we look at the plan?

5

Re: Why different speed of request from IBExpert and from the program?

rdb_dev, it in IBExpert
The plan
PLAN SORT (JOIN (N T INDEX (TFIOS_IDX_COMMON), N S INDEX (PK_SPFIOS)))
PLAN (TC ORDER PK_TCATALOG)
------ The information on productivity------
Time of preparation of request = 15ms
Request runtime = 16ms
Average time for obtaining of one record = 0,55 ms
As in  to look - I do not know sad

6

Re: Why different speed of request from IBExpert and from the program?

And precisely the request brakes, instead of filling FIOsList?

7

Re: Why different speed of request from IBExpert and from the program?

Exteris, is procedure ( "CONSTRUCTFIOSLIST"), it collects the same list of surnames, as well as request, only "search". Here if to fulfill it it too works quickly (from the program). Well the request through List in IBE can hardly more slowly, than.
.. The difference only in operation of the request,  a component as it seems to me, works correctly.

8

Re: Why different speed of request from IBExpert and from the program?

If the request is fulfilled some seconds,  all this fuss with , , etc.
Here I spaced apart on  collections of reports which go from 5 minutes and longer.

Devillio wrote:

But it would be desirable to understand, why the request from the program so slowly works?

Trace include (I assume that  not more low 2.5)

9

Re: Why different speed of request from IBExpert and from the program?

Devillio> .. A difference only in operation of the request,
Devillio> the component as it seems to me, works correctly.
And that show samplings? If all it from a flow to carry out the same?

10

Re: Why different speed of request from IBExpert and from the program?

Devillio wrote:

Time of preparation of request = 15ms
Request runtime = 16ms

Strongly I doubt that this data is received at FetchAll (a button such with TWO triangles).
First of all it is necessary
- To pull out the code from thread, and to issue it in test application by the button
- To measure time of the full performance, then to comment out FIOsList, and  once again.
p.s. Somehow time faced that the data from the text file was very slowly loaded. It appeared that it is guilty TStringList. It was necessary to alter handling on normal textfile.

11

Re: Why different speed of request from IBExpert and from the program?

Devillio wrote:

.. A difference only in operation of the request,  making, as to me
It seems
, works correctly.

You are naive.

12

Re: Why different speed of request from IBExpert and from the program?

kdv;
TStringList - generally terribly brake thing. That, in general,  - if to look at it rather list of methods there is more, than property and even events.
It was a pity in early Delphi not TList <string>:-D

13

Re: Why different speed of request from IBExpert and from the program?

Comparing with IBE is strong not the fair

Devillio wrote:

LaDB.Connected: = True;

Here this additional pause at IBE is not present.

Devillio wrote:

SelectSQL.Text: = ' select count (ID) from TCATALOG ';

And this is not present. And it potentially - reading of ALL table from a disk and garbage collection on it.

Devillio wrote:

Synchronize (MainForm. DrawPBPos);

And they be is not present.

Devillio wrote:

Synchronize (MainForm. HidePB);

And they be is not present.

Devillio wrote:

finally
FreeAndNil (D);
FreeAndNil (LaTRN);
FreeAndNil (LaDB);

And here you simply elicit access violation

Devillio wrote:

FreeAndNil (MainForm. FillUpFIOsThread);
end;

And here generally silent horror and  indefinite duration.

14

Re: Why different speed of request from IBExpert and from the program?

Arioch> LaDB.Connected: = True;
> Here this additional pause at IBE is not present.
> And here you simply elicit access violation
I wish that all! ()
The person on the white says to Russian that
With procedure all normally works for it.
Here it is necessary to clarify, where also who/that to whom says lies;
And you to it about nacreous buttons.

15

Re: Why different speed of request from IBExpert and from the program?

I thank all for variants and councils!!
Arioch, works, any violation already for about a year at people small group. It is never simple. I.e. from the order of several tens thousand starts.
[spoiler if to change here so:]

SelectSQL.Text: = ' select c. ID, (SELECT FIOS FROM CONSTRUCTFIOSLIST (c. ID)) as AUTHLIST from TCATALOG c order by c. ID ';
//SelectSQL.Add (' select c. ID, ');
//SelectSQL.Add (' (with N as (');
//SelectSQL.Add (' select NAME ');
//SelectSQL.Add (' from ');
//SelectSQL.Add (' TFIOS t inner join SPFIOS s on s. ID=t. IDFIO ');
//SelectSQL.Add (' where t. IDCAT = c. ID and t.idkind = 1 ');
//SelectSQL.Add (' order by NAME) ');
//SelectSQL.Add (' select list (NAME ",") ');
//SelectSQL.Add (' from N) as AUTHLIST ');
//SelectSQL.Add (' from TCATALOG c ');//order by c. ID

[/spoiler]
That all flies almost the same as and from IBExpert.
CONSTRUCTFIOSLIST does exactly same, as this request with list, only without list, through search of records (through list in IBE hardly faster).
.. The output arises -  a component, very likely, that at anything. That is, naturally, at what, but I mean all ornament which Arioch selected - it there is nothing.
Any StringList there is not present. There TDictionary (Integer, String)
What for it is selected in a flow? The program for library. Bibliographers (them much) do various samplings permanently. From pair-triple publications to thousand. The program, receiving once at start (in a flow that there were no start time delays) lists of authors for all publications, at requests  very quickly works. Lists are simply drawn in OnGetDataText. The Prior version collected lists at each request, the difference is very noticeable.
I stupidly did not know about list when did))) recently here learned, wanted to "optimize", and it ))

16

Re: Why different speed of request from IBExpert and from the program?

Gadzhimuradov Rustam, here!! Thanks))) Yes, indeed

17

Re: Why different speed of request from IBExpert and from the program?

Devillio> the Program, receiving once at start (in a flow that there were no start time delays) lists of authors for all publications, at requests  very quickly works.
For what and as a question the tenth, it it's up to you.
But if you do not know in what a problem, the first that is necessary
To do - to localize it discarding all superfluous.
At first a flow, then preliminary request to
TCATALOG (simply replace with number), then all
Remaining on one - yet you will not catch a difference.

18

Re: Why different speed of request from IBExpert and from the program?

Devillio;
By the way, about LIST
https://www.ibase.ru/dbgrowth/

19

Re: Why different speed of request from IBExpert and from the program?

kdv, I thank for !
Corrected so:
SelectSQL.Add (' select cast (list (NAME ",") as VARCHAR (4000)) '); (4000 - it is simple that climbed up in a head, 2000 - did not transit, there are publications where authors did not hold an island)
All departed, is direct noticeably was accelerated concerning speed of a variant with procedure.
Something with . It is interesting, why that request without cast in IBE works quickly

20

Re: Why different speed of request from IBExpert and from the program?

If business was with  slowly would be and with IBE.
Or at you IBE "on the server", and a flow - on the client "at the other end of the world"?

21

Re: Why different speed of request from IBExpert and from the program?

Gadzhimuradov Rustam, no, certainly. And the client, and IBE at the other end of the world.
No, with . See if to make on the server in request cast and the data go as a line - all well.
And, at obtaining of result of request with a column bloba not only madly slowly lists of authors in a flow come, but also all client explicitly works heavily - twitches somehow, slowly reacts to the mouse,  it is visible as are drawn at discovery. And if the request goes with a line - all as usual, it is easy and is fluffy.

22

Re: Why different speed of request from IBExpert and from the program?

Devillio wrote:

Arioch, works, any violation already for about a year at people small group.

Habit bad. With these objects in these conditions works.
With other objects or even with other library - can cease.
And at you such code by all program it will be stuffed, search then where exactly and why went.
Well though, if in time you will leave on the best place it there will be any more your problems:-D

Devillio wrote:

from the order of several tens thousand

"select * from table" too on the first time  well works.:-D

23

Re: Why different speed of request from IBExpert and from the program?

Devillio wrote:

it is interesting, why that request without cast in IBE works quickly

And you are assured, what it  finds? I here think, what was not present - what for to it it to do?

24

Re: Why different speed of request from IBExpert and from the program?

Arioch wrote:

  finds? I here think, what was not present - what for to it it to do?

And here yes. Components can pull out  in process of reading of records as it BDE does. And can not pull out.

25

Re: Why different speed of request from IBExpert and from the program?

Arioch wrote:

kdv;
TStringList - generally terribly brake thing. That, in general,  - if to look at it rather list of methods there is more, than property and even events.
It was a pity in early Delphi not TList <string>:-D

... TStrings?
By the way. Being a dense sclerotic and without looking in Delphi c on May, 26th, began to doubt available letters s on a tail. Was not too lazy to launch, type a word and to push F1. The help on MS Windows got out and began to explain
____________________________________________________________
Why it is not possible to receive help under this program?
Help for this program has been created in a format of help Windows which depends on the component which is not entering into given Windows version. For review of the help created in a format of help Windows, it is possible to load the special program.
Additional convergence see the web site of help and support Microsoft.
______________________________________________________________
...