SQL Best Practices for Query Performance

Query performance is not a one-time consideration. You should consider it throughout the design, development, and production phases of the application development life cycle.

Query Coding General Guidelines:

Example:

LINE1: SELECT ISPFOLIO.ISACCOUNTNO, ISPFOLIO.FUNDCODE, FUND.NAME, ISCUST.ISACCOUNTNAME
LINE2: FROM ISPFOLIO, ISCUST, FUND
LINE3: WHERE ----
LINE4: AND BRANCHCODE = ‘020’ -- Condition Returns 2016 Records
LINE5: AND ISCUST.ISACCOUNTNO BETWEEN '570' AND '670' -- Condition Returns 724 records
LINE6: AND ARMCODE = 'RHITUP' -- Condition Returns 1166 Records
LINE7: AND FUND.FUNDCODE = ISPFOLIO.FUNDCODE -- Merge returns a maximum of 320
LINE8: ISCUST.ISACCOUNTNO = ISPFOLIO.ISACCOUNTNO -- Merge returns a maximum of 2524

Number of records in each table:
ISPFOLIO - 4983 Records ISCUST - 2524 Records FUND - 320 Records
Indexes on each table:
ISPFOLIO - PKISPFOLIO (ISACCOUNTNO, FUNDCODE)
ISCUST - PKISCUST (ISACCOUNTNO)
- IDX_ISCUST_1 (ISACCOUNTNO, BRANCHCODE, ARMCODE, CHANNELCODE)
FUND - PKFUND (FUNDCODE)
- IDX_FUND_1 (NAME)

1. The Join conditions should always be at the left of the predicate and then followed by other predicates, as then only, records, which are selected, are merged. Otherwise all the tables are first merged and then the predicates are applied to this full merged table.
2. The join condition which returns the maximum number of rows should be the left, most the predicate to further reduce the number of records being merged between the tables.
3. In a Join condition put the table which has less number of records as the outer table. The reason for this is that, the optimizer does a table scan on the outer table to get the records which satisfy the given predicates and then the records which satisfy these selected rows in the outer table are directly picked up from the inner table using an index scan
4. Arrange the predicates in a query in such a manner that the predicate, which returns the least rows, comes towards the right of the query. Then move to the left of the query with the other predicates using the same rule.
5. Break down the query where ever possible and make it simpler to avoid the optimizer doing the same
6. Arrange the predicates in a query in such a manner that non-equal predicates come before the equality predicates in the query, so that the equality predicates are used first during execution. The reason being that non-equality predicates require range scans unlike equality predicates, which can go straight to the row if an index scan is used. Also when the optimizer tries to retrieve records using the non-equality predicate, it would scan only the records fetched by the equality predicate and not the full table.
7. Ensure that all columns are referenced with table name/alias, as this will prevent the optimizer from looking into the system tables to find which table a column belongs to.
So, the above query can be rewritten as:
LINE1: SELECT ISPFOLIO.ISACCOUNTNO, ISPFOLIO.FUNDCODE, FUND.NAME, ISPFOLIO.NOOFUNITS, ISPFOLIO.LIENAMT, ISCUST.ISACCOUNTNAME
LINE2: FROM ISPFOLIO, ISCUST, FUND
LINE3: WHERE
LINE7: AND ISPFOLIO.FUNDCODE = FUND.FUNDCODE
LINE8: ISPFOLIO.ISACCOUNTNO = ISCUST.ISACCOUNTNO
LINE4: AND ISCUST.BRANCHCODE = ‘020’
LINE6: AND ISCUST.ARMCODE = 'RHITUP'
LINE5: AND (ISCUST.ISACCOUNTNO >= '570' AND ISCUST.ISACCOUNTNO <= '670')
8. Specify the “Order By “clause only when absolutely required because this will cause the sorting of all records in a table before any predicate is applied. This sort will involve the use of considerable resources in terms of time and memory.

Thanks
A.T.J

Session Invalidation in WebSphere

HTTP Sessions are invalidated one of two ways:

•      The application calls the invalidate() method on the session object.
•     The session is not accessed for a period that is longer than the specified session timeout

            (MaxInactiveInterval) in the WebSphere® Application Server session manager.

 

How does the WebSphere Application Server session manager invalidate sessions?

•     A session is eligible for invalidation if it has not been accessed for a period that is longer than the specified session timeout (MaxInactiveInterval) . The session manager has an invalidation process thread that runs every X seconds to invalidate sessions that are eligible for invalidation.
•       The session manager uses a formula to determine the value of X. The value of X is calculated based on MaxInactiveInterval specified in the session manager and is referred to as the ReaperInterval.
•      if maximum inactive interval of <15 minutes, the ReaperInterval (value of X) is approximately 60 to 90 seconds and if the a maximum inactive interval of >15 minutes the ReaperInterval (value of X) is approximately 300 to 360 seconds.
•       As a result, a session might not be invalidated for MaxInactiveInterval + ReaperInterval seconds .            

Can the Reaper Interval be controlled?

You can specify custom value for Reaper Interval using a Java™ virtual machine (JVM™) system property.

For WebSphere Application Server V3.5.6 or higher and V4.0.2 or higher:

-DSessionReaperInterval= (in seconds)

For all releases of WebSphere Application Server V5.0:

                       -DHttpSessionReaperPollInterval= (in seconds)

•       Sessions are eligible for invalidation as soon as their maximum inactive interval expires, but are not actually invalidated until the reaper runs, which might be up to 6 minutes later. You can use this property to force the reaper to run more often, like every 60 seconds.

•       Note that the more often the reaper runs, the more overhead it takes, so it can negatively impact performance. Values less than 60 seconds are not recommended.

 

Java Best Practice:Place Constants on the Left Side of Comparisons

Numeric Constant
Case1:

if ( something — 1){...)

if ( x = 0 ) {...}Compiles fine but very difficult to find the bugs Case 2:

if (1 - something ){...)

if ( 0 = x ) {...} * // Throws Compile time Exception

Seems to be both cases are equivalent, at least on first inspection, the code on the easel compiles and the code on the case2 does not. Why?

Because the second if statement Isnl doing a comparison, it's doing assignment - you can't assign a new value to a constant value such as 0.

By placing constants on the left side of comparisons you achieve the same effect and the compiler will catch it if you accidentally use assignment instead of compansion.

String Constant:

When you placing the String Constant on left on side we can avoid the NuliPointerException at RunTime

public class MyTestStringClass {

public final static String DEFAULT_START_VALUE="test":

public static void main(String[] args) {

String test=null;

System.out.println(DEFAULT_START_VALUE.equolsttest));//returnsfalse System.out.pnntln(test.equals(DEFAULT_START_VALUE));//throw null pointer Exception

}

}

Thanks

A.T.J