Home / black and indian dating / Updating millions of rows bulk collect

Updating millions of rows bulk collect

Jonathan: I think it’s a great move by Microsoft since the open source community expands very fast.

It’s always interesting as a programmer to see the evolution of a project and how people from Microsoft are coding.

A note to our readers: You asked so we have developed a set of features that allow you to reduce the noise: you can get email and web notifications for topics you are interested in. When a database administrators think of high performance data loading, they are thinking of bulk operations, a feature noticeably lacking in Entity Framework. We recently spoke with Jonathan Magnan of ZZZ Projects about their new offerings.

Info Q: Developers can already tell Entity Framework to upload a bunch of records all at once. Jonathan Magnan: Simple: for HUGE performance gains.

Consider the following two tables: You have been tasked with updating the employee salaries in the EMPLOYEES table with the maximum salary for the same employee, based on EMPLOYEE_ID, in the NEW_SALARY_INFO table. There is no index on the NEW_SALARY_INFO table so the database will have to full scan a four million row table for each of the one million employee records to find the maximum salary.

If no record exists in the NEW_SALARY_INFO table for an employee, that employee’s data should remain the same. We’ll just use an UPDATE statement to modify the rows in the EMPLOYEES table: UPDATE employees e SET employee_salary = ( SELECT MAX(salary) FROM new_salary_info nsi WHERE nsi.employee_id = e.employee_id ) WHERE EXISTS ( SELECT NULL FROM new_salary_info nsi2 WHERE nsi2.employee_id = e.employee_id ); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |Temp Spc| Cost (%CPU)| Time | -------------------------------------------------------------------------------------------- | 0 | UPDATE STATEMENT | | 999K| 17M| | 2772M (2)| 9| | 1 | UPDATE | EMPLOYEES | | | | | | |* 2 | HASH JOIN SEMI | | 999K| 17M| 23M| 7887 (1)| | | 3 | TABLE ACCESS FULL| EMPLOYEES | 1000K| 12M| | 690 (2)| | | 4 | TABLE ACCESS FULL| NEW_SALARY_INFO| 3996K| 19M| | 2764 (2)| | | 5 | SORT AGGREGATE | | 1 | 13 | | | | |* 6 | TABLE ACCESS FULL| NEW_SALARY_INFO| 4 | 52 | | 2772 (2)| | -------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("NSI2"."EMPLOYEE_ID"="E"."EMPLOYEE_ID") 6 - filter("NSI"."EMPLOYEE_ID"=: B1) Hmm, that doesn’t look very promising at all. Well, SQL is pretty awesome, so there must be another way to do this. UPDATE ( SELECT e.employee_id, e.employee_salary, NVL(( SELECT MAX(nsi.salary) FROM new_salary_info nsi WHERE e.employee_id = nsi.employee_id ), e.employee_salary) AS new_sal FROM employees e ) SET employee_salary = new_sal; PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------- | 0 | UPDATE STATEMENT | | 1000K| 12M| 2772M (2)|9 | | 1 | UPDATE | EMPLOYEES | | | | | | | 2 | TABLE ACCESS FULL | EMPLOYEES | 1000K| 12M| 690 (2)| | | 3 | SORT AGGREGATE | | 1 | 13 | | | |* 4 | TABLE ACCESS FULL| NEW_SALARY_INFO | 4 | 52 | 2772 (2)| | ------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 4 - filter("NSI"."EMPLOYEE_ID"=: B1)MERGE INTO employees e USING ( SELECT e.rowid AS e_rowid, MAX(nsi.salary) AS new_sal FROM employees e, new_salary_info nsi WHERE e.employee_id = nsi.employee_id GROUP BY e.rowid ) new_salaries ON (e.rowid = new_salaries.e_rowid) WHEN MATCHED THEN UPDATE SET e.employee_salary = new_salaries.new_sal; Curses! It turns out there is a VPD security policy on the EMPLOYEES table that prevents us from using the MERGE. All of our attempts at using SQL have been stymied. By making use of bulk processing in PL/SQL we can perform our task rather quickly and with minimal code.

This method supports all kinds of associations and entity types (TPC, TPH, and TPT).

I fully endorse this approach and both use and reiterate it daily to my teams.Jonathan: Entity Framework performs a round trip to the database for every record it saves.If you have 1,000 entities to update, you will have 1,000 round trips to the database that will execute an update statement and you’ll sit there for a few seconds.ZZZ Projects offers 2 kinds of bulk operations via the Entity Framework Extensions Library.They increase drastically your performance over the Save Changes method from Entity Framework.The Oracle bulk collect tells the SQL engine to fetch multiple rows at once and store them in a collection defined by the developer. Now we can turn that query into a PL/SQL cursor, process the results in bulk, and perform the required update using a FORALL statement.With the addition of the use of the FORALL statement, this greatly reduces the amount of context switching between PL/SQL and SQL by sending DML statements from PL/SQL to SQL in batches rather than one at a time, resulting in dramatic performance improvements over row-by-row processing. Incidentally, the above query returns 999,000 rows.Even if your application isn’t suffering much from this low performance speed, you may impact other applications performances. Our library, like Entity Framework, needs to gather and cache information the first time you use it based on entities and their relations.Even with one row, we do as well as Entity Framework.PL/SQL and SQL both are incredibly powerful languages that allow us to do some pretty amazing things with data.If you’ve been around the Oracle community for some time you’ve probably realized, either through your own experience or by reading articles, if you are able to accomplish a task using a single SQL statement then that is preferred over using PL/SQL and SQL both are incredibly powerful languages that allow us to do some pretty amazing things with data.

799 comments

  1. Can PL/SQL be Faster than SQL. having 1,000,000 rows and approximately. The Oracle bulk collect tells the SQL engine to fetch multiple rows at once and.

  2. Hi, We have a requirement to do a bulk update in few of the tables in oracle. The data volumes are in the range of 30-40 million. We have tried introducing indexes.

  3. Updating Millions Of Rows In A Batch. through bulk collect. There is a table in Database with millions of records and a query --- Select rowid.

  4. For a 9.2.0.5 database, I have been asked to add few columns and update them with new values from another table. Base table contained 35 million rows. To.

  5. Update very large Oracle table tips Search BC Oracle Sites Home E-mail Us. Question I need to run a large update against a table with 300 million rows.

  6. PL/SQL Optimization and Tuning. ways to limit the number of rows that a SELECT BULK COLLECT INTO statement. whenever you are updating a lot of.

Leave a Reply

Your email address will not be published. Required fields are marked *

*