Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2006
    Posts
    6

    Unanswered: Perfomance Tuning

    Hi Guys

    I have this Java program doing selecting and updating tables in the database but It is doing so slow..

    I want to Create Index on the table but not too sure because this table gonna be searched and updated..

    lets say

    i have this two qquery


    Select balance from table_a where la_no = a and pool = b and seq = c


    Another Query for updating

    update table_a set balance = 0 ,last_update_date = sysdate where la_no = a and seq = c


    "la_no" here is the primary key..


    Anytips to create good index since this table going to be searched and updated??

    I m thinking something like this.??
    Create Index faster_perfomance on table_a( a)

    Will this improve the perfomance?? Thanks

  2. #2
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    When you created it and tried it, what did you find out ?? !! ??

  3. #3
    Join Date
    Feb 2006
    Posts
    6
    No , i havent really test it because this tables accessed by a lot of other Class and method which i dont know ..

    Since this is for my company code so i need to be very careful ..
    Thats why i ask first here for good suggestion..

    Thanks..

  4. #4
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    Allright then, you said la_no is a primary key on the table, and you're doing querys like la_no = <some value>, and you indexed la_no with the method you said above, then I could say yes, they will be *generally* faster.. but they can even go faster. How ? instead of creating this type of index, which will probably opt for a IRS operation (index range scan), you created a unique index on this primary key. Then, querys will tipically fly.

    P.S.: On a side note, primary keys should always be indexed.
    Last edited by JMartinez; 02-14-06 at 12:11.

  5. #5
    Join Date
    Jul 2003
    Posts
    2,296
    for the select create an index based on:
    (la_no, pool, seq )

    I assume the PK already created the index on the unique column la_no

    ps: be sure to analyze the table and indexes
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  6. #6
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by The_Duck
    for the select create an index based on:
    (la_no, pool, seq )
    Generally, I'd reverse the order of columns: (la_no, seq, pool). It would allow the update to use that index too. However, since la_no is a PK each of the sample statements will fetch one row at most, regardless of the values of seq and/or pool, so I don't think there would be any benefit from the additional index.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •