TD-4195: TD 4.2 Slow parameter passing

David Hill · Post by **David Hill** » 05 Dec 2007, 04:08

We have recently converted a large application from TD 3.14 to TD4.2 . There is a very noticable difference in execution speed. We have tracked this down to parameter passing in function calls. If the data type of the paramter is EXACTLY the same, all is OK. If the function being called has the parameter defined at a different level (ie the calling routines parameter is derived from the base functions parameter) there is a significant time delay in starting the execution of the function call. When this is in a loop, for example retrieving a set of data from a table, the overall time difference is very significant. 1.5 seconds in 3.14, 14 seconds in 4.2 :-(

We are running on W2000 server and XP over Oracle 10g using oracle 9 client. The client is The Motor Accidents Board of Tasmania (MAIB) and the application developed by Prologic (IT consultants)

TDWiki YouTube Channel · Post by **Dave Rabelink** » 05 Dec 2007, 10:27

Thanks for this report. We are also porting to TD42 and and investigating if there are issues in comparisson

to TD31. I did not notice this yet, but I will test specifically on this now.

Performance has a great importance to us here, so if there is really an issue here, it should be fixed !

Dave

Jean-Marc Gemperle · Post by **Jean-Marc Gemperle** » 10 Dec 2007, 18:10

Hi,

I wanted to check that issue with TD 3.1 vs TD 4.2 and of course TD 5.1 but I'm affraid I did not found a way to show this issue with this information alone
the calling routines parameter is derived from the base functions parameter I try to do that, but obviously I can't see an obvious difference with TD 3.1 vs TD 4.2 neither 5.1 so my testcase must be wrong.

I like to focus only on function call performance with UDV parameter as it seems to be the issue. Can you provide us a testcase. I attach mine TD 3.1 but it does not show your issue. Thanks for additional details you can provide or correction you can put in mine to show the problem.

Jean-Marc

David Hill · Post by **David Hill** » 11 Dec 2007, 00:57

Thankyou for looking into this Jean-Marc

I have attached some timings and code snippets that illustrate the issue we are having. These are taken from the full app, I have not got a small test case but hopefully this will illustrate the issue more clearly.

If there is more material that I can provide just ask.

Kind regards, David Hill

David Hill · Post by **David Hill** » 11 Dec 2007, 01:51

Attached is the full merged TD4.2 app which shows all the class definitions and derivations in the previous post.

It is quite large and double compressed (winrar & zip) to get under the 2mB limit here.

HTH

DH

David Hill · Post by **David Hill** » 19 Dec 2007, 03:15

Here is a small app illustrating the issue. Based on your test case.

In my environment, the matched parameter calls take 0.35 seconds and the derived one takes 4.5 !

Hope this helps track this down. Sorry for the delay with the sample, it seemed so serious I assumed there would be plenty of other examples.

Kind regards,

David Hill

David Hill · Post by **David Hill** » 19 Dec 2007, 04:29

Sorry. . . the timer was not reset in the app and times are now 0.3 secs with matching parameters and 1.1 secs with derived parameters.

Running this example on 3.1 gives a similar time for the matched call and 0.9 secs for the derived which does not seem to explain the full story of why our app is so much slower in some sections changing from 3.1 to 4.2 although the sample does not exactly match the application where the classes have many more variables and functions. But it does still seem to take a LOT longer with the derived parameters. I suspect 1.5 with its less strict parameter typing may be have been better and will try that when time is available.

DH

Jean-Marc Gemperle · Post by **Jean-Marc Gemperle** » 20 Dec 2007, 18:15

Hi David,

I just wanted to let you know that I will upate you asap...and I haven't forget about it.
Hope you don't find me to slow

JM

Jean-Marc Gemperle · Post by **Jean-Marc Gemperle** » 03 Jan 2008, 13:26

Hi,

Here it is

TD-4195
TD 5.1, TD 4.2 performance issue versus TD 3.10 when passing UDV to function call

Attached testcase

Cheers
JM

David Hill · Post by **David Hill** » 07 Jan 2008, 01:29

Thanks for looking into this Jean-Marc.

I did try this on 1.51 and found that it runs significantly faster with NO variation between between using base class or derived parameters. If you can bring 5.1 up to the speed of 1.51 now that would be a bonus.

The client has decided not to implement 4.2 at this stage due to the speed issue which means I may have to retro fit a series of software revisions back into 3.1

Cheers,
DH

Jean-Marc Gemperle · Post by **Jean-Marc Gemperle** » 07 Jan 2008, 19:49

Hi David,

Yes performance problem started since TD 2000 and got improved mainly in TD 3.1 AFAIK to be close to what TD 1.51 was..there still might be some areas of concern like you showed us. TD 5.1 shows indeed some performance problem in this specific case even VS TD 4.2.

JM

Mike Vandine · Post by **Mike Vandine** » 11 Aug 2008, 02:20

Just to complete this thread, defect TD-4195 was fixed with TD 5.1 SP2 which is available on the Unify Support website. See:

This fix is also included in SP3 and the next Team Developer 5.1 Service Pack.

Team Developer SQLWindows Community Forum

TD-4195: TD 4.2 Slow parameter passing

TD-4195: TD 4.2 Slow parameter passing

Re: TD-4195: TD 4.2 Slow parameter passing

Re: TD-4195: TD 4.2 Slow parameter passing

Re: TD-4195: TD 4.2 Slow parameter passing

Re: TD-4195: TD 4.2 Slow parameter passing

Re: TD-4195: TD 4.2 Slow parameter passing

Re: TD-4195: TD 4.2 Slow parameter passing

Re: TD-4195: TD 4.2 Slow parameter passing

Re: TD-4195: TD 4.2 Slow parameter passing

Re: TD-4195: TD 4.2 Slow parameter passing

Re: TD-4195: TD 4.2 Slow parameter passing

Re: TD-4195: TD 4.2 Slow parameter passing

Who is online