EMP608.3-37201 :: Performance issue with OdrPrepareProc

Post found bugs and possible workarounds.
drasmussen

EMP608.3-37201 :: Performance issue with OdrPrepareProc

Post by drasmussen » 05 Feb 2014, 18:52

I found a performance issue with OdrPrepareProc.
Since EMP608.3-37201 for TD60SP8 the call of OdrPrepareProc takes a lot more time than before.

Has anyone else this EMP in use and has possibly seen something similar?

I found the crucial point of the delay through iterative search by stepwise commenting out of the involved codelines.
Because there is a database access, I unfortunately cannot just build a simple test case.
But the effect can be very easily reproduced by de-installation and re-installation of the EMP.
And from my point of view it is one hundred percent the call of OdrPrepareProc and not the subsequent call of SalTblPopulate, because the ommission of the query execution has no effect.

Any help or hint is appreciated!

Best regards
Dirk

drasmussen

Re: EMP608.3-37201 :: Performance issue with OdrPrepareProc

Post by drasmussen » 06 Feb 2014, 15:08

... a further addendum:

The same issue occurs with EMP608.5-38916 !

Jean-Marc Gemperle

Re: EMP608.3-37201 :: Performance issue with OdrPrepareProc

Post by Jean-Marc Gemperle » 06 Feb 2014, 19:19

Hi
OdrPrepareProc takes a lot more time than before.
Do you have any figures? like double time or ?
OdrPrepareProc
This allows execution of SP using ODBC, now what DB are we talking about? I assume SQLSERVER, but it could be DB2, MYSQL etc.
Also knowing a bit more about the SP you try to invoke would have been nice.
But the effect can be very easily reproduced by de-installation and re-installation of the EMP.
I have no doubts you see an issue
Because there is a database access, I unfortunately cannot just build a simple test case.
Why would that be? Generally reproducing DB issues and performance can be simple, it might be specific to data, but that should never be assumed. In contrast RANDON crash due to heap corruption are very hard to repro.
Anyway I DID try....and YES I sees something BUT BELIEVE IT OR NOT the OPOSITE of what your are seeing!
For me TD 6.0 SP8 RTM is twice as slow as TD 6.0 SP8 EMP608.5 !
Attached TESTCASE + VIDEO showing that on my environment
-What I do is create 100 rows of DATA
-call OdrPrepareProc (100 times) fetching these 100 rows
-and write them in a TABLE WINDOW
The video show along with process explorer that using TD 6.0 SP8 EMP608.5 is twice as fast

Please test that testcase, confirm or infirm what I see
do any modification you wish in it to show us your problem if you do not confirm what I see with my testcase.

Well 2M only is of attachment...sad IMO.
So here on dropbox for the video.
https://www.dropbox.com/s/vou4yyidv9v2enp/TESTCASE.zip

JM
You do not have the required permissions to view the files attached to this post.

drasmussen

Re: EMP608.3-37201 :: Performance issue with OdrPrepareProc

Post by drasmussen » 07 Feb 2014, 12:40

First of all thank you for the quick and detailed response!

I am going to put together more information about the relevant point in the program. Perhaps with an excerpt of the relevant lines of code.

But the loss of performance at that location is striking indeed and not reasonable. And as I said, not the execution but the prepare of the database query takes so long. The database we use is SQLSERVER and the StoredProcedure is specially optimized and in the other case - without the EMP - also very fast. It is an "instant search" (as we call it), in which after each character entry in a data field a corresponding results list (TOP 50) is selected in the database. With the EMP you can not type in the data field in normal speed, because each database-select then takes 1-2 seconds.

Your testcase I will test also in our runtime environment and as soon as possible I'll reply here again.

drasmussen

Re: EMP608.3-37201 :: Performance issue with OdrPrepareProc

Post by drasmussen » 10 Feb 2014, 22:09

As promised, I have done more intensive troubleshooting!

Unfortunately with your testcase I was not able to reproduce the effect. I then tried, to align your testcase largely with the certain constellation in our application (see the attached source code). But also this effort did not let me reproduce the mentioned effect. :(

Therefore I have tried again to localize the responsible code-line in our application. I have been able to work out the following result so far:
The anomaly can be switched on or off by alternativly activating one of the following two QrdPrepareProc code lines:

Code: Select all

Function: PopulateGaesteQSearch
   Description:
   Returns
   Parameters
      String: p_sName
      String: p_sVorname
   Static Variables
   Local variables
      String: sName
      String: sVorname
      !
      String: sBisName
      String: sBisVorname
      !
      String: sPassName
      String: sPassVorname
      !
      String: sBisPassName
      String: sBisPassVorname
      !
      Boolean: doPopulate
      Number: nRows
      Number: nMaxRows
      Number: nContextRow
      Number: nInMsgBuffSize
      !
      Number: nNumCol1
      Number: nNumCol2
      String: sStrCol1
      String: sStrCol2
      String: sStrCol3
      String: sStrCol4
      String: sStrCol5
      String: sStrCol6
   Actions
      If gv_bGaesteQSearch and bGaesteQSearchVisible
         Call SalTblReset( ctwGAESTEQSEARCH )
         !
         Set sName = SalStrTrimX( p_sName )
         Set sVorname = SalStrTrimX( p_sVorname )
         !
         Set nRows = 0
         Set nMaxRows = gv_nQSearchMaxRows + 1
         Set doPopulate = FALSE
         !
         If SalStrLength( sName ) > 0 or SalStrLength( sVorname ) > 0
            !
            Call HourGlass( TRUE )
            !
            Call SqlContextClear( hSqlSP )
            Set nInMsgBuffSize = SqlInMessage
            Call SqlSetInMessage( hSqlSP, IN_MESSAGE_BUFFER )
            !
            Set sBisName = SalStrLeftX( sName, 40 - SalStrLength( QUERY_FILTERSTRING_SUFFIX ) ) || QUERY_FILTERSTRING_SUFFIX
            Set sBisVorname = SalStrLeftX( sVorname, 30 - SalStrLength( QUERY_FILTERSTRING_SUFFIX ) ) || QUERY_FILTERSTRING_SUFFIX
            !
            Set sPassName = PassNormalize( sName, TRUE )
            Set sPassVorname = PassNormalize( sVorname, TRUE )
            !
            Set sBisPassName = SalStrLeftX( sPassName, 40 - SalStrLength( QUERY_FILTERSTRING_SUFFIX ) ) || QUERY_FILTERSTRING_SUFFIX
            Set sBisPassVorname = SalStrLeftX( sPassVorname, 30 - SalStrLength( QUERY_FILTERSTRING_SUFFIX ) ) || QUERY_FILTERSTRING_SUFFIX
            !
            Call Logger.Debug( 'gaesteqsearch(1): 
                   sName=[' || sName || '],
                   sBisName=[' || sBisName || '],
                   sVorname=[' || sVorname || '],
                   sBisVorname=[' || sBisVorname || '],
                   sPassName=[' || sPassName || '],
                   sBisPassName=[' || sBisPassName || '],
                   sPassVorname=[' || sPassVorname || '],
                   sBisPassVorname=[' || sBisPassVorname || ']' )
            !
            If nDBMS = DBV_BRAND_INFORMIX
                  or nDBMS = DBV_BRAND_INFORMIXONLINE
               Call SalMessageBeep( -1 )
               Call SalMessageBox( 'Sorry, not yet implemented !', 'Error', MB_ApplModal | MB_IconStop | MB_Ok )
            Else If nDBMS = DBV_BRAND_SQLSERVER
               If gv_bQSearchVolltextIdx
                  If OdrPrepareProc( hSqlSP,
                           '{call ' || gv_sQSearchStoredProc || '(:sName, :sVorname, :sPassName, :sPassVorname)}',
                           ':fwGAST.ctwGAESTEQSEARCH.colGAST_NR,' ||
                           ':fwGAST.ctwGAESTEQSEARCH.colFAMNAME,' ||
                           ':fwGAST.ctwGAESTEQSEARCH.colVORNAME,' ||
                           ':fwGAST.ctwGAESTEQSEARCH.colGEB_DATUM_STR,' ||
                           ':fwGAST.ctwGAESTEQSEARCH.colJ_ALTER,' ||
                           ':fwGAST.ctwGAESTEQSEARCH.colGESPERRT,' ||
                           ':fwGAST.ctwGAESTEQSEARCH.colZSD' )
                     Set doPopulate = TRUE
               Else
                  ! ------------------------------------------------------------------------------------------------
                  ! poor performance (gui-feedback)
                  ! If OdrPrepareProc( hSqlSP,
                           '{call ' || gv_sQSearchStoredProc || '(:sPassName, :sBisPassName, :sPassVorname, :sBisPassVorname)}',
                           ':fwGAST.ctwGAESTEQSEARCH.colGAST_NR,' ||
                           ':fwGAST.ctwGAESTEQSEARCH.colFAMNAME,' ||
                           ':fwGAST.ctwGAESTEQSEARCH.colVORNAME,' ||
                           ':fwGAST.ctwGAESTEQSEARCH.colGEB_DATUM_STR,' ||
                           ':fwGAST.ctwGAESTEQSEARCH.colJ_ALTER,' ||
                           ':fwGAST.ctwGAESTEQSEARCH.colGESPERRT,' ||
                           ':fwGAST.ctwGAESTEQSEARCH.colZSD,' ||
                           ':fwGAST.ctwGAESTEQSEARCH.colGEBNAME' )
                     Set doPopulate = TRUE
                  ! ------------------------------------------------------------------------------------------

                  ! ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
                  ! normal performance (gui-feedback)
                  If OdrPrepareProc( hSqlSP,
                           '{call ' || gv_sQSearchStoredProc || '(:sPassName, :sBisPassName, :sPassVorname, :sBisPassVorname)}',
                           ':fwGAST.ctwGAESTEQSEARCH.colGAST_NR,' ||
                           ':sStrCol1,' ||
                           ':sStrCol2,' ||
                           ':sStrCol3,' ||
                           ':nNumCol2,' ||
                           ':sStrCol4,' ||
                           ':sStrCol5,' ||
                           ':sStrCol6' )
                     Set doPopulate = TRUE
                  ! ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
            !
            If doPopulate
               Call SalTblPopulate( ctwGAESTEQSEARCH, hSqlSP, '', TBL_FillAll )
               Call SalTblSortRows( ctwGAESTEQSEARCH, SalTblQueryColumnID( ctwGAESTEQSEARCH.colSORT ), TBL_SortIncreasing )
               Set nRows = TWRowCount( ctwGAESTEQSEARCH )
               If nRows > gv_nQSearchMaxRows
                  Call SalColorSet( ctwGAESTEQSEARCH, COLOR_IndexWindow, QSEARCH_INCOMPLETE_COLOR )
               Else
                  Call SalColorSet( ctwGAESTEQSEARCH, COLOR_IndexWindow, COLOR_White )
            !
            Call SqlSetInMessage( hSqlSP, nInMsgBuffSize )
            !
            Call HourGlass( FALSE )
            !
         Else
            Call SalColorSet( ctwGAESTEQSEARCH, COLOR_IndexWindow, COLOR_White )
Please note that I have neutralized the body of the underlying StoredProcedure for that test, i.e. that the StoredProcedure now only returns one line with constant values only. I did this to exclude possible side effects from the database!
You do not have the required permissions to view the files attached to this post.

Jean-Marc Gemperle

Re: EMP608.3-37201 :: Performance issue with OdrPrepareProc

Post by Jean-Marc Gemperle » 03 Mar 2014, 12:29

Hi

Thanks for having a look at the testcase and for modifying so for us to see the problem.
Unfortunately I attach here again a video, I see exactly what I used to see previously...
That is 6.0 SP8 RTM much slower (# 23sec) fetching the resultset in a TBLW while 6.0 SP8 EMP608.5 (# 3 sec )
Can you please explain / shows whats the result on your side either snapshot or video because for me clearly 6.0SP8 EMP608.5 is better than the RTM version unless I miss-understand something with the modified testcase
You support provider ( not gupta ) mentioned you went to 6.0 SP8 EMP608.5 for an issue related to ACTIVEX.
Seeing performance issue can you try this simple test that is use SQLODB32.DLL / gtlsi60.dll / odbsal32.dll of the version 6.0 SP8 RTM ( no EMP ) in place of the one of EMP608.5, do you still see the problem. If no at least we know it is in the router....
Now I don't understand fully what is being reported here, other than showing indeed 6.0 SP8 RTM does have a performance issue compared to the EMP608.5 basically I see the reverse than you.

Please contact you support provider ( since you do have one and clarify all this with them )

JM
You do not have the required permissions to view the files attached to this post.

drasmussen

Re: EMP608.3-37201 :: Performance issue with OdrPrepareProc

Post by drasmussen » 07 Mar 2014, 08:08

jmgemperle wrote:Hi

Thanks for having a look at the testcase and for modifying so for us to see the problem.
Unfortunately I attach here again a video, I see exactly what I used to see previously...
That is 6.0 SP8 RTM much slower (# 23sec) fetching the resultset in a TBLW while 6.0 SP8 EMP608.5 (# 3 sec )
Can you please explain / shows whats the result on your side either snapshot or video because for me clearly 6.0SP8 EMP608.5 is better than the RTM version unless I miss-understand something with the modified testcase
...
In response, I just repeat again my last reply:
drasmussen wrote:...
Unfortunately with your testcase I was not able to reproduce the effect. I then tried, to align your testcase largely with the certain constellation in our application (see the attached source code). But also this effort did not let me reproduce the mentioned effect. :(

Therefore I have tried again to localize the responsible code-line in our application. I have been able to work out the following result so far:
The anomaly can be switched on or off by alternativly activating one of the following two QrdPrepareProc code lines:
...
Have you ever looked at my lines of code, that I have quoted here from our application?
And as I said, the body of the StoredProcedure I have already neutralized / emptied!
The point is that apparently the number or composition of the bind variables in OdrPrepareProc statement affects the performance.

Jean-Marc Gemperle

Re: EMP608.3-37201 :: Performance issue with OdrPrepareProc

Post by Jean-Marc Gemperle » 07 Mar 2014, 19:12

Hello ...
Have you ever looked at my lines of code, that I have quoted here from our application?
Excuse me to focus on what we call a testcase which generally serves the purpose to reproduce a problem...
You sending a modification of the testcase I PROVIDED showed me indeed your modifications.
Of course I saw your changes, but please understand we like to focus on what can be reproduced here...
Seeing performance issue can you try this simple test that is use SQLODB32.DLL / gtlsi60.dll / odbsal32.dll of the version 6.0 SP8 RTM ( no EMP ) in place of the one of EMP608.5, do you still see the problem. If not at least we know it is in the router....
What about this above, did you ever try ? is the problem in the router / odbsal32.dll ?
Understand we try to help here.

Thanks
JM

drasmussen

Re: EMP608.3-37201 :: Performance issue with OdrPrepareProc

Post by drasmussen » 18 Mar 2014, 15:52

Hello!

Please excuse my late reply, but I just found some time to investigate the issue further.

As requested, I tried in the TD60SP8 + EMP runtime environment, the original DLL files (SQLODB32.DLL, gtlsi60.dll) from TD60SP8. (By the way: The odbsal32.dll is identical in both releases!) However this didn't solve the problem.
Then I restored again the complete EMP environment and started to replace - step by step - the other EMP DLL files by their respective original: And after the restoring of the original cdlli60.dll, the mentioned anomaly has gone! Accordingly, the error is probably to be sought in the main TD DLL ... :shock:

Kind regards
Dirk

Return to “Bug Reports”

Who is online

Users browsing this forum: [Ccbot] and 1 guest