|
Code:
- H OPTION(*NOSHOWCPY:*NOEXPDDS:*NODEBUGIO:*SRCSTMT)
- H DATFMT(*ISO) TIMFMT(*ISO) DFTACTGRP(*NO)
- H CVTOPT(*VARCHAR:*NODATETIME)
- H THREAD(*SERIALIZE)
- H BndDir('QC2LE')
-
- // SQL.V.BIF //////////////////////////////////////////////////////
- // Compare the execution times of three methods of SQL's LIKE
- // function:
- // 1) SQL's LIKE function
- // 2) Regular expressions
- // 3) Built-in %Scan function
- //
- // The comparisons will be made for two kinds of LIKE conditions.
- // The first is a "contains" function, such as '%mytext%'
- // The second is a more complex comparison (from %SCAN's point
- // of view, anyway) as in '%my%text%'
- //
- // The general flow is as follows:
- // Do OUTER_LOOP times
- // Do SQL_test_1 INNER_LOOP times
- // Do RegEx_test_1 INNER_LOOP times
- // Do BIF_test_1 INNER_LOOP times
- // Do SQL_test_2 INNER_LOOP times
- // Do RegEx_test_2 INNER_LOOP times
- // Do BIF_test_2 INNER_LOOP times
- // EndOuterLoop
- //
- // In this manner, we can avoid performance-related questions
- // that might come up, like "such and such test performed better
- // because it was done first" or "... last" et cetera. We can
- // also determine/adjust differences in setup time (as in regcomp)
- // if desired.
- //
- // On an unknown partition size of a 520 system running minimal
- // processes, the results for 1000 iterations of 1000 inner loops
- // the results on V5R3M0 were:
- // SQL "contains" . . . : 72.201 seconds
- // RegEx "contains" . . : 6.766 seconds
- // %Scan "contains" . . : 1.020 seconds
- // SQL "complex" . . . : 72.097 seconds
- // RegEx "complex" . . : 41.286 seconds
- // %Scan "complex" . . : .987 seconds
- //
- // It is interesting that the more complex %SCAN test performed
- // slightly better than the simpler one. This has held true on
- // each test, and it's a difference that probably only a Barbara
- // might understand. It is also interesting how much more time
- // was used by the "complex" Regular Expressions approach. I
- // personally thought that test would be the clear winner.
-
- /Include LUVPGMSRC,REGEXP_H
-
- D THIS_PGM C 'SQL.V.BIF'
-
- D myProto PR ExtPgm(THIS_PGM)
- D parmOuter 15 5 Const
- D parmInner 15 5 Const
-
- D myProto PI
- D parmOuter 15 5 Const
- D parmInner 15 5 Const
-
-
-
- D MS_FACTOR C 1000000
- D ERROR_RETURN C -999999
-
- D SQLcontains PR 21 3 ExtProc('SQLcontains')
- D regExContains PR 21 3 ExtProc('regExContains')
- D BIFcontains PR 21 3 ExtProc('BIFcontains')
-
- D SQLcomplex PR 21 3 ExtProc('SQLcomplex')
- D regExComplex PR 21 3 ExtProc('regExComplex')
- D BIFcomplex PR 21 3 ExtProc('BIFcomplex')
-
- D myText S 50 Varying Inz('SearchXYabZXXX yz')
-
- // Values for a "contains" test
- D CON_SQL_CT S 20 Varying Inz('%XX y%')
- D CON_REX_CT S 20 Varying Inz('XX y')
- D CON_BIF_CT S 20 Varying Inz('XX y')
-
- // A more complex test
- D CON_SQL_CPX S 20 Varying Inz('%ch%ab%')
- D CON_REX_CPX S 20 Varying Inz('ch.*ab')
- D CON_BIF_CPX1 S 20 Varying Inz('ch')
- D CON_BIF_CPX2 S 20 Varying Inz('ab')
-
- D I S 10U 0
- D innerIters S 10U 0 Inz(2000)
- D msg S 50 Varying
- D outerIters S 10U 0 Inz(1000)
- D timingBIFcpx S Like(BIFcomplex) Inz(*Zero)
- D timingBIFct S Like(BIFcontains) Inz(*Zero)
- D timingREXcpx S Like(regExComplex) Inz(*Zero)
- D timingREXct S Like(regExContains) Inz(*Zero)
- D timingSQLcpx S Like(SQLcomplex) Inz(*Zero)
- D timingSQLct S Like(SQLcontains) Inz(*Zero)
-
- /Free
- Monitor ; // If any errors, we will bail out
- If %Parms > *Zero ; // If any parameters were passed
- outerIters = parmOuter ; // Override default outer loop count
- If %Parms > 1 ; // If two or more parms
- innerIters = parmInner ; // Override inner loop
- EndIF ;
- EndIF ;
- For I = 1 to outerIters ; // Major loop
- timingSQLct += SQLcontains() ; // SQL "%contains%"
- timingREXct += regExContains() ; // RegEx "contains"
- timingBIFct += BIFcontains() ; // %SCAN "contains"
-
- timingSQLcpx += SQLcomplex() ; // SQL "%comp%lex%"
- timingREXcpx += regExComplex() ; // RegEx "comp.*lex"
- timingBIFcpx += BIFcomplex() ; // %SCAN "comp" + "lex"
- EndFOR ;
- On-error *ALL ;
- EndMON ;
-
- // Grunt work...
-
- Msg = %Char(outerIters) + ' outer, '
- + %Char(innerIters) + ' inner loops.' ;
- Dsply Msg ;
- Msg = 'SQL contains: ' + %Char(timingSQLct) ;
- Dsply Msg ;
- Msg = 'RegEx contains: ' + %Char(timingREXct) ;
- Dsply Msg ;
- Msg = '%Scan contains: ' + %Char(timingBIFct) ;
- Dsply Msg ;
- Msg = 'SQL complex: ' + %Char(timingSQLcpx) ;
- Dsply Msg ;
- Msg = 'RegEx complex: ' + %Char(timingREXcpx) ;
- Dsply Msg ;
- Msg = '%Scan complex: ' + %Char(timingBIFcpx) ;
- Dsply Msg ;
- *INLR = *On ;
- Return ;
- /End-free
-
-
-
- P SQLcontains B
- D PI 21 3
-
- // Using SQL, determine if a LIKE '%simple%' condition exists
- // Any error (including "no match" condition) will cause this
- // procedure to return ERROR_RETURN
-
- D startTimestamp S Z Inz
- D stopTimestamp S Z Inz
- D I S 10U 0
- D isFound S N
- D rtnVal S Like(SQLcontains)
-
- /Free
-
- Monitor ;
- startTimestamp = %Timestamp() ;
- For I = 1 to innerIters ;
- Exec SQL SET :isFound = CASE WHEN :myText LIKE :CON_SQL_CT
- THEN '1'
- ELSE '0'
- End
- ;
- If isFound = *Off ;
- stopTimestamp -= %Years(5000) ; // Force an error
- Leave ;
- EndIF ;
- EndFOR ;
- stopTimestamp = %Timestamp ;
- rtnVal = %Diff(stopTimestamp: startTimestamp:*MS) / MS_FACTOR ;
- On-Error *all ;
- rtnVal = ERROR_RETURN ;
- EndMON ;
- Return rtnVal ;
-
- /End-free
-
- P SQLcontains E
-
-
-
- P regExContains B
- D PI 21 3
-
- // Using RegEx, determine if a simple condition exists
- // Any error (including "no match" condition) will cause this
- // procedure to return ERROR_RETURN
-
- D startTimestamp S Z Inz
- D stopTimestamp S Z Inz
- D matched S N Inz(*Off)
- D regEx DS LikeDS(regex_t)
- D match DS LikeDS(regmatch_t)
- D I S 10U 0
- D rc S 10I 0
- D regExErrorBuff S 512
- D rtnVal S Like(regExContains)
-
- /Free
-
- Monitor ;
- startTimestamp = %Timestamp() ;
- rc = regcomp(regEx: CON_REX_CT
- : REG_EXTENDED + REG_NOSUB
- ) ;
- If (rc <> *Zero) ;
- regError(rc: regEx
- : %Addr(regExErrorBuff): %Size(regExErrorBuff)) ;
- stopTimestamp -= %Years(5000) ; // Force an error
- EndIF ;
- For I = 1 to innerIters ;
- rc = regexec(regEx
- : myText
- : *Zero
- : match
- : *Zero
- ) ;
- matched = (rc = *Zero) ; // Match(es) found
- If matched = *Off ;
- stopTimestamp -= %Years(5000) ; // Force an error
- Leave ;
- EndIF ;
- EndFOR ;
- regFree(regEx) ;
- stopTimestamp = %Timestamp ;
- rtnVal = %Diff(stopTimestamp: startTimestamp:*MS) / MS_FACTOR ;
- On-error *ALL ;
- rtnVal = ERROR_RETURN ;
- EndMON ;
- Return rtnVal ;
-
- /End-free
-
- P regExContains E
-
-
-
- P BIFcontains B
- D PI 21 3
-
- // Using %SCAN, determine if a simple condition exists
- // Any error (including "no match" condition) will cause this
- // procedure to return ERROR_RETURN
-
- D startTimestamp S Z
- D stopTimestamp S Z
- D matched S N Inz(*Off)
- D I S 10U 0
- D pos S 10I 0
- D rtnVal S Like(BIFcontains)
-
- /Free
-
- Monitor ;
- startTimestamp = %Timestamp() ;
- For I = 1 to innerIters ;
- pos = %Scan(CON_BIF_CT: myText) ;
- matched = (pos > *Zero) ;
- If matched = *Off ;
- stopTimestamp -= %Years(5000) ; // Force an error
- Leave ;
- EndIF ;
- EndFOR ;
- stopTimestamp = %Timestamp ;
- rtnVal = %Diff(stopTimestamp: startTimestamp:*MS) / MS_FACTOR ;
- On-error *ALL ;
- rtnVal = ERROR_RETURN ;
- EndMON ;
- Return rtnVal ;
-
- /End-free
-
- P BIFcontains E
-
-
-
- P SQLcomplex B
- D PI 21 3
-
- // Using SQL, determine if a LIKE '%comp%lex%' condition exists
- // Any error (including "no match" condition) will cause this
- // procedure to return ERROR_RETURN
-
- D startTimestamp S Z
- D stopTimestamp S Z
- D I S 10U 0
- D isFound S N
- D rtnVal S Like(SQLcomplex)
-
- /Free
-
- Monitor ;
- startTimestamp = %Timestamp() ;
- For I = 1 to innerIters ;
- Exec SQL SET :isFound = CASE WHEN :myText LIKE :CON_SQL_CPX
- THEN '1'
- ELSE '0'
- End
- ;
- If isFound = *Off ;
- stopTimestamp -= %Years(5000) ; // Force an error
- Leave ;
- EndIF ;
- EndFOR ;
- stopTimestamp = %Timestamp ;
- rtnVal = %Diff(stopTimestamp: startTimestamp:*MS) / MS_FACTOR ;
- On-error *ALL ;
- rtnVal = ERROR_RETURN ;
- EndMON ;
- Return rtnVal ;
-
- /End-free
-
- P SQLcomplex E
-
-
-
- P regExComplex B
- D PI 21 3
-
- // Using RegEx, determine if a 'comp.*lex' condition exists
- // Any error (including "no match" condition) will cause this
- // procedure to return ERROR_RETURN
-
- D startTimestamp S Z
- D stopTimestamp S Z
- D matched S N Inz(*Off)
- D regEx DS LikeDS(regex_t)
- D match DS LikeDS(regmatch_t)
- D I S 10U 0
- D rc S 10I 0
- D regExErrorBuff S 512
- D rtnVal S Like(regExComplex)
-
- /Free
-
- Monitor ;
- startTimestamp = %Timestamp() ;
- rc = regcomp(regEx: CON_REX_CPX
- : REG_EXTENDED + REG_NOSUB
- ) ;
- If (rc <> *Zero) ;
- regError(rc: regEx
- : %Addr(regExErrorBuff): %Size(regExErrorBuff)) ;
- stopTimestamp -= %Years(5000) ; // Force an error
- EndIF ;
- For I = 1 to innerIters ;
- rc = regexec(regEx
- : myText
- : *Zero
- : match
- : *Zero
- ) ;
- matched = (rc = *Zero) ; // Match(es) found
- If matched = *Off ;
- stopTimestamp -= %Years(5000) ; // Force an error
- Leave ;
- EndIF ;
- EndFOR ;
- regFree(regEx) ;
- stopTimestamp = %Timestamp ;
- rtnVal = %Diff(stopTimestamp: startTimestamp:*MS) / MS_FACTOR ;
- On-error *ALL ;
- rtnVal = ERROR_RETURN ;
- EndMON ;
- Return rtnVal ;
-
- /End-free
-
- P regExComplex E
-
-
-
- P BIFcomplex B
- D PI 21 3
-
- // Using %SCAN, determine if two conditions exist in proper
- // sequence. Any error (including "no match" condition) will
- // cause this procedure to return ERROR_RETURN
-
- D startTimestamp S Z Inz
- D stopTimestamp S Z Inz
- D matched S N Inz(*Off)
- D I S 10U 0
- D pos S 10I 0
- D s1Len S 5U 0
- D rtnVal S Like(BIFcomplex)
-
- /Free
-
- Monitor ;
- startTimestamp = %Timestamp() ;
- s1Len = %Len(CON_BIF_CPX2) ;
- For I = 1 to innerIters ;
- pos = %Scan(CON_BIF_CPX1: myText) ;
- If pos > *Zero ;
- pos = %Scan(CON_BIF_CPX2: myText: pos + s1Len + 1) ;
- EndIF ;
- matched = (pos > *Zero) ;
- If matched = *Off ;
- stopTimestamp -= %Years(5000) ; // Force an error
- Leave ;
- EndIF ;
- EndFOR ;
- stopTimestamp = %Timestamp ;
- rtnVal = %Diff(stopTimestamp: startTimestamp:*MS) / MS_FACTOR ;
- On-error *ALL ;
- rtnVal = ERROR_RETURN ;
- EndMON ;
- Return rtnVal ;
-
- /End-free
-
- P BIFcomplex E
-
|
|