SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Query optimization
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

msrs
Starting Member

India
32 Posts

Posted - 10/16/2007 :  06:43:22  Show Profile  Reply with Quote
Dear all,

Edited by - msrs on 01/09/2008 03:51:58

harsh_athalye
Flowing Fount of Yak Knowledge

India
5509 Posts

Posted - 10/16/2007 :  06:46:36  Show Profile  Visit harsh_athalye's Homepage  Click to see harsh_athalye's MSN Messenger address  Send harsh_athalye a Yahoo! Message  Reply with Quote
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"

Edited by - harsh_athalye on 10/16/2007 06:53:57
Go to Top of Page

msrs
Starting Member

India
32 Posts

Posted - 10/16/2007 :  07:01:50  Show Profile  Reply with Quote
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

Sweden
29910 Posts

Posted - 10/16/2007 :  07:01:57  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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
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
Flowing Fount of Yak Knowledge

India
5509 Posts

Posted - 10/16/2007 :  07:03:17  Show Profile  Visit harsh_athalye's Homepage  Click to see harsh_athalye's MSN Messenger address  Send harsh_athalye a Yahoo! Message  Reply with Quote
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

Sweden
29910 Posts

Posted - 10/16/2007 :  07:03:51  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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
Starting Member

India
32 Posts

Posted - 10/16/2007 :  08:42:40  Show Profile  Reply with Quote
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

Sweden
29910 Posts

Posted - 10/16/2007 :  08:50:52  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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
Flowing Fount of Yak Knowledge

Ireland
2916 Posts

Posted - 10/16/2007 :  09:53:19  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

United Kingdom
1912 Posts

Posted - 10/16/2007 :  11:24:45  Show Profile  Visit jackv's Homepage  Reply with Quote
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
Starting Member

India
32 Posts

Posted - 10/16/2007 :  23:53:12  Show Profile  Reply with Quote
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

Sweden
29910 Posts

Posted - 10/17/2007 :  01:35:39  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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
Starting Member

India
32 Posts

Posted - 10/17/2007 :  05:19:55  Show Profile  Reply with Quote
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

Sweden
29910 Posts

Posted - 10/17/2007 :  05:32:48  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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

India
22713 Posts

Posted - 01/09/2008 :  05:06:21  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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

Sweden
29910 Posts

Posted - 01/09/2008 :  05:28:58  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000