Re: [fpc-pascal] TSQLQuery and buffering.

[Martin Schreiber]

On Saturday 25 March 2017 10:28:37 Michael Van Canneyt wrote:
> On Sat, 25 Mar 2017, Martin Schreiber wrote:
> > On Saturday 25 March 2017 09:32:33 Gary Doades wrote:
> >> I Understand about the "normal" use of TSQLQuery and data sets and I
> >> also use this for small result set that need the data changing some way
> >> and writing back to the DB. What I need now is an equally convenient and
> >> powerful way of processing "big data".
> >
> > MSEgui has TSQLResult for that purpose without the TDataset overhead.
> > https://gitlab.com/mseide-msegui/mseide-msegui/blob/master/lib/common/db/
> >msesqlresult.pas
>
> Can you explain in 2 words how this differs from TDataset ?
> At first sight it is not fundamentally different.
> The structure looks almost exactly the same, just the names are different.
>
Please follow the datatransfer from database to the destination in application 
in T*Dataset and compare it with TSQLResult and you probably will see the 
difference.

> And while you're at it, please explain what the 'overhead' is of TDataset.
> That TBufDataset introduces overhead is a design decision. But TDataset
> itself introduces no "overhead".
>
TDataset/TDataSource/TDatalink/TField is a complex machine, don't you think? 
Follow the code of a TDataset.Next() call step by step and you will see what 
I mean.

Martin
___
fpc-pascal maillist  -  fpc-pascal@lists.freepascal.org
http://lists.freepascal.org/cgi-bin/mailman/listinfo/fpc-pascal

Re: [fpc-pascal] TSQLQuery and buffering.

[Gary Doades]

>> I had read about UniDirectional and I have indeed tried this. It doesn't 
>> seem to make any significant difference. Looking through the source code for
>> TBufDataset it looks like UniDirectional just turns off building various 
>> indexes/structures etc. and fetches the result set all in one go instead of 
>> 10 row chunks.
>> It still buffers everything in memory.

> In that case, it is a bug. The very purpose of UniDirectional is exactly NOT 
> to buffer anything, just to keep the current record in memory.

Ah, OK. I might have another trawl through the sqldb source then to see if it 
is a simple mistake I can fix or a bigger job.

I guess I should file a bug report?

Thanks,
Gary.


___
fpc-pascal maillist  -  fpc-pascal@lists.freepascal.org
http://lists.freepascal.org/cgi-bin/mailman/listinfo/fpc-pascal

Re: [fpc-pascal] TSQLQuery and buffering.

[Gary Doades]

>
> MSEgui has TSQLResult for that purpose without the TDataset overhead.
> https://gitlab.com/mseide-msegui/mseide-msegui/blob/master/lib/common/db/msesqlresult.pas

Thanks.

 Although it looks interesting I'd rather not get into another set of classes 
etc. at this point. I may come back to it later though!

___
fpc-pascal maillist  -  fpc-pascal@lists.freepascal.org
http://lists.freepascal.org/cgi-bin/mailman/listinfo/fpc-pascal

Re: [fpc-pascal] Delphi for Linux is out

[Michael Schnell]

On 23.03.2017 13:26, Graeme Geldenhuys wrote:
* - place with whatever name suites you. 


Anders Hejlsberg :)

-Michael
___
fpc-pascal maillist  -  fpc-pascal@lists.freepascal.org
http://lists.freepascal.org/cgi-bin/mailman/listinfo/fpc-pascal

Re: [fpc-pascal] TSQLQuery and buffering.

[Michael Van Canneyt]

On Sat, 25 Mar 2017, Gary Doades wrote:



On Fri, 24 Mar 2017, Gary Doades wrote:


Really, the question is simple: Is there ANY way of TSQLQuery NOT 
reading the entire result set into memory?



Set Unidirectional to True, and it will keep only 1 row in memory.
When you are simply scanning through the result set, this is all you need.


Hi Michael,

Many thanks for your reply. I had read about UniDirectional and I have indeed 
tried this. It doesn't seem to make any significant difference. Looking through 
the source code for TBufDataset it looks like UniDirectional just turns off 
building various indexes/structures etc. and fetches the result set all in one 
go instead of 10 row chunks. It still buffers everything in memory.


In that case, it is a bug. The very purpose of UniDirectional is 
exactly NOT to buffer anything, just to keep the current record in memory.


Michael.
___
fpc-pascal maillist  -  fpc-pascal@lists.freepascal.org
http://lists.freepascal.org/cgi-bin/mailman/listinfo/fpc-pascal

Re: [fpc-pascal] TSQLQuery and buffering.

[Michael Van Canneyt]

On Sat, 25 Mar 2017, Martin Schreiber wrote:


On Saturday 25 March 2017 09:32:33 Gary Doades wrote:


I Understand about the "normal" use of TSQLQuery and data sets and I also
use this for small result set that need the data changing some way and
writing back to the DB. What I need now is an equally convenient and
powerful way of processing "big data".


MSEgui has TSQLResult for that purpose without the TDataset overhead.
https://gitlab.com/mseide-msegui/mseide-msegui/blob/master/lib/common/db/msesqlresult.pas



Can you explain in 2 words how this differs from TDataset ? 
At first sight it is not fundamentally different. 
The structure looks almost exactly the same, just the names are different.


And while you're at it, please explain what the 'overhead' is of TDataset.
That TBufDataset introduces overhead is a design decision. But TDataset
itself introduces no "overhead".

Michael.
___
fpc-pascal maillist  -  fpc-pascal@lists.freepascal.org
http://lists.freepascal.org/cgi-bin/mailman/listinfo/fpc-pascal

Re: [fpc-pascal] TSQLQuery and buffering.

[Martin Schreiber]

On Saturday 25 March 2017 09:32:33 Gary Doades wrote:
>
> I Understand about the "normal" use of TSQLQuery and data sets and I also
> use this for small result set that need the data changing some way and
> writing back to the DB. What I need now is an equally convenient and
> powerful way of processing "big data".
>
MSEgui has TSQLResult for that purpose without the TDataset overhead.
https://gitlab.com/mseide-msegui/mseide-msegui/blob/master/lib/common/db/msesqlresult.pas

Martin
___
fpc-pascal maillist  -  fpc-pascal@lists.freepascal.org
http://lists.freepascal.org/cgi-bin/mailman/listinfo/fpc-pascal

Re: [fpc-pascal] Delphi RTTI vs Free Pascal RTTI

[Sven Barth via fpc-pascal]

Am 24.03.2017 19:55 schrieb "Michael Van Canneyt" :
>
>
>
> On Fri, 24 Mar 2017, African Wild Dog wrote:
>
>> Hello,
>>
>> I need to write a code compatilble with both free pascal and delphi using
>> the old style RTTI.
>> What are the diferences between delphi's and free pascal's
approach/types?
>>
>> I took a look at delphi's and fpc's TypInfo unit and it looks like the
>> types/functions are compatible.
>
>
> They are. There is one dereference on Delphi which is not present in fpc,
> although in fpc trunk, this additional dereference is there. If you use
the
> standard functions you should not notice this.

The binary data of RTTI added before 3.1.1 now has the double indirection,
but the Delphi-compatible "field" names have only a single indirection for
backwards compatibility (they are properties to the double indirection
fields). RTTI data added in 3.1.1 only has the double indirection.
So this difference needs to be kept in mind and will always result in
ifdefs for RTTI code that handles both Delphi and FPC.

Regards,
Sven
___
fpc-pascal maillist  -  fpc-pascal@lists.freepascal.org
http://lists.freepascal.org/cgi-bin/mailman/listinfo/fpc-pascal

Re: [fpc-pascal] TSQLQuery and buffering.

[Gary Doades]

On Fri, 24 Mar 2017, Gary Doades wrote:
>
>> Really, the question is simple: Is there ANY way of TSQLQuery NOT 
>> reading the entire result set into memory?

> Set Unidirectional to True, and it will keep only 1 row in memory.
> When you are simply scanning through the result set, this is all you need.

Hi Michael,

Many thanks for your reply. I had read about UniDirectional and I have indeed 
tried this. It doesn't seem to make any significant difference. Looking through 
the source code for TBufDataset it looks like UniDirectional just turns off 
building various indexes/structures etc. and fetches the result set all in one 
go instead of 10 row chunks. It still buffers everything in memory.

I can see this from running a test program. When I call the SQL query .Open 
method then I can see from Task Manager that it is fetching all rows from the 
database. My test program reaches about 1.7GB of ram fetching 4.8 million rows.

Only after the complete fetching does the next line of code execute and my 
processing loop start. Clearly this is looping over the now buffered dataset. 
The memory is finally freed when the Query's Close method is called.

It may be that UniDirectional is meant to not buffer all rows, but at the 
moment it certainly seems to. This is FPC 3.0.2 BTW. I've also tried setting 
ReadOnly to true on the SQL Query.

I Understand about the "normal" use of TSQLQuery and data sets and I also use 
this for small result set that need the data changing some way and writing back 
to the DB. What I need now is an equally convenient and powerful way of 
processing "big data".

Thanks,
Gary.



___
fpc-pascal maillist  -  fpc-pascal@lists.freepascal.org
http://lists.freepascal.org/cgi-bin/mailman/listinfo/fpc-pascal

Re: [fpc-pascal] TSQLQuery and buffering.

[Michael Van Canneyt]

On Fri, 24 Mar 2017, Gary Doades wrote:


Hi everyone,

Firstly, I realise this is an old subject, but I still can't find any easy
answer.



Really, the question is simple: Is there ANY way of TSQLQuery NOT reading
the entire result set into memory?


Set Unidirectional to True, and it will keep only 1 row in memory.
When you are simply scanning through the result set, this is all you need.

The problem is that most drivers also do not support arbitrary navigation 
in the result set, so when we want to support navigating backwards, we have

no choice but to keep everything in memory. (barring re-running the query
and refetching everything, I'm sure you don't want that either)

If you need to move back and forth in the result set, then you 'll need to
set unidirectional to true, and store the needed records yourself.  You can
copy the structure of the dataset really easy to a TBufDataset using
copyfromdataset).

in the TBufDataset you can keep as many records as you like, and just delete 
the old
ones (you need to call mergechangelog or somesuch). 
We could maybe try to find a way to copy the buffer from one

bufdataset to another (since TSQLQuery is just a TBufDataset Descendent)

Michael.
___
fpc-pascal maillist  -  fpc-pascal@lists.freepascal.org
http://lists.freepascal.org/cgi-bin/mailman/listinfo/fpc-pascal

[fpc-pascal] TSQLQuery and buffering.

[Gary Doades]

Hi everyone,

 

Firstly, I realise this is an old subject, but I still can't find any easy
answer.

 

Really, the question is simple: Is there ANY way of TSQLQuery NOT reading
the entire result set into memory?

 

This is really killing me. I've got quite a lot of web code using sqldb and
it works really well. The classes for connection, transaction, query etc.
are perfect for general use and make it so easy to write database
applications. I find it excellent that I only need to change the connection
class in my code to use a different database.

 

For web code (mine included), fetching a few dozen, hundred or even a few
thousand rows is no real problem. The sqldb classes are *very* fast and very
easy to use.

 

The problem comes when I need to write some apps to either analyse or move a
lot of data. Initially all I need to do is analyse some data by simply
reading through rows, doing some calculations and other analysis. For even 1
million rows this is very fast (10 seconds using MySQL) so no major
problems. However, it does use quite a lot of memory.

 

Further use of this simply explodes. I have tables for analysis (and later
extracting/moving) that are over 40 million rows. This currently is simply
not possible in fpc, at least using the excellent sqldb classes. After
blowing 16GB of RAM+swap it takes a while to recover.

 

To be fair I've tried looking through the code and see how it's implemented,
but I can't see any easy way of avoiding the memory buffering of the entire
result set.

 

Apart from re-implementing a lot of classes or going down to the driver
level I can't currently see any easy way round this. I know there is some
old discussion on this, but I'm kind of hoping that there may have been a
few changes or something recently that I've missed.

 

Any help appreciated.

 

Regards,

Gary.

 

 

___
fpc-pascal maillist  -  fpc-pascal@lists.freepascal.org
http://lists.freepascal.org/cgi-bin/mailman/listinfo/fpc-pascal