Super Consulta

Hola a todos,

Tengo una consulta que me tarda 15 segundos en unos
servidores y en otros 6 minutos !!!

La consulta es la siguiente si alguien tiene un poco de
paciencia y me la puede hechar un vistazo


muchas gracias

SET STATISTICS IO ON
SELECT Varios.Descrip, OT.Codigo, OT.CodDpto + '/' +
OT.CodObra + '-' + LTRIM(OT.Indice) +
(CASE WHEN OT.CodOficina IS NULL THEN '' ELSE '/' + right
(ltrim(OT.CodOficina +100),2) end) AS Obra,
OT.CodDptoDestino + '/' + OT.CodObra2
+ '-' + LTRIM(OT.Indice2) + (CASE WHEN OT.PrevPA3 IS NULL
THEN '' ELSE '/' + right(ltrim(OT.PrevPA3 +100),2) end) AS
OT2, TCodigos.Descrip as
MarcaTexto, '01/01/' + LTRIM(OT.Año) as Fecha2 ,
OT.NSIE, Clientes.Nombre, OT.DescTrabajo,OT.FInicio +
OT.Plazo as FFin,OT.Importe,
OT.GradoAvance,OT.GradoAvanceMes,Prevision1, Prevision2,
Prevision3, OT.CodDpto, OT.CodObra, OT.Indice,
OT.Revision,OT.FInicio, OT.Usuario,
OT.Hora, Actividades.Descripcion, OT.TipoOT, OT.TipoOT2,
OT.RetencionInfor, OT.Interna, OT.Estado,
FacturacionDpto, OT.CodDptoDestino,
OT.CodObra2, OT.Indice2, OT.Plazo,
OT.CodigoActividad,OT.CodigoCliente, OT.Campo2,
OT.CodDptoFactura,OT.AlcanceTra,OT.RefOferta, OT.Marca,
OT.CodOficina, OT.PrevPA3, OT.PrecioCerrado,
OT.AlcanceTra, MargenSIE.SIEAcumProd,
MargenSIE.SIETotalAcum, MargenSIE.SIETotProvis,
OTProd.OTMesProd, OTProd.OTAcumProd, OTProd.OTPrevision1,
OTProd.OTPrevision2, OTProd.OTPrevision3,
OT.ParticipacionAjena , H.AcumProd, H.MesProd,
OT.PresupuestoCoste, VCP.TotalAcum, VCP.TotalMes,
SIEUNICO.Codigo AS CODSIEUNICO, (CASE WHEN OT.Interna IS
NULL THEN OT.CodDpto WHEN OT.Interna
IS NOT NULL AND OT.CodObra2 IS NOT NULL THEN
OT.CodDptoDestino ELSE NULL END) + '/' + (CASE WHEN
OT.interna IS NULL THEN OT.CodObra WHEN
OT.interna IS NOT NULL AND OT.CodObra2 IS NOT NULL THEN
OT.Codobra2 ELSE NULL END) AS CodOTProd, H.Campo8
FROM (((((((((OT LEFT JOIN Actividades ON
OT.CodigoActividad = Actividades.Codigo)
LEFT JOIN Clientes ON OT.CodigoCliente = Clientes.Codigo)
LEFT JOIN Varios ON (OT.PrecioCerrado) = Varios.Codigo)
LEFT JOIN TCodigos ON (OT.Marca) = TCodigos.Codigo) LEFT
JOIN Vis_CostesPresupuestados VCP ON VCP.NSIE = OT.NSIE
AND VCP.Marca = OT.Marca
AND VCP.Mes = '01/08/2004' ) LEFT JOIN
HistoricoProduccion H ON H.Campo5 = OT.Codigo AND H.Fecha
= '01/08/2004' )
LEFT JOIN ( Select Codigo From OT WHERE NSIE + Ltrim
(Marca) IN (SELECT NSIE + Ltrim(Marca) AS NSIEMarca
From OT GROUP BY NSIE, Marca HAVING COUNT(Codigo) =
1) ) SIEUNICO ON OT.Codigo = SIEUNICO.Codigo )
LEFT JOIN ( SELECT OT.NSIE, OT.Marca, CP.TotalAcum AS
SIETotalAcum , SUM(H.AcumProd) as SIEAcumProd,
Provis.Importe as SIETotProvis
FROM OT INNER JOIN HistoricoProduccion H ON OT.Codigo =
H.Campo5 AND Fecha = '01/08/2004' LEFT JOIN (SELECT NSIE,
Marca, SUM(TotalAcum) as
TotalAcum From Vis_CostesPresupuestados WHERE Mes
= '01/08/2004' AND CodDpto = '111' GROUP BY NSIE , MARCA)
CP ON CP.NSIE = OT.NSIE
AND CP.Marca = OT.Marca LEFT Join (SELECT VPR.NSIE,
VPR.Marca, SUM(VPR.Importe) as Importe FROM
Vis_Provisiones VP INNER JOIN
Vis_ProvisionesReparto VPR ON VP.CodProvision =
VPR.CodProvision WHERE (FCierre > '01/08/2004' or fcierre
is null) AND CODDPTO = '111'
GROUP BY VPR.NSIE, VPR.Marca) Provis ON PROVIS.NSIE =
OT.NSIE AND PROVIS.Marca = OT.Marca GROUP BY OT.NSIE,
OT.Marca, TotalAcum, Provis.Importe )
MargenSIE ON MargenSIE.NSIE = OT.NSIE AND MargenSIE.Marca
= OT.Marca) LEFT JOIN ( SELECT Año, (CASE WHEN OT.Interna
IS NULL THEN OT.CodDpto
WHEN OT.Interna IS NOT NULL AND OT.CodObra2 IS NOT NULL
THEN OT.CodDptoDestino ELSE NULL END) AS Dpto, (CASE WHEN
OT.interna IS NULL THEN
OT.CodObra WHEN OT.interna IS NOT NULL AND OT.CodObra2 IS
NOT NULL THEN OT.Codobra2 ELSE NULL END) AS Obra, SUM
(H.MesProd) as OTMesProd,
SUM(H.AcumProd) As OTAcumProd, SUM(H.Prev1) as
OTPrevision1, SUM(H.Prev2) as OTPrevision2, SUM(H.Prev3)
as OTPrevision3 From OT INNER JOIN
HistoricoProduccion H ON OT.Codigo = H.Campo5 AND Fecha
= '01/08/2004' Where (OT.CodDpto = '111' AND OT.Indice <>
0 AND OT.Interna IS NULL
OR OT.CodDptoDestino = '111' AND OT.Indice2 <> 0 AND
OT.Interna IS NOT NULL ) AND H.Fecha = '01/08/2004' Group
By AÑO, (CASE WHEN OT.Interna
IS NULL THEN OT.CodDpto WHEN OT.Interna IS NOT NULL AND
OT.CodObra2 IS NOT NULL THEN OT.CodDptoDestino ELSE NULL
END) , (CASE WHEN OT.interna IS
NULL THEN OT.CodObra WHEN OT.interna IS NOT NULL AND
OT.CodObra2 IS NOT NULL THEN OT.Codobra2 ELSE NULL END))
OTProd ON OTProd.Año = OT.Año AND
OTProd.Dpto = (CASE WHEN OT.Interna IS NULL THEN
OT.CodDpto WHEN OT.Interna IS NOT NULL AND OT.CodObra2 IS
NOT NULL THEN OT.CodDptoDestino ELSE
NULL END) AND OTProd.Obra = (CASE WHEN OT.interna IS NULL
THEN OT.CodObra WHEN OT.interna IS NOT NULL AND
OT.CodObra2 IS NOT NULL THEN
OT.Codobra2 ELSE NULL END) AND (OT.CodigoCliente = 0 OR
OT.CodigoActividad = 0) ) WHERE (OT.RetencionInfor <> 'SI'
or OT.RetencionInfor is null)
AND ( OT.INTERNA IS NULL AND OT.CodDpto='111' OR
INTERNA IS NOT NULL AND OT.CodDpto = '111' AND (Estado =
20002) AND FacturacionDpto = 0
OR INTERNA IS NOT NULL AND OT.CodDptoDestino = '111' AND
(Estado = 20002) AND FacturacionDpto = 1 OR INTERNA IS
NOT NULL AND OT.CodDptoDestino
= '111' AND (Estado = 20002) AND FacturacionDpto = 0
AND PrecioCerrado = 1 ) ORDER BY OT.NSIE, OT.Marca,
(CASE WHEN OT.Interna IS NULL THEN
OT.CodDpto WHEN OT.Interna IS NOT NULL AND OT.CodObra2 IS
NOT NULL THEN OT.CodDptoDestino ELSE NULL END), (CASE
WHEN OT.interna IS NULL THEN
OT.CodObra WHEN OT.interna IS NOT NULL AND OT.CodObra2 IS
NOT NULL THEN OT.Codobra2 ELSE NULL END) , (CASE WHEN
OT.interna IS NULL THEN OT.Indice
WHEN OT.interna IS NOT NULL AND OT.CodObra2 IS NOT NULL
THEN OT.Indice2 ELSE NULL END)