Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2002

    Lightbulb Unanswered: fine-tuning of SQL

    Dear All,

    I have a big table "Product" with half a million rows and a small one "Vendor" with about 10, and I need to execute a SQL to join this two tables:

    SELECT * FROM Product, VendorWHERE UPPER(prod_code) = 'ABC' AND Vendor_id = 'v_11'

    It took about 10 seconds to generate the result. Is there anyway to
    fine tune this SQL statement?


  2. #2
    Join Date
    Sep 2002
    Provided Answers: 1
    Try putting in a join condition so that you don't get a Cartesian product!

    Also, if there is an index on prod_code, then unfortunately it cannot be used due to that UPPER. It would be better to ensure that all prod_code values are stored in uppercase, and to remove the UPPER function from the query. Alternatively, you could add an index on UPPER(prod_code).

Posting Permissions

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