Improve performance of stored procedure

-
PL/SQL performance

PL/SQL it self is used to improve the performance. In application, most of logic implemented in java having communication with database to perform database operations. Database operation from java code for more number of iteration is not a good practice. It is always advisable, if the database call are performed under large number of iteration then move that logic into PL/SQL and perform those logic there. Significant performance can be achieved by doing this. While doing code transforming from java code into PL/SQL, developer may face number of difficulties.

Difficulty:
Q. How to pass array of two , array of three from java to PL/SQL?
Ans:- Oracle supports custom data types, where developer can create custom data type. The custom data type in the form of array of two ( i.e. pair of two strings / number ) , array of three ( triplate of  string / number ). Once those custom data type are defined in the oracle database then developer needs to form collection matching to those custom data type to send data from java code.

Q. use of "with" key word in Pl/SQL?
Ans:- "with" key word takes an extra memory to store the fetched data, resulting into slowness of PL/SQL execution. Instate of that developer can use the local variable and use that to increase the performance.

Q. Declaration of loop.
Ans :- it always advisable to declare the variables out side the loop to avoid the multiple declaration.

Passing a PL/SQL block containing multiple statements to the server can reduce network round trips, thereby improving performance.

Most of  the times developer needs to check data present using "IN" clause, instate of that "exist" clause is much faster.

The use of "decode" result into performance improvement and less code to write :).

Comments

Post a Comment

Popular posts from this blog

Custom DataType in Oracle Database.

-
Oracle provides the predefined types which developer can use in the PL/SQL. Those types are like NUMBER, ARRAYOFNUMBERS, VARCHAR2 and many more.  Developer who consumes PL/SQL from JAVA can easily use those types in case of normal scenario. But consider a scenario where, developer want to send collections to PL/SQL, then how that would be possible. So, oracle have provided an option to create own custom types as per requirement and use that types in PL/SQL. That custom type support essay access to data types from JAVA and speed up execution and developer can achieve performance too. Syntax of creation of custom type:- #1 TABLEOF3STRINGS:- create or replace TYPE "TRIPLETOFSTRINGS" as object (field1 varchar2(20), field2 varchar2(20), field3 varchar2(20)); create or replace TYPE "TABLEOF3STRINGS" as table of TripletOfStrings; #2 Structure with number field:- create or replace TYPE "LINEITEM" as object ( item_name varchar2(30), quanti
Read more

Reverse String, Unchanged position of special characters.

-
Reverse a string keeping the position of special characters unchanged in reversed string. ex.          i/p -     a,b#d$e&d          o/p -     d,e#d$b&a Solution :- static boolean isAlphabet(char x) {         return (x >= 'A' && x <= 'Z' ) || (x >= 'a' && x <= 'z' );     }         public static void main(String[] args) {                 String s = "Ab,c,de!$";//a,b$c";         System.out.println("Original String:- " + s);         char[] str = s.toCharArray();         int l = 0;         int r = s.length() - 1;                 while (l<r) {             if(!isAlphabet(str[l])) {                 l++;             }else if(!isAlphabet(str[r])) {                 r--;             }else{                 char a = str[l];                 char b = str[r];                                 str[l] = b;                 str[r] = a;                                 l++;          
Read more

Java 8 Questions.

-
what is functional interface. Ans :- An interface defined with only on abstract method. That interface can have multiple default and static methods. Supplier, consumer and predicate are the functional interface provided by 8. Supplier FI can used as factory pattern to create object using get method of that interface. i.e. import jdk.nashorn.internal.objects.annotations.Setter; import java.util.UUID; @Setter public class Car { UUID id; String name; } public class MarutiCar extends car { } import jdk.nashorn.internal.objects.annotations.Setter; import java.util.UUID; import java.util.function.Supplier; public class CarSupplier { private static <T extends Car> T getCar( final Supplier<T> supplier, final String name) { T car = supplier.get(); car.setId(UUID.randomUUID()); car.setName(name); return car; } public static void main(String args[]) { MarutiCarr car = getCar(MarutiCar:: new , "Maruti C
Read more