C#调用Oracle数据库存储过程的速度很慢,直接在plsql里面执行很快

Sfengj 2016-01-15 03:30:13
我用C#写了个程序执行Oracle的存储过程,但是执行的很慢往往要8,9个小时,而我在plsql里面执行这个存储过程时,只需3,4个小时就完了,这是为什么呢?
C#代码:

try{
OleDbServerDAL.DbHelperOleDb.RunProcedure("DATAIMPORT");
}
catch (Exception ex)
{
SystemLog.WriteLog(ex.Message);
}

/// <summary>
/// 执行存储过程--不带参数执行存储过程
/// </summary>
/// <param name="storedProcName">存储过程名</param>
/// <returns>DataSet</returns>
public static void RunProcedure(string storedProcName)
{
using (OleDbConnection connection = new OleDbConnection(connectionString))
{
connection.Open();
OleDbCommand command = connection.CreateCommand();
command.CommandType = CommandType.StoredProcedure;
command.CommandText = storedProcName;
command.ExecuteNonQuery();
connection.Close();
}
}

求各位帮忙看一下,谢谢
...全文
1856 10 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
10 条回复
切换为时间正序
请发表友善的回复…
发表回复
江南小鱼 2016-02-19
  • 打赏
  • 举报
回复
执行个存储过程要八九个小时,这是多大的数据量啊?在执行过程中,数据库连接一直占用着,这不是浪费资源么?! 另外,在pl/sql连续执行,某些语句是有缓存的,比起程序调用执行,会快。
  • 打赏
  • 举报
回复
单纯执行语句这部分差别应该不大,可以看下其他地方是不是有浪费资源咯》》??
joyweimao 2016-02-19
  • 打赏
  • 举报
回复
1、存储过程重新编译一下 2、存储过程里语句里涉及的索引可以注意一下,有可能是索引没有生效引起的,可以指定索引
秋的红果实 2016-02-19
  • 打赏
  • 举报
回复
贴出存储过程sql语句,以及不用存储过程的sql语句
吉普赛的歌 2016-02-19
  • 打赏
  • 举报
回复
其实是可能的, SQL Server 里这种情况都是少见不鲜。 把存储过程重新编译一下就好了。
xuzuning 2016-02-19
  • 打赏
  • 举报
回复
待处理的数据规模不同,处理时所需的事件自然也不同 如果经 8,9个小时 处理结束后,立即再处理还要 3,4个小时 的话,你应该考虑一下你的存储过程是否写的有问题了
  • 打赏
  • 举报
回复
的代码只有一条 command.ExecuteNonQuery() 语句而已,剩下的执行过程,全都是你的 Oracle 数据库内部的问题。 除非你的 storedProcName 跟 plsql 所调用的不是同一个。
  • 打赏
  • 举报
回复
应用程序只是用来发送命令的,不是用来执行存储过程的。 如果你说你的 RunProcedure 可能比 “plsql里面执行”慢上1秒钟,我或许可以理解;如果说慢上5个小时,完全不可能理解。
Sfengj 2016-02-18
  • 打赏
  • 举报
回复
哎哟,没人理
Sfengj 2016-01-15
  • 打赏
  • 举报
回复
这个是把数据从一个数据库导入到另一个数据库的
Oracle P/L SQL实现FTP上传、下载功能,以下是此过程包的头部,包体经常打包处理plb,感兴趣用户可以下载下来。 --Oracle上的FTP功能 Create or Replace Package UTL_FTP AUTHID CURRENT_USER as Type Connection is Record( Connection UTL_TCP.Connection, AccountInfo VarChar2(1000), TransferMethod Char(1), --A: ASCII, E: EBCDIC, I: IMAGE TransferOption Char(1), LocalDirectory VarChar2(30), LastReply VarChar2(32767 ) ); Type File_List is Table of VarChar2(32767) Index by Binary_Integer; is_FTPStatus VarChar2(800) := 'disconnect'; is_FTPPort Constant Integer := 21; is_TransferMethod Constant VarChar2(10) := 'ASCII'; ii_OutputLog Constant Integer := 1; ii_RollBufferLog Constant Integer := 2; ii_ClientInfoLog Constant Integer := 4; -- Per RFC 959, if account info ( ACCT ) is requested Then a 332 code -- should be Returned from the PASS command instead of a Positive Completion ii_FTPRequestAcct Constant Integer := 332; gb_Verbose Boolean := False; --是否记录冗长、累赘的日志 gi_LogOptions Integer := ii_OutputLog; gs_LogText VarChar2(32767) := Null; Procedure p_SetVerbose( ab_Verbose in Boolean ); Procedure p_SetLogOptions( ai_LogOptions in Integer ); Procedure p_ClearLog; --登录到远程FTP服务器 Function f_Login( as_RemoteHost in VarChar2, as_Username in VarChar2, as_Password in VarChar2, as_LocalDirectory in VarChar2 Default Null, as_RemoteDir in VarChar2 Default Null, as_TransferMethod in VarChar2 Default is_TransferMethod, ai_Timeout in Integer Default Null, ai_FTPPort in Integer Default is_FTPPort, as_AccountInfo in VarChar2 Default Null )Return Connection; Procedure p_Logout( ac_Connection in out Nocopy Connection ); Procedure p_SendFTPCmd( ac_Connection in out Nocopy Connection, as_Command in VarChar2, as_Argument in VarChar2 Default Null, as_AccountInfo in VarChar2 Default Null ); Procedure p_ReadReply( ac_Connection in out Nocopy Connection ); Procedure p_Rename( ac_Connection in out Nocopy Connection, as_OldFilename in VarChar2, as_NewFilename in VarChar2 ); Procedure p_DeleteFile( ac_Connection in out Nocopy Connection, as_Filename in VarChar2 ); Function f_isDirectory( ac_Connection in out Nocopy Connection, as_Directory in VarChar2, ab_CDToo in Boolean Default True )Return Boolean; Procedure p_CreateDirectory( ac_Connection in out Nocopy Connection, as_Directory in VarChar2 ); Procedure p_DeleteDirectory( ac_Connection in out Nocopy Connection, as_Directory in VarChar2 ); Procedure p_SetTransferMethod( ac_Connection in out Nocopy Connection, as_TransferMethod in VarChar2, as_Option in VarChar2 Default Null ); Procedure p_RemoteCD( ac_Connection in out Nocopy Connection, as_Directory in VarChar2, ab_CreateDir in Boolean Default True ); Procedure p_RemoteCDup( ac_Connection in out Nocopy Connection ); Function f_RemotePWD( ac_Connection in out Nocopy Connection )Return VarChar2; Procedure p_PutClob( ac_Connection in out Nocopy Connection, ac_LocalClob in Clob, as_RemoteFilename in VarChar2, as_TransferMethod in VarChar2 Default Null ); Function f_PutClob( ac_Connection in out Nocopy Connection, ac_LocalClob in Clob, as_RemoteFilename in VarChar2, as_TransferMethod in VarChar2 Default Null )Return VarChar2; Procedure p_PutBlob( ac_Connection in out Nocopy Connection, ab_LocalBlob in BLOB, as_RemoteFilename in VarChar2, ab_ForceBinary in Boolean Default True --强制为二进制 ); Procedure p_GetClob( ac_Connection in out Nocopy Connection, as_RemoteFilename in VarChar2, ac_LocalClob in out Nocopy Clob, as_TransferMethod in VarChar2 Default Null ); Function f_GetClob( ac_Connection in out Nocopy Connection, as_RemoteFilename in VarChar2, as_TransferMethod in VarChar2 Default Null )Return Clob; Procedure p_GetBlob( ac_Connection in out Nocopy Connection, as_RemoteFilename in VarChar2, ab_LocalBlob in out Nocopy BLOB, ab_ForceBinary in Boolean Default True ); Function f_GetBlob( ac_Connection in out Nocopy Connection, as_RemoteFilename in VarChar2, ab_ForceBinary in Boolean Default True )Return BLOB; Procedure p_PutFile( ac_Connection in out Nocopy Connection, ai_LocalFilename in UTL_File.File_Type, as_RemoteFilename in VarChar2, as_TransferMethod in VarChar2 Default Null ); Procedure p_PutFile( ac_Connection in out Nocopy Connection, as_LocalDirectory in VarChar2, as_LocalFilename in VarChar2, as_RemoteFilename in VarChar2, as_TransferMethod in VarChar2 Default Null ); Function f_PutFile( ac_Connection in out Nocopy Connection, as_LocalDirectory in VarChar2, as_LocalFilename in VarChar2, as_RemoteFilename in VarChar2, as_TransferMethod in VarChar2 Default Null )Return VarChar2; Procedure p_PutFile( ac_Connection in out Nocopy Connection, as_LocalDirectory in VarChar2, as_LocalFilename in VarChar2 ); Procedure p_GetFile( ac_Connection in out Nocopy Connection, as_RemoteFilename in VarChar2, as_LocalDirectory in VarChar2, as_LocalFilename in VarChar2, as_TransferMethod in VarChar2 Default Null ); Procedure p_GetFile( ac_Connection in out Nocopy Connection, as_RemoteFilename in VarChar2, as_TransferMethod in VarChar2 Default Null ); Procedure p_GetFile( ac_Connection in out Nocopy Connection, as_RemoteFilename in VarChar2, ai_LocalFilename in out Nocopy UTL_File.File_Type, as_TransferMethod in VarChar2 Default Null ); Procedure p_GetFileList( ac_Connection in out Nocopy Connection, afl_List out File_List, as_RemotePath in VarChar2 Default Null, ab_FilenameOnly in Boolean Default True, as_FilenamePrefix in VarChar2 Default Null, as_FilenameExt in VarChar2 Default Null, as_TransferMethod in VarChar2 Default is_TransferMethod ); Function f_GetFileList( ac_Connection in out Nocopy Connection, as_RemotePath in VarChar2 Default Null, ab_FilenameOnly in Boolean Default True, as_FilenamePrefix in VarChar2 Default Null, as_FilenameExt in VarChar2 Default Null, as_TransferMethod in VarChar2 Default is_TransferMethod )Return File_List; --根据FTP参数或系统事先设定好的IP登录到FTP服务器 --Select UTL_FTP.f_ConnectFTP() From dual; Function f_ConnectFTP( as_RemoteSubDir in VarChar2 Default Null, --Remote Subdirectory as_RemoteFileWildcard in VarChar2 Default Null, --Remote File Wildcard --删除之前生成的文件 如I02-UB*.xls as_FTPServer in VarChar2, --FTP Server as_FTPUserID in VarChar2, --FTP User ID as_FTPPasswd in VarChar2 --FTP Password )Return UTL_FTP.Connection; END UTL_FTP; /

111,097

社区成员

发帖
与我相关
我的任务
社区描述
.NET技术 C#
社区管理员
  • C#
  • AIGC Browser
  • by_封爱
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告

让您成为最强悍的C#开发者

试试用AI创作助手写篇文章吧