• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 63
  • Last Modified:

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.

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

Open in new window

0
pzozulka
Asked:
pzozulka
  • 6
  • 5
1 Solution
 
Scott PletcherSenior DBACommented:
Combine the queries that INSERT into @tmpTbl1 and @tmpTbl2 into a single query, if the joins are identical for both queries, which it looks that way to me.  This will take some slight reworking of the code but should save id.

Cluster #tmpTbl3 on EntityId; this could improve the joins to that table.
0
 
pzozulkaAuthor Commented:
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, .............  )
0
 
Scott PletcherSenior DBACommented:
I believe the WHERE conditions could be OR'd together to combine them and eliminate doing effectively the same joins twice.  We'd add a "criteria#" as the first column of the pk that distinguished between the types.

Here's a sample of the first query type combined:

DECLARE @tmpTbl TABLE
(
      criteria# tinyint not null,
      GlAccountId int not null,
      GLESumAmount money null,
      primary key ( criteria#, GlAccountId )
)

IF @EntityType is null
BEGIN

INSERT INTO @tmpTbl

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 (@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
        cross apply (
            select CASE WHEN GLT.WhenBooked < @BeginDate THEN 1 ELSE 2 END as criteria#
        ) as assign_alias_names

WHERE
    ( --tmptbl1 criteria      
        GLT.WhenBooked < @BeginDate
      and GLA.GlAccountTypeId not in (4,5) --rather than add 0 for them, just ignore them
      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)
            )
      )
      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))
      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
      criteria#, GLA.GlAccountId
ORDER BY
        criteria#, GLA.GlAccountId
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
pzozulkaAuthor Commented:
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:

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

Open in new window

0
 
Scott PletcherSenior DBACommented:
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??!
0
 
pzozulkaAuthor Commented:
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.
0
 
pzozulkaAuthor Commented:
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:
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.GlAccountId

Open in new window


Code 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

Open in new window

0
 
Scott PletcherSenior DBACommented:
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.
0
 
pzozulkaAuthor Commented:
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%
0
 
Scott PletcherSenior DBACommented:
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.
0
 
pzozulkaAuthor Commented:
Thanks much, I would appreciate it.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now