Results 1 to 3 of 3

Thread: SQL Query

  1. #1
    Join Date
    Feb 2004
    Posts
    3

    Unanswered: SQL Query

    What are the main things that one should consider while writing a sql query for good performance? Like I know one rule of thumb is that try to bring as many rows as possible into memory to reduce i-o traffic? like this are there any other tips that I should look into while writing a sql query?

    Thanks,
    Vijaya

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

    Re: SQL Query

    That's a very big question - it is largely a matter of experience and common sense, not "rules of thumb". I don't really understand your example of tring to "bring as many rows as possible into memory to reduce i-o traffic". You should only select the rows you actually need in the first place.

    Some guidelines (off the top of my head):
    • Think in sets rather than single rows.
    • Do as much as possible in SQL rather than after you have fetched the data. In particular, join tables in SQL rather than have separate queries to get each bit of data from different tables.
    • Be aware that modifying column values in the WHERE clause can prevent Oracle from using an index. "WHERE col = 123" may use an index on col, but "WHERE col-123 = 0" would not.


    I'd point you to the Performance Tuning Guide, but I expect it may be too advanced for you at this stage.

  3. #3
    Join Date
    Jan 2004
    Location
    Singapore
    Posts
    89

    Re: SQL Query

    Originally posted by Vijaya K
    What are the main things that one should consider while writing a sql query for good performance? Like I know one rule of thumb is that try to bring as many rows as possible into memory to reduce i-o traffic? like this are there any other tips that I should look into while writing a sql query?

    Thanks,
    Vijaya
    Hi Vijaya,

    Praveen here.
    Happned to you question. Whatever you said is ok.
    If you want to improve the performance there are lot of things to be taken care.
    1. See that the query make use of the index. Index is required only if you have huge data and ur query is going to fetch 5% data.
    2. Avoid implicit datataype conversions, try to do it explicitly.
    3. Use joins and view wisely

    Many more can help you. But you will have to have a good knowledge on the data and also the steps taken by the query to reach to the output..

    Thanks

    praveenpr@rediffmail.com
    Praveen

Posting Permissions

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