InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSun, 19 May 2013 16:41:47 GMT20http://www.sqlservercentral.com/Forums/Topic644083-1043-1.aspxCan you let me know what the purpose of the WHERE clause is in the destination table?Couple of more suggestions:1. In your Oracle ODBC DSN, disable LOBs if you're not using them.2. In your Oracle ODBC DSN, increase the Prefetch Count (memory permitting)3. Consider dumping the recordset to file and then kicking off a sqlcmd to kick off a batch file to import the data using sqlldr.Mon, 16 Feb 2009 09:53:13 GMTMax-146500http://www.sqlservercentral.com/Forums/Topic644083-1043-1.aspxYour exactly right... Peak periods does relate to this. however, the biggest impact is the number of records. at peak time 20 records will transfer relatively quickly compared to 2000 records at the same time.I still have no solution. and am thinking about transferring the data in one big field.Mon, 16 Feb 2009 09:06:51 GMTStewart Fayhttp://www.sqlservercentral.com/Forums/Topic644083-1043-1.aspxHi StewartI'm not really an expert but I don't recall having to specify a where clause in the openquery destination. Perhaps it is this that is causing the row level operations?Other things you could check is if it is a regular issue, does it happen at all times of the day - check off-peak performance as well.Mon, 16 Feb 2009 08:36:10 GMTMax-146500http://www.sqlservercentral.com/Forums/Topic644083-1043-1.aspxHello,Just to let people know, we have run a trace on Oracle and it shows that the ODBC does infact process the insert of each record 1 by 1. Is there a bulk insert type of 'thing' we could use with openQuery? i.e. I would like to send all 2000 records in 1 go.the problem is the 'SQL*Net message from client' always waits after each insert.Tue, 27 Jan 2009 07:26:09 GMTStewart Fayhttp://www.sqlservercentral.com/Forums/Topic644083-1043-1.aspxTo whom can help,I am using Sql Server 2000 and have a linked server to Oracle.I am doing an insert into the oracle from data on the Sql Server 2000.it is taking 26 minutes to process 2210 records. at 85 records per minute this appears to be a long time for a relatively small amount of records.Someone has mentioned that I may be inserting each record row by row instead of all 2210 at the same time. is this possible and if so how can I check.Basically I want to speed this up (hopefully using setup) and not have to give the answer to the client that their network is rubbish.Any suggestions or pushes inthe right directions would be useful.Provider using: Microsoft OLE DB Provider for Oracle (Currently Can't get Oracle Provider for OLE DB to work (error 7399 returned) should I get this working?)Query is:Insert into OpenQuery (FinPRUAT, 'Select Field1 From Table1 Where Field1 1=0') Select Field1 From Table1 Inner Join Table2 On Table2.Field1 = Table1.Field1 Where Table2.Field2 = 10001228ThanksTue, 27 Jan 2009 06:31:45 GMTStewart Fay