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 |
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 timeSELECT * 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_TYPEFROM 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_IDWHEREGAM.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 #DUMwhere (SELECT MAX(A.TAX_AMT_IN_ACCT_CRNCY)FROM TDS A,GAM BWHERE B.SOL_ID = #DUM.SOL_IDAND B.CIF_ID = #DUM.CIF_IDand (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.PKinto #afrom GAM INNER JOIN TDS ON GAM.ACID = TDS.ACID AND GAM.BANK_ID = TDS.BANK_IDINNER JOIN CMG ON GAM.CIF_ID = CMG.CIF_ID AND GAM.BANK_ID = CMG.BANK_IDWHERE 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. |
|
|
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 MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|