|
kond.mohan
Posting Yak Master
India
138 Posts |
 Posted - 01/10/2013 : 04:55:08
|
dear team,
below query is having 12 lakh records of output. when i am execute below query it is taking more than 55mins SSMS. we have already created recommanded indexes. i have used 3 tables. count of 3 tables are very large amount of data is there. how to reduce execution time of below query.
pls provide the solution to execute the query in proper time
SELECT * INTO #DUM FROM
(
SELECT distinct
'0510308' AS "BSR CODE",
GAM.ACID,
GAM.BANK_ID,
TDS.REMITTANCE_DETAILS,
CMG.TDS_TBL_CODE,
CMG.PAN_GIR_NUM,
(SELECT TAXID FROM CORPORATE WHERE CORPORATE.CORP_ID = CMG.CORP_ID) TAXID,
CMG.CUST_NAME,
GAM.SOL_ID,
GAM.FORACID,
GAM.CIF_ID,
TDS.INT_AMT,
TDS.TAX_AMT_IN_ACCT_CRNCY,
TDS.REFUND_AMT,
TDS.REMITTED_AMT,
TDS.TRAN_DATE,
TDS.TDS_RATE,
GAM.WTAX_FLG AS "TAXCATEGORY",
(CASE WHEN (TDS.ACCRUED_INT_FLG ='N')
THEN 'PAID'
WHEN (TDS.ACCRUED_INT_FLG !='N')
THEN 'ACCURED'
END
) AS "STATUS",
CMG.CUST_NRE_FLG,
GAM.SCHM_CODE,
GAM.SCHM_TYPE
FROM GAM
INNER JOIN TDS ON GAM.ACID = TDS.ACID AND GAM.BANK_ID = TDS.BANK_ID
INNER JOIN CMG ON GAM.CIF_ID = CMG.CIF_ID AND GAM.BANK_ID = CMG.BANK_ID
WHERE
GAM.SCHM_TYPE = 'TDA'
)a
SELECT *,
SUBSTRing(REMITTANCE_DETAILS,10,CHARINDEX(REMITTANCE_DETAILS,',',10)-1) AS "CHALLAN SERIAL NO.",
SUBSTRing(REMITTANCE_DETAILS,CHARINDEX(REMITTANCE_DETAILS,',',10)+1,LEN(REMITTANCE_DETAILS)-CHARINDEX(REMITTANCE_DETAILS,'ACK NO',1)-2) AS "REMITTANCE DATE",
(SELECT max(IDT.RCRE_TIME) FROM IDT WHERE IDT.ENTITY_ID= #DUM.ACID
AND #DUM.BANK_ID = IDT.BANK_ID
AND IDT.ENTITY_TYPE = 'ACCNT'
AND IDT.INTEREST_IND='C') AS "INT_PAYMENT_DATE"
FROM #DUM
where
(SELECT MAX(A.TAX_AMT_IN_ACCT_CRNCY)
FROM TDS A,GAM B
WHERE B.SOL_ID = #DUM.SOL_ID
AND B.CIF_ID = #DUM.CIF_ID
and (A.TRAN_DATE BETWEEN @FromDate AND @ToDate)
AND A.ACID = B.ACID) != 0
and #DUM.TRAN_DATE BETWEEN @FromDate AND @ToDate
|
|
|
nigelrivett
Flowing Fount of Yak Knowledge
United Kingdom
3328 Posts |
Posted - 01/10/2013 : 05:08:36
|
How long do the two queries take - is one a lot longer than the other?
How many rows in the temp table.
In your status query - select top 1 rather than distinct but don't think that will help much.
What are the PKs on GAM, TDS, CMG?
How long and how many rows from
select GAM.PK, TDS.PK, CMG.PK
into #a
from GAM
INNER JOIN TDS ON GAM.ACID = TDS.ACID AND GAM.BANK_ID = TDS.BANK_ID
INNER JOIN CMG ON GAM.CIF_ID = CMG.CIF_ID AND GAM.BANK_ID = CMG.BANK_ID
WHERE GAM.SCHM_TYPE = 'TDA'
==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy. |
 |
|
Need to reduce the query execution time
