Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2002
    Location
    India
    Posts
    60

    Unanswered: Nested for loop cursors or Joins????

    Hi,
    I have been using around seven tables out of which am fetching rows from three tables.Here the tables are having around 25 lakhs records.I am using 'join' to extract specific rows from the above mentioned three tables satisfying the JOIN conditions.
    So this procedure took around 2 hrs to complete.I have index created for all tables.But
    I have modified the query with the cursors,it took around 1 hr 40 mins to complete.
    But i still want to improve the throughput time to less than 1 hr.
    So how can i proceed????
    WHich one will be giving better performance(JOINs or CURSORS)...If i use cursor i should use nested for loop cursor for the three tables...
    Please guide me in this regard...
    Thanks
    Thanks & Regards
    Manikandan

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Nested for loop cursors or Joins????

    Originally posted by Manikandan
    Hi,
    I have been using around seven tables out of which am fetching rows from three tables.Here the tables are having around 25 lakhs records.I am using 'join' to extract specific rows from the above mentioned three tables satisfying the JOIN conditions.
    So this procedure took around 2 hrs to complete.I have index created for all tables.But
    I have modified the query with the cursors,it took around 1 hr 40 mins to complete.
    But i still want to improve the throughput time to less than 1 hr.
    So how can i proceed????
    WHich one will be giving better performance(JOINs or CURSORS)...If i use cursor i should use nested for loop cursor for the three tables...
    Please guide me in this regard...
    Thanks
    Pure SQL (joins) will usually outperform procedural code (cursors). Make sure all tables have been analyzed. and that the indexes are suitable to support the joins. Whether you can get the time down to 1 hour depends on the size of your tables and the complexity of the query: I'd like to be able to get to work each day in 15 minutes, but as I work 30 miles from home, that just isn't feasible!

  3. #3
    Join Date
    Aug 2002
    Location
    India
    Posts
    60

    Arrow

    Hi,
    Thanks a lot...My joins are not pure SQL joins.My where condition has subquery also...Can you tell me how the driving table should be decided?
    and in which order where conditions are executed????

    Do we need to place a condition which filters maximum rows in the beginning of where clause????
    Please guide me!!!!!
    Thanks & Regards
    Manikandan

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Originally posted by Manikandan
    Hi,
    Thanks a lot...My joins are not pure SQL joins.My where condition has subquery also...Can you tell me how the driving table should be decided?
    and in which order where conditions are executed????

    Do we need to place a condition which filters maximum rows in the beginning of where clause????
    Please guide me!!!!!
    Subqueries can have an impact on performance - sometimes an IN (or NOT IN) is better than an EXISTS (or NOT EXISTS), and sometimes the other is better. The order of conditions in the WHERE clause is irrelevant when using the cost-based optimizer.

Posting Permissions

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