Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2003
    Posts
    60

    Unanswered: Insert running slow

    Is there any way we can rewrite this query to run faster?

    INSERT INTO SESSION.TEMP_CLMCNT03
    (
    SELECT TMP_CLM.CLM_ID
    ,TMP_CLM.CLM_NUM
    ,MIN(TMP_CLM.CLM_CVRG_ID)
    ,TMP_CLM.PROC_DT
    FROM SESSION.TEMP_CLMCNT02 TMP_CLM
    WHERE NOT EXISTS (
    SELECT 1
    FROM SESSION.TEMP_CLMCNT02 TMP_CLM1
    WHERE TMP_CLM1.CLM_ID = TMP_CLM.CLM_ID
    AND TMP_CLM1.CLM_NUM = TMP_CLM.CLM_NUM
    WHERE TMP_CLM1.CLM_ID = TMP_CLM.CLM_ID
    AND TMP_CLM1.CLM_NUM = TMP_CLM.CLM_NUM
    AND TMP_CLM1.PROC_DT = TMP_CLM.PROC_DT
    AND TMP_CLM1.SRC_SYS_PROD_CD = TMP_CLM.SRC_SYS_PROD_CD
    AND TMP_CLM1.RANK < TMP_CLM.RANK
    )
    GROUP BY TMP_CLM.CLM_ID
    ,TMP_CLM.CLM_NUM
    ,TMP_CLM.SRC_SYS_PROD_CD
    ,TMP_CLM.PROC_DT
    )

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650

    Re: Insert running slow

    With the limited idea I have on Global Temp Tables, I think, the primary performance limitation in V7 is that you cannot create indexes .. So, if the tables tend to be big, your SELECT Statement slows down ....

    FYI, with V8, indexing of DGTTs is possible

    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •