存储过程

Question

谁能帮我检查一下附件中的存储过程，在数据量很大的情况下，会不会在性能方面有缺陷可以怎么修改

Answer 1

[code=SQL]
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[statistics_perf_30]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[statistics_perf_30]
GOSET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GOCREATE  procedure statistics_perf_30
as
declare @vCurTime datetime--=getdate(),--??ǰʱ?䣬ͨ??Ϊsysdate
declare @vStaBeginTime datetime--ͳ?ƿ?ʼʱ?䍊declare @vStaEndTime datetime--ͳ?ƽኸʱ?䍊
declare @vInHour smallint--ͳ?ƿ?ʼʱ?䵄Сʱ
declare @vInDay smallint--ͳ?ƿ?ʼʱ?䊇Їƚ????֜ȕΪ0??֜һΪ1
declare @vInDate smallint--ͳ?ƿ?ʼʱ?䵄̬
set   @vCurTime = getdate()/*?Ƌ㍳?ƵĆ

Answer 2

/*perf_ne_port:===========================================*/
insert into perf_ne_port_30
(obj_id,start_time,stop_time,in_hour,in_day,in_date,
ne_ifloss,ne_iferror,ne_wanin_usage,ne_wanout_usage,ne_if_in_traffic,ne_if_out_traffic,ne_if_in_pkts,ne_if_out_pkts,ne_if_in_errpkts,ne_if_out_errpkts,ne_if_in_multicast_pkts,ne_if_out_multicast_pkts)
select obj_id,@vStaBeginTime,@vStaEndTime,@vInHour,@vInDay,@vInDate,
avg(ne_ifloss),
avg(ne_iferror),
avg(ne_wanin_usage),
avg(ne_wanout_usage),
sum(ne_if_in_traffic),
sum(ne_if_out_traffic),
sum(ne_if_in_pkts),
sum(ne_if_out_pkts),
sum(ne_if_in_errpkts),
sum(ne_if_out_errpkts),
sum(ne_if_in_multicast_pkts),
sum(ne_if_out_multicast_pkts)
from perf_ne_port
where start_time>=@vStaBeginTime and stop_time<=@vStaEndTime
group by obj_id;/*perf_pc_cpu:============================================*/
insert into perf_pc_cpu_30
(obj_id,start_time,stop_time,in_hour,in_day,in_date,
pc_cpuidle_rate,pc_cpusys_rate,pc_cpuuser_rate,pc_cpuwait_rate,pc_cpu_usage,pc_cpurunprocess_num)
select obj_id,@vStaBeginTime,@vStaEndTime,@vInHour,@vInDay,@vInDate,
avg(pc_cpuidle_rate),
avg(pc_cpusys_rate),
avg(pc_cpuuser_rate),
avg(pc_cpuwait_rate),
avg(pc_cpu_usage),
avg(pc_cpurunprocess_num)
from perf_pc_cpu
where start_time>=@vStaBeginTime and stop_time<=@vStaEndTime
group by obj_id;/*perf_pc_disk:===========================================*/
insert into perf_pc_disk_30
(obj_id,start_time,stop_time,in_hour,in_day,in_date,
pc_diskio_velocity,pc_diskwait_process,pc_diskbusy_rate,pc_diskread_bps,pc_diskwrite_bps,pc_freedisk_percentage)
select obj_id,@vStaBeginTime,@vStaEndTime,@vInHour,@vInDay,@vInDate,
avg(pc_diskio_velocity),
avg(pc_diskwait_process),--??
avg(pc_diskbusy_rate),
avg(pc_diskread_bps),
avg(pc_diskwrite_bps),
avg(pc_freedisk_percentage)
from perf_pc_disk
where start_time>=@vStaBeginTime and stop_time<=@vStaEndTime
group by obj_id;/*perf_pc_server:=========================================*/
insert into perf_pc_server_30
(obj_id,start_time,stop_time,in_hour,in_day,in_date,
pc_memory_usage,pc_pagerequest_num,pc_pagein_velocity,pc_pageout_velocity,pc_memoryquene_num,pc_sysmemory_usage,pc_usermemory_usage,pc_filesys_buffer,pc_swap_usage,pc_allcpu_usage,pc_filesys_usage)
select obj_id,@vStaBeginTime,@vStaEndTime,@vInHour,@vInDay,@vInDate,
avg(pc_memory_usage),
avg(pc_pagerequest_num),
avg(pc_pagein_velocity),
avg(pc_pageout_velocity),
avg(pc_memoryquene_num),
avg(pc_sysmemory_usage),
avg(pc_usermemory_usage),
avg(pc_filesys_buffer),
avg(pc_swap_usage),
avg(pc_allcpu_usage),
avg(pc_filesys_usage)
from perf_pc_server
where start_time>=@vStaBeginTime and stop_time<=@vStaEndTime
group by obj_id;/*perf_protocol_smtp:=====================================*/
insert into perf_protocol_smtp_30
(obj_id,start_time,stop_time,in_hour,in_day,in_date,
mta_received_msgs,mta_stored_msgs,mta_transmitted_msgs,mta_received_volume,mta_stored_volume,mta_transmitted_volume,mta_received_recipients,mta_stored_recipients,mta_transmitted_recipients)
select obj_id,@vStaBeginTime,@vStaEndTime,@vInHour,@vInDay,@vInDate,
sum(mta_received_msgs),
avg(mta_stored_msgs),
sum(mta_transmitted_msgs),
sum(mta_received_volume),
avg(mta_stored_volume),
sum(mta_transmitted_volume),
sum(mta_received_recipients),
avg(mta_stored_recipients),
sum(mta_transmitted_recipients)
from perf_protocol_smtp
where start_time>=@vStaBeginTime and stop_time<=@vStaEndTime
group by obj_id;/*perf_sec_firewall:======================================*/
insert into perf_sec_firewall_30
(obj_id,start_time,stop_time,in_hour,in_day,in_date,
fw_cur_connect,tun_active,tun_usage,fw_memory,fw_memory_free,fw_memory_usage)
select obj_id,@vStaBeginTime,@vStaEndTime,@vInHour,@vInDay,@vInDate,
avg(fw_cur_connect),
avg(tun_active),
avg(tun_usage),
avg(fw_memory),--??
avg(fw_memory_free),
avg(fw_memory_usage)
from perf_sec_firewall
where start_time>=@vStaBeginTime and stop_time<=@vStaEndTime
group by obj_id;/*perf_sec_firewall_port:=================================*/
insert into perf_sec_firewall_port_30
(obj_id,start_time,stop_time,in_hour,in_day,in_date,
if_index,fwnic_in_octets,fwnic_in_pkts,fwnic_in_broad_pkts,fwnic_in_ip_octets,fwnic_in_tcp_octets,fwnic_in_udp_octets,fwnic_in_icmp_octets,fwnic_in_arp_octets,fwnic_in_ip_pkts,fwnic_in_tcp_pkts,fwnic_in_udp_pkts,fwnic_in_icmp_pkts,fwnic_in_arp_pkts,fwnic_in_ip_brd_pkts,fwnic_in_ip_mst_pkts,fwnic_in_frag_pkts,fwnic_in_option_pkts,fwnic_in_csum_pkts,fwnic_in_deny_pkts,fwnic_in_bad_pkts,fwnic_out_octets,fwnic_out_pkts)
select obj_id,@vStaBeginTime,@vStaEndTime,@vInHour,@vInDay,@vInDate,
if_index,--??
sum(fwnic_in_octets),
sum(fwnic_in_pkts),
sum(fwnic_in_broad_pkts),
sum(fwnic_in_ip_octets),
sum(fwnic_in_tcp_octets),
sum(fwnic_in_udp_octets),
sum(fwnic_in_icmp_octets),
sum(fwnic_in_arp_octets),
sum(fwnic_in_ip_pkts),
sum(fwnic_in_tcp_pkts),
sum(fwnic_in_udp_pkts),
sum(fwnic_in_icmp_pkts),
sum(fwnic_in_arp_pkts),
sum(fwnic_in_ip_brd_pkts),
sum(fwnic_in_ip_mst_pkts),
sum(fwnic_in_frag_pkts),
sum(fwnic_in_option_pkts),
sum(fwnic_in_csum_pkts),
sum(fwnic_in_deny_pkts),
sum(fwnic_in_bad_pkts),
sum(fwnic_out_octets),
sum(fwnic_out_pkts)
from perf_sec_firewall_port
where start_time>=@vStaBeginTime and stop_time<=@vStaEndTime
group by obj_id,if_index;/*perf_pcfilesys:==============================================*/
insert into perf_pcfilesys_30
(obj_id,start_time,stop_time,in_hour,in_day,in_date,
pc_filesys_usage,pc_filesys_size,pc_filesys_unused,pc_filesys_rwrate)
select obj_id,@vStaBeginTime,@vStaEndTime,@vInHour,@vInDay,@vInDate,
avg(pc_filesys_usage),
avg(pc_filesys_size),
min(pc_filesys_unused),
avg(pc_filesys_rwrate)
from perf_pcfilesys
where start_time>=@vStaBeginTime and stop_time<=@vStaEndTime
group by obj_id;
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

Answer 3

简单来说就是从perf_*表中取出30分钟的数据，然后取平均值插入perf_*_30中