How to improve the performance of a query?

[Sangs]

I have a table called DMPD_Product_Lookup_Dom. It is a lookup table which contains values for certain fields of other tables in the database.
This takes long time to run.
Is there any way to improve performance of this query ??

SELECT
BNAD.Benefit_Admin_Cat_CD AS 'AdminCategory',
DOM1.Value_Description AS 'AdminCategoryDesc',
BNAD.Benefit_Component_Name_CD AS 'BenefitAdmin',
DOM2.Value_Description AS 'BenefitAdminDesc',
BNDT.Benefit_Rider_CD AS 'BenefitRider',
DOM3.Value_Description AS 'BenefitRiderDesc',
BNDT.Benefit_Exception_Ind AS 'Exception',
BNDT.Benefit_Detail_Desc_CD AS 'BenefitDetail',
DOM4.Value_Description AS 'BenefitDetailDesc',
DMCS.Cost_Share_Value_Type_CD AS 'CSValueType',
DOM5.Value_Description AS 'CSValueTypeDesc',
DMCS.Cost_Share_Value AS 'CS_Value',
DMCS.Cost_Share_Rule_Cat_CD AS 'CS_Rule_Cat_CD',
DOM6.Value_Description AS 'CS_Rule_Cat_Value',
BNCS.Cost_Share_Rule_Type_CD AS 'CS_Rule_Type_CD',
DOM7.Value_Description AS 'CSRuleTypeDesc',
BNCS.Cost_Share_Mbr_Family_Ind AS 'MemberOrFamily',
DOM8.Value_Description AS 'MemberOrFamilyDesc',
BNCS.Network_Ind AS 'NetworkInd'
FROM
prdtrk01..DMPD_Product_Lookup_Dom DOM1,
prdtrk01..DMPD_Product_Lookup_Dom DOM2,
prdtrk01..DMPD_Product_Lookup_Dom DOM3,
prdtrk01..DMPD_Product_Lookup_Dom DOM4,
prdtrk01..DMPD_Product_Lookup_Dom DOM5,
prdtrk01..DMPD_Product_Lookup_Dom DOM6,
prdtrk01..DMPD_Product_Lookup_Dom DOM7,
prdtrk01..DMPD_Product_Lookup_Dom DOM8,
prdtrk01..BNAD_Benefit_Admin BNAD,
prdtrk01..BNDT_Benefit_Detail BNDT,
prdtrk01..BNCS_Cost_Share_Rule BNCS,
prdtrk01..DMCS_Cost_Share_Dom DMCS
WHERE
BNAD.Benefit_Admin_ID = BNCS.Benefit_Admin_ID
AND BNDT.Benefit_Detail_ID = BNCS.Benefit_Detail_ID
AND DMCS.Cost_Share_Rule_ID = BNCS.Cost_Share_Rule_ID
AND DOM1.Product_Domain_Entity = "BNAD"
AND DOM1.Product_Attribute_Type = "Benefit_Admin_Cat_CD"
AND DOM1.Domain_Value = BNAD.Benefit_Admin_Cat_CD
AND DOM2.Product_Domain_Entity = "BNAD"
AND DOM2.Product_Attribute_Type = "Benefit_Component_Name_CD"
AND DOM2.Domain_Value = BNAD.Benefit_Component_Name_CD
AND DOM3.Product_Domain_Entity = "BNDT"
AND DOM3.Product_Attribute_Type = "Benefit_Rider_CD"
AND BNDT.Benefit_Rider_CD *= DOM3.Domain_Value
AND DOM4.Product_Domain_Entity = "BNDT"
AND DOM4.Product_Attribute_Type = "Benefit_Detail_Desc_CD"
AND DOM4.Domain_Value = BNDT.Benefit_Detail_Desc_CD
AND DOM5.Product_Domain_Entity = "DMCS"
AND DOM5.Product_Attribute_Type = "Cost_Share_Value_Type_CD"
AND DOM5.Domain_Value = DMCS.Cost_Share_Value_Type_CD
AND DOM6.Product_Domain_Entity = "DMCS"
AND DOM6.Product_Attribute_Type = "Cost_Share_Rule_Cat_CD"
AND DOM6.Domain_Value = DMCS.Cost_Share_Rule_Cat_CD
AND DOM7.Product_Domain_Entity = "BNCS"
AND DOM7.Product_Attribute_Type = "Cost_Share_Rule_Type_CD"
AND DOM7.Domain_Value = BNCS.Cost_Share_Rule_Type_CD
AND DOM8.Product_Domain_Entity = "BNCS"
AND DOM8.Product_Attribute_Type = "Cost_Share_Mbr_Family_Ind"
AND DOM8.Domain_Value = BNCS.Cost_Share_Mbr_Family_Ind
AND BNCS.Product_ID = @Product_ID
ORDER BY
DOM1.Sort_Seq_No,
DOM1.Value_Description,
DOM2.Sort_Seq_No,
DOM2.Value_Description,
DOM3.Sort_Seq_No,
DOM3.Value_Description,
DOM4.Sort_Seq_No,
DOM4.Value_Description,
DOM5.Sort_Seq_No,
DOM5.Value_Description,
DOM6.Sort_Seq_No,
DOM6.Value_Description,
DOM7.Sort_Seq_No,
DOM7.Value_Description,
DOM8.Sort_Seq_No,
DOM8.Value_Description

[iburyak]

Do you have indexes on these tables?

[Sangs]

Do you have indexes on these tables?

Yes, the tables mentioned have indexes. But i want to know is there any other way to improve the performance of this query.

[iburyak]

Unfortunately you didn't provide specific answer.
To help you I need to see all index compositions.
Some people think they have indexes but it could be that they are not the once that such query will use.

To view if your query uses indexes in SQL Query Analyzer go to Query – Show Execution Plan.
Execute your query and point to each item and see if your indexes are actually used.

[abhishek8236]

well the performance can be boosted using the appropriate filters in the FROM clause itself. Try to use INNER and the other different appropriate joins in the clause.The default join is the CROSS join which is the worst join. So try to filter out the results as much as possible in the FROM clause. Definitely the performance will be boosted up to some instant.

[Sangs]

well the performance can be boosted using the appropriate filters in the FROM clause itself. Try to use INNER and the other different appropriate joins in the clause.The default join is the CROSS join which is the worst join. So try to filter out the results as much as possible in the FROM clause. Definitely the performance will be boosted up to some instant.

Thanks Everyone! The Problem has been resolved.