user13555152 wrote:That certainly looks like the network is the issue. You may be able to improve query performance by increasing the SQL Array Fetch Size (Tools|Preferences|Database|Advance). However, your insert statement shouldn't be affected by the query performance.
Thanks for the reply. My Query is pulling from two different tables using a nested query.
when I tried running a select * from employee table which took a rough 40 seconds to bring results whereas when I did it in SQL Server management studio, it took around 1-2 seconds to pull the similar amount of data.So I was curious if this was because of the remote connection!?(SQl server is running on my network).
I suspect you are doing something like (pseudocode)
insert into target (x,y) select a, b from source1 inner join source2 ...
This is hugely inefficient.
for s1 in ( select * from source1 ) loop fetch (select * from source2 where source2.key=source1.key insert into ( target ) values (source1.a,source2.b) end loop
Also Can you tell me how to run the query in batches,like inserting and comitting 5000 records once and then inserting next 5000 records and so on! Is there a way doing it from any settings or do we nee do modify the query?Note that commiting like this is considered bad practice in oracle. It does not improve performance and can make it worse. Bulk inserts (without commits) can improve performance but the best improvement would be from recoding as a straight insert..select as suggested above. If you can't recode as a straight query you need to look into the use of 'bulk collect' and 'forall' in conjunction with PL/SQL collections.
user13555152 wrote:I think they are equivalent.
INSERT INTO Temp
(SELECT EmployeeNo FROM Employee e WHERE e.CaseNo = clin.CaseNo) emp,
ROW_NUMBER() OVER (ORDER BY VisitNo, CaseNo) rnum
is the above query same as:
INSERT INTO Temp
ROW_NUMBER() OVER (ORDER BY clin.VisitNo, clin.CaseNo) rnum
ClinicVisit clin left join Employee e WHERE e.CaseNo = clin.CaseNo
the query will simply be sent by sql developer to Oracle for processing. Sql developer will play no further role until the query is actually completed. So based on this example code there is no basis at all for saying:
INSERT INTO Temp SELECT VisitNo, EmployeeNo, (SELECT EmployeeNo FROM Employee e WHERE e.CaseNo = clin.CaseNo) emp, ROW_NUMBER() OVER (ORDER BY VisitNo, CaseNo) rnum FROM ClinicVisit clin ORDER BY VisitNo, CaseNo;