taking 15 mins to execute - 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

General SQL Server Forums

New to SQL Server Programming

taking 15 mins to execute

Author

Topic

imfairozkhan
Starting Member

11 Posts

Posted - 2008-12-03 : 03:29:06

hi guys can u help me in optimizing the below query. it is taking more than 15 mins to execute

SELECT CASE WHEN FPLDATE IS NULL THEN FPLDATE2 ELSE FPLDATE END AS FINALDATE, CASE WHEN FCUR IS NULL
THEN FCUR2 ELSE FCUR END AS FINALCUR, CASE WHEN FCURNAME IS NULL THEN FCURNAME2 ELSE FCURNAME END AS FINALCURNAME,
CASE WHEN FBNPL IS NULL THEN 0 ELSE FBNPL END AS FINALBNPL, CASE WHEN FFXPL IS NULL THEN 0 ELSE FFXPL END AS FINALFXPL,
(CASE WHEN FBNPL IS NULL THEN 0 ELSE FBNPL END) + (CASE WHEN FFXPL IS NULL THEN 0 ELSE FFXPL END) AS FINALNETPL
FROM (SELECT T1.PLDate AS FPLDATE, T2.PLDate AS FPLDATE2, T1.CUR AS FCUR, T2.CUR AS FCUR2, T1.CURNAME AS FCURNAME,
T2.CURNAME AS FCURNAME2, T1.BanknotePL AS FBNPL, T2.FXPL AS FFXPL
FROM (SELECT vwPL_LastRevals_1.setl_order_date_time AS PLDate, ccy_1.ccy_code AS CUR, ccy_1.ccy_name AS CURNAME,
SUM(vwPL_LastRevals_1.SODCalcAmt - vwPL_LastRevals_1.SODPosition * dbo.ccyr.ccyr_rate) AS BanknotePL
FROM dbo.ccy AS ccy_1 FULL OUTER JOIN
dbo.ccyr ON ccy_1.ccy_code = dbo.ccyr.ccyr_ccy_code FULL OUTER JOIN
dbo.vwPL_LastRevals AS vwPL_LastRevals_1 ON dbo.ccyr.ccyr_ccy_code = vwPL_LastRevals_1.tran_ccy_code
WHERE (LEFT(vwPL_LastRevals_1.tran_status, 2) IN ('01', '02', '03')) AND (dbo.ccyr.ccyr_ccyt_type = '01')
GROUP BY vwPL_LastRevals_1.setl_order_date_time, ccy_1.ccy_code, ccy_1.ccy_name) AS T1 FULL OUTER JOIN
(SELECT vwPL_LastRevals_1.setl_order_date_time AS PLDate, ccy_1.ccy_code AS CUR, ccy_1.ccy_name AS CURNAME,
SUM(vwPL_LastRevals_1.SODCalcAmt - vwPL_LastRevals_1.SODPosition * ccyr_1.ccyr_rate) AS FXPL
FROM dbo.ccy AS ccy_1 FULL OUTER JOIN
dbo.ccyr AS ccyr_1 ON ccy_1.ccy_code = ccyr_1.ccyr_ccy_code FULL OUTER JOIN
dbo.vwPL_LastRevals AS vwPL_LastRevals_1 ON ccyr_1.ccyr_ccy_code = vwPL_LastRevals_1.tran_ccy_code
WHERE (LEFT(vwPL_LastRevals_1.tran_status, 2) = ' ') AND (ccyr_1.ccyr_ccyt_type = '03')
GROUP BY vwPL_LastRevals_1.setl_order_date_time, ccy_1.ccy_code, ccy_1.ccy_name) AS T2 ON T1.CUR = T2.CUR AND
T1.PLDate = T2.PLDate) AS Final

thanks
fairozkhan

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-03 : 03:37:21

With 5 FULL JOINs?

E 12°55'05.63"
N 56°04'39.26"

imfairozkhan
Starting Member

11 Posts

Posted - 2008-12-03 : 03:44:08

quote:
Originally posted by Peso

With 5 FULL JOINs?

E 12°55'05.63"
N 56°04'39.26"

hi peso,
i didnt got u please elaborate

thanks
fairozkhan

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-03 : 03:48:11

seem like what you posted is equivalent to

SELECT COALESCE(FPLDATE,FPLDATE2) AS FINALDATE, 
COALESCE(FCUR,FCUR2)  AS FINALCUR, 
COALESCE(FCURNAME,FCURNAME2) AS FINALCURNAME, 
COALESCE(FBNPL,0) AS FINALBNPL, 
COALESCE(FFXPL,0) AS FINALFXPL, 
COALESCE(FBNPL,0) + COALESCE(FFXPL,0) AS FINALNETPL
FROM 
(
	SELECT T1.FPLDATE, 
	T1.FPLDATE2, 
	T1.FCUR, 
	T1.FCUR2, 
	T1.FCURNAME, 
	T1.FCURNAME2, 
	T1.FBNPL, 
	T1.FFXPL
	FROM 
		(
			SELECT  
			MAX(CASE WHEN (LEFT(vwPL_LastRevals_1.tran_status, 2) IN ('01', '02', '03')) AND (dbo.ccyr.ccyr_ccyt_type = '01') THEN vwPL_LastRevals_1.setl_order_date_time ELSE NULL END)  AS FPLDATE, 
			MAX(CASE WHEN (LEFT(vwPL_LastRevals_1.tran_status, 2) = ' ') AND (dbo.ccyr.ccyr_ccyt_type = '03') THEN vwPL_LastRevals_1.setl_order_date_time ELSE NULL END)  AS FPLDATE2, 
			MAX(CASE WHEN (LEFT(vwPL_LastRevals_1.tran_status, 2) IN ('01', '02', '03')) AND (dbo.ccyr.ccyr_ccyt_type = '01') THEN ccy_1.ccy_code ELSE NULL END)  AS FCUR, 
			MAX(CASE WHEN (LEFT(vwPL_LastRevals_1.tran_status, 2) = ' ') AND (dbo.ccyr.ccyr_ccyt_type = '03') THEN ccy_1.ccy_code ELSE NULL END)  AS FCUR2, 
			MAX(CASE WHEN (LEFT(vwPL_LastRevals_1.tran_status, 2) IN ('01', '02', '03')) AND (dbo.ccyr.ccyr_ccyt_type = '01') THEN ccy_1.ccy_name ELSE NULL END)AS FCURNAME, 
			MAX(CASE WHEN (LEFT(vwPL_LastRevals_1.tran_status, 2) = ' ') AND (dbo.ccyr.ccyr_ccyt_type = '03') THEN ccy_1.ccy_name ELSE NULL END)AS FCURNAME2, 
			SUM(CASE WHEN (LEFT(vwPL_LastRevals_1.tran_status, 2) IN ('01', '02', '03')) AND (dbo.ccyr.ccyr_ccyt_type = '01') THEN vwPL_LastRevals_1.SODCalcAmt - vwPL_LastRevals_1.SODPosition * dbo.ccyr.ccyr_rate ELSE 0 END) AS FBNPL,
			SUM(CASE WHEN (LEFT(vwPL_LastRevals_1.tran_status, 2) = ' ') AND (dbo.ccyr.ccyr_ccyt_type = '03') THEN vwPL_LastRevals_1.SODCalcAmt - vwPL_LastRevals_1.SODPosition * dbo.ccyr.ccyr_rate ELSE 0 END) AS FXPL
			FROM dbo.ccy AS ccy_1 FULL OUTER JOIN
			dbo.ccyr ON ccy_1.ccy_code = dbo.ccyr.ccyr_ccy_code FULL OUTER JOIN
			dbo.vwPL_LastRevals AS vwPL_LastRevals_1 ON dbo.ccyr.ccyr_ccy_code = vwPL_LastRevals_1.tran_ccy_code
			GROUP BY vwPL_LastRevals_1.setl_order_date_time, ccy_1.ccy_code, ccy_1.ccy_name
		)T1
		
) AS Final

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-03 : 03:54:18

Why not this?

SELECT		lr.setl_order_date_time AS FINALDATE,
		cy.ccy_code AS FINALCUR,
		cy.ccy_name AS FINALCURNAME,
		SUM(CASE WHEN lr.tran_status LIKE '0[123]%' AND yr.ccyr_ccyt_type = '01' THEN lr.SODCalcAmt - lr.SODPosition * yr.ccyr_rate ELSE 0 END) AS FINALBNPL,
		SUM(CASE WHEN lr.tran_status LIKE '  %' AND yr.ccyr_ccyt_type = '03' THEN lr.SODCalcAmt - lr.SODPosition * yr.ccyr_rate ELSE 0 END) AS FINALFXPL,
		SUM(lr.SODCalcAmt - lr.SODPosition * yr.ccyr_rate) AS FINALNETPL
FROM		dbo.ccy AS cy
INNER JOIN	dbo.ccyr AS yr ON yr.ccyr_ccy_code = cy.ccy_code
INNER JOIN	dbo.vwPL_LastRevals AS lr ON lr.tran_ccy_code = yr.ccyr_ccy_code
WHERE		(lr.tran_status LIKE '0[123]%' AND yr.ccyr_ccyt_type = '01')
		OR (lr.tran_status LIKE '  %' AND yr.ccyr_ccyt_type = '03')
GROUP BY	lr.setl_order_date_time,
		cy.ccy_code,
		cy.ccy_name

E 12°55'05.63"
N 56°04'39.26"

imfairozkhan
Starting Member

11 Posts

Posted - 2008-12-03 : 04:22:30

quote:

Originally posted by Peso

Why not this?

SELECT		lr.setl_order_date_time AS FINALDATE,
		cy.ccy_code AS FINALCUR,
		cy.ccy_name AS FINALCURNAME,
		SUM(CASE WHEN lr.tran_status LIKE '0[123]%' AND yr.ccyr_ccyt_type = '01' THEN lr.SODCalcAmt - lr.SODPosition * yr.ccyr_rate ELSE 0 END) AS FINALBNPL,
		SUM(CASE WHEN lr.tran_status LIKE '  %' AND yr.ccyr_ccyt_type = '03' THEN lr.SODCalcAmt - lr.SODPosition * yr.ccyr_rate ELSE 0 END) AS FINALFXPL,
		SUM(lr.SODCalcAmt - lr.SODPosition * yr.ccyr_rate) AS FINALNETPL
FROM		dbo.ccy AS cy
INNER JOIN	dbo.ccyr AS yr ON yr.ccyr_ccy_code = cy.ccy_code
INNER JOIN	dbo.vwPL_LastRevals AS lr ON lr.tran_ccy_code = yr.ccyr_ccy_code
WHERE		(lr.tran_status LIKE '0[123]%' AND yr.ccyr_ccyt_type = '01')
		OR (lr.tran_status LIKE '  %' AND yr.ccyr_ccyt_type = '03')
GROUP BY	lr.setl_order_date_time,
		cy.ccy_code,
		cy.ccy_name

E 12°55'05.63"
N 56°04'39.26"

hi
Great this works fine, need to check the output

thanks
fairozkhan

imfairozkhan
Starting Member

11 Posts

Posted - 2008-12-03 : 04:23:23

quote:

Originally posted by visakh16

seem like what you posted is equivalent to

SELECT COALESCE(FPLDATE,FPLDATE2) AS FINALDATE, 
COALESCE(FCUR,FCUR2)  AS FINALCUR, 
COALESCE(FCURNAME,FCURNAME2) AS FINALCURNAME, 
COALESCE(FBNPL,0) AS FINALBNPL, 
COALESCE(FFXPL,0) AS FINALFXPL, 
COALESCE(FBNPL,0) + COALESCE(FFXPL,0) AS FINALNETPL
FROM 
(
	SELECT T1.FPLDATE, 
	T1.FPLDATE2, 
	T1.FCUR, 
	T1.FCUR2, 
	T1.FCURNAME, 
	T1.FCURNAME2, 
	T1.FBNPL, 
	T1.FFXPL
	FROM 
		(
			SELECT  
			MAX(CASE WHEN (LEFT(vwPL_LastRevals_1.tran_status, 2) IN ('01', '02', '03')) AND (dbo.ccyr.ccyr_ccyt_type = '01') THEN vwPL_LastRevals_1.setl_order_date_time ELSE NULL END)  AS FPLDATE, 
			MAX(CASE WHEN (LEFT(vwPL_LastRevals_1.tran_status, 2) = ' ') AND (dbo.ccyr.ccyr_ccyt_type = '03') THEN vwPL_LastRevals_1.setl_order_date_time ELSE NULL END)  AS FPLDATE2, 
			MAX(CASE WHEN (LEFT(vwPL_LastRevals_1.tran_status, 2) IN ('01', '02', '03')) AND (dbo.ccyr.ccyr_ccyt_type = '01') THEN ccy_1.ccy_code ELSE NULL END)  AS FCUR, 
			MAX(CASE WHEN (LEFT(vwPL_LastRevals_1.tran_status, 2) = ' ') AND (dbo.ccyr.ccyr_ccyt_type = '03') THEN ccy_1.ccy_code ELSE NULL END)  AS FCUR2, 
			MAX(CASE WHEN (LEFT(vwPL_LastRevals_1.tran_status, 2) IN ('01', '02', '03')) AND (dbo.ccyr.ccyr_ccyt_type = '01') THEN ccy_1.ccy_name ELSE NULL END)AS FCURNAME, 
			MAX(CASE WHEN (LEFT(vwPL_LastRevals_1.tran_status, 2) = ' ') AND (dbo.ccyr.ccyr_ccyt_type = '03') THEN ccy_1.ccy_name ELSE NULL END)AS FCURNAME2, 
			SUM(CASE WHEN (LEFT(vwPL_LastRevals_1.tran_status, 2) IN ('01', '02', '03')) AND (dbo.ccyr.ccyr_ccyt_type = '01') THEN vwPL_LastRevals_1.SODCalcAmt - vwPL_LastRevals_1.SODPosition * dbo.ccyr.ccyr_rate ELSE 0 END) AS FBNPL,
			SUM(CASE WHEN (LEFT(vwPL_LastRevals_1.tran_status, 2) = ' ') AND (dbo.ccyr.ccyr_ccyt_type = '03') THEN vwPL_LastRevals_1.SODCalcAmt - vwPL_LastRevals_1.SODPosition * dbo.ccyr.ccyr_rate ELSE 0 END) AS FXPL
			FROM dbo.ccy AS ccy_1 FULL OUTER JOIN
			dbo.ccyr ON ccy_1.ccy_code = dbo.ccyr.ccyr_ccy_code FULL OUTER JOIN
			dbo.vwPL_LastRevals AS vwPL_LastRevals_1 ON dbo.ccyr.ccyr_ccy_code = vwPL_LastRevals_1.tran_ccy_code
			GROUP BY vwPL_LastRevals_1.setl_order_date_time, ccy_1.ccy_code, ccy_1.ccy_name
		)T1
		
) AS Final

hi
Great this works fine, but getting null records and then the correct results

thanks
fairozkhan

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources