Load 500,000 rows in 100 minutes?

My friend is a data analyst. He frequently loads the data from Access into MS SQL Server using OLE DB. Yesterday he asked me if there is a fast way to load data. I told him to save Access data into csv, then  using bcp to insert into MS SQL Server. He said he will try bcp, since it makes him crazy to load 500,000 rows in 100 minutes using OLE DB.

Hold on a second, load 500,000 rows in 100 minutes? I am pretty sure OLE DB is not the primary reason. I suspect there are indexes and constraints on the table, which causes the problem. I suggested him to remove indexes on the table before loading the data, and re-create indexes after the load.

OK, here is my conclusion: when a user asks a question, please have a better understanding why he asks this question. That is the way of "good communication".

Performance of Lookup Function

Recently one of my developer friends experienced a performance problem. They run a PL/SQL lookup function over a table with one million rows. Every time the query failed since it reached the CPU usage limit specified by CPU_PER_CALL.

My suggestion is, DO NOT use lookup function if you can do it in SQL statement. SQL statements always have better performance then PL/SQL. Here is my example.

Create two tables,

create table scott.t as select * from dba_tables;

create table scott.u as select * from dba_users;

Now we create a lookup function, 

create function scott.get_uid(l_name in varchar2) return number

as

  ret number;

begin

  select user_id into ret from scott.u where username=l_name;

  return ret;

end;

/

alter system flush shared_pool;
alter system flush buffer_cache;

Now we run two queries,

set timing on

alter session set sql_trace=true; 

select user_id from scott.t, scott.u where t.owner=u.username;

Elapsed: 00:00:01.74

select scott.get_uid(owner) from scott.t;

Elapsed: 00:00:06.86

alter session set sql_trace=false;

Two queries return the same results. The join statement takes 1.74 seconds but the lookup function consumes 6.86 seconds. Why? Look into the trace file and we will find the lookup function is executed 2475 times!!!

********************************************************************************

SQL ID : bta262awhyx23

select scott.get_uid(owner)

from

 scott.t

call     count       cpu    elapsed       disk      query    current        rows

------- ------  -------- ---------- ---------- ---------- ----------  ----------

Parse        1      0.00       0.00          0          0          0           0

Execute      1      0.00       0.00          0          0          0           0

Fetch      166      0.96       2.80          0        242          0        2475

------- ------  -------- ---------- ---------- ---------- ----------  ----------

total      168      0.96       2.81          0        242          0        2475

Misses in library cache during parse: 0

Optimizer mode: ALL_ROWS

Parsing user id: SYS

Rows     Row Source Operation

-------  ---------------------------------------------------

   2475  TABLE ACCESS FULL T (cr=242 pr=0 pw=0 time=68 us cost=25 size=41854 card=2462)

********************************************************************************

SQL ID : 574gd4b0c2rxr

SELECT USER_ID

FROM

 SCOTT.U WHERE USERNAME=:B1

call     count       cpu    elapsed       disk      query    current        rows

------- ------  -------- ---------- ---------- ---------- ----------  ----------

Parse        1      0.00       0.00          0          0          0           0

Execute   2474      0.14       0.61          0          0          0           0

Fetch     2474      0.28       1.96          0       7422          0        2474

------- ------  -------- ---------- ---------- ---------- ----------  ----------

total     4949      0.42       2.57          0       7422          0        2474

Misses in library cache during parse: 0

Optimizer mode: ALL_ROWS

Parsing user id: 81     (recursive depth: 1)

Rows     Row Source Operation

-------  ---------------------------------------------------

      1  TABLE ACCESS FULL U (cr=3 pr=0 pw=0 time=0 us cost=3 size=17 card=1)

********************************************************************************