首页新闻找找看学习计划

SQL存储过程比SQL语句慢,甚至卡死

0
悬赏园豆:100 [待解决问题]

因公司业务需要,需要每天凌晨从别的系统(Oracle数据库)取前一天的数据,建了存储过程后,发现执行存储过程经常卡死,单独执行却挺快,不知道是哪里出了问题,求大神帮忙分析下,万分感谢!以下为存储过程代码:

USE [HERP_BHYY]
GO
/****** 对象:  StoredProcedure [dbo].[sp_GetHISChargeOReceiptDataInHISDBbyDay]    脚本日期: 12/03/2013 08:36:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[sp_GetHISChargeOReceiptDataInHISDBbyDay] @ImpDate as DATETIME
AS
BEGIN
    --SET NOCOUNT ON;
    DECLARE @ST_DATE AS DATETIME
    DECLARE @ED_DATE AS DATETIME
    DECLARE @TEMP_DATE DATETIME

    IF @ImpDate is not null
    begin
        SET @ST_DATE=LEFT(@ImpDate,10)
        SET @ED_DATE=dateadd(day,1,@ST_DATE)
    end

    delete hisdb2.dbo.his_charge_acc_o where charge_date>=@ST_DATE and charge_date<@ED_DATE
    insert into hisdb2.dbo.his_charge_acc_o
          (charge_date, deposit_pre, pay_type_code, patient_type_code, his_irecord, charge_money, patient_code, invoice_no, operator, acc_flag, balance_flag, invoice_type)
    select charge_date, deposit_pre, pay_type_code, patient_type_code, his_irecord, charge_money, patient_code, invoice_no, operator, acc_flag, balance_flag, invoice_type
    from openquery(HIS,'select charge_date, deposit_pre, pay_type_code, patient_type_code, his_irecord, charge_money, patient_code, invoice_no, operator, acc_flag, balance_flag, invoice_type     from data_hrp.HIS_CHARGE_ACC_o')
    where charge_date>=@ST_DATE and charge_date<@ED_DATE

end
胡明hoo的主页 胡明hoo | 初学一级 | 园豆:84
提问于:2013-12-03 09:08
< >
分享
所有回答(7)
0

这段sql 逻辑很简单也就只有删除和新增操作,Sql语句效率上应该不存在问题,而且你也单独试过了。所以问题应该不在这里

我有以下几种猜测

1  到凌晨时你取数据的系统,也在执行大量的操作(因为很多业务逻辑是0点过后操作的),导致出现IO瓶颈,使得取数据慢或者卡死,

2  不清楚你是否在同一个局域网内,如果不是则还可能是网络的原因。

 建议你可以在非凌晨时间测试一下。顺便看看这段sql的执行计划 IO开销 

SET STATISTICS IO ON  //可以准确的看到SQL读取数据的次数

SET STATISTICS Time ON  //可以准确的看到SQL语句的执行时间

Zery | 园豆:6069 (大侠五级) | 2013-12-03 09:58
0

能否把单独执行的代码也贴出来,让大家看看?

LiuKaiFa | 园豆:1491 (小虾三级) | 2013-12-03 11:03
 delete hisdb2.dbo.his_charge_acc_o where charge_date>='2013-12-01' and charge_date<'2013-12-02'
    insert into hisdb2.dbo.his_charge_acc_o
          (charge_date, deposit_pre, pay_type_code, patient_type_code, his_irecord, charge_money, patient_code, invoice_no, operator, acc_flag, balance_flag, invoice_type)
    select charge_date, deposit_pre, pay_type_code, patient_type_code, his_irecord, charge_money, patient_code, invoice_no, operator, acc_flag, balance_flag, invoice_type
    from openquery(HIS,'select charge_date, deposit_pre, pay_type_code, patient_type_code, his_irecord, charge_money, patient_code, invoice_no, operator, acc_flag, balance_flag, invoice_type     from data_hrp.HIS_CHARGE_ACC_o')
    where charge_date>='2013-12-01' and charge_date<'2013-12-02'

单独执行就一个删除,一个插入,变量换成日期。

支持(0) 反对(0) 胡明hoo | 园豆:84 (初学一级) | 2013-12-03 12:18
0

"发现执行存储过程经常卡死,单独执行却挺快"
是每次测试都会出现这种情况吗?

SP也能看执行计划的,对比单独执行,查看一下执行计划.以及IO/CUP Time数据

 

失落映画 | 园豆:216 (菜鸟二级) | 2013-12-03 11:46
0

想问一下几个问题:

    一次会取多少数据过来呢?   如果大数据量,那么一次取一部分,然后就提交,避免大数据引起网络延迟。

    是局域网吗?网路是否稳定?   曾经遇到过由于交换机不稳定引起你描述的现象。dblink有一个特点,发出请求后会一直等待对方的响应,如果连接期间网络不稳定或者断掉,那么dblink就会假死,会傻傻的等待对方未完成的响应。

 

建议你一次取少量数据试试,这样可以避免由于大数据带来的长时间dblink连接。

bitbug | 园豆:470 (菜鸟二级) | 2013-12-03 14:24

贴出来的代码只是操作一个表,实际上有6个表,每次执行下来共有数据约8000至10000条,都是局域网内的操作,网络暂时不知道是不是不稳定,谢谢您的建议,先取一部分数据提交试试

支持(0) 反对(0) 胡明hoo | 园豆:84 (初学一级) | 2013-12-03 14:38

@胡明hoo: 如果只有8000至10000条数据的话,应该不是数据量引起的。  你的语句为什么不把where条件写到dblink的查询中呢?   

支持(0) 反对(0) bitbug | 园豆:470 (菜鸟二级) | 2013-12-04 09:44
0

重新编译一下这个存储过程试一试,单独执行这个存储过程的时候可能还是用的是以前的执行计划,而现在的数据源的数据可能发生了变化,以前的执行计划可能并不是最优的执行计划了。你单独运行 SQL 查询的时候,它每次的执行计划都是根据现有的数据来选择的,所以每次应该都不一样。重新编译存储过程再看看是不是这个问题,如果可以解决这个问题的话,试着看看这个执行计划可以适应几天的数据变化。

BIWORK | 园豆:156 (初学一级) | 2013-12-04 17:45
0

看日志写了什么

CICN | 园豆:188 (初学一级) | 2013-12-05 12:55
0

单凭修改这个是不够的。 当时的发生卡死的环境很重要。建议你抓取服务器的performance

sym_cn | 园豆:798 (小虾三级) | 2013-12-05 15:45
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册