Hi
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
I_MANUF_DATE
I_EXPIRE_DATE
I_VENDOR_ID
I_BRAND_ID

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?

Why not perform a SQL join?

select m.*, i.data
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;