rewrite poor query - 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

SQL Server 2000 Forums

SQL Server Development (2000)

rewrite poor query

Author

Topic

joanne
Starting Member

46 Posts

Posted - 2009-03-25 : 23:36:05

hello,

i whant to know how can i rewrite this query to not use
select from()......
and select from()
in the where ....
thanks very much

select
'COST_REVENUE' AS COST_TYPE,

cr.source_system_id,
cr.Trans_Sequence_No as Trans_Sequence_No,
Source_System_Id + LEFT(CONVERT(VARCHAR(10),Trans_Sequence_No),8) AS

Trans_Sequence_id,

CR.ACCNT_STRCTR_SEQ_NO AS ACCNT_STRCTR_SEQ_NO,
case when P.DIV_ID = 'XO' and P.PROJ_CLNT_NO is not NULL and

P.PROJ_ROUTNG_REF_NO is NULL
then P.ALLOC_CO_ID
else P.CO_ID
END AS CO_ID,
case when P.DIV_ID = 'XO' and P.PROJ_CLNT_NO is not NULL and

P.PROJ_ROUTNG_REF_NO is NULL
then P.ALLOC_DIV_ID
else P.DIV_ID
END AS DIV_ID,
case when P.DIV_ID = 'XO' and P.PROJ_CLNT_NO is not NULL and

P.PROJ_ROUTNG_REF_NO is NULL
then P.DIV_OFFCL_NM_ALLOC
else P.DIV_OFFCL_NM
END AS DIV_OFFCL_NM,
case when P.DIV_ID = 'XO' and P.PROJ_CLNT_NO is not NULL and

P.PROJ_ROUTNG_REF_NO is NULL
then P.CO_OFFCL_NM_ALLOC
else P.CO_OFFCL_NM
END AS CO_OFFCL_NM,
A.COMPANY_NO AS COMPANY_NO,
A.ACCNT_NO AS ACCNT_NO,
A.SUBJ AS SUBJ,
A.SUBDIV AS SUBDIV,
A.BILL_FLAG AS BILL_FLAG,
A.MINOR AS MINOR,
A.EXPENS_CATGRY AS EXPENS_CATGRY,
S.ACNT_STRCT_VALUE_DS AS ACCT_SUBJ_DS,
P.PROJ_TYPE AS PROJ_TYPE,
P.PROJ_NM AS PROJ_NM,
P.PROJ_MANAG_FIRST_NM AS Manager_First_Name,
P.PROJ_MANAG_LAST_NM AS Manager_Last_Name,

CR.Acct_Base_Cost_Am AS BASE_COST,
CR.ACCT_REAL_COST_AM AS REAL_COST,
CR.ACCT_REVNU_AM AS REVENUE,
CR.ACT_FRING_BEN AS FRING_BEN,

CR.VOUCHER_ID AS VOUCHER_ID,

CR.Period_End_Date AS Period_End_Date

from dbo.O_PR_COST_V01 CR

join dbo.M_Account_V01 A on CR.Accnt_strctr_Seq_No =

A.ACCNT_STRCTR_SEQ_NO
left join M_OVERHEAD_ACCOUNT_E01 S on S.SUBJ = A.SUBJ and

S.EXPENS_CATGRY = A.EXPENS_CATGRY_2 and S.LANGUE = 'EN'
left join dbo.M_PROJ_V02 P on CR.MD_PROJ_NO = P.MD_PROJ_NO

where
CR.Accnt_strctr_Seq_No in ( select ACCNT_STRCTR_SEQ_NO
from dbo.M_Account_V01
where EXPENS_CATGRY in('311','304') )
and

A.SUBJ in (select ACCTBL_SUBJ_CD
from dbo.ACCTBL_SUBJ_V01
where ACCTBL_SUBJ_SOURC IN ('GTT','CDD') )

and (P.PROJ_TYPE in ('2','21','211','214','22','221','222','223','225')
or
( P.PROJ_TYPE in ('212', '213')
and P.DIV_ID = 'XO'
and P.PROJ_CLNT_NO is not NULL
and P.PROJ_ROUTNG_REF_NO is NULL))

and CR.MD_PROJ_NO not in
(select MD_PROJ_NO from dbo.ACCTBL_PROJ_V01
where EXPEN_ACCT_IN = 1)

and CR.TRANS_CODE in ('20','15','16','18','19','40')

and (
(CR.VOUCHER_ID in (select VOUCHER_ID from

O_PR_COST_V01 where
O_PR_COST_V01.SITE_CD = 'TGI'
and O_PR_COST_V01.PERIOD_END_DATE = CR.PERIOD_END_DATE)
)
OR
(
CR.VOUCHER_ID like '%GT%' or CR.VOUCHER_ID like '%CD%' or

CR.VOUCHER_ID like '%PM%')
)

AND YEAR(CR.Period_End_Date) = 2008

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-03-26 : 13:42:56

Can you format your query and then post. I'm sure someone will help out once your post is readible.

Ifor
Aged Yak Warrior

700 Posts

Posted - 2009-03-27 : 06:39:47

I am not too sure about some of your brackets, but in general this query does not look too bad. You could try re-writing the IN subqueries with EXISTS subqueries to see if you are more likely to produce a semi-join in the query plan.

