none
ayuda con query, Select funciona Ok pero el insert no funciona. RRS feed

  • Pregunta

  • Hola A todos, necesito una ayuda urgente.

    Estamos revisando modulo de nuestro ERp en produccion y resulta que hay un proceso que que no anda.

    El proceso se queda "pegado" haciendo el Insert.

    No tengo bloqueos, cree una nueva tabla (fisica para el insert) , cree una #tenmporal con creación en tiempo de ejecución. Simplemente no anda

    la sentencia (Responde en menos de 1 segundo para extraer 1170 registros.)

     SELECT
          numero proceso ,
          SRBT_ORDER_VW.BUSINESS_UNIT,
          SRBT_ORDER_VW.SOLD_TO_CUST_ID,
          SRBT_ORDER_VW.INVOICE,
          SRBT_ORDER_VW.PRODUCT_ID,
          SRBT_ORDER_VW.UNIT_OF_MEASURE,
          SRBT_ORDER_VW.NET_EXTENDED_BSE,
          SRBT_ORDER_VW.QTY_SCHEDULED,
          SRBT_CUST_TMP.END_DATE,
          0, 
          SRBT_CUST_TMP.SRBT_ID,
          SRBT_ORDER_VW.CURRENCY_CD_BASE,
          SRBT_ORDER_VW.RT_TYPE 
       FROM
          PS_AJ_SRBT_ORDF_VW SRBT_ORDER_VW,
          PS_SRBT_CUST_TMP SRBT_CUST_TMP,
          PS_SRBT_PROD_TMP SRBT_PROD_TMP 
       WHERE
          SRBT_CUST_TMP.PROCESS_INSTANCE = 98657942 
          AND SRBT_PROD_TMP.PROCESS_INSTANCE = 98657942 
          AND SRBT_CUST_TMP.BUSINESS_UNIT = SRBT_ORDER_VW.BUSINESS_UNIT 
          AND SRBT_ORDER_VW.SOLD_TO_CUST_ID = SRBT_CUST_TMP.CUST_ID 
          AND SRBT_ORDER_VW.PRODUCT_ID >= SRBT_PROD_TMP.PRODUCT_ID 
          AND SRBT_ORDER_VW.PRODUCT_ID <= SRBT_PROD_TMP.PRODUCT_ID 
          AND SRBT_CUST_TMP.PRC_BREAK_DATE = 'I' 
          AND SRBT_CUST_TMP.RBT_PNLTY_ORD_TYPE = 'I' 
          AND SRBT_ORDER_VW.ORDER_DATE >= SRBT_CUST_TMP.STARTDATETIME 
          AND SRBT_ORDER_VW.ORDER_DATE <= SRBT_CUST_TMP.ENDDATETIME 
          AND SRBT_CUST_TMP.COMPARE_TO_CRIT <> 'BLAH' ) as t
    OPTION (FAST 1, LOOP       JOIN, HASH GROUP)

    La sentrencia con el Insert 

    INSERT INTO
       PS_SRBT_ORDER_TMP3 (PROCESS_INSTANCE, BUSINESS_UNIT, SOLD_TO_CUST_ID, INVOICE, PRODUCT_ID, UNIT_OF_MEASURE, 
       NET_EXTENDED_BSE, QTY_SCHEDULED, END_DATE, STD_DISCOUNT, SRBT_ID, CURRENCY_CD_BASE, RT_TYPE) 
       SELECT
          numero proceso ,
          SRBT_ORDER_VW.BUSINESS_UNIT,
          SRBT_ORDER_VW.SOLD_TO_CUST_ID,
          SRBT_ORDER_VW.INVOICE,
          SRBT_ORDER_VW.PRODUCT_ID,
          SRBT_ORDER_VW.UNIT_OF_MEASURE,
          SRBT_ORDER_VW.NET_EXTENDED_BSE,
          SRBT_ORDER_VW.QTY_SCHEDULED,
          SRBT_CUST_TMP.END_DATE,
          0 ,
          SRBT_CUST_TMP.SRBT_ID,
          SRBT_ORDER_VW.CURRENCY_CD_BASE,
          SRBT_ORDER_VW.RT_TYPE 
       FROM
          PS_AJ_SRBT_ORDF_VW SRBT_ORDER_VW,
          PS_SRBT_CUST_TMP SRBT_CUST_TMP,
          PS_SRBT_PROD_TMP SRBT_PROD_TMP 
       WHERE
          SRBT_CUST_TMP.PROCESS_INSTANCE = 98657942 
          AND SRBT_PROD_TMP.PROCESS_INSTANCE = 98657942 
          AND SRBT_CUST_TMP.BUSINESS_UNIT = SRBT_ORDER_VW.BUSINESS_UNIT 
          AND SRBT_ORDER_VW.SOLD_TO_CUST_ID = SRBT_CUST_TMP.CUST_ID 
          AND SRBT_ORDER_VW.PRODUCT_ID >= SRBT_PROD_TMP.PRODUCT_ID 
          AND SRBT_ORDER_VW.PRODUCT_ID <= SRBT_PROD_TMP.PRODUCT_ID 
          AND SRBT_CUST_TMP.PRC_BREAK_DATE = 'I' 
          AND SRBT_CUST_TMP.RBT_PNLTY_ORD_TYPE = 'I' 
          AND SRBT_ORDER_VW.ORDER_DATE >= SRBT_CUST_TMP.STARTDATETIME 
          AND SRBT_ORDER_VW.ORDER_DATE <= SRBT_CUST_TMP.ENDDATETIME 
          AND SRBT_CUST_TMP.COMPARE_TO_CRIT <> 'BLAH' 
    OPTION (FAST 1, LOOP       JOIN, HASH GROUP)

    La sentencia con #temp en tiempo de ejcución. --> tampoco Trabaja.

    SELECT 
          numero proceso ,
          SRBT_ORDER_VW.BUSINESS_UNIT,
          SRBT_ORDER_VW.SOLD_TO_CUST_ID,
          SRBT_ORDER_VW.INVOICE,
          SRBT_ORDER_VW.PRODUCT_ID,
          SRBT_ORDER_VW.UNIT_OF_MEASURE,
          SRBT_ORDER_VW.NET_EXTENDED_BSE,
          SRBT_ORDER_VW.QTY_SCHEDULED,
          SRBT_CUST_TMP.END_DATE,
          0,
          SRBT_CUST_TMP.SRBT_ID,
          SRBT_ORDER_VW.CURRENCY_CD_BASE,
          SRBT_ORDER_VW.RT_TYPE 

      INTO  #temporal 
       FROM
          PS_AJ_SRBT_ORDF_VW SRBT_ORDER_VW,
          PS_SRBT_CUST_TMP SRBT_CUST_TMP,
          PS_SRBT_PROD_TMP SRBT_PROD_TMP 
       WHERE
          SRBT_CUST_TMP.PROCESS_INSTANCE = 98657942 
          AND SRBT_PROD_TMP.PROCESS_INSTANCE = 98657942 
          AND SRBT_CUST_TMP.BUSINESS_UNIT = SRBT_ORDER_VW.BUSINESS_UNIT 
          AND SRBT_ORDER_VW.SOLD_TO_CUST_ID = SRBT_CUST_TMP.CUST_ID 
          AND SRBT_ORDER_VW.PRODUCT_ID >= SRBT_PROD_TMP.PRODUCT_ID 
          AND SRBT_ORDER_VW.PRODUCT_ID <= SRBT_PROD_TMP.PRODUCT_ID 
          AND SRBT_CUST_TMP.PRC_BREAK_DATE = 'I' 
          AND SRBT_CUST_TMP.RBT_PNLTY_ORD_TYPE = 'I' 
          AND SRBT_ORDER_VW.ORDER_DATE >= SRBT_CUST_TMP.STARTDATETIME 
          AND SRBT_ORDER_VW.ORDER_DATE <= SRBT_CUST_TMP.ENDDATETIME 
          AND SRBT_CUST_TMP.COMPARE_TO_CRIT <> 'BLAH' 
    OPTION (FAST 1, LOOP       JOIN, HASH GROUP)

    Tiene alguien alguna idea de donde puedo revisar? Por que esta bien extraño-

    Gracias

    carlos A-


    If the answer was helpful, please mark it as useful. Si la respuesta le ayudó, por favor márquela como útil.

    lunes, 3 de septiembre de 2018 18:15

Respuestas