How I can optimize a B.D in Progress SLQ with TM1

[jolteon]

Hello everybody.

I have a DataBase. ODBC ,we use PROGRESS OPENEDGE ODBC DRIVER (64 bits), but when we use TM1 Architect>Processes , when executing the task this process takes by 1 hrs , when this process takes five minutes , we have an old TM1 server in Architect , the Task is 5 minutes.

Is there any procedure to speed up these Database in PROGRESS OPENEDGE ODBC DRIVER (64 bits).
modifying some log TM1cgf

[tomok]

Have you ever read the request for assistance guidelines for this board? No kidding but how can you expect someone to be able to provide assistance with such a paucity of information and what little information that is provided makes almost no sense and so full of acronyms that it would take a mystic to figure out what you are asking. What the heck is a BD? What is Progress and what does that have to do with TM1? What task are you talking about. What exactly does it do? Your request is equivalent to asking someone how long it takes to drive from Point A to Point B without telling anyone what those two points are. It could be anywhere from a few minutes to a few days. How can we help you speed something up when we have no idea what it is?

[jolteon]

Have you ever read the request for assistance guidelines for this board? No kidding but how can you expect someone to be able to provide assistance with such a paucity of information and what little information that is provided makes almost no sense and so full of acronyms that it would take a mystic to figure out what you are asking. What the heck is a BD? What is Progress and what does that have to do with TM1? What task are you talking about. What exactly does it do? Your request is equivalent to asking someone how long it takes to drive from Point A to Point B without telling anyone what those two points are. It could be anywhere from a few minutes to a few days. How can we help you speed something up when we have no idea what it is?

You are right I apologize for that

[David Usherwood]

That's good to hear, thanks.
But we are hoping you are following on with some more explanations. I've come across Progress as a 4GL/DB ( https://en.wikipedia.org/wiki/OpenEdge_ ... s_Language) and recall a prospect who had a very old version which would only do 32-bit ODBC (before IBM surprised us all by reviving the 64 to 32 bit gateway in a recent release). I'm still intrigued as to what a BD might be. You also need to specify what release of TM1 you are using, and (I suggest) the SQL (not SLQ) query you are issuing to Progress. I'm agog

[Alan Kirk]

The edits provide something of an improvement over the original post, most of the points in Tomok's post still stand.

I have a DataBase. ODBC ,

No you don't. ODBC is a programming interface for connecting to a database via a database management system (DBMS). It is not a database. Nor is it a database management system. This matters because in telling us this you aren't really telling us anything about what the nature of the data source is.

we use PROGRESS OPENEDGE ODBC DRIVER (64 bits),

Which presumably means that you are using the DBMS created by Progress, a company that many people will never have heard of previously. Having dug through another forum (not that I should have had to, since this should have been explained in your post), I found:

so is progress a database?

Yes, but it's primary attraction, at least for me, is the 4GL programming language that goes with it. I've worked in Progress for about 10 years now, including a couple of custom written apps for corporate clients and I like it a lot.

It's low profile is because they market the DB and tools primarily to developers rather than end-user customers. That means there are a lot of places running Progress that don't know they are, they just bought an application they wanted and it happens to be written in Progress and/or use the Progress DB.

However it is fair to say that you and most of the rest of the market could be justifiably confused about this since Progress seems to be yet another of those companies whose web site uses a perversion of English more commonly known as "Marketing Bullspeak":

Future-Proof
Adapts to technology and market shifts easily, for fast, powerful apps.

Does anyone still use the expression "future proof" with a straight face?

Easy to Use
Seamlessly integrates with most systems for a platform independent, cloud agnostic, end-to-end app dev solution.

Individually, each of those words has a meaning. Collectively, less so.

Cost Effective
Proven to be the most cost-effective enterprise development environment.

Yeah, let's see the objective numbers on that. Just for laughs.

I could go on, but the key point is that they do not identify the product as a DBMS (or even a development environment that has a DBMS at its core) but rather as some magical thing which is a "web solution to fuel next gen applications", because "web" and "next gen" both sound irrefutably cool without needing to be nailed down, and "fuel" suggests power and energy again without needing to be too specific.

Nonetheless, we now have some idea of what we're dealing with.

but when we use TM1 Architect>Processes , when executing the task this process takes by 1 hrs , when this process takes five minutes , we have an old TM1 server in Architect , the Task is 5 minutes.

I was originally going to try to guess what you were saying here (not that I or anyone else should have to), but re-reading it I really can't make any sense of that at all. At one point I thought you meant that the process ran in 5 minutes using Progress' own tools but took an hour when running the same query on the TM1 server, but then you start talking about Architect, so I really have no idea what you mean.

Here is what is missing:
(a) You have given no indication at all of what "this process" is supposed to do. We don't know whether you're running a select query from your database, or some sort of update, delete or insertion based on TM1 data. What, in short, is the actual SQL code that you are using for the process datasource?
(b) You have given none of the TurboIntegrator code for the process. We don't know what the process is trying to do, or how.
(c) What exactly is taking 5 minutes, and what exactly is taking an hour?

(David got in ahead of me but I'll still post the above.)

[Wim Gielis]

What the heck is a BD?

Maybe, and it's a guess just like everything is this topic is a guess, it stands for the French word "base de données", database in plan English.

[paulsimon]

I have a DataBase. ODBC ,we use PROGRESS OPENEDGE ODBC DRIVER (64 bits), but when we use TM1 Architect>Processes , when executing the task this process takes by 1 hrs , when this process takes five minutes , we have an old TM1 server in Architect , the Task is 5 minutes.

Do you mean the Task is 5 minutes if you run it in Progress and an hour when run from a TM1 TI Process, or that you have two TI Processes one that takes 5 minutes and one that takes an hour?

Is the process writing to Progress or reading from it?

If it is writing to it, the slow running might be because each INSERT is a separate transactions. It can be better to write to a file and then run a command to BULK INSERT the file into Progress, assuming that Progress has that facility.

If it is reading from Progress there is unlikely to be anything in TM1 that can be changed.

Look at the ODBC Settings. I had a similar issue with reading from AS400 iSeries where by default the query was optimised for online use so it tried to deliver the first 100 records quickly, and get the rest later, but for a TM1 read you probably want it to optimise to deliver the full record set.

Regards

Paul Simon