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
所有回答(7)
0
这段sql 逻辑很简单也就只有删除和新增操作，Sql语句效率上应该不存在问题，而且你也单独试过了。所以问题应该不在这里
我有以下几种猜测
1 到凌晨时你取数据的系统，也在执行大量的操作(因为很多业务逻辑是0点过后操作的)，导致出现IO瓶颈，使得取数据慢或者卡死，
2 不清楚你是否在同一个局域网内，如果不是则还可能是网络的原因。
建议你可以在非凌晨时间测试一下。顺便看看这段sql的执行计划 IO开销
SET STATISTICS IO ON //可以准确的看到SQL读取数据的次数
SET STATISTICS Time ON //可以准确的看到SQL语句的执行时间
0
能否把单独执行的代码也贴出来，让大家看看？
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
"发现执行存储过程经常卡死，单独执行却挺快"
是每次测试都会出现这种情况吗?
SP也能看执行计划的,对比单独执行,查看一下执行计划.以及IO/CUP Time数据
0
想问一下几个问题：
一次会取多少数据过来呢？ 如果大数据量，那么一次取一部分，然后就提交，避免大数据引起网络延迟。
是局域网吗？网路是否稳定？ 曾经遇到过由于交换机不稳定引起你描述的现象。dblink有一个特点，发出请求后会一直等待对方的响应，如果连接期间网络不稳定或者断掉，那么dblink就会假死，会傻傻的等待对方未完成的响应。
建议你一次取少量数据试试，这样可以避免由于大数据带来的长时间dblink连接。
贴出来的代码只是操作一个表，实际上有6个表，每次执行下来共有数据约8000至10000条，都是局域网内的操作，网络暂时不知道是不是不稳定，谢谢您的建议，先取一部分数据提交试试
@胡明hoo: 如果只有8000至10000条数据的话，应该不是数据量引起的。 你的语句为什么不把where条件写到dblink的查询中呢？
0
重新编译一下这个存储过程试一试，单独执行这个存储过程的时候可能还是用的是以前的执行计划，而现在的数据源的数据可能发生了变化，以前的执行计划可能并不是最优的执行计划了。你单独运行 SQL 查询的时候，它每次的执行计划都是根据现有的数据来选择的，所以每次应该都不一样。重新编译存储过程再看看是不是这个问题，如果可以解决这个问题的话，试着看看这个执行计划可以适应几天的数据变化。
0
看日志写了什么
0
单凭修改这个是不够的。 当时的发生卡死的环境很重要。建议你抓取服务器的performance
