June 26, 2014 at 11:11 am
I have been told to optimize this stored procedure :w00t: . Please view the attached database schema.
Please let me know if you have seen any thing wrong with this stored procedure.
Your URGENT HELP IS HIGHLY appreciated.
/****** Object: StoredProcedure [dbo].[spProfitNLossWithPreviousMonthYear] Script Date: 06/26/2014 21:44:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[spProfitNLossWithPreviousMonthYear]
@StartDate datetime = null,
@EndDate datetime = null,
@IsClosingEntry tinyint = 0
AS
BEGIN
declare @MonthStartDate as datetime
declare @MonthEndDate as datetime
declare @YearStartDate as datetime
declare @YearEndDate as datetime
declare @PreviousYearMonthStartDate as datetime
declare @PreviousYearMonthEndDate as datetime
declare @PreviousYearStartDate as datetime
declare @PreviousYearEndDate as datetime
if isdate(@StartDate) = 0
begin
set @YearStartDate = '1900-01-01 00:00:00.000'
set @PreviousYearStartDate = DATEADD(yy, -1, '1900-01-01 00:00:00.000')
end
else
begin
set @YearStartDate = @StartDate
set @PreviousYearStartDate = DATEADD(yy, -1, @StartDate)
end
if isdate(@EndDate) = 0
begin
set @YearEndDate = getdate()
set @PreviousYearEndDate = DATEADD(yy, -1, getdate())
end
else
begin
set @YearEndDate = @EndDate
set @PreviousYearEndDate = DATEADD(yy, -1, @EndDate)
end
----First Day of Month
set @MonthStartDate = DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@YearEndDate),0)) + 1
----Last Day of Month
set @MonthEndDate = DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@YearEndDate)+1,0))
----First Day of Month in Previous Year
set @PreviousYearMonthStartDate = DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@PreviousYearEndDate),0)) + 1
----Last Day of Month in Previous Year
set @PreviousYearMonthEndDate = DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@PreviousYearEndDate)+1,0))
--print (cast(@MonthStartDate as varchar))
--print (cast(@MonthEndDate as varchar))
--print (cast(@PreviousYearMonthStartDate as varchar))
--print (cast(@PreviousYearMonthEndDate as varchar))
--If the user selects “Select Fiscal Date” from the date selector combobox, then the calculation for YearBalance
--should include all transactions (even Closing Entry and Closing Entry Revision transactions)
if @IsClosingEntry = 1
begin
select GLAccountsID, GLAccountTypeID,GLAccountTypesDescription,GLNumber,GLAccountsDescription, AccountLevel, isnull(ParentGLAccountID,0) ParentGLAccountID,
vwGLAccountsWithLevels.EnumKey GLAccountTypesEnumKey,
--isnull(MonthBalance,0.00) MonthBalance, isnull(YearBalance,0.00) YearBalance,
--isnull(PreviousYearMonthBalance,0.00) PreviousYearMonthBalance, isnull(PreviousYearBalance,0.00) PreviousYearBalance
MonthBalance, YearBalance, PreviousYearMonthBalance, PreviousYearBalance
from vwGLAccountsWithLevels
left outer join
(select GLAccountID, sum(DebitAmount - CreditAmount) YearBalance from vwTransactionDetails
where cast(convert(varchar(10),PostingDate,110) as datetime) between cast(convert(varchar(10),@YearStartDate,110) as datetime) and cast(convert(varchar(10),@YearEndDate,110) as datetime)
group by GLAccountID) YearlyBalance
on vwGLAccountsWithLevels.GLAccountsID = YearlyBalance.GLAccountID and IsProfitLoss = 1
left outer join
(select GLAccountID, sum(DebitAmount - CreditAmount) MonthBalance from vwTransactionDetails
where cast(convert(varchar(10),PostingDate,110) as datetime) between cast(convert(varchar(10),@MonthStartDate,110) as datetime) and cast(convert(varchar(10),@MonthEndDate,110) as datetime)
and EnumKey != 'ClosingEntry' and EnumKey != 'ClosingEntryRevision'
group by GLAccountID) MonthlyBalance
on vwGLAccountsWithLevels.GLAccountsID = MonthlyBalance.GLAccountID and IsProfitLoss = 1
left outer join
(select GLAccountID, sum(DebitAmount - CreditAmount) PreviousYearBalance from vwTransactionDetails
where cast(convert(varchar(10),PostingDate,110) as datetime) between cast(convert(varchar(10),@PreviousYearStartDate,110) as datetime) and cast(convert(varchar(10),@PreviousYearEndDate,110) as datetime)
group by GLAccountID) PreviousYearlyBalance
on vwGLAccountsWithLevels.GLAccountsID = PreviousYearlyBalance.GLAccountID and IsProfitLoss = 1
left outer join
(select GLAccountID, sum(DebitAmount - CreditAmount) PreviousYearMonthBalance from vwTransactionDetails
where cast(convert(varchar(10),PostingDate,110) as datetime) between cast(convert(varchar(10),@PreviousYearMonthStartDate,110) as datetime) and cast(convert(varchar(10),@PreviousYearMonthEndDate,110) as datetime)
and EnumKey != 'ClosingEntry' and EnumKey != 'ClosingEntryRevision'
group by GLAccountID) PreviousYearMonthlyBalance
on vwGLAccountsWithLevels.GLAccountsID = PreviousYearMonthlyBalance.GLAccountID and IsProfitLoss = 1
--where (MonthBalance is not null OR YearBalance is not null OR PreviousYearBalance is not null OR PreviousYearMonthBalance is not null)
where GLAccountTypesDescription in ('Income','Cost of Sales','Selling & Operating Expense','General & Admin. Expense','Other Income','Other Expense')
order by GLAccountTypeID,cast(GLNumber as varchar)
end
else
begin
select GLAccountsID, GLAccountTypeID,GLAccountTypesDescription,GLNumber,GLAccountsDescription, AccountLevel, isnull(ParentGLAccountID,0) ParentGLAccountID,
vwGLAccountsWithLevels.EnumKey GLAccountTypesEnumKey,
--isnull(MonthBalance,0.00) MonthBalance, isnull(YearBalance,0.00) YearBalance,
--isnull(PreviousYearMonthBalance,0.00) PreviousYearMonthBalance, isnull(PreviousYearBalance,0.00) PreviousYearBalance
MonthBalance, YearBalance, PreviousYearMonthBalance, PreviousYearBalance
from vwGLAccountsWithLevels
left outer join
(select GLAccountID, sum(DebitAmount - CreditAmount) YearBalance from vwTransactionDetails
where cast(convert(varchar(10),PostingDate,110) as datetime) between cast(convert(varchar(10),@YearStartDate,110) as datetime) and cast(convert(varchar(10),@YearEndDate,110) as datetime)
and EnumKey != 'ClosingEntry' and EnumKey != 'ClosingEntryRevision'
group by GLAccountID) YearlyBalance
on vwGLAccountsWithLevels.GLAccountsID = YearlyBalance.GLAccountID and IsProfitLoss = 1
left outer join
(select GLAccountID, sum(DebitAmount - CreditAmount) MonthBalance from vwTransactionDetails
where cast(convert(varchar(10),PostingDate,110) as datetime) between cast(convert(varchar(10),@MonthStartDate,110) as datetime) and cast(convert(varchar(10),@YearEndDate,110) as datetime)
and EnumKey != 'ClosingEntry' and EnumKey != 'ClosingEntryRevision'
group by GLAccountID) MonthlyBalance
on vwGLAccountsWithLevels.GLAccountsID = MonthlyBalance.GLAccountID and IsProfitLoss = 1
left outer join
(select GLAccountID, sum(DebitAmount - CreditAmount) PreviousYearBalance from vwTransactionDetails
where cast(convert(varchar(10),PostingDate,110) as datetime) between cast(convert(varchar(10),@PreviousYearStartDate,110) as datetime) and cast(convert(varchar(10),@PreviousYearEndDate,110) as datetime)
and EnumKey != 'ClosingEntry' and EnumKey != 'ClosingEntryRevision'
group by GLAccountID) PreviousYearlyBalance
on vwGLAccountsWithLevels.GLAccountsID = PreviousYearlyBalance.GLAccountID and IsProfitLoss = 1
left outer join
(select GLAccountID, sum(DebitAmount - CreditAmount) PreviousYearMonthBalance from vwTransactionDetails
where cast(convert(varchar(10),PostingDate,110) as datetime) between cast(convert(varchar(10),@PreviousYearMonthStartDate,110) as datetime) and cast(convert(varchar(10),@PreviousYearMonthEndDate,110) as datetime)
and EnumKey != 'ClosingEntry' and EnumKey != 'ClosingEntryRevision'
group by GLAccountID) PreviousYearMonthlyBalance
on vwGLAccountsWithLevels.GLAccountsID = PreviousYearMonthlyBalance.GLAccountID and IsProfitLoss = 1
--where (MonthBalance is not null OR YearBalance is not null OR PreviousYearBalance is not null OR PreviousYearMonthBalance is not null)
where GLAccountTypesDescription in ('Income','Cost of Sales','Selling & Operating Expense','General & Admin. Expense','Other Income','Other Expense')
order by GLAccountTypeID,cast(GLNumber as varchar)
end
END
June 26, 2014 at 11:47 am
Plenty wrong. For starters, this
where cast(convert(varchar(10),PostingDate,110) as datetime) between cast(convert(varchar(10),@YearStartDate,110) as datetime) and cast(convert(varchar(10),@YearEndDate,110) as datetime)
Change that so that you have no functions of any form on the columns. Hint, just drop the casts and converts.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 26, 2014 at 1:09 pm
To add to what Gail already pointed out:
Multiple execution paths.
Repeatedly hitting the same view with nonSARGable predicates (EnumKey != )
Tuning this is far more than an online forum is capable of. First of all your sql script is almost 10k lines of t-sql. To truly optimize is several days. I am not willing to put that much effort into a forum post. My recommendation would be to find somebody local who is good at optimizing procedures and hire them as a consultant.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 28, 2014 at 5:54 am
HI-Please let me know what do you suggest how I Can change that I have to change the datetime in varchar
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply