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 - 2007-10-16 : 06:43:22
Dear all,

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts
Posted - 2007-10-16 : 06:46:36
First of all format your query with basic indentation to be readable and then place it inside [<CODE>][/<CODE>] tags to retain the formating.

In the below manner:

select * from 
	(
	SELECT 
		COLUMN001 ACCOUNT_ID, 
		COLUMN002 BRANCH_ID, 
		COLUMN003 ACCOUNT_CODE, 
		COLUMN004 ACCOUNT_NAME, 
		case 
			when 
				(
				select 
					dbo.fgenaccbalance('7F4932A5-4709-40C8-A87F-C2D978A2FD2F' ,column001,getdate())
				) > 0 then (select dbo.fgenaccbalance('7F4932A5-4709-40C8-A87F-C2D978A2FD2F',column001,getdate())) 
			else 0 
		end as [Net_Debit_Balance] , 
		case 
			when 
				(
				select 
					dbo.fgenaccbalance('7F4932A5-4709-40C8-A87F-C2D978A2FD2F',column001,getdate())
				) < 0 then -(select dbo.fgenaccbalance( '7F4932A5-4709-40C8-A87F-C2D978A2FD2F',column001,getdate())) 
			else 0 
		end as [Net_Credit_Balance] , 
		column005 ALIAS_NAME, 
		COLUMN006 SHORT_NAME, 
		COLUMN010 CONTROL_ACC1,
		COLUMN007 ACC_TYPE_ID, 
		COLUMN008 ACCGROUP_ID, 
		COLUMN009 CG_ACCGROUP_ID, 
		COLUMN010 CONTROL_ACC, 
		COLUMN011 CONTROL_ACCTYPE, 
		COLUMN012 BLOCKED, 
		COLUMN014 ACCOUNT_TYPE 
	FROM OATABLE014 
	where 
		( COLUMN010 !='1' and 	COLUMN012 !='1'and ( COLUMN002 = '7f4932a5-4709-40c8-a87f-c2d978a2fd2f' or COLUMN002 = '799b860e-a832-4a68-8e94-fada44bbb7b9' ) ) 
	union 
	select 
		COLUMN001 ACCOUNT_ID, 
		COLUMN009 BRANCH_ID, 
		COLUMN003 ACCOUNT_CODE, 
		COLUMN004 ACCOUNT_NAME, 
		case 
			when 
				(
				select 
					dbo.fSubAccBalance('7F4932A5-4709-40C8-A87F-C2D978A2FD2F' ,column001,getdate())
				) > 0 then (select dbo.fSubAccBalance('7F4932A5-4709-40C8-A87F-C2D978A2FD2F',column001,getdate())) 
			else 0 
		end as [Net_Debit_Balance] , 
		case 
			when 
				(
				select 
					dbo.fSubAccBalance('7F4932A5-4709-40C8-A87F-C2D978A2FD2F',column001,getdate())
				) < 0 then -(select dbo.fSubAccBalance( '7F4932A5-4709-40C8-A87F-C2D978A2FD2F',column001,getdate())) 
			else 0 
		end as [Net_Credit_Balance] , 
		column005 ALIAS_NAME, 
		COLUMN006 SHORT_NAME, 
		COLUMN002 CONTROL_ACC1,
		COLUMN010 ACC_TYPE_ID, 
		'-' ACCGROUP_ID, 
		COLUMN009 CG_ACCGROUP_ID, 
		'-' CONTROL_ACC, 
		'-' CONTROL_ACCTYPE, 
		'-' BLOCKED, 
		'Cnt'+Convert(char(1),column010,001) ACCOUNT_TYPE 
	from 
		oatable030  
	) TBL
where 
	((tbl.branch_id = '7f4932a5-4709-40c8-a87f-c2d978a2fd2f' or tbl.branch_id = '799b860e-a832-4a68-8e94-fada44bbb7b9' ) 
	and tbl.acc_type_id != 'c' and tbl.acc_type_id != 'v' ) 
order by tbl.account_name


Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"
Go to Top of Page

msrs

32 Posts
Posted - 2007-10-16 : 07:01:50
What is the basic indentation i din't get what i have to do........

Thanks&Regards,

