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
Slow at closing a query with FetchAll = False
Re: notice 2
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?
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?
code sample
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!
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!
Re: code sample
Thank you for information.
We reproduced your problem and fixed it. This fix will be included in the next MyDAC build.
We reproduced your problem and fixed it. This fix will be included in the next MyDAC build.
Re: code sample
I have the same problem with Delphi 7 and newest MyDac 4.00.2.8.Ikar wrote:Thank you for information.
We reproduced your problem and fixed it. This fix will be included in the next MyDAC build.
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