Duda con Store Procedure

Hola:
Algunas cosa:
a) Puedes sustutir el UNION por un UNION ALL? El UNION requiere que se
eliminen las filas repetidas, por lo que generalmente el servidor termina
ordenandolas para poder quitar las filas que tienen los mismos valores en
las columnas, el UNION ALL no. Esto no explica la diferencia entre QA y en
SP.
b) Podrias simplificar el código:
= when isnull(gd.guia_det_nro, '') <> '' then 1
else 0
end) Guia
= Por:
=, SUM( CASE WHEN gd.guia_det_nro IS NOT NULL THEN 1
ELSE 0 END) Guia
, SUM(CASE WHEN (gd.guia_det_nro IS NOT NULL)
OR (gd.guia_det_nro<>'') THEN 1
ELSE 0 END) Guia
c) Cuando haces:
inner join calendario m
on rm.reg_ano = year(m.cal_fecha) and
rm.reg_mes = month(m.cal_fecha)
Se estan "eliminando" el uso de indices sobre la tabla m (calendario) porque
usas una funcion YEAR y MONTH sobre las columa cal_fecha.
Puedes hacer 3 cosas
- Hacer 2 columnas calculadas con las funciones YEAR y MONTH en la tabla
calendario:
ON rm.reg_ano = m.Anio and
rm.reg_mes = m.Mes
- Agregar a la tabla calendario 2 columnas FechaInicioMes y FechaFinMes.
ON rm.reg_ano BETWEEN mFechaInicio AND m.FechaMes
- Antes del procedimiento calcular la fecha inicio del mes y la fecha final
del mes y usar
WHERE ...
AND rm.reg_ano BETWEEN @FechaInicioMes AND @FechaFinMes
d) El código:
and year(case when gd.pro_cod = 'OUT'
then gd.guia_det_fch_emi
else cast(convert(char(10)
, gd.guia_det_fch_reg
, 103) as datetime) end) = @anio

Tiene el problema de no permitir indices sobre la tabla
gd(guia_detalle_courier) que esa si debe ser una tabla grande. Aparte de
esto hay un problema porque la guia_det_fch_reg esta haciendo 2 operaciones
de de conversion, el primero para convertirlo en 10 caracteres y el segundo
para volverlo fecha, asumo que es para cortar en año.
No estoy tan seguro pero es probable que el siguiente codigo sea mas
efectivo:
AND ( gd.pro_cod = 'OUT' AND gd.guia_det_fch_emi BETWEEN @FechaInicioMes

AND @FechaFinMes)
OR ( gd.pro_cod <> 'OUT' AND gd.gd.guia_det_fch_reg BETWEEN @FechaInicioMes

AND @FechaFinMes)
Ahora debes ser mas cuidadoso con la @FechaFinMes y asegurarse que incluye
no al principio del dia sino mas bien al final, ejemplo: 31/01/2006
23:59:59.
d) La primera parte de la consulta solo tiene una fila por Region y por
Fecha?:
Usa en lugar de:
, rm.reg_meta_siccsa MetaSiccsa
, rm.reg_meta_fedex MetaFedex
...
GROUP BY r.reg_nom, m.cal_fecha, rm.reg_meta_siccsa, rm.reg_meta_fedex

usa:
, MAX(rm.reg_meta_siccsa) AS MetaSiccsa
, MAX(rm.reg_meta_fedex) MetaFedex
...
GROUP BY r.reg_nom, m.cal_fecha

La segunda columna es solo un total? La primera parte solo tiene una fila
por Region?
e) La ultima consulta es solo un total?, Puedes considerar eliminarla y
agregar la opcion de ROLLUP en la consulta del primer GROUP BY.
==GROUP BY r.reg_nom, m.cal_fecha WITH ROLLUP
== Es muy posible que algunas de las anteriores sugerencias te ayuden a
mejorar el desmepeño y a que genere el mismo resultado entre la consulta y
el procedimiento.
Este tipo de problema ocurre cuando la primera vez que se ejecuta el
procedimiento se genera un plan de acceso para esa consulta, y luego
siguientes ejecuciones con el mismo valor deben ser igualmente eficientes,
pero luego una consulta con parametros diferentes requiere un plan
totalmente diferente, pero el servidor no recompila el procedimiento y se
usa un plan inadecuado.
Saludos,



Javier Loria
Costa Rica-MVP
Solid Quality Learning


