Query optimization - 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

Query optimization

Author

Topic

msrs

32 Posts

Posted - 2008-08-07 : 00:04:42

Dear All,

Please optimize this query .this query takes 21 sec to execute. any suggestions plz.....

SELECT distinct V1.Id Id,V1.Name PName,V2.Id CBranch_Id,
V2.Name CBranch_Name,'','K','','','P&L','Profit or Loss for the period ',
(SUM(BALANCE_Amt1)-SUM(BALANCE_Amt2)),0,0,0 FROM ftbl ('9ee015b3-9568-446a-9eec-1bce9bbf3343','2008-8-6') PBL
INNER JOIN Table14 T14 ON T14.COLUMN001=PBL.Acc_Id AND T14.COLUMN007 IN ('I','E')
INNER JOIN Table13 T13 ON T13.COLUMN001=T14.COLUMN008
INNER JOIN Vcompany V2 ON V2.ID = PBL.CBranch_Id
INNER JOIN Vcompany V1 ON V2.Parent = V1.Id
GROUP BY V1.Id,V1.Name,V2.Id,V2.Name

Thanks&Regards,

Msrs

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-07 : 01:28:35

Why do you want both DISTINCT & GROUP BY? Also what does function ftbl do?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-07 : 03:40:00

1) You are usingh the view vCompany twice.
2) Function ftbl can be poorly written.
3) There are no table aliases fro BALANCE_Amt1 and BALANCE_Amt2 columns.

SELECT		v1.ID AS ID,
		v1.Name AS pName,
		v2.ID AS CBranch_ID,
		v2.Name AS CBranch_Name,
		'',
		'K',
		'',
		'',
		'P&L',
		'Profit or Loss for the period',
		SUM(BALANCE_Amt1) - SUM(BALANCE_Amt2),
		0,
		0,
		0
FROM		ftbl('9ee015b3-9568-446a-9eec-1bce9bbf3343', '2008-8-6') AS PBL
INNER JOIN	Table14 AS t14 ON t14.COLUMN001 = PBL.Acc_ID
			AND t14.COLUMN007 IN ('I', 'E')
INNER JOIN	Table13 AS t13 ON t13.COLUMN001 = t14.COLUMN008
INNER JOIN	vCompany AS v2 ON v2.ID = PBL.CBranch_ID
INNER JOIN	vCompany AS v1 ON v2.Parent = v1.ID
GROUP BY	v1.ID,
		v1.Name,
		v2.ID,
		v2.Name

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources