CODEWITHSUNDEEP
sql
user1356042
user1356042

Reputation: 385

Optimize SQL query with many left join

I have a SQL query with many left joins

SELECT COUNT(DISTINCT po.o_id)
FROM T_PROPOSAL_INFO po
LEFT JOIN T_PLAN_TYPE tp ON tp.plan_type_id = po.Plan_Type_Fk
LEFT JOIN T_PRODUCT_TYPE pt ON pt.PRODUCT_TYPE_ID = po.cust_product_type_fk
LEFT JOIN T_PROPOSAL_TYPE prt ON prt.PROPTYPE_ID = po.proposal_type_fk
LEFT JOIN T_BUSINESS_SOURCE bs ON bs.BUSINESS_SOURCE_ID = po.CONT_AGT_BRK_CHANNEL_FK
LEFT JOIN T_USER ur ON ur.Id = po.user_id_fk
LEFT JOIN T_ROLES ro ON ur.roleid_fk = ro.Role_Id
LEFT JOIN T_UNDERWRITING_DECISION und ON und.O_Id = po.decision_id_fk
LEFT JOIN T_STATUS st ON st.STATUS_ID = po.piv_uw_status_fk
LEFT OUTER JOIN T_MEMBER_INFO mi ON mi.proposal_info_fk = po.O_ID
WHERE 1 = 1
     AND po.CUST_APP_NO LIKE '%100010233976%'
     AND 1 = 1
     AND po.IS_STP <> 1
     AND po.PIV_UW_STATUS_FK != 10

The performance seems to be not good and I would like to optimize the query.

Any suggestions please?

Upvotes: 2

Views: 1260

Answers (2)

quetzalcoatl
quetzalcoatl

Reputation: 33536

First, check your indexes. Are they old? Did they get fragmented? Do they need rebuilding?

Then, check your "execution plan" (varies depending on the SQL Engine): are all joins properly understood? Are some of them 'out of order'? Do some of them transfer too many data?

Then, check your plan and indexes: are all important columns covered? Are there any outstandingly lengthy table scans or joins? Are the columns in indexes IN ORDER with the query?

Then, revise your query: - can you extract some parts that normally would quickly generate small rowset? - can you add new columns to indexes so join/filter expressions will get covered? - or reorder them so they match the query better?

And, supporting the solution from @Devart:

Can you eliminate some tables on the way? does the where touch the other tables at all? does the data in the other tables modify the count significantly? If neither SELECT nor WHERE never touches the other joined columns, and if the COUNT exact value is not that important (i.e. does that T_PROPOSAL_INFO exist?) then you might remove all the joins completely, as Devart suggested. LEFTJOINs never reduce the number of rows. They only copy/expand/multiply the rows.

Upvotes: 0

Devart
Devart

Reputation: 121952

Try this one -

SELECT COUNT(DISTINCT po.o_id)
FROM T_PROPOSAL_INFO po
WHERE PO.CUST_APP_NO LIKE '%100010233976%'
     AND PO.IS_STP <> 1
     AND po.PIV_UW_STATUS_FK != 10

Upvotes: 1

Related Questions