What you do not want to do is move the extra tables in the WHERE clause to the FROM clause as this will almost certainly increase the amount of processing.

Also, replacing
AND YEAR(CR.Period_End_Date) = 2008
with
AND CR.Period_End_Date >= '20080101'
AND CR.Period_End_Date < '20090101'

should make CR.Period_End_Date SARGable.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-27 : 06:55:07

[code]SELECT 'COST_REVENUE' AS COST_TYPE,
cr.source_system_id,
cr.Trans_Sequence_No as Trans_Sequence_No,
Source_System_Id + LEFT(CONVERT(VARCHAR(10), Trans_Sequence_No),8) AS Trans_Sequence_id,
CR.ACCNT_STRCTR_SEQ_NO AS ACCNT_STRCTR_SEQ_NO,
case
when P.DIV_ID = 'XO' and P.PROJ_CLNT_NO is not NULL and P.PROJ_ROUTNG_REF_NO is NULL then P.ALLOC_CO_ID
else P.CO_ID
END AS CO_ID,
case
when P.DIV_ID = 'XO' and P.PROJ_CLNT_NO is not NULL and P.PROJ_ROUTNG_REF_NO is NULL then P.ALLOC_DIV_ID
else P.DIV_ID
END AS DIV_ID,
case
when P.DIV_ID = 'XO' and P.PROJ_CLNT_NO is not NULL and P.PROJ_ROUTNG_REF_NO is NULL then P.DIV_OFFCL_NM_ALLOC
else P.DIV_OFFCL_NM
END AS DIV_OFFCL_NM,
case
when P.DIV_ID = 'XO' and P.PROJ_CLNT_NO is not NULL and P.PROJ_ROUTNG_REF_NO is NULL then P.CO_OFFCL_NM_ALLOC
else P.CO_OFFCL_NM
END AS CO_OFFCL_NM,
A.COMPANY_NO AS COMPANY_NO,
A.ACCNT_NO AS ACCNT_NO,
A.SUBJ AS SUBJ,
A.SUBDIV AS SUBDIV,
A.BILL_FLAG AS BILL_FLAG,
A.MINOR AS MINOR,
A.EXPENS_CATGRY AS EXPENS_CATGRY,
S.ACNT_STRCT_VALUE_DS AS ACCT_SUBJ_DS,
P.PROJ_TYPE AS PROJ_TYPE,
P.PROJ_NM AS PROJ_NM,
P.PROJ_MANAG_FIRST_NM AS Manager_First_Name,
P.PROJ_MANAG_LAST_NM AS Manager_Last_Name,
CR.Acct_Base_Cost_Am AS BASE_COST,
CR.ACCT_REAL_COST_AM AS REAL_COST,
CR.ACCT_REVNU_AM AS REVENUE,
CR.ACT_FRING_BEN AS FRING_BEN,
CR.VOUCHER_ID AS VOUCHER_ID,
CR.Period_End_Date AS Period_End_Date
from dbo.O_PR_COST_V01 AS CR
inner join dbo.M_Account_V01 as A on CR.Accnt_strctr_Seq_No = A.ACCNT_STRCTR_SEQ_NO
left join M_OVERHEAD_ACCOUNT_E01 AS S on S.SUBJ = A.SUBJ
and S.EXPENS_CATGRY = A.EXPENS_CATGRY_2
and S.LANGUE = 'EN'
left join dbo.M_PROJ_V02 as P on CR.MD_PROJ_NO = P.MD_PROJ_NO
where exists (select * FROM dbo.M_Account_V01 AS x where CR.Accnt_strctr_Seq_No = x.ACCNT_STRCTR_SEQ_NO and x.EXPENS_CATGRY in ('311', '304'))
and exists (select * from dbo.ACCTBL_SUBJ_V01 as x WHERE A.SUBJ = x.ACCTBL_SUBJ_CD and x.ACCTBL_SUBJ_SOURC IN ('GTT', 'CDD'))
and (P.PROJ_TYPE in ('2','21','211','214','22','221','222','223','225')
or
( P.PROJ_TYPE in ('212', '213')
and P.DIV_ID = 'XO'
and P.PROJ_CLNT_NO is not NULL
and P.PROJ_ROUTNG_REF_NO is NULL)
)
and not exists (select * FROM dbo.ACCTBL_PROJ_V01 as x WHERE CR.MD_PROJ_NO = x.MD_PROJ_NO and x.EXPEN_ACCT_IN = 1)
and CR.TRANS_CODE in ('20','15','16','18','19','40')
and (exists (SELECT * FROM O_PR_COST_V01 AS x WHERE CR.VOUCHER_ID = x.VOUCHER_ID and x.SITE_CD = 'TGI' and x.PERIOD_END_DATE = CR.PERIOD_END_DATE)
OR CR.VOUCHER_ID like '%GT%'
or CR.VOUCHER_ID like '%CD%'
or CR.VOUCHER_ID like '%PM%'
)
AND CR.Period_End_Date >= '20080101'
AND CR.Period_End_Date < '20090101'
[/code]

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

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-03-27 : 07:04:56

Peso, You have 2 'from' there in your first exist :))

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-27 : 08:57:09

A good exercise for OP?
:-)

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources