Results 1 to 2 of 2

Thread: Nested loop SQL

  1. #1
    Join Date
    Sep 2003
    Chiang Mai Thailand

    Question Unanswered: Nested loop SQL

    I'm so new in SQL. I have SQL statement that nested loop.
    That I don't like.
    for example.
    I have one table that keep Product Master
    Table T_PRD_MS
    COLUMN I_PRD_TYPE : primary key
    I_PRD_ID : primary key

    And Other tables that keep Information about this product to readable format eg. T_PRD_TYPE --- > to get product type name
    T_PRD_ID --- > to gett product name and detail

    When I select data from table T_PRD_MS that in my criteria eg. Product that ID = '111'
    I must to copy data to temp table for transaction up date. then I must to work in two steps.
    1. select data from T_PRD_MS in criteria to TEMP table
    2. use primary key in each row in TEMP table to get data record by record.

    EG. sql = select * from T_PRD_MS where I_EXPIRE_DATE
    = '20060210'
    set SQL as recordset A
    If recordset A not eof then
    select data from T_PRD_ID where I_PRD_ID = recordset A.fields(0)
    and I_PRD_TYPE = recordset A.fields(1)

    How can I reduce my job for increase performance?
    Kindly regards,
    Thana p.
    Chiangmai Thailand

  2. #2
    Join Date
    Sep 2002
    Provided Answers: 1

    Re: Nested loop SQL

    Why not perform a SQL join?

    select m.*,
    from T_PRD_MS m, T_PRD_ID i
    where m.I_EXPIRE_DATE = '20060210'
    and m.I_PRD_TYPE = i.I_PRD_TYPE;
    and m.I_PRD_ID = i.I_PRD_ID;

Posting Permissions

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