Performance Enhancements - SQL Server Forums

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.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

Performance Enhancements

Author

Topic

kumarpav
Starting Member

18 Posts

Posted - 2005-01-24 : 12:50:31

Hi,
Here i am using 3 sql queries with 2 union all's.
In every query i am using COLM,RACD,COLT tables as common execpt PBSV in 1st query,TDRV in 2nd query,SHRC in 3rd query.
Can u give me any suggestion to modify this query?

SELECT
SUBSTR(A.KEY_1,1,3),
A.BRANCH_NO,
A.PRIM_CUST_NO,
SUBSTR(B.KEY_1,23,16),
' ' ,
' ' ,
' ' ,
A.TYPE,
A.SUB_TYPE,
UPPER(D.DESCRIPTION),
C.TOTAL_MARKET_VAL,
C.TOTAL_FACE_VAL,
C.NO_OF_UNITS,
(C.TOTAL_MARKET_VAL * C.NO_OF_UNITS),
SUBSTR(B.KEY_1,23,16),
C.STATUS,
A.CREATED_DATE,
C.SC_RECT_NO,
'A',
1 FLAG
FROM COLM A,RACD B,PBSV C,COLT D
WHERE
SUBSTR(A.KEY_1,1,3) = :WS-INST-NO AND
A.KEY_1 = SUBSTR(B.KEY_1,1,19) AND
A.STATUS IN ( '01' , '05' ) AND
CONCAT(A.KEY_1,A.PBS_SEQ) = SUBSTR(C.KEY_1,1,23) AND
SUBSTR(A.KEY_1,1,3) = D.INST_NO AND
A.TYPE = D.COL_TYPE AND
A.SUB_TYPE = D.SUB_TYPE
UNION ALL
SELECT
SUBSTR(A.KEY_1,1,3),
A.BRANCH_NO,
A.PRIM_CUST_NO,
SUBSTR(B.KEY_1,23,16),
' ' ,
' ' ,
' ' ,
A.TYPE,
A.SUB_TYPE,
UPPER(D.DESCRIPTION),
T.TOTAL_TDR_VAL,
T.TOTAL_MATURITY_VAL,
T.NO_OF_UNITS,
(T.TOTAL_TDR_VAL * T.NO_OF_UNITS),
T.ACCOUNT_NO,
T.STATUS,
A.CREATED_DATE,
T.SC_RECT_NO,
T.ACCOUNT_NO,
2 FLAG
FROM COLM A,RACD B,TDRV T,COLT D
WHERE
SUBSTR(A.KEY_1,1,3) = :WS-INST-NO AND
A.KEY_1 = SUBSTR(B.KEY_1,1,19) AND
A.STATUS IN ( '01' , '05' ) AND
CONCAT(A.KEY_1,A.TDR_SEQ) = SUBSTR(T.KEY_1,1,23) AND
SUBSTR(A.KEY_1,1,3) = D.INST_NO AND
A.TYPE = D.COL_TYPE AND
A.SUB_TYPE = D.SUB_TYPE
UNION ALL
SELECT
SUBSTR(A.KEY_1,1,3),
A.BRANCH_NO,
A.PRIM_CUST_NO,
SUBSTR(B.KEY_1,23,16),
' ' ,
' ' ,
' ' ,
A.TYPE,
A.SUB_TYPE,
UPPER(D.DESCRIPTION),
C.TOTAL_MARKET_VAL,
C.TOTAL_FACE_VAL,
C.NO_OF_UNITS,
(C.TOTAL_MARKET_VAL * C.NO_OF_UNITS),
SUBSTR(B.KEY_1,23,16),
C.STATUS,
A.CREATED_DATE,
C.SC_RECT_NO,
'C',
3 FLAG
FROM COLM A,RACD B,SHRV C,COLT D
WHERE
SUBSTR(A.KEY_1,1,3) = :WS-INST-NO AND
A.KEY_1 = SUBSTR(B.KEY_1,1,19) AND
A.STATUS IN ( '01' , '05' ) AND
CONCAT(A.KEY_1,SHARE_SEQ) = SUBSTR(C.KEY_1,1,23) AND
SUBSTR(A.KEY_1,1,3) = D.INST_NO AND
A.TYPE = D.COL_TYPE AND
A.SUB_TYPE = D.SUB_TYPE

======================================================================

Give me any idea to modify this query also...............

SELECT KEY_1
FROM INGT
WHERE POST_DATE = 38220
AND SUBSTR(GOVT_CODE,1,3) IN
(SELECT DISTINCT MAJOR_HEA
FROM INH2
WHERE EXTR_REQ = '01')
AND SUBSTR(GOVT_CODE,4,8) IN
(SELECT DISTINCT SUB_HEAD
FROM INH2
WHERE EXTR_REQ = '01')
AND REV_IND IN (' ','L','B','C','S')

kumarpav
Starting Member

18 Posts

Posted - 2005-02-09 : 13:35:24

can we rewrite this query?

Here i am using query in query ....

how can i rewrite this query as a single query and it should run fast..

SELECT ACCT_NO,
INSTR_NO,
INSTR_DATE,
BRANCH_NO
FROM BPDC
WHERE (ACCT_NO ,INSTR_STATUS) IN
(SELECT ACCT_NO ,INSTR_STATUS
FROM BPDC WHERE INSTR_STATUS = '07'
GROUP BY ACCT_NO , INSTR_STATUS
HAVING COUNT(INSTR_STATUS) <= :WA-PDCS-IN-HAND AND
COUNT(INSTR_STATUS) != 0)
AND INSTR_DATE = :WA-TOMORROW

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources