Consulta para 2005

11/01/2008 - 20:31 por Carlos A. | Informe spam
Hola a todos, tengo el siguiente query, y necesito cambiar los operadores *=
por LEFT OUTER JOINS, ya lo intente pero la consulta original dura 30 seg. y
reemplazando los operadores *= por left joins, despues de 3 minutos tengo que
cancelarla.
Alguien me puede ayudar? Mil gracias.

SELECT A.BUSINESS_UNIT ,
A.ORDER_NO ,
G.ORDER_DATE ,
A.ORDER_INT_LINE_NO ,
A.SCHED_LINE_NBR ,
A.SHIP_TO_CUST_ID ,
E.NAME1 ,
C.PRODUCT_ID ,
A.QTY_SCHEDULED ,
C.UNIT_OF_MEASURE ,
B.PO_ID ,
B.LINE_NBR ,
B.QTY_PO ,
D.VOUCHER_ID ,
D.VOUCHER_LINE_NUM ,
D.QTY_VCHR ,
F.INVOICE ,
F.INVOICE_LINE ,
F.ADD_DTTM ,
F.QTY
FROM PS_ORD_SCHEDULE A WITH (NOLOCK) ,
PS_PO_LINE_SHIP B WITH (NOLOCK) ,
PS_ORD_LINE C WITH (NOLOCK) ,
PS_VOUCHER_LINE D WITH (NOLOCK) ,
PS_CUSTOMER E WITH (NOLOCK) ,
PS_BI_LINE F WITH (NOLOCK) ,
PS_ORD_HEADER G WITH (NOLOCK)
WHERE A.BUSINESS_UNIT LIKE 'CRS%' AND
A.BUSINESS_UNIT <> 'CRS80' AND
A.DROP_SHIP_FLAG = 'Y' AND
A.ORD_SCH_STATUS <> 'X' AND
A.DATETIME_ADDED >= '2005-06-01' AND
A.BUSINESS_UNIT_PO *= B.BUSINESS_UNIT AND
A.PO_LINE_NBR *= B.LINE_NBR AND
A.PO_ID *= B.PO_ID AND
A.PO_SCHED_NBR *= B.SCHED_NBR AND
A.ORDER_NO *= B.ORDER_NO AND
A.ORDER_INT_LINE_NO *= B.ORDER_INT_LINE_NO AND
A.SCHED_LINE_NBR *= B.SCHED_LINE_NBR AND
A.ORDER_NO *= B.ORDER_NO AND
A.ORDER_INT_LINE_NO *= B.ORDER_INT_LINE_NO AND
A.SCHED_LINE_NBR *= B.SCHED_LINE_NBR AND
A.BUSINESS_UNIT = C.BUSINESS_UNIT AND
A.ORDER_NO = C.ORDER_NO )AND
A.ORDER_INT_LINE_NO = C.ORDER_INT_LINE_NO AND
D.BUSINESS_UNIT = 'CRA01' AND D.PO_ID <> '' AND
D.BUSINESS_UNIT_PO <> '' AND A.PO_ID *=D.PO_ID AND
A.PO_LINE_NBR *= D.LINE_NBR AND
A.BUSINESS_UNIT_PO *= D.BUSINESS_UNIT_PO AND
E.CUST_ID = A.SHIP_TO_CUST_ID AND E.SETID = 'CRE01' AND
A.BUSINESS_UNIT *= F.BUSINESS_UNIT AND
A.ORDER_NO *= F.ORDER_NO AND
A.ORDER_INT_LINE_NO *= F.ORDER_INT_LINE_NO AND
A.SCHED_LINE_NBR *= F.SCHED_LINE_NBR AND
F.BUSINESS_UNIT <> '' AND F.INVOICE <> '' AND
G.BUSINESS_UNIT = C.BUSINESS_UNIT AND
G.ORDER_NO = C.ORDER_NO
 

Leer las respuestas

#1 qwalgrande
12/01/2008 - 11:38 | Informe spam
Hola.

