Boost performance JAVA EJB

Using EJB (entity java beans) for enterprise client server application may be a good idea.

but it is highly important to divide your code into many EJB's as you can , for each EJB class being responsible for its logical functionality.  

But doing the above can result in a performance issue , because jumping and calling many time to the EJB server from the client code takes time.

for example having this EJB:

@remote

MyEjb

..

..

public List<Object> getObject1()

{

return new ArrayList<Object>();

}

public List<Object> getObject1()

{

return new ArrayList<Object>();

}

Client code:

public static void main (String []args)

{

List<Object> list1 = MyEjb.getObject1();

List<Object> list2 = MyEjb.getObject1();

}

we have here 2 calling and to the EJB server. which takes time....

and time is something that we want to save.

A Much better option would be:

@remote

MyEjb

..

..

public Pair<List<Object>,List<Object>> getObjects()

{

return new Pair<List<Object>,List<Object>>( new ArrayList<Object>,  new ArrayList<Object>);

}

Client code:

public static void main (String []args)

{

Pair pair = MyEjb.getObjects();

//and then retrieve the data from the pair.

}

this is a better option!!

*please note: this is a sample code , calling the EJB server must be done via the interface.

Hibernate updating rows when selecting?

Assume you have an entity that is mapped to your db with Hibernate.

when you are trying only to select this entity , you see many update sentences running in your server console.

this is a very bad thing because update may take time and to result in unwanted behavior.

please note that the updates are actually meaningless because they update the same value over and over again.

your console may result in:

select * ... from EMP

{10 rows being selected}

update  EMP set ..

update  EMP set ..

update  EMP set ..

.

.

.

10 times.

this is happening because your entity has logic of different filed in the setter.

for example:

class EMP

{

mapped..

 int a;

int b ;

getA()

grtB()

setA()

setB()

{

 setA(something);

}

} 

because the B filed has logic with the A filed ,in B's setter the problem occurs.

this is happening because hibernate loads data into setters when performing select , and then thjins that the entity has changed because of the setB().

to fix it : remove the extra logic on A field from setB().

What is a transaction?

When you log into your database (with sqlplus or any other DB tool), you assign to a Session.
For each log in, a new session will be created.

On that session you can make your DML and DDL commands, and that session is private for the user logged in.

Oracle, and some other DBs, work in a transaction-based mode.
A transaction is a set of DML commands that hasn't been executed for the entire DB but only for the user's session.
To close a transaction you use either -
COMMIT: for saying - OK, I want those changes
ROLLBACK: for omitting the changes on the data.

This allows you to perform multiple DML commands and preview the result in your private session before publishing it.

A session can have only one active transaction (See more here) and a rule of thumb says that you should not leave an open transaction. Every command (or commands set) should be finished with a Commit or Rollback.

Oracle works in a 'Rollback segment' method which is very important to understand:

When a session performs a DML, the data is being changed on the table! It doesn't wait for a commit.
But it marks those affected rows with a flag means they are not updated.
The opposite command is being saved as well.
Lets say we Inserted 30 rows, those rows has been actually inserted into the table, and a 30-rows-delete-clause has been created.

When the transaction is still open, and other sessions query that data, oracle will perform that reverse command and show those sessions that data BEFORE that change.

After a commit, those affected rows are marked as updated and the transaction is terminate.
After a rollback, on the other hand, Oracle will use the reverse script to "fix" the non-wanted rows.

Now you can understand several things:

1) Why Commit takes zero time (Oracle thought that commit is more common than rollback).
2) Why Rollback takes a lot of time.
3) Why deleting a large amount of rows without using commit each 100 rows is a bad thing.
4) Why leaving an open transaction can cause problems.

Autonomous Transactions

There are cases when you have to run a large script, that will influence your data (DML Only) but the situation of half work is impossible due to data consistency.

In Oracle it's very easy because when you make a change in your data (Insert, Update, Delete, Merge), Oracle will create a new transaction.
The changes will be limited to your session only (Read more in our Transaction post).

After running the first DML command, the following DMLs will stack in that transaction created for the first command, and all the changes are local in your session.

Transaction is terminate whether a Commit Or a Rollback is execute.
(DDL commands Commit the transaction).


Getting back to our script, This format is suitable:


BEGIN
<DML Command>
<DML Command>
<DML Command>
<DML Command>
<DML Command>
<DML Command>

COMMIT;

EXCEPTION WHEN OTHERS THEN
     ROLLBACK;
END;


This will ensure the script will either run completely or not at all.
But what if we want to keep track on the script? and report to a log table for each successful DML? (For example- how many rows were affected).
Ofcourse we can use the dopl (dbms_output.print_line()) and write directly to the console,  but that is too temporary.

