Temp Table as parameter or Shared temp table

Hi Everyone,

I will have a huge project where there is many temp tables. For some reason I will build them in a procedure file before using them in a window file.
I would like to know if it's best to push all the temp-table by parameter or by using them as shared temp-table?

What do you think about this ?

Best Regards,

- BobyIsProgress -
 

Cringer

ProgressTalk.com Moderator
Staff member
Shared anything is bad. And passing tamp table parameters around is pretty old fashioned. You should at least use datasets.
 

tamhas

ProgressTalk.com Sponsor
Put the temp-table in a persistent procedure or class and provide methods to operate on it. Much cleaner.
 

TomBascom

Curmudgeon
"For some reason"? Maybe you could elaborate on what that reason is? It is extremely difficult to suggest "best" when one does not know "why".
 
"For some reason"? Maybe you could elaborate on what that reason is? It is extremely difficult to suggest "best" when one does not know "why".

Nothing tricky.

We have some use in production that is using our ERP only for viewing our production documentation . So they don't need to be permantly connected to our DB. I'm planning to build a programm that will only work with temp-tables that are load with data of the DB tables. While runing some test and checking on the internet it occured to me that I can't call the DB table from the main window because it give me an error while runing ("You are connected to no DB and so I don't recognize the table called in the programm" not the exact error message but something like that).

So I build something like this as test:
Code:
CHOOSE BUTTON TRIGGER:

    CONNECT /* Connexion info */ NO-ERROR.

    IF ERROR-STATUS:ERROR THEN DO:
        MESSAGE "kapout"
            VIEW-AS ALERT-BOX INFO BUTTONS OK.
        RETURN.
    END.
    
    RUN dev/pxTest.p .

    DISCONNECT VALUE("dbname") .

    RUN dev/pxtest.p .
    
dev/pxtest.p :

FIND FIRST SDTPRA NO-LOCK NO-ERROR.

MESSAGE SDTPRA.proref
    VIEW-AS ALERT-BOX INFO BUTTONS OK.

RELEASE SDTPRA.

And this was working. So I assumed that I will have to do it that way. The thing is I will have some huge temp-table, and I don't want to sacrifice performance to much.

Best Regards,

- BobyIsProgress -
 

Rob Fitzpatrick

ProgressTalk.com Sponsor
The thing is I will have some huge temp-table, and I don't want to sacrifice performance to much.
If the cost of this client-side caching approach is "huge" temp-tables and possible performance impacts, what is the perceived offsetting benefit? Are you trying to reduce database reads? Or network traffic? Or keep production documentation data out of the buffer pool? Or something else?
 
We trying to reduce the followings things:
1. network traffic
2. number of licence in use. It's a bit sad to use a full licence to only access the database max 5times per day maybe 10. for us we are talking about 40 licences
3. Also reduce our dbreads because the actual programm they use is refresh every 5 minutes.
 

tamhas

ProgressTalk.com Sponsor
If you have a top level procedure which is not connected to the DB ... and I am not particularly sympathetic that it is sensible to be so stingy with licenses ... then the usual thing is to connect to the DB and transfer control to a subprocedure since it is only in that subprocedure that the DB will be connected. But, I am still fuzzy on your actual problem i.e., what the relationship is between the 5-10 accesses a day vs the refresh every 5 minutes. Do the accesses last long enough to get multiple refreshes during an access? How is it that you refresh every 5 minutes and yet expect not to have a live on-going DB connection? A clear description of what you want to accomplish and how you think you are going to accomplish it would move this exchange along a good bit.
 
Hi,

I hear you @tamhas .

So the thing is, I agree, mainly about money. The actual setup for our production line (around 40 production line on our plant) is a followed:
- 5 desktop computer with our ERP on it.
- 1 use it to declare production and watch production satistics of the line in real time -> added value
- 4 use it only for our doc app. This doc app is open on the same product during the production of the "product" -> no added value at all.

So this why I would like to reduce the using time of our licence for this 4 computer.

I'm agree with the fact that if we need more licence we should buy more.

Also I saw this as a challenge in my growing on progress mastery.

Best Regards,

- BobyIsProgress -
 

TomBascom

Curmudgeon
If there is no value to these "doc app" sessions being open then why not close them? Why were they ever even opened?

It seems to me that if you have users adding no value that the obvious thing to do is to simply eliminate those users.
 

Rob Fitzpatrick

ProgressTalk.com Sponsor
number of licence in use
Are you referring to OpenEdge licenses? It's not clear to me that this approach has any impact on your licensing requirements. Or do you mean the number of database connections in use concurrently?

Also reduce our dbreads because the actual programm they use is refresh every 5 minutes.
Caching the data on the client will reduce network traffic, if it will be read often. Whether it is a meaningful reduction is something you will have to measure.

If your temp-table buffers are too small, you could end up reading and writing that data many times over, to and from the DBI file on the client. That could offset any gain from avoiding database I/O. If you go this route, keep an eye on DBI size and I/O and adjust -Bt accordingly.
 

tamhas

ProgressTalk.com Sponsor
More info would be useful as usual. What version of Progress. What license model? Do you have replication? If not, why not? Might it be a solution to run the read-only users on the replicate?

If you are refreshing these temp-tables every 5 minutes to have current data for reporting, then it is likely that it creating more DB traffic than it would to merely query the DB for each individual question.
 
Hi,

I'm running OE 10.2B08 (I know we are on a not supported version, we are planning our migration on OE 11.7 and I hope OE 12)

I don't know our licence model (i'm not in charge of that subject) .

I think I didn't explain it well before.


So we have develop a long time ago an application that we called small menu for the production line. With this app the user can see what is there production schedule, launch a production, end it, generate label, declare production (add finish good to stock and backflush the component) and also read the documentation about the part they are making. We have this app on every computer in our production line.

Each production line has around 4 - 6 computer on it something like that. They can declare the production from only one of them (connected to a barrecode scanner, usueally on the end of the line) and start/end the production on the first one. So this two are needed to be connected at real time.
So we have 2 - 4 computer that just need to display the product documentation (that is a sastic data).

That's why we thought about to just connect our new app only when needed and not at real time.

But I'm agreed on one thing if every user change reload there documentation at same time, we will still have a pick connection and we will still have to buy more progress licence.

Hope I was more understandable than the last time,

Best Regards,

- BobyIsProgress -
 
Top