Question Performance loss due to Temp-Tables

[Rajat Sharma]

Thanks for replying Andre, Good to see that from 11.0 version temp-table are initiated only when we are actually using them.

@ Tom, thanks for your inputs. As of now I understood the difference between Temp-Table/Work Table with respect to memory.

There is one more relevant knowledge base article (please consider below link for this) which says that: In Openedge 11.4, there is performance lack when we use FIND/CAN-FIND on temp-table that hasn't been initiated.

http://knowledgebase.progress.com/articles/Article/000054732?q=-nottdelay&l=en_US&fs=Search&pn=1

Please check.

Regards!
Rajat.

 

[Rob Fitzpatrick]

I don't believe you've stated which version of OpenEdge you're using. Are you on 11.4?

Please check.

Please check what? The article is pretty clear. If you can measure that the bug impacts your application performance, use the workaround until a fix is available. If you can't measure the impact, it's a non-issue.

 

[tamhas]

Note too that you really have no business defining a temp-table you aren't going to use. Yes, that means that you need to encapsulate your logic properly, but that is a good goal always anyway.

 

[Rajat Sharma]

Thanks for replying Rob!

Yes, we are using 11.4 but didn't notice anything relevant yet.

I agree, that if this bugs impacts my application then i could use -nottdelay startup parameter. I wrote "Please Check" because i was wondering that version 11.3 and 11.4 both are using delay initialization concept for temp-tables than how could it be specific to 11.4. I was seeking everyone's supervision on this. As suggested, If i found any performance loss regarding this then i would use -nottdelay utility.

Regards!
Rajat.

 

[TheMadDBA]

Unless you are getting the errors from the KB while running your code there is no need for you to use -nottdelay.

What we have been trying to say is you probably have other performance issues besides the overhead of Progress doing the dirty work of making/dropping those internal temp-table structures. So focus on that instead.

 

[Rajat Sharma]

@TheMadDBA

I agree, because i didn't find any specific information that we are passing (from calling to called external procedure) for temp-table so i have replaced that external procedure (calc.p, i mentioned few post back) with persistent procedure. For that i had to take care PP's handles/memory clearance but that has reduced some time of execution.

Regards!
Rajat.

 

[TheMadDBA]

Look into using the progress profiler and/or log-manager to get detailed tracings on where your code is spending the time. That is always a much better use of your time than speculating on the causes.

You can guess right or wrong... knowing which lines of code are taking the most time makes life a lot easier. It also makes life a lot easier for people trying to help you out

 

[Rajat Sharma]

I would start using/learning Profiler. Thanks for your mentorship on this.

Regards!
Rajat.

 

[Rob Fitzpatrick]

Some info on the profiler:
http://pugchallenge.org/2012PPT/App_Profiler_PUGC_US_2012.ppt
http://dbappraise.com/ppt/profiler.pptx

Also the help file that comes with the profiling viewer tool contains a lot of useful information.

 

[TheMadDBA]

It is about time that PSC just makes that part of the standard toolkit... along with temp-table/table monitoring. Simple debugging right out of the box is one my biggest gripes about Progress.

 

[Rob Fitzpatrick]

It is about time that PSC just makes that part of the standard toolkit... along with temp-table/table monitoring. Simple debugging right out of the box is one my biggest gripes about Progress.

Agreed. There are two "ideas" on their site requesting this.

https://community.progress.com/comm...ported_profiling_data_viewer_application.aspx
https://community.progress.com/comm.../i/openedge_116_community_input/profiler.aspx

 

[TheMadDBA]

Yeah... I had already voted for the 11.6 one... voted for the general one just now. Debugging in the 4GL is a joke compared to what is available in .NET or PL/SQL. The fact that a developer can't just click a button and/or use a session option to get complete information about what happened is a sad joke.

Same thing goes for the database side really... promon and VSTs are nice, but once you have used some of the Oracle tools to see what is happening inside a database it is very hard to go back

I guess the plus side is it keeps us employed/busy fixing a lot of bad code/installs

 

[Rob Fitzpatrick]

Some 11.x-specific client startup parameters can help when looking into possible application issues related to temp-tables. In the Startup Command and Parameter Reference manual, look into -tttablerangesize and -ttindexrangesize. In the Debugging and Troubleshooting manual, look at log entry types and in particular TTStats. Properly configured, you will see your temp-table CRUD stats (from the temp-table VSTs) written to the client log (you must use -clientlog).

There are also classes you can use to write your own code to read the temp-table VSTs (Progress.Database.VSTTableId and Progress.Database.TempTableinfo) but I can't help you with that. Read the ABL Reference manual for more info.

 

[tamhas]

Bottom line, there *are* actually a bunch of tools available, even if some of them are not documented all that well. But, the first step is to get a handle on the big picture ... not just on the end to end for the whole task, but which pieces are using the bulk of the time. Then you can drill down on whether that is necessary for the task or whether there is some alternate which would make things much faster.

 

[Cringer]

It is about time that PSC just makes that part of the standard toolkit... along with temp-table/table monitoring. Simple debugging right out of the box is one my biggest gripes about Progress.

I believe this is something that will be looked at for 11.6 judging by the discussions at the PUG.

 

[Rob Fitzpatrick]

I believe this is something that will be looked at for 11.6 judging by the discussions at the PUG.

I'll believe it when I see it.

 

[TheMadDBA]

I agree that with the addition of TT monitoring quite a bit of what you need to debug an app is in place. It just isn't very user friendly and requires you to look in way too many places. People that are new to Progress expect to be able to just click "Run with Debug" or something similar and get all of the information you need.

It would be nice if Progress had something similar where you could establish break points (or hit a key combo) and get the current values of all your variables, temp-tables, objects as well as time spent executing statements and records read,created,updated regardless if they are temp-tables or db tables.

No more messing with startup parameters and external apps (profiler), parsing log files and writing your VST queries. In 2014 there is no reason for us to have to manually deal with so much of this and people coming from other languages certainly expect a lot of that to be there.

 

[tamhas]

There is a debugger in PDSOE ...

 

[TheMadDBA]

If you want to call it that

 

[GregTomkins]

Just gonna throw out there that - possibly the most awesome piece of software EVER WRITTEN, at least as it affects my daily life ... has to be ... the Chrome/Firefox debugger. If PSC could pull off even 10% of its awesomeness ... well, wouldn't that be something!

However for a start, I seriously think I'd save dozens of wasted hours per year if they could just added file name/line number to error messages (everywhere, not just in the UI and AppServer logs).