"xtian" wrote in message
news:
Hola amigos
Tengo una gran duda, tengo un query que cuando lo corro en el
analizador de consultas demora un promedio de 20 segundos, pero al
incoporarlo a un store y ejecutarlo se demora el increible tiempo de 3
horas!!!!!
Hay alguien al que le haya sucedido algo similar? es problema del Sql
Server? una configuración? hardware? Service Pack? Tengo Sql Server
2000, el query es el siguiente:

select r.reg_nom Region,
m.cal_fecha Fecha,
rm.reg_meta_siccsa MetaSiccsa,
rm.reg_meta_fedex MetaFedex,
sum( case
when isnull(gd.guia_det_nro, '') <> '' then 1
else 0
end) Guia,
sum( case
when isnull(gd.guia_det_nro, '') <> '' then
gd.guia_det_pqt_nro - 1
else 0
end) MPS,
sum( case
when isnull(gd.guia_det_nro, '') <> '' then
gd.guia_det_pqt_peso
else 0
end) Peso
from region r
inner join region_meta rm
on rm.reg_cod = r.reg_cod
inner join ciudad c
on r.reg_cod = c.reg_cod
inner join calendario m
on rm.reg_ano = year(m.cal_fecha) and
rm.reg_mes = month(m.cal_fecha)
left outer join
(select gd.empr_cod, gd.suc_cod, gd.uni_neg_cod, gd.pro_cod,
gd.guia_ano, gd.guia_nro, gd.guia_det_nro,
case when gd.pro_cod = 'OUT' then substring(g.guia_ori, 4,3) else
substring(gd.guia_det_dst, 4,3) end ciudad,
case when gd.pro_cod = 'OUT' then gd.guia_det_fch_emi
else cast(convert(char(10), gd.guia_det_fch_reg, 103) as datetime) end
fecha,
gd.guia_det_pqt_nro, gd.guia_det_pqt_peso
from guia_courier g
inner join guia_detalle_courier gd
on g.empr_cod = gd.empr_cod and
g.suc_cod = gd.suc_cod and
g.uni_neg_cod = gd.uni_neg_cod and
g.pro_cod = gd.pro_cod and
g.guia_ano = gd.guia_ano and
g.guia_nro = gd.guia_nro
where gd.pro_cod = @pro_cod and
year(case when gd.pro_cod = 'OUT' then
gd.guia_det_fch_emi else cast(convert(char(10), gd.guia_det_fch_reg,
103) as datetime) end) = @anio and
month(case when gd.pro_cod = 'OUT' then
gd.guia_det_fch_emi else cast(convert(char(10), gd.guia_det_fch_reg,
103) as datetime) end) = @mes) gd
on rm.reg_pro = gd.pro_cod and
c.ciu_cod_iata = gd.ciudad and
m.cal_fecha = gd.fecha
where rm.reg_pro = @pro_cod and
rm.reg_ano = @anio and
rm.reg_mes = @mes
group by r.reg_nom, m.cal_fecha, rm.reg_meta_siccsa,
rm.reg_meta_fedex

union

select 'TOTAL PERU' Region,
m.cal_fecha Fecha,
(select sum(rm1.reg_meta_siccsa)
from region_meta rm1
where rm1.reg_pro = @pro_cod and
rm1.reg_ano = @anio and
rm1.reg_mes = @mes) MetaSiccsa,
(select sum(rm1.reg_meta_fedex)
from region_meta rm1
where rm1.reg_pro = @pro_cod and
rm1.reg_ano = @anio and
rm1.reg_mes = @mes) MetaFedex,
sum( case
when isnull(gd.guia_det_nro, '') <> '' then 1
else 0
end) Guia,
sum( case
when isnull(gd.guia_det_nro, '') <> '' then
gd.guia_det_pqt_nro - 1
else 0
end) MPS,
sum( case
when isnull(gd.guia_det_nro, '') <> '' then
gd.guia_det_pqt_peso
else 0
end) Peso
from region r
inner join region_meta rm
on rm.reg_cod = r.reg_cod
inner join ciudad c
on r.reg_cod = c.reg_cod
inner join calendario m
on rm.reg_ano = year(m.cal_fecha) and
rm.reg_mes = month(m.cal_fecha)
left outer join
(select gd.empr_cod, gd.suc_cod, gd.uni_neg_cod, gd.pro_cod,
gd.guia_ano, gd.guia_nro, gd.guia_det_nro,
case when gd.pro_cod = 'OUT' then substring(g.guia_ori, 4,3) else
substring(gd.guia_det_dst, 4,3) end ciudad,
case when gd.pro_cod = 'OUT' then gd.guia_det_fch_emi
else cast(convert(char(10), gd.guia_det_fch_reg, 103) as datetime) end
fecha,
gd.guia_det_pqt_nro, gd.guia_det_pqt_peso
from guia_courier g
inner join guia_detalle_courier gd
on g.empr_cod = gd.empr_cod and
g.suc_cod = gd.suc_cod and
g.uni_neg_cod = gd.uni_neg_cod and
g.pro_cod = gd.pro_cod and
g.guia_ano = gd.guia_ano and
g.guia_nro = gd.guia_nro
where gd.pro_cod = @pro_cod and
year(case when gd.pro_cod = 'OUT' then
gd.guia_det_fch_emi else cast(convert(char(10), gd.guia_det_fch_reg,
103) as datetime) end) = @anio and
month(case when gd.pro_cod = 'OUT' then
gd.guia_det_fch_emi else cast(convert(char(10), gd.guia_det_fch_reg,
103) as datetime) end) = @mes) gd
on rm.reg_pro = gd.pro_cod and
c.ciu_cod_iata = gd.ciudad and
m.cal_fecha = gd.fecha
where rm.reg_pro = @pro_cod and
rm.reg_ano = @anio and
rm.reg_mes = @mes
group by m.cal_fecha
order by Fecha


Se que tal vez la sintaxis no este bien del todo, que se puede hacer
mejoras, agregar indices, etc... pero como les digo mi duda es porque
en el analizador de consultas demora tan poco y porque al meterlo en un
store demora una eternidad.

Si alguien tiene una respuesta le rogaria explicarmela.

Muchas gracias ...

PD .. Por si acaso si se quita el UNION la consulta sigue demorando lo
mismo, es decir no termina de ejecutarse asi solo tenga una de las
partes del UNION

Que mas que agradecerte Javier, tus sugerencias las implante(casi
todas) y el rendimiento fue muchisimo mejor que el de antes, sobre todo
lo del Rollup que con eso se me elimino la diferencia entre QA y SP.
Ah me olvidaba navegando encontre que el problema que surgio entre el
QA y el SP se debio a un problema con los UNION que se corrige con el
service pack 4 de Sql Server 2000, Service pack que aun no instalo,
pero pronto lo hare, la referencia de microsoft acerca de este problema
es el siguiente:
http://support.microsoft.com/kb/822033

De todas formas al haber suprimido el union, esa sugerencia ya no sirve
de mucho pero me imagino que para otros tal vez si le sea de utilidad.
Muchas gracias de nuevo Javier y de verdad me gustaria que me des mas
sugerencias en el futuro especialmente con un query que publique hace
ya algunas semanas y que hasta ahora me esta sacando canas verdes.

Muchas gracias


Javier Loria ha escrito:

Hola:
Algunas cosa:
a) Puedes sustutir el UNION por un UNION ALL? El UNION requiere que se
eliminen las filas repetidas, por lo que generalmente el servidor termina
ordenandolas para poder quitar las filas que tienen los mismos valores en
las columnas, el UNION ALL no. Esto no explica la diferencia entre QA y en
SP.
b) Podrias simplificar el código:
=> when isnull(gd.guia_det_nro, '') <> '' then 1
else 0
end) Guia
=> Por:
=> , SUM( CASE WHEN gd.guia_det_nro IS NOT NULL THEN 1
ELSE 0 END) Guia
, SUM(CASE WHEN (gd.guia_det_nro IS NOT NULL)
OR (gd.guia_det_nro<>'') THEN 1
ELSE 0 END) Guia
> c) Cuando haces:
> inner join calendario m
on rm.reg_ano = year(m.cal_fecha) and
rm.reg_mes = month(m.cal_fecha)
> Se estan "eliminando" el uso de indices sobre la tabla m (calendario) porque
usas una funcion YEAR y MONTH sobre las columa cal_fecha.
Puedes hacer 3 cosas
- Hacer 2 columnas calculadas con las funciones YEAR y MONTH en la tabla
calendario:
> ON rm.reg_ano = m.Anio and
rm.reg_mes = m.Mes
> - Agregar a la tabla calendario 2 columnas FechaInicioMes y FechaFinMes.
> ON rm.reg_ano BETWEEN mFechaInicio AND m.FechaMes
> - Antes del procedimiento calcular la fecha inicio del mes y la fecha final
del mes y usar
> WHERE ...
AND rm.reg_ano BETWEEN @FechaInicioMes AND @FechaFinMes
> d) El código:
> and year(case when gd.pro_cod = 'OUT'
then gd.guia_det_fch_emi
else cast(convert(char(10)
, gd.guia_det_fch_reg
, 103) as datetime) end) = @anio

> Tiene el problema de no permitir indices sobre la tabla
gd(guia_detalle_courier) que esa si debe ser una tabla grande. Aparte de
esto hay un problema porque la guia_det_fch_reg esta haciendo 2 operaciones
de de conversion, el primero para convertirlo en 10 caracteres y el segundo
para volverlo fecha, asumo que es para cortar en año.
No estoy tan seguro pero es probable que el siguiente codigo sea mas
efectivo:
> AND ( gd.pro_cod = 'OUT' AND gd.guia_det_fch_emi BETWEEN @FechaInicioMes

AND @FechaFinMes)
OR ( gd.pro_cod <> 'OUT' AND gd.gd.guia_det_fch_reg BETWEEN @FechaInicioMes

AND @FechaFinMes)
> Ahora debes ser mas cuidadoso con la @FechaFinMes y asegurarse que incluye
no al principio del dia sino mas bien al final, ejemplo: 31/01/2006
23:59:59.
d) La primera parte de la consulta solo tiene una fila por Region y por
Fecha?:
Usa en lugar de:
> , rm.reg_meta_siccsa MetaSiccsa
, rm.reg_meta_fedex MetaFedex
...
GROUP BY r.reg_nom, m.cal_fecha, rm.reg_meta_siccsa, rm.reg_meta_fedex

> usa:
> , MAX(rm.reg_meta_siccsa) AS MetaSiccsa
, MAX(rm.reg_meta_fedex) MetaFedex
...
GROUP BY r.reg_nom, m.cal_fecha

> La segunda columna es solo un total? La primera parte solo tiene una fila
por Region?
e) La ultima consulta es solo un total?, Puedes considerar eliminarla y
agregar la opcion de ROLLUP en la consulta del primer GROUP BY.
==> GROUP BY r.reg_nom, m.cal_fecha WITH ROLLUP
==> Es muy posible que algunas de las anteriores sugerencias te ayuden a
mejorar el desmepeño y a que genere el mismo resultado entre la consulta y
el procedimiento.
Este tipo de problema ocurre cuando la primera vez que se ejecuta el
procedimiento se genera un plan de acceso para esa consulta, y luego
siguientes ejecuciones con el mismo valor deben ser igualmente eficientes,
pero luego una consulta con parametros diferentes requiere un plan
totalmente diferente, pero el servidor no recompila el procedimiento y se
usa un plan inadecuado.
Saludos,



Javier Loria
Costa Rica-MVP
Solid Quality Learning


"xtian" wrote in message
news:
Hola amigos
Tengo una gran duda, tengo un query que cuando lo corro en el
analizador de consultas demora un promedio de 20 segundos, pero al
incoporarlo a un store y ejecutarlo se demora el increible tiempo de 3
horas!!!!!
Hay alguien al que le haya sucedido algo similar? es problema del Sql
Server? una configuración? hardware? Service Pack? Tengo Sql Server
2000, el query es el siguiente:

select r.reg_nom Region,
m.cal_fecha Fecha,
rm.reg_meta_siccsa MetaSiccsa,
rm.reg_meta_fedex MetaFedex,
sum( case
when isnull(gd.guia_det_nro, '') <> '' then 1
else 0
end) Guia,
sum( case
when isnull(gd.guia_det_nro, '') <> '' then
gd.guia_det_pqt_nro - 1
else 0
end) MPS,
sum( case
when isnull(gd.guia_det_nro, '') <> '' then
gd.guia_det_pqt_peso
else 0
end) Peso
from region r
inner join region_meta rm
on rm.reg_cod = r.reg_cod
inner join ciudad c
on r.reg_cod = c.reg_cod
inner join calendario m
on rm.reg_ano = year(m.cal_fecha) and
rm.reg_mes = month(m.cal_fecha)
left outer join
(select gd.empr_cod, gd.suc_cod, gd.uni_neg_cod, gd.pro_cod,
gd.guia_ano, gd.guia_nro, gd.guia_det_nro,
case when gd.pro_cod = 'OUT' then substring(g.guia_ori, 4,3) else
substring(gd.guia_det_dst, 4,3) end ciudad,
case when gd.pro_cod = 'OUT' then gd.guia_det_fch_emi
else cast(convert(char(10), gd.guia_det_fch_reg, 103) as datetime) end
fecha,
gd.guia_det_pqt_nro, gd.guia_det_pqt_peso
from guia_courier g
inner join guia_detalle_courier gd
on g.empr_cod = gd.empr_cod and
g.suc_cod = gd.suc_cod and
g.uni_neg_cod = gd.uni_neg_cod and
g.pro_cod = gd.pro_cod and
g.guia_ano = gd.guia_ano and
g.guia_nro = gd.guia_nro
where gd.pro_cod = @pro_cod and
year(case when gd.pro_cod = 'OUT' then
gd.guia_det_fch_emi else cast(convert(char(10), gd.guia_det_fch_reg,
103) as datetime) end) = @anio and
month(case when gd.pro_cod = 'OUT' then
gd.guia_det_fch_emi else cast(convert(char(10), gd.guia_det_fch_reg,
103) as datetime) end) = @mes) gd
on rm.reg_pro = gd.pro_cod and
c.ciu_cod_iata = gd.ciudad and
m.cal_fecha = gd.fecha
where rm.reg_pro = @pro_cod and
rm.reg_ano = @anio and
rm.reg_mes = @mes
group by r.reg_nom, m.cal_fecha, rm.reg_meta_siccsa,
rm.reg_meta_fedex

union

select 'TOTAL PERU' Region,
m.cal_fecha Fecha,
(select sum(rm1.reg_meta_siccsa)
from region_meta rm1
where rm1.reg_pro = @pro_cod and
rm1.reg_ano = @anio and
rm1.reg_mes = @mes) MetaSiccsa,
(select sum(rm1.reg_meta_fedex)
from region_meta rm1
where rm1.reg_pro = @pro_cod and
rm1.reg_ano = @anio and
rm1.reg_mes = @mes) MetaFedex,
sum( case
when isnull(gd.guia_det_nro, '') <> '' then 1
else 0
end) Guia,
sum( case
when isnull(gd.guia_det_nro, '') <> '' then
gd.guia_det_pqt_nro - 1
else 0
end) MPS,
sum( case
when isnull(gd.guia_det_nro, '') <> '' then
gd.guia_det_pqt_peso
else 0
end) Peso
from region r
inner join region_meta rm
on rm.reg_cod = r.reg_cod
inner join ciudad c
on r.reg_cod = c.reg_cod
inner join calendario m
on rm.reg_ano = year(m.cal_fecha) and
rm.reg_mes = month(m.cal_fecha)
left outer join
(select gd.empr_cod, gd.suc_cod, gd.uni_neg_cod, gd.pro_cod,
gd.guia_ano, gd.guia_nro, gd.guia_det_nro,
case when gd.pro_cod = 'OUT' then substring(g.guia_ori, 4,3) else
substring(gd.guia_det_dst, 4,3) end ciudad,
case when gd.pro_cod = 'OUT' then gd.guia_det_fch_emi
else cast(convert(char(10), gd.guia_det_fch_reg, 103) as datetime) end
fecha,
gd.guia_det_pqt_nro, gd.guia_det_pqt_peso
from guia_courier g
inner join guia_detalle_courier gd
on g.empr_cod = gd.empr_cod and
g.suc_cod = gd.suc_cod and
g.uni_neg_cod = gd.uni_neg_cod and
g.pro_cod = gd.pro_cod and
g.guia_ano = gd.guia_ano and
g.guia_nro = gd.guia_nro
where gd.pro_cod = @pro_cod and
year(case when gd.pro_cod = 'OUT' then
gd.guia_det_fch_emi else cast(convert(char(10), gd.guia_det_fch_reg,
103) as datetime) end) = @anio and
month(case when gd.pro_cod = 'OUT' then
gd.guia_det_fch_emi else cast(convert(char(10), gd.guia_det_fch_reg,
103) as datetime) end) = @mes) gd
on rm.reg_pro = gd.pro_cod and
c.ciu_cod_iata = gd.ciudad and
m.cal_fecha = gd.fecha
where rm.reg_pro = @pro_cod and
rm.reg_ano = @anio and
rm.reg_mes = @mes
group by m.cal_fecha
order by Fecha


Se que tal vez la sintaxis no este bien del todo, que se puede hacer
mejoras, agregar indices, etc... pero como les digo mi duda es porque
en el analizador de consultas demora tan poco y porque al meterlo en un
store demora una eternidad.

Si alguien tiene una respuesta le rogaria explicarmela.

Muchas gracias ...

PD .. Por si acaso si se quita el UNION la consulta sigue demorando lo
mismo, es decir no termina de ejecutarse asi solo tenga una de las
partes del UNION