pzozulka
asked on
SQL Query Optimization Help
I have been trying to convert an inline SQL query (C# code behind) into a stored procedure, and tried to optimize it. Here is my finished version below, but it is still running slower than the inline query. Can you see any way to optimize further?
Details:
The nice thing about the inline query was that if the user did not specify any filters (parameters), then the query would be very stripped down. Here's what I mean:
string sql = "select * from Customer";
if ( stateFilter.IsSelected )
{
sql += " where Customer.State = " + stateFilter.SelectedState;
}
this.DataSource = this.ExecuteQuery(sql);
This was just an example, and there are 7 total filters they can choose from to filter down the result set. With the stored procedure, I have to account for all the possible filters the user selected, and conditionally use them in the WHERE clause. This slows down my query dramatically.
When the user selects a full load of filters, then of course the stored procedure runs faster, but my goal is to have the stored procedure run faster than the inline sql when the user doesn't select any filters.
Details:
The nice thing about the inline query was that if the user did not specify any filters (parameters), then the query would be very stripped down. Here's what I mean:
string sql = "select * from Customer";
if ( stateFilter.IsSelected )
{
sql += " where Customer.State = " + stateFilter.SelectedState;
}
this.DataSource = this.ExecuteQuery(sql);
This was just an example, and there are 7 total filters they can choose from to filter down the result set. With the stored procedure, I have to account for all the possible filters the user selected, and conditionally use them in the WHERE clause. This slows down my query dramatically.
When the user selects a full load of filters, then of course the stored procedure runs faster, but my goal is to have the stored procedure run faster than the inline sql when the user doesn't select any filters.
-- exec pReport_GLActivitySummaryRecap_CrossYear '1/1/2013', '12/31/2014', null, null, null, null, '', null, null
-- STORED PROCEDURE pReport_GLActivitySummaryRecap_CrossYear
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID('pReport_GLActivitySummaryRecap_CrossYear') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE pReport_GLActivitySummaryRecap_CrossYear
GO
CREATE PROCEDURE pReport_GLActivitySummaryRecap_CrossYear
(
@pBeginDate datetime,
@pEndDate datetime,
@pCustAccount int,
@pEntityGroup int,
@pCategory int,
@pEntityType int,
@pSpecificEntities varchar(8000),
@pGLAccountType int,
@pGoverningRegion int
--<Builder ReturnType="Select"/>
)
AS
BEGIN
DECLARE @BeginDate datetime = @pBeginDate
DECLARE @EndDate datetime = @pEndDate
DECLARE @CustAccount int = @pCustAccount
DECLARE @EntityGroup int = @pEntityGroup
DECLARE @Category int = @pCategory
DECLARE @EntityType int = @pEntityType
DECLARE @SpecificEntities varchar(8000) = @pSpecificEntities
DECLARE @GLAccountType int = @pGLAccountType
DECLARE @GoverningRegion int = @pGoverningRegion
DECLARE @FDY_EndDate DATETIME = DATEADD(yy, DATEDIFF(yy,0,@EndDate), 0)
IF @CustAccount = 0 SET @CustAccount = null
IF @EntityGroup = 0 SET @EntityGroup = null
IF @Category = 0 SET @Category = null
IF @EntityType = 0 SET @EntityType = null
IF @SpecificEntities = '' SET @SpecificEntities = null
IF @GLAccountType = 0 SET @GLAccountType = null
IF @GoverningRegion = 0 SET @GoverningRegion = null
DECLARE @specificEntitiesList TABLE
(
id int not null primary key
)
insert into @specificEntitiesList select * from dbo.createnumbertable(@SpecificEntities)
DECLARE @tmpTbl1 TABLE
(
GlAccountId int not null primary key,
GLESumAmount money
)
DECLARE @tmpTbl2 TABLE
(
GlAccountId int not null primary key,
GLESumAmount money
)
CREATE TABLE #tmpTbl3
(
EntityId int not null,
BusinessGroupId int,
BusinessCategoryId int
)
insert into #tmpTbl3
select x.EntityId, x.BusinessGroupId, x.BusinessCategoryId
from
(
select
EBC.EntityId,
BG.BusinessGroupId,
BC.BusinessCategoryId,
row_number() over (partition by EBC.EntityId order by EBC.EntityBusinessCategoryMapId) as seq
from
EntityBusinessCategoryMap as EBC
join BusinessCategory as BC on BC.BusinessCategoryId = EBC.BusinessCategoryId
join BusinessGroup as BG on BG.BusinessGroupId = BC.BusinessGroupId
) x
where x.seq = 1
-- @tmpTbl1
--------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------
IF @EntityType is null
BEGIN
INSERT INTO @tmpTbl1
SELECT
GLA.GlAccountId,
CASE WHEN GLA.GlAccountTypeId IN (4,5) THEN 0 ELSE Sum(GLE.Amount) END AS GleSumAmount
FROM
GLEntry as GLE
join GLTransaction as GLT on GLT.GLTransactionId = GLE.GLTransactionId
join GLAccount as GLA on GLA.GlAccountId = GLE.GLAccountId and (@GLAccountType is null or GLA.GlAccountTypeId = @GLAccountType)
left join CustomerAccount as CA on CA.PartyId = GLT.PartyId and (@CustAccount is null or GLT.PartyId = @CustAccount)
left join Region AS R ON CA.GoverningRegionId = R.RegionId and (@GoverningRegion is null or R.RegionId = @GoverningRegion)
left join Entity as E on E.PartyId = GLT.PartyId
left join Entity s ON CA.SubmittedById = s.PartyId
left join #tmpTbl3 y on y.EntityId = CA.SubmittedById
WHERE
GLT.WhenBooked < @BeginDate
and (@EntityGroup is null
or
(@Category is not null and (y.BusinessGroupId = @EntityGroup and y.BusinessCategoryId = @Category))
or
(@Category is null and y.BusinessGroupId = @EntityGroup)
)
GROUP BY
GLA.GlAccountId, GLA.GlAccountTypeId
INSERT INTO @tmpTbl2
SELECT
GLA.GlAccountId,
Sum(GLE.Amount) as 'GLESumAmount'
FROM
GLEntry as GLE
join GLTransaction as GLT on GLT.GLTransactionId = GLE.GLTransactionId
join GLAccount as GLA on GLA.GlAccountId = GLE.GLAccountId and (@GLAccountType is null or GLA.GlAccountTypeId = @GLAccountType)
left join CustomerAccount as CA on CA.PartyId = GLT.PartyId and (@CustAccount is null or GLT.PartyId = @CustAccount)
left join Region AS R ON CA.GoverningRegionId = R.RegionId and (@GoverningRegion is null or R.RegionId = @GoverningRegion)
left join Entity as E on E.PartyId = GLT.PartyId
left join Entity s ON CA.SubmittedById = s.PartyId
left join #tmpTbl3 y on y.EntityId = CA.SubmittedById
WHERE
GLT.WhenBooked <= @EndDate
and ((GLA.GlAccountTypeId IN (4,5) and GLT.WhenBooked >= @FDY_EndDate) or (GLA.GlAccountTypeId NOT IN (4,5) and 1 = 1))
and (@EntityGroup is null
or
(@Category is not null and (y.BusinessGroupId = @EntityGroup and y.BusinessCategoryId = @Category))
or
(@Category is null and y.BusinessGroupId = @EntityGroup)
)
GROUP BY
GLA.GlAccountId
END
--------------------------------------------------------------------------------------------------------------------------------
ELSE IF @EntityType = 1
BEGIN
INSERT INTO @tmpTbl1
SELECT
GLA.GlAccountId,
CASE WHEN GLA.GlAccountTypeId IN (4,5) THEN 0 ELSE Sum(GLE.Amount) END AS GleSumAmount
FROM
GLEntry as GLE
join GLTransaction as GLT on GLT.GLTransactionId = GLE.GLTransactionId
join GLAccount as GLA on GLA.GlAccountId = GLE.GLAccountId and (@GLAccountType is null or GLA.GlAccountTypeId = @GLAccountType)
left join CustomerAccount as CA on CA.PartyId = GLT.PartyId and (@CustAccount is null or GLT.PartyId = @CustAccount)
left join Region AS R ON CA.GoverningRegionId = R.RegionId and (@GoverningRegion is null or R.RegionId = @GoverningRegion)
left join Entity as E on E.PartyId = GLT.PartyId
left join Entity s ON CA.SubmittedById = s.PartyId
left join #tmpTbl3 y on y.EntityId = CA.SubmittedById
WHERE
GLT.WhenBooked < @BeginDate
and (@EntityGroup is null
or
(@Category is not null and (y.BusinessGroupId = @EntityGroup and y.BusinessCategoryId = @Category))
or
(@Category is null and y.BusinessGroupId = @EntityGroup)
)
and (CA.AgentId in (select id from @specificEntitiesList) or e.PartyId in (select id from @specificEntitiesList))
GROUP BY
GLA.GlAccountId, GLA.GlAccountTypeId
INSERT INTO @tmpTbl2
SELECT
GLA.GlAccountId,
Sum(GLE.Amount) as 'GLESumAmount'
FROM
GLEntry as GLE
join GLTransaction as GLT on GLT.GLTransactionId = GLE.GLTransactionId
join GLAccount as GLA on GLA.GlAccountId = GLE.GLAccountId and (@GLAccountType is null or GLA.GlAccountTypeId = @GLAccountType)
left join CustomerAccount as CA on CA.PartyId = GLT.PartyId and (@CustAccount is null or GLT.PartyId = @CustAccount)
left join Region AS R ON CA.GoverningRegionId = R.RegionId and (@GoverningRegion is null or R.RegionId = @GoverningRegion)
left join Entity as E on E.PartyId = GLT.PartyId
left join Entity s ON CA.SubmittedById = s.PartyId
left join #tmpTbl3 y on y.EntityId = CA.SubmittedById
WHERE
GLT.WhenBooked <= @EndDate
and ((GLA.GlAccountTypeId IN (4,5) and GLT.WhenBooked >= @FDY_EndDate) or (GLA.GlAccountTypeId NOT IN (4,5) and 1 = 1))
and (@EntityGroup is null
or
(@Category is not null and (y.BusinessGroupId = @EntityGroup and y.BusinessCategoryId = @Category))
or
(@Category is null and y.BusinessGroupId = @EntityGroup)
)
and (CA.AgentId in (select id from @specificEntitiesList) or e.PartyId in (select id from @specificEntitiesList))
GROUP BY
GLA.GlAccountId
END
--------------------------------------------------------------------------------------------------------------------------------
ELSE IF @EntityType = 2
BEGIN
INSERT INTO @tmpTbl1
SELECT
GLA.GlAccountId,
CASE WHEN GLA.GlAccountTypeId IN (4,5) THEN 0 ELSE Sum(GLE.Amount) END AS GleSumAmount
FROM
GLEntry as GLE
join GLTransaction as GLT on GLT.GLTransactionId = GLE.GLTransactionId
join GLAccount as GLA on GLA.GlAccountId = GLE.GLAccountId and (@GLAccountType is null or GLA.GlAccountTypeId = @GLAccountType)
left join CustomerAccount as CA on CA.PartyId = GLT.PartyId and (@CustAccount is null or GLT.PartyId = @CustAccount)
left join Region AS R ON CA.GoverningRegionId = R.RegionId and (@GoverningRegion is null or R.RegionId = @GoverningRegion)
left join Entity as E on E.PartyId = GLT.PartyId
left join Entity s ON CA.SubmittedById = s.PartyId
left join #tmpTbl3 y on y.EntityId = CA.SubmittedById
WHERE
GLT.WhenBooked < @BeginDate
and (@EntityGroup is null
or
(@Category is not null and (y.BusinessGroupId = @EntityGroup and y.BusinessCategoryId = @Category))
or
(@Category is null and y.BusinessGroupId = @EntityGroup)
)
and (CA.LenderId in (select id from @specificEntitiesList))
GROUP BY
GLA.GlAccountId, GLA.GlAccountTypeId
INSERT INTO @tmpTbl2
SELECT
GLA.GlAccountId,
Sum(GLE.Amount) as 'GLESumAmount'
FROM
GLEntry as GLE
join GLTransaction as GLT on GLT.GLTransactionId = GLE.GLTransactionId
join GLAccount as GLA on GLA.GlAccountId = GLE.GLAccountId and (@GLAccountType is null or GLA.GlAccountTypeId = @GLAccountType)
left join CustomerAccount as CA on CA.PartyId = GLT.PartyId and (@CustAccount is null or GLT.PartyId = @CustAccount)
left join Region AS R ON CA.GoverningRegionId = R.RegionId and (@GoverningRegion is null or R.RegionId = @GoverningRegion)
left join Entity as E on E.PartyId = GLT.PartyId
left join Entity s ON CA.SubmittedById = s.PartyId
left join #tmpTbl3 y on y.EntityId = CA.SubmittedById
WHERE
GLT.WhenBooked <= @EndDate
and ((GLA.GlAccountTypeId IN (4,5) and GLT.WhenBooked >= @FDY_EndDate) or (GLA.GlAccountTypeId NOT IN (4,5) and 1 = 1))
and (@EntityGroup is null
or
(@Category is not null and (y.BusinessGroupId = @EntityGroup and y.BusinessCategoryId = @Category))
or
(@Category is null and y.BusinessGroupId = @EntityGroup)
)
and (CA.LenderId in (select id from @specificEntitiesList))
GROUP BY
GLA.GlAccountId
END
--------------------------------------------------------------------------------------------------------------------------------
ELSE IF @EntityType = 3
BEGIN
INSERT INTO @tmpTbl1
SELECT
GLA.GlAccountId,
CASE WHEN GLA.GlAccountTypeId IN (4,5) THEN 0 ELSE Sum(GLE.Amount) END AS GleSumAmount
FROM
GLEntry as GLE
join GLTransaction as GLT on GLT.GLTransactionId = GLE.GLTransactionId
join GLAccount as GLA on GLA.GlAccountId = GLE.GLAccountId and (@GLAccountType is null or GLA.GlAccountTypeId = @GLAccountType)
left join CustomerAccount as CA on CA.PartyId = GLT.PartyId and (@CustAccount is null or GLT.PartyId = @CustAccount)
left join Region AS R ON CA.GoverningRegionId = R.RegionId and (@GoverningRegion is null or R.RegionId = @GoverningRegion)
left join Entity as E on E.PartyId = GLT.PartyId
left join Entity s ON CA.SubmittedById = s.PartyId
left join #tmpTbl3 y on y.EntityId = CA.SubmittedById
WHERE
GLT.WhenBooked < @BeginDate
and (@EntityGroup is null
or
(@Category is not null and (y.BusinessGroupId = @EntityGroup and y.BusinessCategoryId = @Category))
or
(@Category is null and y.BusinessGroupId = @EntityGroup)
)
and (s.SalespersonId in (select id from @specificEntitiesList) or e.PartyId in (select id from @specificEntitiesList))
GROUP BY
GLA.GlAccountId, GLA.GlAccountTypeId
INSERT INTO @tmpTbl2
SELECT
GLA.GlAccountId,
Sum(GLE.Amount) as 'GLESumAmount'
FROM
GLEntry as GLE
join GLTransaction as GLT on GLT.GLTransactionId = GLE.GLTransactionId
join GLAccount as GLA on GLA.GlAccountId = GLE.GLAccountId and (@GLAccountType is null or GLA.GlAccountTypeId = @GLAccountType)
left join CustomerAccount as CA on CA.PartyId = GLT.PartyId and (@CustAccount is null or GLT.PartyId = @CustAccount)
left join Region AS R ON CA.GoverningRegionId = R.RegionId and (@GoverningRegion is null or R.RegionId = @GoverningRegion)
left join Entity as E on E.PartyId = GLT.PartyId
left join Entity s ON CA.SubmittedById = s.PartyId
left join #tmpTbl3 y on y.EntityId = CA.SubmittedById
WHERE
GLT.WhenBooked <= @EndDate
and ((GLA.GlAccountTypeId IN (4,5) and GLT.WhenBooked >= @FDY_EndDate) or (GLA.GlAccountTypeId NOT IN (4,5) and 1 = 1))
and (@EntityGroup is null
or
(@Category is not null and (y.BusinessGroupId = @EntityGroup and y.BusinessCategoryId = @Category))
or
(@Category is null and y.BusinessGroupId = @EntityGroup)
)
and (s.SalespersonId in (select id from @specificEntitiesList) or e.PartyId in (select id from @specificEntitiesList))
GROUP BY
GLA.GlAccountId
END
--------------------------------------------------------------------------------------------------------------------------------
ELSE IF @EntityType = 4
BEGIN
INSERT INTO @tmpTbl1
SELECT
GLA.GlAccountId,
CASE WHEN GLA.GlAccountTypeId IN (4,5) THEN 0 ELSE Sum(GLE.Amount) END AS GleSumAmount
FROM
GLEntry as GLE
join GLTransaction as GLT on GLT.GLTransactionId = GLE.GLTransactionId
join GLAccount as GLA on GLA.GlAccountId = GLE.GLAccountId and (@GLAccountType is null or GLA.GlAccountTypeId = @GLAccountType)
left join CustomerAccount as CA on CA.PartyId = GLT.PartyId and (@CustAccount is null or GLT.PartyId = @CustAccount)
left join Region AS R ON CA.GoverningRegionId = R.RegionId and (@GoverningRegion is null or R.RegionId = @GoverningRegion)
left join Entity as E on E.PartyId = GLT.PartyId
left join Entity s ON CA.SubmittedById = s.PartyId
left join #tmpTbl3 y on y.EntityId = CA.SubmittedById
WHERE
GLT.WhenBooked < @BeginDate
and (@EntityGroup is null
or
(@Category is not null and (y.BusinessGroupId = @EntityGroup and y.BusinessCategoryId = @Category))
or
(@Category is null and y.BusinessGroupId = @EntityGroup)
)
and (ca.SubmittedById in (select id from @specificEntitiesList) or e.PartyId in (select id from @specificEntitiesList))
GROUP BY
GLA.GlAccountId, GLA.GlAccountTypeId
INSERT INTO @tmpTbl2
SELECT
GLA.GlAccountId,
Sum(GLE.Amount) as 'GLESumAmount'
FROM
GLEntry as GLE
join GLTransaction as GLT on GLT.GLTransactionId = GLE.GLTransactionId
join GLAccount as GLA on GLA.GlAccountId = GLE.GLAccountId and (@GLAccountType is null or GLA.GlAccountTypeId = @GLAccountType)
left join CustomerAccount as CA on CA.PartyId = GLT.PartyId and (@CustAccount is null or GLT.PartyId = @CustAccount)
left join Region AS R ON CA.GoverningRegionId = R.RegionId and (@GoverningRegion is null or R.RegionId = @GoverningRegion)
left join Entity as E on E.PartyId = GLT.PartyId
left join Entity s ON CA.SubmittedById = s.PartyId
left join #tmpTbl3 y on y.EntityId = CA.SubmittedById
WHERE
GLT.WhenBooked <= @EndDate
and ((GLA.GlAccountTypeId IN (4,5) and GLT.WhenBooked >= @FDY_EndDate) or (GLA.GlAccountTypeId NOT IN (4,5) and 1 = 1))
and (@EntityGroup is null
or
(@Category is not null and (y.BusinessGroupId = @EntityGroup and y.BusinessCategoryId = @Category))
or
(@Category is null and y.BusinessGroupId = @EntityGroup)
)
and (ca.SubmittedById in (select id from @specificEntitiesList) or e.PartyId in (select id from @specificEntitiesList))
GROUP BY
GLA.GlAccountId
END
--------------------------------------------------------------------------------------------------------------------------------
ELSE IF @EntityType = 5
BEGIN
INSERT INTO @tmpTbl1
SELECT
GLA.GlAccountId,
CASE WHEN GLA.GlAccountTypeId IN (4,5) THEN 0 ELSE Sum(GLE.Amount) END AS GleSumAmount
FROM
GLEntry as GLE
join GLTransaction as GLT on GLT.GLTransactionId = GLE.GLTransactionId
join GLAccount as GLA on GLA.GlAccountId = GLE.GLAccountId and (@GLAccountType is null or GLA.GlAccountTypeId = @GLAccountType)
left join CustomerAccount as CA on CA.PartyId = GLT.PartyId and (@CustAccount is null or GLT.PartyId = @CustAccount)
left join Region AS R ON CA.GoverningRegionId = R.RegionId and (@GoverningRegion is null or R.RegionId = @GoverningRegion)
left join Entity as E on E.PartyId = GLT.PartyId
left join Entity s ON CA.SubmittedById = s.PartyId
left join #tmpTbl3 y on y.EntityId = CA.SubmittedById
WHERE
GLT.WhenBooked < @BeginDate
and (@EntityGroup is null
or
(@Category is not null and (y.BusinessGroupId = @EntityGroup and y.BusinessCategoryId = @Category))
or
(@Category is null and y.BusinessGroupId = @EntityGroup)
)
and (s.ThirdPartySalespersonId in (select id from @specificEntitiesList) or e.PartyId in (select id from @specificEntitiesList))
GROUP BY
GLA.GlAccountId, GLA.GlAccountTypeId
INSERT INTO @tmpTbl2
SELECT
GLA.GlAccountId,
Sum(GLE.Amount) as 'GLESumAmount'
FROM
GLEntry as GLE
join GLTransaction as GLT on GLT.GLTransactionId = GLE.GLTransactionId
join GLAccount as GLA on GLA.GlAccountId = GLE.GLAccountId and (@GLAccountType is null or GLA.GlAccountTypeId = @GLAccountType)
left join CustomerAccount as CA on CA.PartyId = GLT.PartyId and (@CustAccount is null or GLT.PartyId = @CustAccount)
left join Region AS R ON CA.GoverningRegionId = R.RegionId and (@GoverningRegion is null or R.RegionId = @GoverningRegion)
left join Entity as E on E.PartyId = GLT.PartyId
left join Entity s ON CA.SubmittedById = s.PartyId
left join #tmpTbl3 y on y.EntityId = CA.SubmittedById
WHERE
GLT.WhenBooked <= @EndDate
and ((GLA.GlAccountTypeId IN (4,5) and GLT.WhenBooked >= @FDY_EndDate) or (GLA.GlAccountTypeId NOT IN (4,5) and 1 = 1))
and (@EntityGroup is null
or
(@Category is not null and (y.BusinessGroupId = @EntityGroup and y.BusinessCategoryId = @Category))
or
(@Category is null and y.BusinessGroupId = @EntityGroup)
)
and (s.ThirdPartySalespersonId in (select id from @specificEntitiesList) or e.PartyId in (select id from @specificEntitiesList))
GROUP BY
GLA.GlAccountId
END
--------------------------------------------------------------------------------------------------------------------------------
SELECT
Ltrim(Rtrim(GL.AccountNumber)) as GLAccountNumber,
Ltrim(Rtrim(GL.AccountName)) as GLAccountName,
at.NAME AS GLAccountType,
ISNULL(GleSumBeginning.GleSumAmount,0) as BeginningBalance,
ISNull(GleSumEnding.GLEsumAmount,0) - ISNull(GleSumBeginning.GleSumAmount,0) As NetChanges,
ISNULL(GleSumEnding.GLEsumAmount,0) as EndingBalance
FROM
GLAccount as GL
join GlAccountType at ON GL.GlAccountTypeId = at.GlAccountTypeId
left join @tmpTbl1 as GLeSumBeginning ON GLeSumBeginning.GlAccountId = GL.GlAccountId
left join @tmpTbl2 as GLeSumEnding ON GLeSumEnding.GlAccountId = GL.GlAccountId
ORDER BY GL.AccountNumber
DROP TABLE #tmpTbl3
END
GOASKER
I don't see a way to combine them. While they are nearly identical, there is a couple lines inside the WHERE clause that differentiates them, and in result returns significantly different data.
What I could do is combine all the common SELECTS and JOINS into a single query, and insert that result into a new temp table @tmpTbl4.
Then:
INSERT into @tmpTbl1
SELECT * FROM @tmpTbl4
WHERE ABC = 123
INSERT into @tmpTbl2
SELECT * FROM @tmpTbl4
WHERE ABC = 456
Is that what you meant?
Also, what did you mean by "Cluster #tmpTbl3 on EntityId".
Did you mean put a primary key on EntityId, or did you mean:
CREATE CLUSTERED INDEX cx ON #tmpTbl3 (EntityId, ............. )
What I could do is combine all the common SELECTS and JOINS into a single query, and insert that result into a new temp table @tmpTbl4.
Then:
INSERT into @tmpTbl1
SELECT * FROM @tmpTbl4
WHERE ABC = 123
INSERT into @tmpTbl2
SELECT * FROM @tmpTbl4
WHERE ABC = 456
Is that what you meant?
Also, what did you mean by "Cluster #tmpTbl3 on EntityId".
Did you mean put a primary key on EntityId, or did you mean:
CREATE CLUSTERED INDEX cx ON #tmpTbl3 (EntityId, ............. )
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
All the numbers for criteria# 1 are correct, but all for criteria #2 are not.
Not sure why.
Criteria #1 WHERE CLAUSE should be:
GLT.WhenBooked < @BeginDate and ((GLA.GlAccountTypeId IN (4,5) and GLT.WhenBooked >= @FDY_BeginDate) or (GLA.GlAccountTypeId NOT IN (4,5) and 1 = 1))
Criteria #2 WHERE CLAUSE should be:
GLT.WhenBooked <= @EndDate and ((GLA.GlAccountTypeId IN (4,5) and GLT.WhenBooked >= @FDY_EndDate) or (GLA.GlAccountTypeId NOT IN (4,5) and 1 = 1))
Having said that, before you provided your solution, which is GREAT by the way, I came up with something similar, which returns the correct results, but no where near the performance of your query. It's below:
Not sure why.
Criteria #1 WHERE CLAUSE should be:
GLT.WhenBooked < @BeginDate and ((GLA.GlAccountTypeId IN (4,5) and GLT.WhenBooked >= @FDY_BeginDate) or (GLA.GlAccountTypeId NOT IN (4,5) and 1 = 1))
Criteria #2 WHERE CLAUSE should be:
GLT.WhenBooked <= @EndDate and ((GLA.GlAccountTypeId IN (4,5) and GLT.WhenBooked >= @FDY_EndDate) or (GLA.GlAccountTypeId NOT IN (4,5) and 1 = 1))
Having said that, before you provided your solution, which is GREAT by the way, I came up with something similar, which returns the correct results, but no where near the performance of your query. It's below:
DECLARE @tmpTbl1 TABLE
(
GlAccountId int not null primary key,
GLESumAmountBeginning money,
GLESumAmountEnding money
)
IF @EntityType is null
BEGIN
INSERT INTO @tmpTbl1
SELECT
GLA.GlAccountId,
SUM(CASE WHEN GLT.WhenBooked < @BeginDate and ((GLA.GlAccountTypeId IN (4,5) and GLT.WhenBooked >= @FDY_BeginDate) or (GLA.GlAccountTypeId NOT IN (4,5) and 1 = 1)) THEN GLE.Amount END),
SUM(CASE WHEN GLT.WhenBooked <= @EndDate and ((GLA.GlAccountTypeId IN (4,5) and GLT.WhenBooked >= @FDY_EndDate) or (GLA.GlAccountTypeId NOT IN (4,5) and 1 = 1)) THEN GLE.Amount END)
FROM
GLEntry as GLE
join GLTransaction as GLT on GLT.GLTransactionId = GLE.GLTransactionId
join GLAccount as GLA on GLA.GlAccountId = GLE.GLAccountId and (@GLAccountType is null or GLA.GlAccountTypeId = @GLAccountType)
left join CustomerAccount as CA on CA.PartyId = GLT.PartyId and (@CustAccount is null or GLT.PartyId = @CustAccount)
left join Region AS R ON CA.GoverningRegionId = R.RegionId and (@GoverningRegion is null or R.RegionId = @GoverningRegion)
left join Entity as E on E.PartyId = GLT.PartyId
left join Entity s ON CA.SubmittedById = s.PartyId
left join #tmpTbl3 y on y.EntityId = CA.SubmittedById
WHERE
(@EntityGroup is null
or
(@Category is not null and (y.BusinessGroupId = @EntityGroup and y.BusinessCategoryId = @Category))
or
(@Category is null and y.BusinessGroupId = @EntityGroup)
)
GROUP BY
GLA.GlAccountId
END
I did an exact copy/paste from your original code for the conditions in the WHERE for the two different criteria, or at least I thought I did. Maybe you need to review the WHERE conditions in your initial post of the query??!
ASKER
After looking into this further, the conditions appear to fine. I think it may have something to do with the OR in the WHERE clause.
Criteria #2 numbers appear to be wrong only when there exists a criteria #1 item with the same GlAccountId (since we are grouping by both of these items).
All criteria #2 numbers that have a matching GlAccountId in Criteria #1 are correct.
Criteria #2 numbers appear to be wrong only when there exists a criteria #1 item with the same GlAccountId (since we are grouping by both of these items).
All criteria #2 numbers that have a matching GlAccountId in Criteria #1 are correct.
ASKER
I found something interesting in the result set of your query. Notice that if you SUM the GleSumAmount column, the result would be the correct answer I'm looking for -- my query's EndingSum column (criteria #2).
Here's a sample from my query below, I have verified these numbers are correct:
GlAccountId BeginningSum EndingSum
6 18973.14 2937.86
Here's what I'm getting from your query:
criteria# GlAccountId GleSumAmount
1 6 18973.14
2 6 -16035.28
Code for my query:
Code for your query:
Here's a sample from my query below, I have verified these numbers are correct:
GlAccountId BeginningSum EndingSum
6 18973.14 2937.86
Here's what I'm getting from your query:
criteria# GlAccountId GleSumAmount
1 6 18973.14
2 6 -16035.28
Code for my query:
SELECT
GLA.GlAccountId,
SUM(CASE WHEN GLT.WhenBooked < @BeginDate and (GLA.GlAccountTypeId NOT IN (4,5)) THEN GLE.Amount END) as BeginningSum,
SUM(CASE WHEN GLT.WhenBooked <= @EndDate and ((GLA.GlAccountTypeId IN (4,5) and GLT.WhenBooked >= @FDY_EndDate) or (GLA.GlAccountTypeId NOT IN (4,5) and 1 = 1)) THEN GLE.Amount END) as EndingSum
FROM
GLEntry as GLE
join GLTransaction as GLT on GLT.GLTransactionId = GLE.GLTransactionId
join GLAccount as GLA on GLA.GlAccountId = GLE.GLAccountId and GLA.GlAccountId = 6
GROUP BY
GLA.GlAccountIdCode for your query:
SELECT
criteria#,
GLA.GlAccountId,
Sum(GLE.Amount) AS GleSumAmount
FROM
GLEntry as GLE
join GLTransaction as GLT on GLT.GLTransactionId = GLE.GLTransactionId
join GLAccount as GLA on GLA.GlAccountId = GLE.GLAccountId and GLA.GlAccountId = 6
cross apply ( select CASE WHEN GLT.WhenBooked < @BeginDate THEN 1 ELSE 2 END as criteria# ) as crit
WHERE
( --tmptbl1 criteria
GLT.WhenBooked < @BeginDate
and GLA.GlAccountTypeId not in (4,5) --rather than add 0 for them, just ignore them
)
OR
( --tmptbl2 criteria
GLT.WhenBooked <= @EndDate
and ((GLA.GlAccountTypeId IN (4,5) and GLT.WhenBooked >= @FDY_EndDate) or (GLA.GlAccountTypeId NOT IN (4,5) and 1 = 1))
)
GROUP BY
criteria#, GLA.GlAccountId
ORDER BY
criteria#, GLA.GlAccountId
If the top version works, use it. The important thing is to get both totals in one query/join rather than having to run the query twice, once for each version.
ASKER
Below are the stats as to why I would much prefer to use your query. I just can't seem to make it return the correct results for criteria #2:
* Note: While some of the numbers below may seem to be small, in the production environment they are exponentially larger.
* Baseline query ( inline SQL query from the C# code behind -- using two temp tables )
* My query ( using one temp table -- using SUM and CASE to get totals )
* Your query (using the cross apply to get the criteria #)
Include Client Statistic (Avg Total Execution Time):
* Baseline query : 1730.1430
* My query : 1490.8000
* Your query: 1133.2860
SET STATISTICS IO ON
SET STATISTICS TIME ON
SQL Server Execution Times: Elapsed Time:
* Baseline query : 1535 ms
* My query : 1371
* Your query: 1118
Include Actual Execution Path
* Baseline query vs. My query: --- My query takes 43% of the total batch, while the baseline takes 57%
* Baseline query vs. Your query: --- Your query takes 38% of the total batch, while the baseline takes 62%
* Note: While some of the numbers below may seem to be small, in the production environment they are exponentially larger.
* Baseline query ( inline SQL query from the C# code behind -- using two temp tables )
* My query ( using one temp table -- using SUM and CASE to get totals )
* Your query (using the cross apply to get the criteria #)
Include Client Statistic (Avg Total Execution Time):
* Baseline query : 1730.1430
* My query : 1490.8000
* Your query: 1133.2860
SET STATISTICS IO ON
SET STATISTICS TIME ON
SQL Server Execution Times: Elapsed Time:
* Baseline query : 1535 ms
* My query : 1371
* Your query: 1118
Include Actual Execution Path
* Baseline query vs. My query: --- My query takes 43% of the total batch, while the baseline takes 57%
* Baseline query vs. Your query: --- Your query takes 38% of the total batch, while the baseline takes 62%
That makes sense. I'll look at it again as soon as I can.
I copied the original WHERE conditions exactly. We probably need to refine the way in the combined query that we determine if a row matches one or both of the conditions.
I copied the original WHERE conditions exactly. We probably need to refine the way in the combined query that we determine if a row matches one or both of the conditions.
ASKER
Thanks much, I would appreciate it.
Cluster #tmpTbl3 on EntityId; this could improve the joins to that table.