[SOLVED]Is it possible to fetch records one by one ?
lazarus / 16-Databases

----------------------------------------------------------------------
kinlion · 2020-03-06 02:49

Hi all,

I have to get many records from MySql, it's a time-cost task to fetch
all. So I want to show progess during fetching.
However I found that TSQLQuery.Open costs much time, maybe to fill up
it's inner buffer I guess. Thus it seems not meaningful to update
progress in TSQLQuery.Next loop.

Can I make TSQLQuery to fetch records one by one or any other way to
update progress perfectly ?

Thanks in advance

----------------------------------------------------------------------
af0815 · 2020-03-06 06:13

Quote from: kinlion on March 06, 2020, 03:49:29 amI have to get many
records from MySql, it's a time-cost task to fetch all. So I want to
show progess during fetching.Use a prepared query with parameters and
a useful where clause. Then you can fetch defined chunks of data.

If TSQLQuery is slow think about your design, because normally after
getting the dataset on the server the visible DB-Componnets only fetch
a part of Dataset from server. For explaination look for recordcount,
this hold normally the count the fetched records.

----------------------------------------------------------------------
eljo · 2020-03-06 12:01

Quote from: kinlion on March 06, 2020, 03:49:29 amHi all,

I have to get many records from MySql, it's a time-cost task to fetch
all. So I want to show progess during fetching.
However I found that TSQLQuery.Open costs much time, maybe to fill up
it's inner buffer I guess. Thus it seems not meaningful to update
progress in TSQLQuery.Next loop.

Can I make TSQLQuery to fetch records one by one or any other way to
update progress perfectly ?

Thanks in advance
tsqlquery is already paged. Look at the PacketRecords property it
usually has a default value of 10 which means that it reads data from
the server in packets of 10 records at a time and refreshes when the
user move the cursor beyond that count. If it is slow to get data back
you either have a huge record usually with blobs or the sql command
needs attention. In the first case a redesign of the application might
be required, in the second case a rewrite of the sql is usually your
best bet for better performance.

----------------------------------------------------------------------
rvk · 2020-03-06 12:53

Quote from: kinlion on March 06, 2020, 03:49:29 amI have to get many
records from MySql, it's a time-cost task to fetch all. So I want to
show progess during fetching.
However I found that TSQLQuery.Open costs much time, maybe to fill up
it's inner buffer I guess. Thus it seems not meaningful to update
progress in TSQLQuery.Next loop.TSQLQuery.Open itself doesn't read
records except the first.
Do you have something attached to the TSQLQuery? A TDBGrid or
something else?

Quote from: kinlion on March 06, 2020, 03:49:29 amCan I make TSQLQuery
to fetch records one by one or any other way to update progress
perfectly ?
Yes. You can do this with the normal loop
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i
Query1.Open;while not Query1.EOF do  // do something 
Query1.Next;end;But this won't help you if you have something attached
which wants to read more records on TSQLQuery.Open.

Another problem with a slow TSQLQuery.Open could be an inefficient SQL
query.

----------------------------------------------------------------------
kinlion · 2020-03-25 07:08

Maybe my question is not clear enough, sorry.
In fact, I have tens of records with blob field to fetch.

I hope to fetch them one by one.
I mean, after query.Open, it just fetch the first record from Server,
after each query.Next, it fetch only one record from server until
query.Eof.

It seems PacketRecords is what I want.
I will try later.

Thanks a lot. 

Quote from: eljo on March 06, 2020, 01:01:57 pmtsqlquery is already
paged. Look at the PacketRecords property it usually has a default
value of 10 which means that it reads data from the server in packets
of 10 records at a time and refreshes when the user move the cursor
beyond that count. If it is slow to get data back you either have a
huge record usually with blobs or the sql command needs attention. In
the first case a redesign of the application might be required, in the
second case a rewrite of the sql is usually your best bet for better
performance.

----------------------------------------------------------------------
kinlion · 2020-03-25 08:10

I tested PacketRecords, set it to 1, but it plays no difference. 
I'm using MySQL 5.5

Quote from: kinlion on March 25, 2020, 08:08:47 amMaybe my question is
not clear enough, sorry.
In fact, I have tens of records with blob field to fetch.

I hope to fetch them one by one.
I mean, after query.Open, it just fetch the first record from Server,
after each query.Next, it fetch only one record from server until
query.Eof.

It seems PacketRecords is what I want.
I will try later.

Thanks a lot. 

Quote from: eljo on March 06, 2020, 01:01:57 pmtsqlquery is already
paged. Look at the PacketRecords property it usually has a default
value of 10 which means that it reads data from the server in packets
of 10 records at a time and refreshes when the user move the cursor
beyond that count. If it is slow to get data back you either have a
huge record usually with blobs or the sql command needs attention. In
the first case a redesign of the application might be required, in the
second case a rewrite of the sql is usually your best bet for better
performance.