¿Puedes pasarnos el plan de ejecución de la consulta?

Alberto López Grande

qwalgrande


"Carlos A." wrote:

Hola a todos, tengo el siguiente query, y necesito cambiar los operadores *=
por LEFT OUTER JOINS, ya lo intente pero la consulta original dura 30 seg. y
reemplazando los operadores *= por left joins, despues de 3 minutos tengo que
cancelarla.
Alguien me puede ayudar? Mil gracias.

SELECT A.BUSINESS_UNIT ,
A.ORDER_NO ,
G.ORDER_DATE ,
A.ORDER_INT_LINE_NO ,
A.SCHED_LINE_NBR ,
A.SHIP_TO_CUST_ID ,
E.NAME1 ,
C.PRODUCT_ID ,
A.QTY_SCHEDULED ,
C.UNIT_OF_MEASURE ,
B.PO_ID ,
B.LINE_NBR ,
B.QTY_PO ,
D.VOUCHER_ID ,
D.VOUCHER_LINE_NUM ,
D.QTY_VCHR ,
F.INVOICE ,
F.INVOICE_LINE ,
F.ADD_DTTM ,
F.QTY
FROM PS_ORD_SCHEDULE A WITH (NOLOCK) ,
PS_PO_LINE_SHIP B WITH (NOLOCK) ,
PS_ORD_LINE C WITH (NOLOCK) ,
PS_VOUCHER_LINE D WITH (NOLOCK) ,
PS_CUSTOMER E WITH (NOLOCK) ,
PS_BI_LINE F WITH (NOLOCK) ,
PS_ORD_HEADER G WITH (NOLOCK)
WHERE A.BUSINESS_UNIT LIKE 'CRS%' AND
A.BUSINESS_UNIT <> 'CRS80' AND
A.DROP_SHIP_FLAG = 'Y' AND
A.ORD_SCH_STATUS <> 'X' AND
A.DATETIME_ADDED >= '2005-06-01' AND
A.BUSINESS_UNIT_PO *= B.BUSINESS_UNIT AND
A.PO_LINE_NBR *= B.LINE_NBR AND
A.PO_ID *= B.PO_ID AND
A.PO_SCHED_NBR *= B.SCHED_NBR AND
A.ORDER_NO *= B.ORDER_NO AND
A.ORDER_INT_LINE_NO *= B.ORDER_INT_LINE_NO AND
A.SCHED_LINE_NBR *= B.SCHED_LINE_NBR AND
A.ORDER_NO *= B.ORDER_NO AND
A.ORDER_INT_LINE_NO *= B.ORDER_INT_LINE_NO AND
A.SCHED_LINE_NBR *= B.SCHED_LINE_NBR AND
A.BUSINESS_UNIT = C.BUSINESS_UNIT AND
A.ORDER_NO = C.ORDER_NO )AND
A.ORDER_INT_LINE_NO = C.ORDER_INT_LINE_NO AND
D.BUSINESS_UNIT = 'CRA01' AND D.PO_ID <> '' AND
D.BUSINESS_UNIT_PO <> '' AND A.PO_ID *=D.PO_ID AND
A.PO_LINE_NBR *= D.LINE_NBR AND
A.BUSINESS_UNIT_PO *= D.BUSINESS_UNIT_PO AND
E.CUST_ID = A.SHIP_TO_CUST_ID AND E.SETID = 'CRE01' AND
A.BUSINESS_UNIT *= F.BUSINESS_UNIT AND
A.ORDER_NO *= F.ORDER_NO AND
A.ORDER_INT_LINE_NO *= F.ORDER_INT_LINE_NO AND
A.SCHED_LINE_NBR *= F.SCHED_LINE_NBR AND
F.BUSINESS_UNIT <> '' AND F.INVOICE <> '' AND
G.BUSINESS_UNIT = C.BUSINESS_UNIT AND
G.ORDER_NO = C.ORDER_NO

Preguntas similares