Let's create a new Table for logging our script:

CREATE TABLE script_log (execute_time DATE,
                         dml_name VARCHAR2(100));

And now, we'll just insert the information to the log table.

Can you see the problem?
This insertion to the log table is a DML command itself, therefore it will be roll-backed each time an error occurred somewhere in the script and we lost the information.

We want a mechanism that allows us to use Commit on certain commands.

And thankfully Oracle supply this kind of mechanism  called - Autonomous Transaction.
This transaction is a child transaction of the original one, and can be committed without effecting the host transaction.
You can easily create a pl/sql paragraph that is autonomous like that:

DECLARE
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
    INSERT INTO sciprt_log(execute_time, dml_name)
    VALUES (sysdate, 'Insert into Employees');
  COMMIT;
END;

Or even create an autonomous function/procedure but remember that- an autonomous section has to end either with a commit or a rollback clause. You can leave that transaction open!

Good luck!

Analytic dilemma: row_number vs rank vs dense_rank

Row_number() , rank(), dense_rank()

                                                                                             

So what are the differences between those functions? And for which purpose they are used for

create table bank_customers (

id number,

full_name varchar2(100),

birth_date date);

create table current_account (

customer_id number,

action varchar2(100),

amount number,

action_date date);

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

Table created.

Table created.

insert into bank_customers values (1, 'Tal Avinari', sysdate);

insert into bank_customers values (2, 'Daniel Kagan', sysdate);

insert into bank_customers values (3, 'Ben David', sysdate);

insert into bank_customers values (4, 'Alex Rubin', sysdate);

insert into current_account

values (1, 'deposit', 100, to_date('01/01/2013', 'dd/mm/yyyy'));

insert into current_account

values (1, 'withdrawal', 230, to_date('01/01/2013', 'dd/mm/yyyy'));

insert into current_account

values (1, 'withdrawal', 100, to_date('02/01/2012', 'dd/mm/yyyy'));

insert into current_account

values (2, 'withdrawal', 1000, to_date('03/01/2012', 'dd/mm/yyyy'));

insert into current_account

values (2, 'deposit', 1060, to_date('25/05/2012', 'dd/mm/yyyy'));

insert into current_account

values (3, 'withdrawal', 560, to_date('25/01/2013', 'dd/mm/yyyy'));

insert into current_account

values (1, 'deposit', 670, to_date('01/01/2012', 'dd/mm/yyyy'));

select b.full_name, a.action_date, a.amount, a.action

from current_account a,

bank_customers b

where a.customer_id = b.id;

FULL_NAM   ACTION_DATE                                                                         AMOUNT ACTION
-------------------- ------------------ ---------- --------------------
Tal Avinari     01-JAN-12                                                                                          100 deposit
Tal Avinari     01-JAN-13                                                                                          100 deposit
Tal Avinari     01-JAN-13                                                                                          230 withdrawal
Tal Avinari     02-JAN-12                                                                                          100 withdrawal
Daniel Kagan     03-JAN-12                                                                                     1000 withdrawal
Daniel Kagan     25-MAY-12                                                                                  1060 deposit
Ben David     25-JAN-13                                                                                           560 withdrawal
Tal Avinari     01-JAN-12                                                                                          670 deposit

8 rows selected.

Let’s start with a little motivation, assuming we want to show all the bank customers and their last account action.

The analytic function row_number() is a good solution, let’s see how it works:

 select row_number() over (partition by b.id order by a.action_date desc) as row_number,b.full_name, a.action_date,a.amountfrom current_account a,bank_customers bwhere a.customer_id = b.id; 

ROW_NUMBER FULL_NAME              ACTION_DATE        AMOUNT

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

             1 Tal Avinari               01-JAN-13                      230

             2 Tal Avinari               01-JAN-13                      100

             3 Tal Avinari               02-JAN-12                      100

             4 Tal Avinari               01-JAN-12                      670

             5 Tal Avinari               01-JAN-12                      100

             1 Daniel Kagan           25-MAY-12                  1060

             2 Daniel Kagan           03-JAN-12                    1000

So the function actually partitions the table by the customer id and counts the rows one by one according to the order by clause. So the final step is to filter it to rownumber = 1 and we’ve finished!

Unfortunately, in our case we have 2 account actions for Tal at the same date, so if we take the first rownumber we’ll lose information.

Rank() is the answer for that !

Let’s have a look

select rank() over (partition by b.id order by a.action_date desc) as rank,

b.full_name, 

a.action_date,

a.amount

from current_account a,

bank_customers b

where a.customer_id = b.id;

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

RANK         FULL_NAME                      ACTION_DATE        AMOUNT

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

             1 Tal Avinari  01-JAN-13                      230

             1 Tal Avinari   01-JAN-13                      100

             3 Tal Avinari   02-JAN-12                      100

             4 Tal Avinari   01-JAN-12                      670

             4 Tal Avinari   01-JAN-12                      100

             1 Daniel Kagan 25-MAY-12               1060

             2 Daniel Kagan 03-JAN-12                 1000

             1 Ben David    25-JAN-13                      560

8 rows selected.

Notice that after the two rows of rank = 1 we have the next rank, =3.

If we don’t want this gap then we use the dense_rank() function.

  

select dense_rank() over (partition by b.id order by a.action_date desc) as dense_rank,

b.full_name, 

a.action_date,

a.amount

from current_account a,

bank_customers b

where a.customer_id = b.id;

DENSE_RANK FULL_NAME                 ACTION_DATE        AMOUNT

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

             1 Tal Avinari  01-JAN-13                      230

             1 Tal Avinari   01-JAN-13                      100

             2 Tal Avinari   02-JAN-12                      100

             3 Tal Avinari   01-JAN-12                      670

             3 Tal Avinari   01-JAN-12                      100

             1 Daniel Kagan 25-MAY-12               1060

             2 Daniel Kagan 03-JAN-12                 1000

             1 Ben David    25-JAN-13                      560

8 rows selected.

Hope you enjoyed it.

Happy new year !

  

Nice little SQL tricks #1

Here are some nice little SQL tricks:

1) Filter or not

Sometimes you want to get an input from the user and use that value for filtering your query.

Let's say:

SELECT count(1) 
FROM emp
WHERE dept_id = :input;

And that's fine as long as the user obligated to choose a specific department.
We want to give him the option of selecting 'All departments', in the same query.

The best way to do so is:
(without using "or" or "union all")

SELECT count(1) 
FROM emp
WHERE nvl(:input, dept_id) = dept_id;

While the application sends NULL whenever 'all departments' has been chosen.

2) Dynamic table

If you want to dynamically create a table that contains numbers between 1 to 50 (or any other natural number), try this one:

SELECT level as num
FROM dual
CONNECT BY level < 50; 

This trick comes in hand a lot in data migration, when you have one row need to be splitted into several rows. (Just make a Cartesian product between the original and the dynamic).

For example, the following query produces 3 rows for each employee in the table:

SELECT emp_id, num

FROM emp, (select level as num from dual connect by level < 4);

3) Paging mechanism

If you have a need for taking only rows 20-30 (like if you're task involves paging)

you can use the row_number analytic function which in this case, order the result in your column(s) choice and give each row a unique number in that order.

In the following example, we show 10 employees, ordered by their hire date, starting with the 20th employee:

SELECT * FROM

(

      SELECT e.*, row_number() over (order by date_hire desc) as rwnm

      FROM emp

)

WHERE rwnm BETWEEN 20 AND 30;

More tricks soon!

Tutorial: How to return a table

Oracle lets you create a function that returns a table.

This can be useful in some situations like creating a view that receives arguments or creating a dynamic table.

In this tutorial, I'll show you how to create a function that returns the Fibonacci series.

-If you have an idea how to do that without functions and/or other tables, please comment-

First, create the type of row that we want to return:

CREATE TYPE t_fibunacci_row AS OBJECT 

(

 id           NUMBER,

 value        NUMBER

)

Create an array of the previous type:

CREATE TYPE t_fibunacci_array IS TABLE OF t_fibunacci_row

Now the function will look like this (Don't forget to use the PIPELINED clause):

CREATE OR REPLACE FUNCTION make_fibunacci (p_limit IN NUMBER) 

                  RETURN t_fibunacci_array PIPELINED IS

num_a number;

num_b number;

BEGIN

num_a := 0;

num_b := 1;

  FOR i IN 1 .. p_limit LOOP

    -- This is the important command

    PIPE ROW(t_fibunacci_row(i, num_a));   

    

  num_b := num_a + num_b;

  num_a := num_b - num_a;

    

  END LOOP;

  RETURN;

END;

/

The Pipe row command will return a t_fibunacci_row type each time the outer query that uses this function will need the next row.

Using the pipe row grantee that the function is running parallel to the main query,

and doesn't wait for every object in the returning result to be calculate.

whenever a record is "ready" it will be piped to the main query.

Using this function is easy:

SELECT * FROM table(make_fibunacci(10));

ID     VALUE

=== =====

1        0

2        1

3        1

4        2    

5        3

6        5

7        8

8        13

9        21

10      34

This function acts like a view in all situations so you can easily join it with other tables, create a view depending on it and more.

You cannot index this function however, as you cannot index a view.