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 2008 Forums
 Transact-SQL (2008)
 Need to reduce the query execution time

Author  Topic 

kond.mohan
Posting Yak Master

213 Posts

Posted - 2013-01-10 : 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
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2013-01-10 : 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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-10 : 05:39:11
did you see execution plan? what are costly steps?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
   

- Advertisement -