Zeos 7.0.3 TZTable; Huge memory consumption

[A.Zukunft]

Hi everyone,

we are in the process of moving our application from Delphi 7 to Delphi XE3. In Delphi 7 we used the Borland Database Engine (BDE) as data connector to a SQL database (MySQl, MSSQL). In Delphi XE3 we replaced BDE with Zeos Components. The code is constructed upon TZTables. Most data access is handled over those tables which are created on application launch.

We have a huge memory consumption issues using those TZTables. Our application which maybe uses 200MB needs several hundreds MB just for a couple TZTables. A 60 MB SQL dump needs over 1GB in total application memory. I don't know why but it seems 1GB memory consumptions seems to be some kind of limit even after I set a Delphi flag to use up to 3GB on 32Bit systems. Hitting (and exceeding) that limit makes the application incredible slow as it's using one full CPU core just to "fetch" data.

I analysed the TZTable memory issue itself. It seems Zeos is preallocating ALL the space of an DB entry, even if it's not (fully) used. So e.g. you have a field varchar(200) Zeos seems to allocate memory for all those 200 signs (in Unicode) - even if the field is NULL or filled with 20 signs.

Are you aware of this issue? What is your recommendation?

Best Regards,

Andy

[mdaems]

Andy,

I'm afraid your approach is wrong from the beginning. Using TZTable you're effectively using the full tables, even if you only need some records. I suppose you use filters to limit the rows to the ones you effectively use? And sorting is done using TZTable property? Both are client-side operations, meaning your program needs to download the full table to do filtering and sorting. This may work for small lookup tables, but looks like a bad idea when MB of information is involved.
For all other cases you should use a TZ(Readonly)Query and include your main filters and sorting in the sql string. And when possible you should use parameters in the where part of the query when these filters are applied multiple times with different values.
The advantages are:
- sorting and filtering is done on the database server, and that server is designed to do this job efficiently
- only relevant data is sent over the network
- memory consumption is limited to relevant data
- no need to use the slow expression handling code inside zeoslib.

Concerning the assignment of memory for the data internally: yes, your description is correct for ZeosLib 7.0. If I remember correctly ZeosLib 7.1 has a different memory management. I'm not sure if it's more performant, but I think it's using much less memory when fields are only partially filled. So I think you should upgrade your zeoslib version to 7.1 when you absolutely keep using TZTables.

Mark

[A.Zukunft]

Hi Mark,

I am somewhat new to the development team. I can assure you we are aware of the bad software design. Background is the software history and the fact that the software is not our product it's "just" what makes the product work (so there is barely any time besides the most important changes). Some parts are 16 to 18 years old. The software started with simple text files, migrated to Paradox, migrated those Paradox Files to MySQL via BDE, got some (but not much) revamp on the database structure and is finally moving to your Zeos library. The guy who originally developed the software was an electrical engineer. Maybe this gives you an idea on how the source code of the software looks like .

As a matter of fact we do want to switch to TZQuery however the code is not designed that we simply can move everything to TZQuery right now. Thanks for your information on the memory consumption regarding Zeos v7.1. We will take your advice to heart and start upgrading. In the meantime I disabled the biggest Table (~400k entries) which reduced the memory consumption from 1GB (I believe there is some kind of limitation) to ~500MB and increased the speed tremendously.

What's the primary difference between TZQuery and TZReadonlyQuery and how big of a difference are we talking about? When using Queries I guess you need to transmit your SELECT statemens manually and if you want to update/insert data you need to create INSERT/UPDATE commands anyway. Your assumptions are correct. We are using the TZTable.Filter option to limit the display set to the relevant data, we do not sort as far as I am aware. Between the primary data table (think of it as the main data header) we have a Master/Detail-relation in place to the secondary data table (main data detail). All tables dependent on the secondary table are "linked" manually using TZTable.Locate if the "pointer" on the secondary table is moved. On several places in the code we are refreshing the TZTables because sometimes a different component (mainly DLLs) could write into the DB.

Let me assure you: I am aware this is insane. Some of my collegues starting to realise that as well ("OMG. Did you know? We are transfering the whole database!"). I already removed most of the TZTable.Refresh parts in the last few months.

Btw thanks for your reply. You may already helped me a lot with the information regarding Zeos v7.1.

Regards,

Andy

[mdaems]

Andy,

ZQuery is almost the same as a TZTable. (Actualy, internally a ZTable is a ZQuery with the automated sql setting 'select * from <tablename>')
As far as I know transforming a TZTable + filter to a TZQuery involves:
Step 1
- remove tztable
- add tzquery (eventually with identical name to avoid a 'replace all' text operation)
- insert a select statement on te sql property (select * from <tablename>)
- done -> nothing should have changed, filtering works identical, same amount of data is retrieved, dataset is read/write
Step 2
Remove columns you don't need by replacing the * with actual field names. Attention, even if you don't show the primary key, you need to select it for efficient updating. If you want to do client filtering also these coumns should be selected.
Step 3
Replace the filter code where useful. If you're always filtering on the same (group of) field(s) this is as simple as adding 'where <fieldname1>=:param1 and <fieldname2>=:param2...' to the end of the sql string and then fill the parameters (query.ParamByName('param1').asString := 'value') before query.open. (And use close/set parameter/open for each filter change).
Changing the where clause of the sql statement between close and open is a little less efficient, but gives full flexibility concerning the filter conditions.
It may be good to combine client (zeoslib) filtering with sql filtering. Eg. sql filtering for getting relevant data and client filtering for quick scrolling.

You see, re-enabling that big table shouldn't be a big job. No need to fire update statements, insert statements, ... that's all done by zeoslib when using simple selects from a single table and when zeoslib can map every row in the dataset to a single row in the database (primary key or at least all rows are unique)
Even if these conditions are not met it's not that difficult to combine TZQuery with TZUpdateSql, but indeed that's a little more advanced.

The difference between ZQuery and ZReadOnlyQuery is mainly
- readonly doesn't need update buffers, so should be a little less demanding for memory
- readonly+forwardonly setting *can* be more performant for some databases (actually mysql is one of these)
- readonly is readonly
So : Use ZReadOnly when you just need to show some extra information.

[A.Zukunft]

Hey Mark,

I already knew that TZQuery and TZTable inherit from the same object (TZAbstractDataset) but it's good to know they behave very similiar as well.

The one table with ~400k in the development database can be converted easily as it's just accessed in one place. We can convert all the other TZTable to TZQuery as well - provided we create them with "SELECT * FROM tablename". Everything beyond that is more complex. Our software initiates "components" which among others are creating the database access via TZTable. To access data you simple access "Component.Table.FieldByName" etc. This happens "everywhere" in the code including write access to the current table row. As a result we can't just limit the SELECT statement as we need all or most of the fields somewhere in the code. However adding a simple WHERE clause should be possible in most cases. Instead of loading the same table a few times (depending on the configuration) we are only loading the data from a table we need for the component.

I have a question in that regard. Let's assume we have a table called Table1. We are creating two TZTable objects accessing Table1. Will Zeos load and store the data once and then set a "pointer" for each TZTable (dataset is shared) or will it load and store for each TZTable individually (dataset is unique)?

Regards,

Andy

[mdaems]

Bad news. The table will be two times in memory.

Good luck!!

Mark

[EgonHugeist]

For the record. Since 7.3 i changed the memory consume of Zeos:
Types having a fixed size are in a rowbuffer, doesn't matter if null or not. Changing this would kill the memory manager (to many small allocs).
Variable types are allocated with Size: SizeOf(Pointer) in rowbuffer + Cardinal(Length)+Data.
Lob's, having a locator API in any kind, have the instance size in memory as long CachedLobs option is set to false.

And to be clear: there is no more duplicated memory in DataSets vs. IZCachedResultSet.

Summary the Memory-consume is halved inbetween.