SQL Querry Optimization in MS Access

[shabar]

I want to run below query to get the result set that I am after. But It takes long time even with the indexes




Then I broke the query into each group. That case its better.

But I had to add two join keys later. Then it didn't give results. (Running for long time)

Just want to know if there is any possibilty of running the query with all the Groups in one go

SELECT DISTINCT T_Extract.[SerGrp], T_Extract.[SerCat], T_Extract.[Component Description (Product)], T_Extract.[Mod_Item ID],
" ##" AS IM, IM_Mapping.TrfCode, IM_Mapping.CatCode, IM_Mapping.CatDescr, IM_Mapping.SubCatCode, IM_Mapping.SubCatDescr,
LKP_Item.Group, LKP_Item.[Modality Source]
FROM ((LKP_Product
INNER JOIN IM_Mapping ON (LKP_Product.State=IM_Mapping.StateCode) AND (LKP_Product.OptCode=IM_Mapping.OptCode))
INNER JOIN T_Extract ON LKP_Product.[Component Description (Product)]=T_Extract.[Component Description (Product)])
INNER JOIN LKP_Item ON (T_Extract.[Mod_Item ID]=LKP_Item.[Item ID]) AND (IM_Mapping.TrfCode=LKP_Item.[iMed TrfCode])
AND (IM_Mapping.PubNum = LKP_Item.PubNum) AND (IM_Mapping.PracType = LKP_Item.PracType)
WHERE (((T_Extract.[SerGrp])<>[CatDescr])
AND ((T_Extract.[SerCat])<>[SubCatDescr])
AND ((LKP_Item.Group)="B1"));






I have created Indexes on below columns




LKP_Item.PubNum
LKP_Item.PracType
LKP_Item.[Item ID]
LKP_Item.[iMed TrfCode]




LKP_Product.State
LKP_Product.OptCode
LKP_Product.[Component Description (Product)]




IM_Mapping.TrfCode
IM_Mapping.StateCode
IM_Mapping.OptCode
IM_Mapping.PubNum
IM_Mapping.PracType
IM_Mapping.[CatDescr]
IM_Mapping.[SubCatDescr]



T_Extract.[Component Description (Product)]
T_Extract.[Mod_Item ID]
T_Extract.[SerCat]
T_Extract.[SerGrp]

Even If I run group wise after adding another two join columns between LKP_items and IM_Mapping (practype &pubnum) it took 30 min and didn't give any results


Appreciate your help


Cheers

Shabar

[shabar]

Please note IM_mapping table is having 1.7 mio records