speed of select

[killerjohn]

I've the following piece of code (MySQL 5.0.15 and Delphi 2006). The result is about 90.000 records. #1 execution time is about 2 seconds. #2 execution time is about 15 minutes. Why???

//#1
list.sql.text := 'SELECT * FROM POSITION_001 ORDER BY D_SECONDS;';
list.open;

//#2
while not list.eof do
begin
list.next;
end;


(Sorry for my english)

[mdaems]

Hi,

That's because the first statement is just preparing the database server to return the results and the second statement is retrieving these results one by one over the network connection. For each record caches will be filled and the current record of the dataset changes. It still processes 100 records per second this way.
How big are your records? Do you need all the fields? Do you need all those records? I can't imagine somebody wants to see 90000 records at once. (Or are you exporting these data to file?) If you are making statistics you should try to write some sql doing the work.

Mark

[dragos]

Try setting TZQuery's RequestLive option to TRUE (I assume you're using a TZQuery).

If it works (i.e. it takes less than 15'), I'll then tell you why :-)

[killerjohn]

Hi!

I need all 90.000 records, and all columns. This is a table with GPS navigation coordinates , and I use very complex calculations with Delphi.

[dragos]

I understand, but does the RequestLive option make a difference?

[killerjohn]

I'm using TZReadOnlyQuery.... I think it's faster because it's "read only". or not ?

[dragos]

I'm using TZReadOnlyQuery.... I think it's faster because it's "read only". or not ?

If you're using large SELECTs, TZQuery w/ RequestLive will be faster (my experience some time ago - maybe now it is different).
And the explanation had something to do with the way qry.Next() and the mysql driver work.
I remember having this problem some time ago, and after some profiling found the culprit and then the solution.

Hope it still works.

[killerjohn]

I've checked TZQuery, but there is no RequestLive property. Only hidden in the source code

[gto]

What is your Zeos build? In the last one (cvs 13/10/2005), requestlive become readonly

Also, the options are inverted, when requestlive was active, now means readonly will be inactive

[killerjohn]

Hello!

1st : sorry for my bad English...

Please give me some hint about this problem:







Tables:

create table DEF_OBJECT
(
ID INTEGER not null,
OBJECT_TYPE_ID INTEGER not null,
LAST_DETAIL_ID INTEGER not null,
INACTIVE TINYINT UNSIGNED not null default 0,
INTEGRATED TINYINT UNSIGNED not null default 0,
USER_KEY VARCHAR(255),
primary key (ID)
);

create table DETAIL_OBJECT
(
ID INTEGER not null,
OBJECT_ID INTEGER not null,
PREV_DETAIL_ID INTEGER not null,
D_DATE DATE not null,
D_TIME TIME not null,
D_DATETIME DATETIME not null,
DESCRIPTION TEXT,
primary key (ID)
);

create table DEF_OBJECT_PROPERTY
(
ID INTEGER not null,
DETAIL_ID INTEGER not null,
PROPERTY_ID INTEGER not null,
VALUE_INT INTEGER,
VALUE_STR TEXT,
VALUE_DATE DATETIME,
VALUE_EXT DOUBLE,
primary key (ID)
);

Query:

SELECT *,op.ID as OPID,h.ID as OBJID FROM DEF_OBJECT h INNER JOIN DETAIL_OBJECT d ON h.LAST_DETAIL_ID=d.ID INNER JOIN DEF_OBJECT_PROPERTY op ON op.DETAIL_ID=d.ID WHERE h.OBJECT_TYPE_ID=:pObjectTypeID ORDER BY h.ID,op.ID;

Record count in database:
DEF_OBJECT : 1100
DETAIL_OBJECT : 1100
DEF_OBJECT_PROPERTY : 15400

The Delphi Code:

sql.Open;
While not sql.Eof Do
Begin
o := CDBO_Object.Create;
o.iOuterPropertyCache := True;
o.iPropertyCache := iPropertyCache;
o.LoadHeaderFromSQL(sql);
o.LoadObjectPropertiesFromSQL(sql);
iObjects.Add(o);
End;
sql.Free;

Run times:
mysql console : very fast
delphi code : >10 seconds!!! (my .exe's CPU=88%, and mysql's CPU=10%)

[killerjohn]

When I change the Delphi code to this :

sql.Open;
While not sql.Eof Do
Begin
sql.Next;
End;
sql.Free;

my program is not faster - Zeos is toooooo slow....

[mdaems]

Did you run this code 'dry'? I mean, without the code between the begin and end clauses? Just to make sure it's only the zeos component that is slow. I don't know what these 'Load...' functions do, but they can also be slow.

If the loop keeps taking that long we'll have to take a deeper look at it. Is the data in your table confidential? If not, please send me a private message to agree how we can exchange your files. (Data and test project)

Mark


BTW : I hope you did not forget to fetch next record (never ending loop, of course)? If it's in one of these 'Load...' functions, i would not recommend to write it there. Even if it works, you will later spend hours to find where it exactly is.

[mdaems]

Oops,

We 've been writing together.

[killerjohn]

it's simple... this code on 15400 records (all columns and rows needed) :

<----------------- 0 second
sql.Open;
<----------------- 1 second
While not sql.Eof Do
Begin
sql.Next;
End;
sql.Free;
<----------------- 10 second

[killerjohn]

Have somebody some experience with the MyDAC component pack ? (http://crlab.com/mydac)