| Author |
 Topic  |
|
|
msrs
Starting Member
India
32 Posts |
 Posted - 10/16/2007 : 06:43:22
|
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
|
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 |
 |
|
|
msrs
Starting Member
India
32 Posts |
Posted - 10/16/2007 : 07:01:50
|
What is the basic indentation i din't get what i have to do........
Thanks&Regards,
Msrs |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 10/16/2007 : 07:01:57
|
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_NAMEI think this can be optimized further depending on what the two functions do.
E 12°55'05.25"
N 56°04'39.16" |
|
|
|
harsh_athalye
Flowing Fount of Yak Knowledge
India
5509 Posts |
Posted - 10/16/2007 : 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" |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
|
|
msrs
Starting Member
India
32 Posts |
Posted - 10/16/2007 : 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 |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 10/16/2007 : 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" |
|
|
|
AndrewMurphy
Flowing Fount of Yak Knowledge
Ireland
2915 Posts |
Posted - 10/16/2007 : 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. |
|
|
|
jackv
Flowing Fount of Yak Knowledge
United Kingdom
1773 Posts |
Posted - 10/16/2007 : 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
|
|
|
|
msrs
Starting Member
India
32 Posts |
Posted - 10/16/2007 : 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 |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 10/17/2007 : 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" |
|
|
|
msrs
Starting Member
India
32 Posts |
Posted - 10/17/2007 : 05:19:55
|
Thank u. it's working fine... the query will take 7sec to Retrieve data.
Thanks&Regards,
Msrs |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 10/17/2007 : 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" |
|
|
|
madhivanan
Premature Yak Congratulator
India
22461 Posts |
Posted - 01/09/2008 : 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 |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 01/09/2008 : 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" |
|
|
| |
Topic |
|
|
|
Query optimization
