rewrite poor query 1

[ersatz]

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 db_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

 

[markros]

where exists (select V.ACCNT_STRCTR_SEQ_NO
from dbo.M_Account_V01 V
where EXPENS_CATGRY in('311','304') and V.ACCNT_STRCTR_SEQ_NO = CR.Accnt_strctr_Seq_No)
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)
)

etc.

WHERE IN (select)

can be re-written in two ways:

using INNER JOIN or using where exists

where exists in some cases more accurate, see

http://forums.asp.net/p/1383812/2936717.aspx#2936717

 

[ersatz]

many many thanks, i'll try it..

 

[markros]

I only re-wrote one, because your query is too complex.

If you use SQL Server 2005 or up I would recommend to split your query into pieces and use multiple CTEs.

 

[ersatz]

i suppose is more complicated... i don't understand how to rewrite this....

 

[markros]

Can you simplify it? Let's try from the simple and add complexity iteratively.

Post an English description of the problem and also post the first simple version of the query (one or two criteria only) and let's build it step by step.

 

[ersatz]

yea, i k now, my english is not so good..
the query is more complicated....
it has 3 unions....

i put here juste one.
i search data in 3 db, on the same server(sql 2k0. but i want to migrate one db to sql 2k5 .
when i test the query, on the sql2k5, the execution time is aprox 20 minutes. on the sql 2k the execution time is just 2 min....
is it more clear now?

 

[markros]

No, not really. I wanted to simplify this query if possible. Can you remove some of the conditions for initial tests ?

 

[ersatz]

problem solved. the code was rewritten.

thanks for your help anyway

 

[markros]

Can you show some of the ideas you used to simplify it?

 

[ersatz]

by creating a new view, on the first server(dwh db), with less columns and data. like i told you the query was more complicated, like select from ..union select from ..union select from.... and now is select from ...union select from.....

so the problem was the the big number of data from the first server and the number of columns used by the views...
thanks

 

[hmckillop]

Makros,

I think you deserve a star for your persistence (even though ersatz didnt think so), keep up the good work.

"I'm living so far beyond my income that we may almost be said to be living apart