ADO - lost records with asynchronous fetching

[Pavel]

I have a simple task: Delphi7 ADO dataset >> select query (SELECT Name FROM
Names WHERE Name LIKE "nov*";) in a separate thread. The dataset needs
asynchrounous fetching (due to the huge amount of matching record that I
need to display during the fetching), so this property is set to True.

But the result is very strange - the query sometimes looses first record (or
records) unless I slow down the thread priority to tpLower (or, on more
powerful machines even slower!).

Whene asynchrounous fetching is set to False - everything is OK (but that's
not the goal).

Additionaly, if some windows task runs and consumes more CPU time,
asnynchrounous query looses first records too. The behaviour is sometime
accidental (repeated query gives different results).

system: Windows XP, latest updates...

I have already posted this problem some time ago, but have not got a
satisfactory...

Can anybody help or should I try Microsoft hotline?? :-))

Pavel

[Vitali Kalinin]

Are you creating ADOConnection and ADODataSet in a thread? Also since you
are doing that in a separate thread I don't see any reason for async
fetching, since the only purpose of this thread is to fetch records or am I
missing something?

[Pavel]

There is a reason:

1. There should be thousands of records retrieving from server and I need
quick response, so in asynchronous fetching I can get first 10 records
immediatelly and the rest is reading in background thread...

2. I need a progressbar displaying the progress. Its necessary if getting
10000 records takes about 30seconds...

3. It works fine on a middle power machine with the thread priority set to
tpLower

I guess the reason is in Jet Engine...


When not using asynchronous fetching, I must somtimes wait 30 second till I
get the first record (at that time whole dataset is ready). If you run any
query in MS Access, it works exactly the way I need, it gives you the first
page immediatelly and the rest is fetching in background...


The sample project is simple, but the database that I am retrieving has
about 1mil. records...


Pavel

Post by Vitali Kalinin
Are you creating ADOConnection and ADODataSet in a thread? Also since you
are doing that in a separate thread I don't see any reason for async
fetching, since the only purpose of this thread is to fetch records or am I
missing something?

[Vitali Kalinin]

So make fetching in a separate thread with low priority and have main VCL
thread to display the progress of service thread.

[Pavel]

This works fine on most pcs, but does not e.g. on dual AMD 2GHz or powerfull
machines... even if using the lowest possible thread priority... The
searching with low priority is on contrary slower, which is also not good...

Pavel

[Vitali Kalinin]

My point is as follows - if you are fetching within separate (dedicated for
this purpose) thread then you just don't need async fetching, it just
slowing down process.

[Pavel]

I think you did not get the point... The query sometimes gets thousands of
records, it takes some time and synchronous query gets the first record not
earlier then all records are ready. But I need to get the first number of
records as soon as possible and show them to user and the rest can be
fetched in background... The same way how MS Access does if you run a
selection query...

It may be other way how to achieve that, but I do not know...


Pavel

Post by Vitali Kalinin
My point is as follows - if you are fetching within separate (dedicated for
this purpose) thread then you just don't need async fetching, it just
slowing down process.

[Vitali Kalinin]

Then you should consider serverside cursors

[Brian Bushay TeamB]

Post by Pavel
I have already posted this problem some time ago, but have not got a
satisfactory...
Can anybody help

BetterADODataset has a fix for this
http://betterado.hit.bg/
--
Brian Bushay (TeamB)
***@NMPLS.com

[Pavel]

Hi Brian,

I think that BetterADO really works!!! It depends on cache size setting
pretty fine, but it can be tuned so it does not looses records... So this is
a problem of Delphi's original ADO component???

Once again, thanks for the idea...

Pavel

Post by Brian Bushay TeamB

Post by Pavel
I have already posted this problem some time ago, but have not got a
satisfactory...
Can anybody help

BetterADODataset has a fix for this
http://betterado.hit.bg/
--
Brian Bushay (TeamB)

[Pavel]

Well, my pleasure about the BetterADO was premature. I did not notice, that
with both AsyncFetch and AsyncFetchNonblocking set to True the
BetterADODataset does not realy fetch records asynchronously in background,
so with only AsyncFetch=true it works, but the records are still lost :-(((

I tried many parameters combinations, but the result was still the same...
(minimally the first record missing).

So I created a simpe sample program that shows the problem. If you like, you
can download it here:

www.pc-slovniky.cz/test/test.rar

It containes test.mdb database, you just need to reconect the right file in
your folder. And small very usefull BMDThread component, that I use for easy
threading...

You can togle between using standard Delphi ADO and BetterADO datasets and
see the results. When searching "machine" you get a total of 573 or less
instead of 574 records unless you change the thread priority to lower (but
this does not help on quick PCs)... The best way how to see the background
fetching process, is using pattern, like "%machine" or "%drop" and 'no
sorting'...

(searching with pattern through 900000 records takes about 18s, but with
right fetching you can get the first records almost immediatelly! and the
rest in background)

I hope, that someone smarter than me can find out where the problem is...

Pavel

Post by Brian Bushay TeamB

Post by Pavel
I have already posted this problem some time ago, but have not got a
satisfactory...
Can anybody help

BetterADODataset has a fix for this
http://betterado.hit.bg/
--
Brian Bushay (TeamB)

[Brian Bushay TeamB]

Post by Pavel
So I created a simpe sample program that shows the problem. If you like, you
www.pc-slovniky.cz/test/test.rar
It containes test.mdb database, you just need to reconect the right file in
your folder. And small very usefull BMDThread component, that I use for easy
threading...

Since you are not actually using the TadoDataset or TadoBetterDataset as
components to hook dataAware controls you can sidestep the problem by directly
using a recordset.
From your sample code something like this

var
rs:_Recordset;
ADOcn:TadoConnection;
Begin

if UseRecodSet.Checked then Begin
ADOcn := TADOConnection.Create(nil);
adoCn.LoginPrompt := false;
Adocn.ConnectionString := ADOConnection1.ConnectionString;
ADoCn.Open;

rs := AdoCn.Execute( 'SELECT Term, ID '+
'FROM [Term1] '+
'WHERE (Term LIKE "'+Edit1.text+'%")'+' ORDER BY
Term;',cmdText,[eoAsyncFetch, eoAsyncFetchNonBlocking]);
with Rs Do begin
while not (Eof or Thread.Terminated) do
begin
inc(FCounter);
FRecord:=IntToStr(FCounter)+' '+rs.Fields.Item[0].Value;
Thread.Synchronize(ThreadSynchro, Data);
moveNext;
end;
close;
end
end
--
Brian Bushay (TeamB)
***@NMPLS.com

[Pavel]

Hi,

I tried that and it really works!!! Thanks! No lost records and the fetching
is really asynchronous. But one problem is obvious, and that is how to
determine and show the fetching progress??

The ADODataset has OnFetchProgress event where I can determine the number of
records from which I can build a progressbar to show user the fetching
progress...

Can you advise how to do that with your model once again?

Pavel

Post by Brian Bushay TeamB

Post by Pavel
So I created a simpe sample program that shows the problem. If you like, you
www.pc-slovniky.cz/test/test.rar
It containes test.mdb database, you just need to reconect the right file in
your folder. And small very usefull BMDThread component, that I use for easy
threading...

Since you are not actually using the TadoDataset or TadoBetterDataset as
components to hook dataAware controls you can sidestep the problem by directly
using a recordset.
From your sample code something like this
var
rs:_Recordset;
ADOcn:TadoConnection;
Begin
if UseRecodSet.Checked then Begin
ADOcn := TADOConnection.Create(nil);
adoCn.LoginPrompt := false;
Adocn.ConnectionString := ADOConnection1.ConnectionString;
ADoCn.Open;
rs := AdoCn.Execute( 'SELECT Term, ID '+
'FROM [Term1] '+
'WHERE (Term LIKE "'+Edit1.text+'%")'+' ORDER BY
Term;',cmdText,[eoAsyncFetch, eoAsyncFetchNonBlocking]);
with Rs Do begin
while not (Eof or Thread.Terminated) do
begin
inc(FCounter);
FRecord:=IntToStr(FCounter)+' '+rs.Fields.Item[0].Value;
Thread.Synchronize(ThreadSynchro, Data);
moveNext;
end;
close;
end
end
--
Brian Bushay (TeamB)

[Brian Bushay TeamB]

Post by Pavel
I tried that and it really works!!! Thanks! No lost records and the fetching
is really asynchronous. But one problem is obvious, and that is how to
determine and show the fetching progress??
The ADODataset has OnFetchProgress event where I can determine the number of
records from which I can build a progressbar to show user the fetching
progress...
Can you advise how to do that with your model once again?

Ok try this revision of your sample code
with ADODataSet1old do
begin
// Enables using patterns for searching
CommandText:=
'SELECT Term, ID '+
'FROM [Term1] '+
'WHERE (Term LIKE "'+Edit1.text+'%")';

if Fsort.checked then CommandText:=CommandText+' ORDER BY Term;'
else CommandText:=CommandText+';';
Open;
rs := ADODataSet1old.Recordset;
rs.MoveFirst;
with Rs Do begin

while not (Eof or Thread.Terminated) do
begin
inc(FCounter);
FRecord:=IntToStr(FCounter)+' '+rs.Fields.Item[0].Value;
Thread.Synchronize(ThreadSynchro, Data);
moveNext;
end;
end;

close;
end;

--
Brian Bushay (TeamB)
***@NMPLS.com

[Pavel]

Hi Brian,

I have to say that you are a magician! :o)

So, I guess problem solved. Thanx again for you time...

Pavel

Post by Brian Bushay TeamB

Post by Pavel
I tried that and it really works!!! Thanks! No lost records and the fetching
is really asynchronous. But one problem is obvious, and that is how to
determine and show the fetching progress??
The ADODataset has OnFetchProgress event where I can determine the number of
records from which I can build a progressbar to show user the fetching
progress...
Can you advise how to do that with your model once again?

Ok try this revision of your sample code
with ADODataSet1old do
begin
// Enables using patterns for searching
CommandText:=
'SELECT Term, ID '+
'FROM [Term1] '+
'WHERE (Term LIKE "'+Edit1.text+'%")';
if Fsort.checked then CommandText:=CommandText+' ORDER BY Term;'
else CommandText:=CommandText+';';
Open;
rs := ADODataSet1old.Recordset;
rs.MoveFirst;
with Rs Do begin
while not (Eof or Thread.Terminated) do
begin
inc(FCounter);
FRecord:=IntToStr(FCounter)+' '+rs.Fields.Item[0].Value;
Thread.Synchronize(ThreadSynchro, Data);
moveNext;
end;
end;
close;
end;
--
Brian Bushay (TeamB)

[Blake McNeill]

Interesting. I'm also testing an asynchronous query in Delphi 5 and I am
very much surprised to hear that this bug hasn't been fixed within Delphi 7.
I fixed it by using a Woll2woll data component (even then you have to scroll
up to the first element), but still I would have thought that by Delphi 7
Borland would have resolved this issue or am I missing something.

The tests that I'm doing are centered around bringing back huge result sets
from a simulation and loading them into a grid (I'm still after the
engineers to tell me what it is they expect to find in a grid with millions
of items, just because you can load a millions of items into a grid doesn't
necessarily mean its a good idea) and asynchronous is certainly one option
for loading this grid.

Blake

[Pavel]

Hi,

as you could see, I got help from Brian Bushay (TeamB), ***@NMPLS.com,
and used his recommendation like this:


with ADODataset1 do
begin
CommandText:='SELECT ...
Open;

// here we work with recordset instead of dataset itself
with recordset do
begin
MoveFirst;
while not (Eof or Thread.Terminated) do
begin
HandleData(Fields.Item[0].Value,Fields.Item[1].Value);
MoveNext;
end;
end;
close;
end;


... and it really works like it is supposed to! No lost records anymore
:-)))

Pavel

Post by Blake McNeill
Interesting. I'm also testing an asynchronous query in Delphi 5 and I am
very much surprised to hear that this bug hasn't been fixed within Delphi 7.
I fixed it by using a Woll2woll data component (even then you have to scroll
up to the first element), but still I would have thought that by Delphi 7
Borland would have resolved this issue or am I missing something.
The tests that I'm doing are centered around bringing back huge result sets
from a simulation and loading them into a grid (I'm still after the
engineers to tell me what it is they expect to find in a grid with millions
of items, just because you can load a millions of items into a grid doesn't
necessarily mean its a good idea) and asynchronous is certainly one option
for loading this grid.
Blake

[Gonzalo Torres]

Hi
I have a similar situation, the problem is that I execute a TADOStoredProc
to calculate
salaries from different tables. This store procedure lasts about 45 minutes,
and as it's
executing in a synchronous mode, so it doesn't allow me to show a counter or
a progress bar,
until it finishes the process. Let's suppose it updates 75400 records.
If I change the ExecuteOptions property it only updates 75398 records when
the CacheSize
is set to 1; it is worse when I increase the CacheSize.
Furthermore AsyncFetch and AsyncFetchNonblocking set to True doesn't allow
me to show a
counter because it still freezes the program.
I'm using Delphi 6 under WinXP.
I don't want to change the threat priority. Even if I wanted to, I don't
know how.
I don't want to use the BetterADODataset either.
I don't need to show records, only to execute the store procedure.

If there's a way by using a recordset like the other examples? how can I
solve the problem?

Here is the code for the calling to the TADOStoreProc:

procedure TPrima.GenerateSalaryClick(Sender: TObject);
begin
if strtofloat(MinimumSalary.text) <= 0 then
showmessage('Wrong Salary')
else
begin
Screen.Cursor := crSQLWait;
DMNominaRep1.DatosRep1.ADOSTPPRIMAVAC.Parameters.Items[1].Value :=
strtofloat(MinimumSalary.text);
DMNominaRep1.DatosRep1.ADOSTPPRIMAVAC.Parameters.Items[2].Value :=
sTRtOdATE(period.text);
DMNominaRep1.DatosRep1.ADOSTPPRIMAVAC.Open;
Screen.Cursor := crDefault;
SHOWMESSAGE('Process Finished');
end;
end;