Ciao a tutti,
io ho una query che fa delle select annidate....su una vista che ha circa 1500 righe....la query mi ritorna 3 righe....ma il mio problema è che
1. ci mette circa 15 minuti!
2. la cpu mi va al 100%!
la query è questa:
DECLARE @CODE_PREFIX_CA as VARCHAR(255)
DECLARE @CODE_PREFIX_CNV as VARCHAR(255)
DECLARE @CODE_PREFIX_SERSTA as VARCHAR(255)
DECLARE @CODE_PREFIX_MA as varchar(255)
----
DECLARE @data_ora_da as VARCHAR(255)
DECLARE @data_ora_a as VARCHAR(255)
--
DECLARE @CODE_PREFIX as VARCHAR(255)
set @CODE_PREFIX = 'H/CL/DI'
if @CODE_PREFIX = 'H/CL/DI'
begin
set @CODE_PREFIX_CA = 'H/CL/DI/CA/%'
set @CODE_PREFIX_CNV = 'H/CL/DI/CNV/%'
set @CODE_PREFIX_SERSTA = 'H/CL/DI/SERSTA/%'
set @CODE_PREFIX_MA = 'H/CL/DI/MA'
end
else
begin
set @CODE_PREFIX_CA = 'H/CL/PO/CA/%'
set @CODE_PREFIX_CNV = 'H/CL/PO/CNV/%'
set @CODE_PREFIX_SERSTA = 'H/CL/PO/SERSTA/%'
set @CODE_PREFIX_MA = 'H/CL/PO/MA'
end
--
set @data_ora_da = '05/04/2011 01:00:00.000'
set @data_ora_a = '05/12/2011 23:59:00.000'
--
select *, ISNULL(TOT_MI, 0) + ISNULL(TOT_ME, 0)
from
( select distinct qi.user_code as cdc, qi.code_prefix as sede_tecnica, qi.code,
(select sum(real_con_length)
from
export_ore
where (code_prefix = qi.code_prefix or code_prefix like qi.code_prefix + '/%') and commessa is null
and (competenza = 'E' or competenza = 'G') and tipo_op = 'interno'
) as MI_ELET,
(select sum(real_con_length)
from export_ore
where (code_prefix = qi.code_prefix or code_prefix like qi.code_prefix + '/%') and commessa is null
and competenza = 'M' and tipo_op = 'interno'
) as MI_MEC,
(select sum(real_con_length)
from export_ore
where (code_prefix = qi.code_prefix or code_prefix like qi.code_prefix + '/%') and commessa is null
and tipo_op = 'interno'
) as TOT_MI,
(select sum(real_con_length)
from export_ore
where (code_prefix = qi.code_prefix or code_prefix like qi.code_prefix + '/%') and commessa is null
and tipo_op = 'esterno'
) as TOT_ME
FROM
(select user_code, code_prefix, code, real_con_length, DATA_AVANZAMENTO,
[description], tipo_op, commessa, competenza
from export_ore where (code_prefix like @CODE_PREFIX_CA or code_prefix like @CODE_PREFIX_CNV
or code_prefix like @CODE_PREFIX_SERSTA or code_prefix = @CODE_PREFIX_MA)
OR description like 'cdc%'
and commessa is null
) qi
) qe
where qe.tot_mi is not null or qe.tot_me is not null
order by qe.sede_tecnica
c'è un modo per ottimizzare sia i tempi di esecuzione che l'impiego della cpu???
grazie
Rispondi quotando