Using PostgreSQL, I find myself frequently adding views (many w/ subqueries) as tables in the DCT. This is much better than struggling to duplicate using Clarion procedure views.
While there is not any need to add a lot of keys & relationships w/ everything handled by PG, as the number of tables moves to 400+, I keep thinking back to long ago reports of hitting DCT table limits or seg limits when generating. Has anyone hit such problems using up to date Clarion versions (v11.1)?
I’ve seen a dictionary with 1200 tables, so I don’t think it’s an absolute table limit. However;
This is not necessarily a great idea, especially if it is bloating your dictionary. Each table in your dictionary consumes resources (which takes some time) every time you start a thread. So the more tables you have the “slower” each thread starts (about 1 tenth of a second or so for each 300 tables) and the more ram (for file buffers) each thread consumes.
This is likely not an issue in a Desktop program, but for a web app it might become significant.
This seems worthy of a discussion. While I certainly recognize your point about 300 tables consuming resources on every thread, I believe adding backend views to the DCT as tables is absolutely essential. As a consequence, I have to wonder if every table for every thread is really necessary? Afterall, would it not be possible (with some inspection) to determine which tables can possibly be used on a given thread?
This would be astonishingly difficult to accomplish. (I actually spent some time thinking this through some years ago for performance reasons.)
the (very condensed) list of things that this would entail;
No more global table declarations. Tables would need to be defined locally, in all cases where they are used. ie they would switch from being Global objects (and record buffers) to Local objects and record buffers.
Since the tables are declared locally a much more elaborate scheme of passing data between procedures would need to be architected. So it would take a new class framework, and a new template framework to go with it. that would have an impact on every 3rd party accessory, since accessories would need to be tailored to work in this framework. So basically, inventing everything again from scratch.
An alternative would be a radically different mechanism in the way Clarion starts threads - deferring the way objects are instantiated, and memory for global threaded variables allocated. The possible ways you could mess up a program with a delayed system like this would be impressive. Basically it could work, but would take and extra level of programming skill for all developers.
the idea of “looking down the tree” - no, that’s not a thing, and frankly given the way tables relate to each other would be useless, because in concept pretty much every thread can touch pretty much every table - if only via things like related updates and deletes. So trying to predict what is needed is not the way to go, the programmer would have to specifically say it’s needed, before it is used.
Could such a system be done? yes. Is it worth doing? no. There are easier ways to solve your root issue than redesigning everything from scratch. Between Clarion client-side views, and prop:sql, and client-side data caching in temporary tables, there are a lot of alternative architectures that would be more suitable.
I appreciate the lengthy response. One primary consideration is whether TPS files need to be accommodated or not. For example, with SQL I wonder if a RelationManager object is even necessary? Off the top of my head, a FileManager object and buffer is all my PG view really needs. If 300+ RelationManager objects could be eliminated for every thread, is the tenth of a second halved?
As for widespread use of PROP:Sql as an alternative to DCT tables for views, I see that as a Clarion-centric solution that either duplicates backend capabilities or ignores them altogether. What happens when a non-Clarion app is added and needs the exact same view involving two subqueries?
This suggests that if the table is only used in one place, and only in what is basically a hand-code procedure, then it should be removed from the dict, and just declared locally where it is used. You could basically have one conversion procedure where they are all declared if you like. This would have a noticeable impact on your program performance (when starting a thread from the frame).
That’s an interesting question. Conceptually I can see removing the RelationManager as a possibility, although that would incur some number of changes to the shipping templates.
The savings though would be minimal, and not significant. Each RelationManager object only has 21 bytes of data space, and a trivial constructor, so removing that would have a negligible impact and would not be visible.
Balanced against the restrictions that would impose on the environment (ie, SQL only, Server-side relation management only) I don’t think it would be worth it.
I’m not sure I agree. Firstly you are free to add the views to the SQL db of course, so other programs and systems are not affected. Just because they are in the DB does not mean they have to be in the DCT.
From the Clarion point of view you could still make use of those Views, but via a generic table, not a specific table. This is doubly true if those views are used in a read-only way. In that situation you can have 1 (or a small number) of generic tables on the client side, and map them to Views on the server side as you need to.
Using the approach the code is somewhat less elegant (since there isn’t exact naming for each field) - you’d be dealing with Field1, Field2 and so on - but the overall performance impact on the program would be significant.
Have not tested myself, so will ask if you have confirmed that thread start time is determined more by actual data space than number of objects? My assumption was that construct code would certainly be a factor, but Init code must be called.
A thread starting consumes 2 resources; memory and CPU.
The CPU will be based on the code in the CONSTRUCT method, “mallocing” the memory required, and then clearing that memory (in most cases, ie where there isn’t ,AUTO).
The memory usage is record buffers, plus object data area. Generally speaking the CPU part will be the “time” (although getting and clearing memory is done by the CPU).
So Size of the record buffers and data area will impact RAM, but also CPU time for clearing that memory. Plus any extra code in the CONSTRUCT method.
Understood. In the case of 300 tables & FileManagers, do we know or safely assume the time cost of starting a thread is the primarily the record buffer & size or a FileManager object? While I’m sure I will never reach the 1200 table DCT you mentioned, and I likely will not fret over the thread cost of another 1/10 sec for the next 300 views I could add without retirement, many old folks here probably still have DAB “Squeezing the Last Drop” habits.