Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Need help on this SP Urgent Expand / Collapse
Author
Message
Posted Thursday, June 26, 2014 11:11 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 1:28 PM
Points: 27, Visits: 50
I have been told to optimize this stored procedure . 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


  Post Attachments 
DB with Schema.zip (4 views, 39.82 KB)
Post #1586576
Posted Thursday, June 26, 2014 11:47 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 2:17 AM
Points: 40,157, Visits: 36,542
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1586598
Posted Thursday, June 26, 2014 1:09 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, October 17, 2014 2:15 PM
Points: 13,077, Visits: 12,523
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1586625
Posted Saturday, June 28, 2014 5:54 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 1:28 PM
Points: 27, Visits: 50
HI-Please let me know what do you suggest how I Can change that I have to change the datetime in varchar
Post #1587128
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse