SQL优化

CubieZhou 2011-08-28 08:51:02

请问以下的SQL如何优化执行速度最快？

ALTER procedure [dbo].[Insert_Manual]

(

 @Year as int = 2011,

 @Month  as int =2

)



as

Begin

----Testing

--declare @Year as int = 2011

--declare @Month  as int =2



set @Year = (Select top 1 Year(GLDGJ) from ZIG)

set @Month= (Select top 1 MONTH(GLDGJ) from ZIG)



----------delete and Insert into ZIg HIST

Delete from ZIG_HIST 

where MONTH(GLDGJ)=@Month

and Year(GLDGJ)=@Year

and GLCO in 

(

	Select Distinct GLCO from ZIG

)



insert into ZIG_HIST

select  [GLDOC]

      ,[GLDGJ]

      ,[GLMCU]

      ,[GLOBJ]

      ,[GLEXA]

      ,[GLEXR]

      ,[GLAA]

      ,[GLSBL]

      ,[GLAN8]

      ,[GLFY]

      ,[GLPN]

      ,[GLCO] from ZIG







--select * from Service_Log

--select * from ZIG



------Delete Previos Record

--delete from AD50_FACT

--where REF_YEAR=@Year and MONTH(date)=@Month

-- and ANALYSIS_CODE='ACT'

delete from AD50_FACT where Table_Name in ('PARENT_SCRIPT', 'PARENT_SCRIPT_AC') and REF_YEAR=@Year and MONTH(date)=@Month

 and ANALYSIS_CODE='ACT'



delete  from AD50_FACT 

where REF_YEAR=@Year and MONTH(date)=@Month

 and ANALYSIS_CODE='ACT'

 and exists

 (

	Select * from ZIG 

	where 

	 MONTH(GLDGJ)=@Month

	and YEAR(GLDGJ)= @Year

	and GLOBJ	= AD50_FACT.ACCOUNT

	and GLMCU = AD50_FACT.BU_CODE

	

 )

 

 

 

------ Insert from the ZIG



 INSERT INTO [dbo].[AD50_FACT]

           ([ACCOUNT]

           ,[BU_CODE]

           ,[ANALYSIS_CODE]

           ,[DATE]

           ,[AMOUNT]

           ,[AMOUNT_CNY]

           ,[AMOUNT_EUR]

           ,[AMOUNT_BRATE]

           ,[REF_YEAR]

           ,[LOCAL_CURRENCY]

           ,[Table_Name]

           ,[YTD_LC]

           ,[YTD_CNY]

           ,[YTD_EUR]

           ,[YTD_BRATE]) 

          

Select GLOBJ, GLMCU,'ACT',  

cast( (@Year ) as varchar(4)) + '-' + REPLICATE('0',2-LEN(MONTH(GLDGJ)))+ cast(month(GLDGJ) as varchar(2))+ '-'+ '01'  as [Date],

CASE

	WHEN (GLOBJ LIKE 'A3%') THEN

		sum(-GLAA/country.cur_divide)

	ELSE

		sum(GLAA/country.cur_divide)

END AS GLAA, 

isnull([BRASI].[dbo].[ConvertCurrency] (

		COUNTRY.CURRENCY

		,'CNY'

		,'ACT'

		,CASE

			when (GLOBJ LIKE 'A3%') THEN

				sum(-GLAA/COUNTRY.CUR_DIVIDE)

			else

				sum(GLAA/COUNTRY.CUR_DIVIDE)

		 end

		,MONTH(GLDGJ)

		,(@Year)),0) ,

isnull([BRASI].[dbo].[ConvertCurrency] (

		COUNTRY.CURRENCY

		,'EUR'

		,'ACT'

		,CASE

			when (GLOBJ LIKE 'A3%') THEN

				sum(-GLAA/COUNTRY.CUR_DIVIDE)

			else

				sum(GLAA/COUNTRY.CUR_DIVIDE)

		 end

		,MONTH(GLDGJ)

		,(@Year)),0),

isnull([BRASI].[dbo].[ConvertCurrency] (

		COUNTRY.CURRENCY

		,'EUR'

		,'BUD'

		,CASE

			when (GLOBJ LIKE 'A3%') THEN

				sum(-GLAA/COUNTRY.CUR_DIVIDE)

			else

				sum(GLAA/COUNTRY.CUR_DIVIDE)

		 end

		,MONTH(GLDGJ)

		,(@Year)),0)

,@Year

,COUNTRY.CURRENCY

,'ZIGTransfer'

,0,0,0,0						  

								 

from ZIG inner join ORGANISATION on ZIG.GLMCU= ORGANISATION.ORG_CODE inner join

COUNTRY on ORGANISATION.COUNTRY_CODE= COUNTRY.COUNTRY_CODE

group by GLOBJ, GLMCU,COUNTRY.CURRENCY ,cast( (@Year ) as varchar(4)) + '-' + REPLICATE('0',2-LEN(MONTH(GLDGJ)))+ cast(month(GLDGJ) as varchar(2))+ '-'+ '01'  , MONTH(GLDGJ)





-----Calculate the Parent Balance

declare @Imax as int

set @Imax = (select MAX(LEVEL_HIERARCHY)as MAX_LEVELHIERARCY from ACCOUNTS_AD50)



-----Update the Parent----------------------

	while (@Imax >=1)

	Begin

			---Insert -------------------------	

			Insert into AD50_FACT

			select distinct  PARENT,BU_CODE,ANALYSIS_CODE,DATE,0,0,0,0, REF_YEAR,AD50FACT.LOCAL_CURRENCY, 'PARENT_SCRIPT_AC',0,0,0,0

						from AD50_FACT as AD50FACT inner join ACCOUNTS_AD50

						on AD50FACT.ACCOUNT=ACCOUNTS_AD50.ACCOUNT

						where AD50FACT.ANALYSIS_CODE='ACT' AND PARENT is not null and AD50FACT.REF_YEAR in (@Year) and MONTH(date)=@Month  and ACCOUNTS_AD50.LEVEL_HIERARCHY=@Imax and not exists

						(

							Select * from  AD50_FACT

							where

							AD50_FACT.ACCOUNT=ACCOUNTS_AD50.PARENT

							and

							AD50_FACT.BU_CODE = AD50FACT.BU_CODE

							and

							AD50_FACT.ANALYSIS_CODE = AD50FACT.ANALYSIS_CODE

							and

							AD50_FACT.DATE = AD50FACT.DATE

							and

							AD50_FACT.REF_YEAR = AD50FACT.REF_YEAR

							



						)

						

	-------------Update-------------------------



						Update AD50_FACT  set

								Amount =  vw.Amount,

								AMOUNT_CNY = vw.AMOUNT_CNY  ,

								Amount_EUR= vw.AMOUNT_EUR,

								Amount_BRATE= vw.AMOUNT_BRATE

							from

							(

								Select SUM(amount) as Amount,0 as AMOUNT_CNY,0 as AMOUNT_EUR,0 as AMOUNT_BRATE , Parent,BU_CODE, Analysis_Code, Date

								from AD50_FACT inner join ACCOUNTS_AD50 on AD50_FACT.ACCOUNT= ACCOUNTS_AD50.ACCOUNT

								where   LEVEL_HIERARCHY = @Imax and REF_YEAR in (@Year) and MONTH(date)=@Month

								and ANALYSIS_CODE='ACT'

								group by Parent,BU_CODE, Analysis_Code, Date



							) as vw

							where AD50_FACT.Account=vw.Parent

							and

							AD50_FACT.BU_CODE=vw.BU_CODE

							and

							AD50_FACT.Analysis_Code =vw.Analysis_Code

							and

							AD50_FACT.DATE = vw.DATE

							and

							AD50_FACT.REF_YEAR in (@Year)

							and AD50_FACT.Analysis_Code='ACT'

							and MONTH(AD50_FACT.DATE)=@Month



	set @Imax = @Imax -1



	END





END