Uploaded image for project: 'OpenJPA'
  1. OpenJPA
  2. OPENJPA-338

Remove unnecessary CAST in SQL statement for DB2

    Details

    • Type: Improvement
    • Status: Closed
    • Priority: 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]

        Attachments

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

          Activity

            People

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

              Dates

              • Created:
                Updated:
                Resolved: