求教怎么提高夸server的数据插入性能

Question

serveroracle性能

Answer 1

oracle服务器是在远程吗？ 有可能是网络速度的问题，这个得监控。

Answer 2

oracle服务器是在远程上,排除掉网络速度问题,还有其他方法可以提高性能吗?

Answer 3

10万应该很快的。秒秒钟钟的事。你从 mssql 拿10万数据到 oracle的临时表，用了多久?
判断数据是否满足条件，从临时表插入到正式表，又用了多久?你的判断语句可以看看?

Answer 4

临时表是mssql的临时表
select into #temp这样的 然后从这个临时表插入到oracle
判断也相当简单 也是在mssql上进行判断
整个过程20秒左右 然后就是插入到oracle这一步需要1小时USE [FHDW]
GO
/****** Object:  StoredProcedure [dbo].[ETL_FHDW_F_WMS_Receive_Detail_To_E10AINM]    Script Date: 04/02/2014 08:39:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROC [dbo].[ETL_FHDW_F_WMS_Receive_Detail_To_E10AINM]
AS
BEGIN
    /**************************************************************************************
    --ETL Raw data WMS_Receive_Detail into E10AINM    --创建时间：2014-03-31
    --更新时间：2014-03-31
    --创建人员：Seven.Gong
    --数据源表：
WMS_Receive_Detail

    --输出数据：
E10AINM.WTMS.F_WMS_Receive_Detail

    **************************************************************************************/
DECLARE @IntSuccessFlag INT
DECLARE @Flag INT
DECLARE @CurrentDate AS DATETIME
DECLARE @START_DATE AS CHAR(10)
DECLARE @END_DATE AS CHAR(10)

SET @CurrentDate=(SELECT CurrentDate FROM dbo.P_Basedate_Range WITH (NOLOCK))
SET @START_DATE=CONVERT(CHAR(10),@CurrentDate-90,120)
SET @END_DATE  =CONVERT(CHAR(10),@CurrentDate,120)

--1.1 判断ETL是否执行成功
SELECT @intsuccessFlag = COUNT(1)
FROM FHDW.DBO.P_Task_Log_Center WITH(NOLOCK)
WHERE StatusCode = 1
AND TaskDate = CONVERT(CHAR(10),GETDATE(),120)
AND TaskID = 3020 --1.2 抓取数据
SELECT CONVERT(CHAR(10),GETDATE(),120) AS EAI_PID,
ISNULL(A.LegalenTity,'SZFXT') AS Mandt,
B.Expense_Code AS Werks,
CONVERT(CHAR(10),A.Receive_Date,120) AS Budat,
ISNULL(A.Batch_No,'N/A') AS Mblnr,
CONVERT(CHAR(4),A.Receive_Date,120) AS Mjahr,
ISNULL(A.Receive_Detail_ID,1) AS Zeile,
REPLACE(A.WarehouseNumber,'-','') AS Lgort,
A.Product_No AS Matnr,
A.Received_QTY AS Menge,
CONVERT(CHAR(10),A.Receive_Date,120)                      AS Cpudt,
CONVERT(CHAR(8),A.Receive_Date,114) AS Cputm,
'apmt150' AS Bwart,
'' AS Smbln,
'' AS Sobkz,
CONVERT(CHAR(22),A.Receive_Date,120) AS CpuDateTime,
'' AS Valid_Ind,
CONVERT(CHAR(22),A.Receive_Date,120) AS BuDatime,
'apmt150' AS Lock_Type_Ind,
'N' AS EAI_Send_Flag,
'' AS EAI_Send_Date,
'' AS EAI_Memo
INTO #WMS_Receive_Temp
FROM F_WMS_Receive_Detail A WITH(NOLOCK)
JOIN D_Product B WITH(NOLOCK)
ON A.Product_No = B.Product_No
AND A.Receive_Date>=@START_DATE
AND A.Receive_Date< @END_DATE

IF @IntSuccessFlag = 1
BEGIN

--1.3 删除当天数据
SET XACT_ABORT ON
DECLARE @SQLSTR VARCHAR(4000);
SET @SQLSTR = 'EXEC (''BEGIN WTMS.ETL_DELETE_TABLE.D_F_WMS_RECEIVE_DETAIL('''''+@START_DATE+''''','''''+@END_DATE+'''''); END;'') AT E10AINM;' EXEC (@SQLSTR) --1.4 插入当天数据
INSERT OPENQUERY(E10AINM,'SELECT
EAI_PID,
Mandt,
Werks,
Budat,
Mblnr,
Mjahr,
Zeile,
Lgort,
Matnr,
Menge,
Cpudt,
Cputm,
Bwart,
Smbln,
Sobkz,
CpuDateTime,
Valid_Ind,
BuDatime,
Lock_Type_Ind,
EAI_Send_Flag,
EAI_Send_Date,
EAI_Memo
   FROM WTMS.F_WMS_Receive_Detail')
SELECT
EAI_PID,
Mandt,
Werks,
Budat,
Mblnr,
Mjahr,
Zeile,
Lgort,
Matnr,
Menge,
Cpudt,
Cputm,
Bwart,
Smbln,
Sobkz,
CpuDateTime,
Valid_Ind,
BuDatime,
Lock_Type_Ind,
EAI_Send_Flag,
EAI_Send_Date,
EAI_Memo
FROM #WMS_Receive_Temp

   
--1.5 插入Flag标识
INSERT OPENQUERY(E10AINM,'SELECT *
   FROM WTMS.WMS_Sync_Flag')
SELECT GETDATE(),
'F_WMS_Receive_Detail'     --1.6 删除临时表
DROP TABLE #WMS_Receive_Temp END
END

Answer 5

临时表是mssql的临时表
select into #temp这样的 然后从这个临时表插入到oracle
判断也相当简单 也是在mssql上进行判断
整个过程20秒左右 然后就是插入到oracle这一步需要1小时USE [FHDW]
GO
/****** Object:  StoredProcedure [dbo].[ETL_FHDW_F_WMS_Receive_Detail_To_E10AINM]    Script Date: 04/02/2014 08:39:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROC [dbo].[ETL_FHDW_F_WMS_Receive_Detail_To_E10AINM]
AS
BEGIN
    /**************************************************************************************
    --ETL Raw data WMS_Receive_Detail into E10AINM    --创建时间：2014-03-31
    --更新时间：2014-03-31
    --创建人员：Seven.Gong
    --数据源表：
WMS_Receive_Detail

    --输出数据：
E10AINM.WTMS.F_WMS_Receive_Detail

    **************************************************************************************/
DECLARE @IntSuccessFlag INT
DECLARE @Flag INT
DECLARE @CurrentDate AS DATETIME
DECLARE @START_DATE AS CHAR(10)
DECLARE @END_DATE AS CHAR(10)

SET @CurrentDate=(SELECT CurrentDate FROM dbo.P_Basedate_Range WITH (NOLOCK))
SET @START_DATE=CONVERT(CHAR(10),@CurrentDate-90,120)
SET @END_DATE  =CONVERT(CHAR(10),@CurrentDate,120)

--1.1 判断ETL是否执行成功
SELECT @intsuccessFlag = COUNT(1)
FROM FHDW.DBO.P_Task_Log_Center WITH(NOLOCK)
WHERE StatusCode = 1
AND TaskDate = CONVERT(CHAR(10),GETDATE(),120)
AND TaskID = 3020 --1.2 抓取数据
SELECT CONVERT(CHAR(10),GETDATE(),120) AS EAI_PID,
ISNULL(A.LegalenTity,'SZFXT') AS Mandt,
B.Expense_Code AS Werks,
CONVERT(CHAR(10),A.Receive_Date,120) AS Budat,
ISNULL(A.Batch_No,'N/A') AS Mblnr,
CONVERT(CHAR(4),A.Receive_Date,120) AS Mjahr,
ISNULL(A.Receive_Detail_ID,1) AS Zeile,
REPLACE(A.WarehouseNumber,'-','') AS Lgort,
A.Product_No AS Matnr,
A.Received_QTY AS Menge,
CONVERT(CHAR(10),A.Receive_Date,120)                      AS Cpudt,
CONVERT(CHAR(8),A.Receive_Date,114) AS Cputm,
'apmt150' AS Bwart,
'' AS Smbln,
'' AS Sobkz,
CONVERT(CHAR(22),A.Receive_Date,120) AS CpuDateTime,
'' AS Valid_Ind,
CONVERT(CHAR(22),A.Receive_Date,120) AS BuDatime,
'apmt150' AS Lock_Type_Ind,
'N' AS EAI_Send_Flag,
'' AS EAI_Send_Date,
'' AS EAI_Memo
INTO #WMS_Receive_Temp
FROM F_WMS_Receive_Detail A WITH(NOLOCK)
JOIN D_Product B WITH(NOLOCK)
ON A.Product_No = B.Product_No
AND A.Receive_Date>=@START_DATE
AND A.Receive_Date< @END_DATE

IF @IntSuccessFlag = 1
BEGIN

--1.3 删除当天数据
SET XACT_ABORT ON
DECLARE @SQLSTR VARCHAR(4000);
SET @SQLSTR = 'EXEC (''BEGIN WTMS.ETL_DELETE_TABLE.D_F_WMS_RECEIVE_DETAIL('''''+@START_DATE+''''','''''+@END_DATE+'''''); END;'') AT E10AINM;' EXEC (@SQLSTR) --1.4 插入当天数据
INSERT OPENQUERY(E10AINM,'SELECT
EAI_PID,
Mandt,
Werks,
Budat,
Mblnr,
Mjahr,
Zeile,
Lgort,
Matnr,
Menge,
Cpudt,
Cputm,
Bwart,
Smbln,
Sobkz,
CpuDateTime,
Valid_Ind,
BuDatime,
Lock_Type_Ind,
EAI_Send_Flag,
EAI_Send_Date,
EAI_Memo
   FROM WTMS.F_WMS_Receive_Detail')
SELECT
EAI_PID,
Mandt,
Werks,
Budat,
Mblnr,
Mjahr,
Zeile,
Lgort,
Matnr,
Menge,
Cpudt,
Cputm,
Bwart,
Smbln,
Sobkz,
CpuDateTime,
Valid_Ind,
BuDatime,
Lock_Type_Ind,
EAI_Send_Flag,
EAI_Send_Date,
EAI_Memo
FROM #WMS_Receive_Temp

   
--1.5 插入Flag标识
INSERT OPENQUERY(E10AINM,'SELECT *
   FROM WTMS.WMS_Sync_Flag')
SELECT GETDATE(),
'F_WMS_Receive_Detail'     --1.6 删除临时表
DROP TABLE #WMS_Receive_Temp END
END
你的意思是，慢的地方在insert openquery到oracle中是吧，照理这个不太可能啊。你用的是连接服务器吧。你试试，直接从查询这个oracle的表，如果也很慢，估计就是网络问题，如果很快，说明应该不是网速的问题

Answer 6

临时表是mssql的临时表
select into #temp这样的 然后从这个临时表插入到oracle
判断也相当简单 也是在mssql上进行判断
整个过程20秒左右 然后就是插入到oracle这一步需要1小时
你的意思是，慢的地方在insert openquery到oracle中是吧，照理这个不太可能啊。你用的是连接服务器吧。你试试，直接从查询这个oracle的表，如果也很慢，估计就是网络问题，如果很快，说明应该不是网速的问题是的 是用的linkserver 
查询也是用的select openquery,速度也不慢 3秒钟返回1W条数据的样子,这种速度是网络慢呢还是正常呢?

Answer 7

临时表是mssql的临时表
select into #temp这样的 然后从这个临时表插入到oracle
判断也相当简单 也是在mssql上进行判断
整个过程20秒左右 然后就是插入到oracle这一步需要1小时
你的意思是，慢的地方在insert openquery到oracle中是吧，照理这个不太可能啊。你用的是连接服务器吧。你试试，直接从查询这个oracle的表，如果也很慢，估计就是网络问题，如果很快，说明应该不是网速的问题是的 是用的linkserver 
查询也是用的select openquery,速度也不慢 3秒钟返回1W条数据的样子,这种速度是网络慢呢还是正常呢?
这样来看，问题应该不是在sql server上，能监控一下oracle吗

Answer 8

这样来看，问题应该不是在sql server上，能监控一下oracle吗
请问应该怎么监控呢?IO还是网络?

Answer 9

这样来看，问题应该不是在sql server上，能监控一下oracle吗
请问应该怎么监控呢?IO还是网络?我在想，在执行insert语句的时候，sql server是如何来执行的。因为数据在sql server中，但是数据需要插入到oracle，那么sql server不可能去具体执行插入数据的操作，应该是插入数据的语句，发送到oracle中，如果你有10w条数据，会不会就发送10w条insert数据到oracle中，然后性能就降低了。

Answer 10

这样来看，问题应该不是在sql server上，能监控一下oracle吗
请问应该怎么监控呢?IO还是网络?我在想，在执行insert语句的时候，sql server是如何来执行的。因为数据在sql server中，但是数据需要插入到oracle，那么sql server不可能去具体执行插入数据的操作，应该是插入数据的语句，发送到oracle中，如果你有10w条数据，会不会就发送10w条insert数据到oracle中，然后性能就降低了。sql server是发送10w次insert还是发送一条包含10w条数据的insert我就不清楚了,
所以我现在就是在想有没有什么办法可以提高性能.
比如不直接调用insert,想办法先把数据导出再发送到oracle服务器,然后在oracle服务器端进行导入.这样有可行性吗?
或者还有什么其他更优的办法?
头都大了.1小时的job时间,完全无法接受啊.让我赶紧调优.

Answer 11

这样来看，问题应该不是在sql server上，能监控一下oracle吗
请问应该怎么监控呢?IO还是网络?我在想，在执行insert语句的时候，sql server是如何来执行的。因为数据在sql server中，但是数据需要插入到oracle，那么sql server不可能去具体执行插入数据的操作，应该是插入数据的语句，发送到oracle中，如果你有10w条数据，会不会就发送10w条insert数据到oracle中，然后性能就降低了。sql server是发送10w次insert还是发送一条包含10w条数据的insert我就不清楚了,
所以我现在就是在想有没有什么办法可以提高性能.
比如不直接调用insert,想办法先把数据导出再发送到oracle服务器,然后在oracle服务器端进行导入.这样有可行性吗?
或者还有什么其他更优的办法?
头都大了.1小时的job时间,完全无法接受啊.让我赶紧调优.你试试用sql server 导出数据，然后把表里的数据导出到oracle试试，看看速度怎样

Answer 12

这样来看，问题应该不是在sql server上，能监控一下oracle吗
请问应该怎么监控呢?IO还是网络?我在想，在执行insert语句的时候，sql server是如何来执行的。因为数据在sql server中，但是数据需要插入到oracle，那么sql server不可能去具体执行插入数据的操作，应该是插入数据的语句，发送到oracle中，如果你有10w条数据，会不会就发送10w条insert数据到oracle中，然后性能就降低了。sql server是发送10w次insert还是发送一条包含10w条数据的insert我就不清楚了,
所以我现在就是在想有没有什么办法可以提高性能.
比如不直接调用insert,想办法先把数据导出再发送到oracle服务器,然后在oracle服务器端进行导入.这样有可行性吗?
或者还有什么其他更优的办法?
头都大了.1小时的job时间,完全无法接受啊.让我赶紧调优.你试试用sql server 导出数据，然后把表里的数据导出到oracle试试，看看速度怎样
我不知道这个程序应该怎么写?又要先导出,再copy过去,再在oracle那端导入.这都要写在一个ETL里怎么写啊?

Answer 13

或许要执行10W次insert, 那么与ORACLE要通讯10W次，假设一次花0.03秒（他们之间通讯有定义好的协议，远远不只是发一条命令这么简单的），那也要3000秒了。
能否换一种方式，Oracle到mssql读10w条数据，读取完了再insert到数据库中。

Answer 14

你的意思是 在oracle端进行select into的操作吗?但是从oracle到msssql读取数据的话,速度会变快吗?是否可以理解为就只需要一次通讯,剩下的只是读取数据的耗时而已?

Answer 15

如果你直接导出数据到文件然后用ORACLE自己的工具倒入速度会快多少？
你要先知道目前的问题出在那里是Ｏｒａｃｌｅ本身的插入速度有问题还是传输方面或者其他。

Answer 16

1. oracle中是否有是否有批量批量导入语句，类似于SQL server中的bulk insert。如有，改为批量插入。2. 导入的oracle 表中是否建立了索引？如有，导入数据时，需要重整索引，会很耗时，可以先暂时去掉索引。3.调整Oracle数据库文件的物理设计，将DB文件分布存储在多个盘片上，批量导入时可以并行向多个盘片同时写入数据

Answer 17

有个job是insert openquery的 每天10W条左右的数据你弄10个job每个1W条数据就行了
不行20个.每个5K条.

Answer 18

你的意思是 在oracle端进行select into的操作吗?但是从oracle到msssql读取数据的话,速度会变快吗?是否可以理解为就只需要一次通讯,剩下的只是读取数据的耗时而已?
从oracle到msssql读取10W数据，一次通讯读这些，而不是分为10W次来读，应该是比较快的。那在Oracle上插入10W数据应该也行。被插入数据的表有无触发器等。这也可能影响影响速度的。

Answer 19

如果网络没有问题的话 以下几种方法都可以
1. 生成临时文件 10万条数据 用sqlserver 生成csv也就几秒的事， oracle那边用sqlloder一次性插入
2. 用 bulk copy oracle 一次性插入，看你内存 和网络状况10 万应该没什么大问题，我500万数据大概30多秒吧另外看你插入表有无复杂de tregger 或者索引太多，不过10万数据量真的影响不是很大

Answer 20

看了上面的回帖，其实lz已经说到了一个解决办法。拷贝数据到oracle端，然后导入。拷贝过去，可以使用数据压缩。sql server数据出来，可以直接导出。这样分离各个节点，就知道哪儿是最慢的了。 基础理解，应该是传输端的问题。参考