Msrs
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2007-10-16 : 07:01:57
[code]SELECT ACCOUNT_ID,
BRANCH_ID,
ACCOUNT_CODE,
ACCOUNT_NAME,
CASE
WHEN Balance > 0 THEN Balance
ELSE 0
END AS Net_Debit_Balance,
CASE
WHEN Balance < 0 THEN ABS(Balance)
ELSE 0
END AS Net_Credit_Balance,
ALIAS_NAME,
SHORT_NAME,
CONTROL_ACC1,
ACC_TYPE_ID,
ACCGROUP_ID,
CG_ACCGROUP_ID,
CONTROL_ACC,
CONTROL_ACCTYPE,
BLOCKED,
ACCOUNT_TYPE
FROM (
SELECT COLUMN001 AS ACCOUNT_ID,
COLUMN002 AS BRANCH_ID,
COLUMN003 AS ACCOUNT_CODE,
COLUMN004 AS ACCOUNT_NAME,
dbo.fGenAccBalance('7F4932A5-4709-40C8-A87F-C2D978A2FD2F', COLUMN001, CURRENT_TIMESTAMP) AS Balance
COLUMN005 AS ALIAS_NAME,
COLUMN006 AS SHORT_NAME,
COLUMN010 AS CONTROL_ACC1,
COLUMN007 AS ACC_TYPE_ID,
COLUMN008 AS ACCGROUP_ID,
COLUMN009 AS CG_ACCGROUP_ID,
COLUMN010 AS CONTROL_ACC,
COLUMN011 AS CONTROL_ACCTYPE,
COLUMN012 AS BLOCKED,
COLUMN014 AS ACCOUNT_TYPE
FROM OATABLE014
WHERE COLUMN010 <> '1'
AND COLUMN012 <> '1'
AND COLUMN002 IN ('{7f4932a5-4709-40c8-a87f-c2d978a2fd2f}', '{799b860e-a832-4a68-8e94-fada44bbb7b9}')
AND COLUMN007 NOT IN ('c', 'v')

UNION ALL

SELECT COLUMN001 AS ACCOUNT_ID,
COLUMN009 AS BRANCH_ID,
COLUMN003 AS ACCOUNT_CODE,
COLUMN004 AS ACCOUNT_NAME,
dbo.fSubAccBalance('7F4932A5-4709-40C8-A87F-C2D978A2FD2F', COLUMN001, CURRENT_TIMESTAMP) AS Balance,
COLUMN005 AS ALIAS_NAME,
COLUMN006 AS SHORT_NAME,
COLUMN002 AS CONTROL_ACC1,
COLUMN010 AS ACC_TYPE_ID,
'-' AS ACCGROUP_ID,
COLUMN009 AS CG_ACCGROUP_ID,
'-' AS CONTROL_ACC,
'-' AS CONTROL_ACCTYPE,
'-' AS BLOCKED,
'Cnt' + LEFT(COLUMN010, 1) AS ACCOUNT_TYPE
FROM OATABLE030
WHERE COLUMN009 IN ('{7f4932a5-4709-40c8-a87f-c2d978a2fd2f}', '{799b860e-a832-4a68-8e94-fada44bbb7b9}'
AND COLUMN010 NOT IN ('c', 'v')
) AS d
ORDER BY ACCOUNT_NAME[/code]I think this can be optimized further depending on what the two functions do.


E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts
Posted - 2007-10-16 : 07:03:17
One optimization can be avoiding repeated calls to the function by rewriting the query as below:

select 
	ACCOUNT_ID,
	BRANCH_ID,
	...,
	Case When Balance > 0 then Balance else 0 End as [Net_Debit_Balance],
	Case When Balance < 0 then Balance * -1 else 0 End as [Net_Credit_Balance],
	.... 	-- Other columns here
from 
	(
	SELECT 
		COLUMN001 ACCOUNT_ID, 
		COLUMN002 BRANCH_ID, 
		COLUMN003 ACCOUNT_CODE, 
		COLUMN004 ACCOUNT_NAME, 
		dbo.fgenaccbalance('7F4932A5-4709-40C8-A87F-C2D978A2FD2F' ,column001,getdate()) as Balance
		column005 ALIAS_NAME, 
		COLUMN006 SHORT_NAME, 
		COLUMN010 CONTROL_ACC1,
		COLUMN007 ACC_TYPE_ID, 
		COLUMN008 ACCGROUP_ID, 
		COLUMN009 CG_ACCGROUP_ID, 
		COLUMN010 CONTROL_ACC, 
		COLUMN011 CONTROL_ACCTYPE, 
		COLUMN012 BLOCKED, 
		COLUMN014 ACCOUNT_TYPE 
	FROM OATABLE014 
	where 
		( COLUMN010 !='1' and 	COLUMN012 !='1'and ( COLUMN002 = '7f4932a5-4709-40c8-a87f-c2d978a2fd2f' or COLUMN002 = '799b860e-a832-4a68-8e94-fada44bbb7b9' ) ) 
	union 
	select 
		COLUMN001 ACCOUNT_ID, 
		COLUMN009 BRANCH_ID, 
		COLUMN003 ACCOUNT_CODE, 
		COLUMN004 ACCOUNT_NAME, 
		dbo.fSubAccBalance('7F4932A5-4709-40C8-A87F-C2D978A2FD2F' ,column001,getdate()) as Balance
		column005 ALIAS_NAME, 
		COLUMN006 SHORT_NAME, 
		COLUMN002 CONTROL_ACC1,
		COLUMN010 ACC_TYPE_ID, 
		'-' ACCGROUP_ID, 
		COLUMN009 CG_ACCGROUP_ID, 
		'-' CONTROL_ACC, 
		'-' CONTROL_ACCTYPE, 
		'-' BLOCKED, 
		'Cnt'+Convert(char(1),column010,001) ACCOUNT_TYPE 
	from 
		oatable030  
	) TBL
where 
	((tbl.branch_id = '7f4932a5-4709-40c8-a87f-c2d978a2fd2f' or tbl.branch_id = '799b860e-a832-4a68-8e94-fada44bbb7b9' ) 
	and tbl.acc_type_id != 'c' and tbl.acc_type_id != 'v' ) 
order by tbl.account_name


Also, you can change UNION to UNION ALL if you are sure that result of both queries do not overlap.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2007-10-16 : 07:03:51
Also same type of question
here http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=85159
and here http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=84937


E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

msrs

32 Posts
Posted - 2007-10-16 : 08:42:40
even i am giving column names of a table then it will Retrieve the data with in 34 sec time.

Thanks&Regards,

Msrs
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2007-10-16 : 08:50:52
Did you try the suggestion posted by me on 10/16/2007 : 07:01:57 ?


E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts
Posted - 2007-10-16 : 09:53:19
post the execution plan....maybe you don't have sufficient/proper indices to support anything faster. Also you didn't mention the volume of data being processed nor the spec of the machine running the query....both have a BIG impact on the speed of a query.
Go to Top of Page

jackv
Master Smack Fu Yak Hacker

2179 Posts
Posted - 2007-10-16 : 11:24:45
Remember when comparing changes , always compare from an equal basis , run DBCC FREEPROCCACHE and
DBCC DROPCLEANBUFFERS , every time you make a change.
Further , update your statistics on all relevant indices

Jack Vamvas
--------------------
Search IT jobs from multiple sources- http://www.ITjobfeed.com
Go to Top of Page

msrs

32 Posts
Posted - 2007-10-16 : 23:53:12
Hi peso,

yes i tryed ur suggestions but it will not improve the performance ,plz tell me any other suggestions.




Thanks&Regards,

Msrs
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2007-10-17 : 01:35:39
There might be two problems here

1) None or not useful indexes
2) Your functions are so badly written they will kill any performance


E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

msrs

32 Posts
Posted - 2007-10-17 : 05:19:55
Thank u. it's working fine... the query will take 7sec to Retrieve data.

Thanks&Regards,

Msrs
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2007-10-17 : 05:32:48
What did you do?
You recently said my suggestion still took 34 seconds, and now you tell us it takes 7 seconds.


E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts
Posted - 2008-01-09 : 05:06:21
quote:
Originally posted by msrs

Dear all,


Who told you to delete all the old questions?

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2008-01-09 : 05:28:58
Or he realized SQL is not his cup of tee and decided to leave the business?

E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site