----------------------------------------------------------------------
rvk · 2020-03-25 08:31

Quote from: kinlion on March 25, 2020, 09:10:46 amI tested
PacketRecords, set it to 1, but it plays no difference. 
I'm using MySQL 5.5
What is .Recordcount directly after open? If it's more than 1 or 10,
you have somthing connected to the query. Maybe a tdbgrid (which makes
the tquery fetch more records).

If it's 1, your problem lies somewhere else. Maybe you have a complex
sql statement without index.

----------------------------------------------------------------------
eljo · 2020-03-25 09:08

Quote from: kinlion on March 25, 2020, 09:10:46 amI tested
PacketRecords, set it to 1, but it plays no difference. 
I'm using MySQL 5.5

Quote from: kinlion on March 25, 2020, 08:08:47 amMaybe my question is
not clear enough, sorry.
In fact, I have tens of records with blob field to fetch.

I hope to fetch them one by one.
I mean, after query.Open, it just fetch the first record from Server,
after each query.Next, it fetch only one record from server until
query.Eof.

It seems PacketRecords is what I want.
I will try later.

Thanks a lot. 

Quote from: eljo on March 06, 2020, 01:01:57 pmtsqlquery is already
paged. Look at the PacketRecords property it usually has a default
value of 10 which means that it reads data from the server in packets
of 10 records at a time and refreshes when the user move the cursor
beyond that count. If it is slow to get data back you either have a
huge record usually with blobs or the sql command needs attention. In
the first case a redesign of the application might be required, in the
second case a rewrite of the sql is usually your best bet for better
performance.
A quick way out is to use a master detail link (using lcl datasets)
and  moving the blob to the detail dataset.
If properly linked the detail dataset will load its data every time
the master dataset scroll to a new record and if you make sure that
the detail only returns a single field in a signle row you get your
desired results.

Having said that, please answer AVK's question first to help you find
the problem.

----------------------------------------------------------------------
SamC · 2020-04-14 06:29

Hello,

for MySql you can use th LIMIT 1 in your SQL-Statement.

qry.SQL.Text:='select count(1) from table_name';
qry.open;
if not eof
   then max_rec:=qry.Fields[0].asInteger else max_rec:=0;
qry.close;

if max_rec>0 then
begin
  rec:=1;
  qry.SQL.Text:='select field1, field2 from table_name LIMIT :start,
1;';
  qry.Params[0].asInteger:=rec;
  qry.open;
  while not qry.eof do
  begin
    qry.Params[0].asInteger:=rec;
    ...
    do what ever you need to do
    ...
    inc(rec);
    qry.next;
  end;
end;


Good Luck :-)

----------------------------------------------------------------------
kinlion · 2020-04-25 11:25

I re-test using PacketRecords = 1, but I got RecordCount = 10 just
after open the query.
It seems like PacketRecords is no use 
Below is the test code:
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i  
Screen.Cursor := crHourGlass;  with MasterDB.newQuery do // a new
created query, linked to nothing  try    PacketRecords := 1;   
MasterDB.StartTransaction;     SQL.Text := 'SELECT data FROM
files_data WHERE file_name=:file_name order by part_no';   
ParamByName('file_name').AsString := file_name;    Open;   
ShowMessage(IntToStr(RecordCount)); // Here shows 10    while not Eof
do    begin      Next;    end;  finally    Free;    Screen.Cursor :=
crDefault;    MasterDB.Commit;  end; 
Quote from: rvk on March 25, 2020, 09:31:15 amQuote from: kinlion on
March 25, 2020, 09:10:46 amI tested PacketRecords, set it to 1, but it
plays no difference. 
I'm using MySQL 5.5
What is .Recordcount directly after open? If it's more than 1 or 10,
you have somthing connected to the query. Maybe a tdbgrid (which makes
the tquery fetch more records).

If it's 1, your problem lies somewhere else. Maybe you have a complex
sql statement without index.

----------------------------------------------------------------------
kinlion · 2020-04-25 11:40

Yes, I know the records can be fetched one by one, each by a
query.open.
But I just want to fetch them by one query.open,  and each record is
fetched only when query.next.
Because it's a little more simple, and more effective (I guess).

Quote from: SamC on April 14, 2020, 08:29:45 amHello,

for MySql you can use th LIMIT 1 in your SQL-Statement.

qry.SQL.Text:='select count(1) from table_name';
qry.open;
if not eof
   then max_rec:=qry.Fields[0].asInteger else max_rec:=0;
qry.close;

if max_rec>0 then
begin
  rec:=1;
  qry.SQL.Text:='select field1, field2 from table_name LIMIT :start,
1;';
  qry.Params[0].asInteger:=rec;
  qry.open;
  while not qry.eof do
  begin
    qry.Params[0].asInteger:=rec;
    ...
    do what ever you need to do
    ...
    inc(rec);
    qry.next;
  end;
end;


Good Luck :-)

----------------------------------------------------------------------
rvk · 2020-04-25 12:18

What connection component are you using?
Maybe packetrecords isn't implenented in that one.

And you are sure MasterDB hasn't a datasource component or anything
connected to it?

----------------------------------------------------------------------
kinlion · 2020-04-25 12:59

I'm using MySQL5.5 Connection, MasterDB = TMySQL55Connection.Create.
No DB-control is used.

After digging in FCL source code, I add a line, make PacketRecords
WORKED. 

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i  
Screen.Cursor := crHourGlass;  with MasterDB.newQuery do  try   
UniDirectional:=True; // ADD THIS LINE, THEN PacketRecords WORKED   
PacketRecords := 1;    MasterDB.StartTransaction;     SQL.Text :=
'SELECT data FROM files_data WHERE file_name=:file_name order by
part_no';    ParamByName('file_name').AsString := file_name;    Open; 
  ShowMessage(IntToStr(RecordCount)); // NOW RecordCount is 1    while
not Eof do    begin      Next;    end;  finally    Free;   
Screen.Cursor := crDefault;    MasterDB.Commit;  end;       
However, I found if query.Open hit much data, it will still take too
much time.
Because the server has to prepare much data, I think.
So,  to read a lot of data from server, fetching sub-set records by
one query.open to notify progress maybe better.

Thanks to all!

Quote from: rvk on April 25, 2020, 02:18:45 pmWhat connection
component are you using?
Maybe packetrecords isn't implenented in that one.

And you are sure MasterDB hasn't a datasource component or anything
connected to it?

----------------------------------------------------------------------
rvk · 2020-04-25 14:31

Quote from: kinlion on April 25, 2020, 02:59:33 pmHowever, I found if
query.Open hit much data, it will still take too much time.Is there an
index on part_no?
If not, create one.

If there is an index, how large is 'data'?
There might be no other way to speed things up.

----------------------------------------------------------------------
kinlion · 2020-04-26 01:29

Quote from: rvk on April 25, 2020, 04:31:56 pmQuote from: kinlion on
April 25, 2020, 02:59:33 pmHowever, I found if query.Open hit much
data, it will still take too much time.Is there an index on part_no?
If not, create one.

If there is an index, how large is 'data'?
There might be no other way to speed things up.

There's a unique index on (file_name, part_no).
In fact,  I have a file of about 150MB size to distribute to clients.
To transfer such a big file over a poor WAN connection is very time-
costing, says over 10 minutes.
I realize that  there is no way to speed up transfering.
So I just want to indicate the transfering progress 
I put the file in database, and the max size of a MySql blob field is
64K-1 bytes.
After fetching each record from server, I can update the indicator-
bar.

----------------------------------------------------------------------
rvk · 2020-04-26 04:32

Quote from: kinlion on April 26, 2020, 03:29:33 amIn fact,  I have a
file of about 150MB size to distribute to clients.
To transfer such a big file over a poor WAN connection is very time-
costing, says over 10 minutes.
Yikes   There is your problem.
I was wondering why even one record would be slow.
But you say over WAN (internet).
You SHOULD have mentioned that from the beginning.

The problem is not the record size of 150MB (although large) but the
fact you're accessing the database over the internet. Database
communication protocols are inherently chatty. That means for every
packet of 64K bytes there are multiple messages over the line. All are
subject to a large transfer time (look at the ping-time of each
message). And one record of 150Mb equals tens of thousands messages.
Each with it's own 'ping'-delay.

Databases are not meant to be accessed directly over the internet.
It's just to slow. And if you have your mysql server exposed to the
full internet (without vpn etc)...   O, don't get me started about
security. Just don't do it.

You are better of creating a multi-tier solution. Create a middle-man
(for example a web-server) which does the actual sql query. The web-
server is on the same network as the mysql-server, and only send json
messages with the result back to the client. Only when you really need
the actual record, then you transfer the actual data (and 150MB should
transfer in 12 seconds to 2 minutes, 100Mbps resp. 10Mbps). With this
solution it would even be possible to zip the resulting file before
transfer. Or send multiple files in one zip. Because file transfer
over the internet is much faster then 'database-protocol' (really, DB-
protocol is really really chatty) you'll speed up your client
considerably.

Really, databases are not meant to be accessed directly over the
internet and especially not for 'file-transfer'.

----------------------------------------------------------------------
kinlion · 2020-04-26 06:27

Quote from: rvk on April 26, 2020, 06:32:11 amReally, databases are
not meant to be accessed directly over the internet and especially not
for 'file-transfer'.
You are right.
My App is designed for running in LAN. But now the servrer has been
moved to CLOUD

Thanks