Slow at closing a query with FetchAll = False

Discussion of open issues, suggestions and bugs regarding MyDAC (Data Access Components for MySQL) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
lsc82

Slow at closing a query with FetchAll = False

Post by lsc82 » Wed 22 Dec 2004 16:21

I'm using D5, MyDac 3.1.13, MySQL 4.0.20a

I've a table in InnoDB with 100.000 records.

I've a form with

1 TMyConnection, standard parameters
1 TMyQuery with
select * from tablename
fetchall = false

When opening the dataset is very faster, 0.015 ms.
When closing the dataset it takes 1 s apparently without sending command to mysql (nothing in dbmonitor).

Why does I takes that time for close the dataset?

Thanks

Luca

lsc82

notice

Post by lsc82 » Wed 22 Dec 2004 17:29

I've also noticed that when I set tmyquery.active to false there is a peak of CPU usage, 50% by the program's process, 50% by mysqld process.

Thanks

lsc82

notice 2

Post by lsc82 » Wed 22 Dec 2004 17:38

I've also noticed that using

Options.Direct := False;

the problem doesn't appear.

Thanks

Ikar
Posts: 1693
Joined: Thu 28 Oct 2004 13:56

Re: notice 2

Post by Ikar » Thu 23 Dec 2004 14:29

We couldn't reproduce the problem.
Please send us complete small sample to demonstrate it and include a script to create a table.
Also send a script to fill a table partly (a hundred records will be enough).

Is it possible that you call Last before closing?

lsc82

code sample

Post by lsc82 » Thu 23 Dec 2004 15:05

I send you an example, including the my.ini configuration file...

my.ini

memlock=1

query_cache_size=256
query_cache_limit=8M

bulk_insert_buffer_size=64M
innodb_data_file_path=dati1:256M;dati2:256M:autoextend

key_buffer_size=64M
innodb_buffer_pool_size=256M
innodb_additional_mem_pool_size=10M

innodb_log_file_size=32M
innodb_log_buffer_size=8M

max_connections=200
read_buffer_size=1M
sort_buffer_size=1M

Form DFM

object cn: TMyConnection
Database = 'DatabaseName'
Username = 'UserName'
Password = 'Password'
Server = 'server'
Connected = True
LoginPrompt = False
Left = 44
Top = 28
end
object q: TMyQuery
Connection = cn
SQL.Strings = (
'select * from tabella')
FetchAll = False
Left = 96
Top = 32
end

Delphi code

type
TForm1 = class(TForm)
Button1: TButton;
cn: TMyConnection;
q: TMyQuery;
procedure Button1Click(Sender: TObject);
end;

var
Form1: TForm1;

procedure TForm1.Button1Click(Sender: TObject);
begin
q.open; //very fast
q.close; //very slow
end;

table layout

CREATE TABLE `tabella` (
`nregis` decimal(11,0) NOT NULL default '0',
`numrig` tinyint(4) NOT NULL default '0',
`codiva` char(4) default NULL,
`imponi` decimal(12,3) default NULL,
`ivadet` decimal(12,3) default NULL,
`ivande` decimal(12,3) default NULL,
`impval` decimal(12,3) default NULL,
`ivdval` decimal(12,3) default NULL,
PRIMARY KEY (`nregis`,`numrig`),
KEY `Tabella001` (`codiva`,`nregis`,`numrig`)
) TYPE=InnoDB;

To fill the table you only have to increment nregis from 1 to x and keep numrig to 1!

Thank you very much!

Ikar
Posts: 1693
Joined: Thu 28 Oct 2004 13:56

Re: code sample

Post by Ikar » Fri 24 Dec 2004 14:35

Thank you for information.
We reproduced your problem and fixed it. This fix will be included in the next MyDAC build.

Sedma
Posts: 3
Joined: Thu 12 Jan 2006 04:01

Re: code sample

Post by Sedma » Thu 12 Jan 2006 04:18

Ikar wrote:Thank you for information.
We reproduced your problem and fixed it. This fix will be included in the next MyDAC build.
I have the same problem with Delphi 7 and newest MyDac 4.00.2.8.
MySQL 5.0.18

I have query with over 10^6 records, FetchAll = False, connect, open, close, disconnect.

Connect and open very quick, close freeze for aprox. 20 seconds. No difference when Direct is true or false.

Is there something I do wrong ?

Thanks,

Martin

Post Reply