OpenJPA
  1. OpenJPA
  2. OPENJPA-338

Remove unnecessary CAST in SQL statement for DB2

    Details

    • Type: Improvement Improvement
    • Status: Closed
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: 1.0.0
    • Fix Version/s: 1.0.1, 1.1.0
    • Component/s: sql
    • Labels:
      None
    • Environment:
      Window XP, JDK 1.5

      Description

      There were unnecessary CAST added to the SQL statements that can be removed to improve the performance. The change will eliminate the CAST for the constants. For example, the original query is :
      Executing query: select d from DogABS d where LENGTH(d.name) < 10

      The current generated SQL statement:
      executing prepstmnt 1335250838 SELECT t0.id2, t0.datastoreid, t0.domestic, t0.name, t0.price, t0.total FROM DOGABS t0 WHERE (CAST(LENGTH(CAST(t0.name AS VARCHAR(1000))) AS BIGINT) < CAST(? AS BIGINT)) [params=(long) 10]

      After the changes:
      executing prepstmnt 1749706826 SELECT t0.id2, t0.datastoreid, t0.domestic, t0.name, t0.price, t0.total FROM DOGABS t0 WHERE (LENGTH(t0.name) < ?) [params=(long) 10]

      1. OPENJPA-338.patch
        3 kB
        Teresa Kan
      2. OPENJPA338-comparsion.txt
        7 kB
        Teresa Kan

        Activity

        Hide
        Kevin Sutter added a comment -

        Resolved via svn revision 570240.

        Show
        Kevin Sutter added a comment - Resolved via svn revision 570240.
        Hide
        Teresa Kan added a comment -

        Attach the comparsion file between original generated SQL statements and the converted SQL statements.

        Show
        Teresa Kan added a comment - Attach the comparsion file between original generated SQL statements and the converted SQL statements.
        Hide
        Teresa Kan added a comment -

        Attach the patch and end result conversion for DB2 in various SQL functions.

        Show
        Teresa Kan added a comment - Attach the patch and end result conversion for DB2 in various SQL functions.

          People

          • Assignee:
            Teresa Kan
            Reporter:
            Teresa Kan
          • Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved:

              Development