Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
vijayisonly
Master Smack Fu Yak Hacker
1836 Posts |
 Posted - 2009-02-18 : 12:05:58
|
| This is the query I have right nowSELECT A.POLICY_NUMBER, A.COMPANY_CODE, A.CREDIT_CODE, A.TRANSACTION_AMOUNT, C.MLTC_RECORD_YES, F.CANCEL_REASN, D.ISSUE_DATE, D.POLICY_DATE, PO_NAME.NAME, PO_ADDR.CITY, PO_ADDR.STATE, PO_ADDR_ZIP, G.AGENTFROM CACTGDAILY AS A WITH (NOLOCK) LEFT JOIN PMLTC AS C WITH (NOLOCK) ON C.MLTC_KEY0 = A.ACTG_KEY0 LEFT JOIN PPOLM AS F WITH (NOLOCK) ON F.POLM_KEY0 = CAST(A.COMPANY_CODE + CAST(A.POLICY_NUMBER AS CHAR(12)) + '000012' AS BINARY(20)) LEFT JOIN PPOLC AS D WITH (NOLOCK) ON D.POLC_KEY0 = CAST(A.COMPANY_CODE + CAST(A.POLICY_NUMBER AS CHAR(12)) AS BINARY(14)) LEFT JOIN PRELA_KEY4 AS PO_KEY WITH (NOLOCK) ON ((PO_KEY.RELA_KEY4 >= D.POLC_KEY0 + CAST('00PO00000000' AS BINARY(12))) AND PO_KEY.RELA_KEY4 <= D.POLC_KEY0 + CAST('00PO99999999' AS BINARY(12))) LEFT JOIN PNAME AS PO_NAME WITH (NOLOCK) ON (PO_NAME.NAME_KEY0 = SUBSTRING(PO_KEY.RELA_KEY4,19,8)) LEFT JOIN (SELECT NALK_RELA_KEY, NALK_ADDRESS_ID, RANK1 FROM (SELECT SUBSTRING(NALK_KEY0,34,8) AS NALK_ADDRESS_ID, SUBSTRING(NALK_KEY0,1,8) AS NALK_RELA_KEY, RANK() OVER (PARTITION BY NAME_ID ORDER BY EFFECTIVE_DATE DESC, ADDRESS_ID DESC) AS RANK1 FROM PNALK WITH (NOLOCK)WHERE ADDRESS_ID <> 0 AND EFFECTIVE_DATE <= CONVERT(char, GETDATE(),112)) A WHERE RANK1 = 1) NA ON NALK_RELA_KEY = SUBSTRING(PO_KEY.RELA_KEY4,19,8) LEFT JOIN PADDR AS PO_ADDR WITH (NOLOCK) ON PO_ADDR.ADDR_KEY0 = NALK_ADDRESS_ID LEFT JOIN PCOMCAGNTS AS G WITH (NOLOCK) ON G.POLICY_NUMBER = A.POLICY_NUMBER AND G.COMPANY_CODE = A.COMPANY_CODE AND G.X = '1' AND D.ISSUE_DATE = G.SPLIT_EFF_DATE WHERE A.CREDIT_ACCOUNT > '' AND ((A.CREDIT_CODE > 89 AND A.CREDIT_CODE < 100 ) OR A.CREDIT_CODE = 1900) AND A.REVERSAL_CODE <> 'Y' AND A.REDO_PAYMENT_FLAG <> 'Y' The table CACTGDAILY can have multiple records for the same company code and policy number. It is a table that holds accounts. So if thre are more than one transactions for a policy on a day, it will have that many records.The requirement is to get the details of all transactions in a day along with other information like Name,Address agent's name etc.So I have used a LEFT JOIN for all the other tables coz they have only one particular row for a company code and policy number (PPOLC,PPOLM etc). Is using so many LEFT JOINs really advisable with respect to performace or is it going to stall the DB. We are talking about a million records in CACTGDAILY.If you need more information on anything, i would be happy to provide it |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-02-18 : 12:08:40
|
| left joins definitely perform less comparing to inner join |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-02-18 : 12:12:29
|
| not sure why used LEFT JOINs in above case. if your tables are related in many to 1 it will give you multiple records regardless of whether you use left or inner join. |
|
|
|
vijayisonly
Master Smack Fu Yak Hacker
1836 Posts |
Posted - 2009-02-18 : 13:19:36
|
| I will try to replace the LEFT JOIN with INNER JOIN for a few of the tables and see if the number of rows returned is the same.But some tables may or may not have the record, for those I will have to use LEFT JOIN. I will try this and post again. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-02-18 : 13:22:07
|
| ok... your second scenario makes sense to use left join. in such cases you need to use left join if you want info regardless of match |
|
|
|
|
|
|
|
|
Best way to do it