asked on
SQL: Convert correlated subquery to JOIN
Among other performance issues in my query, the correlated subquery below is causing a massive performance hit. I would like to try to re-write the subquery into a JOIN (derived table) to see if perhaps this improves the queries performance to satisfactory levels. I tried re-writing it into a JOIN, but my biggest issue is that inside the JOIN statements in the outer query, RT.PeriodDate and RT.PartyId are not visible.
Correlated Subquery
Correlated Subquery
(
SELECT COUNT(*) as countAcitve
FROM Entity en
join CustomerAccount CuAc on en.PartyId = (CASE WHEN @reportType = 'Agent' THEN CuAc.AgentId
WHEN @reportType = 'Submitted By' THEN CuAc.SubmittedById
END)
WHERE
en.PartyId = RT.PartyId
AND ((@reportType = 'Agent' AND en.EntityTypeId = 3 AND en.Code NOT LIKE '%T%') OR (@reportType = 'Submitted By'))
AND CuAc.AccountCreateDate < DATEADD(MONTH, DATEDIFF(MONTH, 0, RT.PeriodDate) + 1, 0)
AND (CuAc.ArchiveDate IS NULL OR CuAc.ArchiveDate >= DATEADD(MONTH, DATEDIFF(MONTH, 0, RT.PeriodDate) + 1, 0))
AND (@pAgentList IS NULL OR en.PartyId IN (select * from @AgentsList))
AND (@pPortfolioRegionList IS NULL OR en.PortfolioRegionId IN (select * from @PortfolioRegionList))
) as ActiveLoans,
-- STORED PROCEDURE pReport_AgentProfitability
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID('pReport_AgentProfitability') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE pReport_AgentProfitability
GO
-- ************************************************************************************************************
-- Case ####:
-- Description:
--
-- ************************************************************************************************************
CREATE PROCEDURE [pReport_AgentProfitability]
@pStartDate Datetime,
@pEndDate Datetime,
@pReportType VARCHAR(50),
@pPortfolioRegionList VARCHAR(8000),
@pMonthlyCostPerAccount MONEY,
@pCostOfFunds MONEY,
@pAgentList VARCHAR(8000),
@pSortBy VARCHAR(100) = ''
AS
BEGIN
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
DECLARE @startDate DATETIME = @pStartDate
DECLARE @endDate DATETIME = @pEndDate
DECLARE @reportType VARCHAR(50) = @pReportType
DECLARE @monthlyCostPerAccount MONEY = @pMonthlyCostPerAccount
DECLARE @costOfFunds MONEY = @pCostOfFunds
IF (@pPortfolioRegionList ='') SET @pPortfolioRegionList = null
IF (@pAgentList ='') SET @pAgentList = null
DECLARE @PortfolioRegionList TABLE
(
portfolioId int not null primary key
)
INSERT INTO @PortfolioRegionList select * from dbo.createnumbertable(@pPortfolioRegionList)
--select * from @PortfolioRegionList
DECLARE @AgentsList TABLE
(
agentId int not null primary key
)
INSERT INTO @AgentsList select * from dbo.createnumbertable(@pAgentList)
--select * from @AgentsList
IF OBJECT_ID('tempdb..#tmpMap') IS NOT NULL
DROP TABLE #tmpMap
-- Reference table containing GlAccountingMap
CREATE TABLE #tmpMap
(
GlAccountTypeId INT NOT NULL,
AccountType VARCHAR(50),
GlAbstractTypeId INT,
AbstractType VARCHAR(50),
GlAccountingMapTypeId INT,
AccountingMapType VARCHAR(50),
GlAccountId INT,
AccountName VARCHAR(50),
GlAccountingMapModifierTypeId INT,
GlAccountingMapModifierType VARCHAR(50)
)
INSERT INTO #tmpMap
SELECT DISTINCT
act.GlAccountTypeId, act.Name AS AccountType, at.GlAbstractTypeId, at.Name AS AbstractType, amt.GlAccountingMapTypeId,
amt.Name AS AccountingMapType, a.GlAccountId, a.AccountName, ammt.GlAccountingMapModifierTypeId, ammt.Name AS GlAccountingMapModifierType
FROM
(
SELECT DISTINCT
GlAccountingMapTypeId, GlAbstractTypeId, GlAccountId, GlAccountingMapModifierTypeId
FROM
GlAccountingMap
) am
JOIN GlAbstractType at ON am.GlAbstractTypeId = at.GlAbstractTypeId
JOIN GlAccount a ON am.GlAccountId = a.GlAccountId
JOIN GlAccountType act ON a.GlAccountTypeId = act.GlAccountTypeId
JOIN GlAccountingMapType amt ON am.GlAccountingMapTypeId = amt.GlAccountingMapTypeId
JOIN GlAccountingMapModifierType ammt ON am.GlAccountingMapModifierTypeId = ammt.GlAccountingMapModifierTypeId
ORDER BY amt.GlAccountingMapTypeId, at.GlAbstractTypeId, act.GlAccountTypeId, a.GlAccountId, ammt.GlAccountingMapModifierTypeId
;WITH
cteTally10 AS (
SELECT 0 AS tally UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL
SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
),
cteTally100 AS (
SELECT *
FROM (
SELECT ROW_NUMBER() OVER (ORDER BY c1.tally) - 1 AS tally
FROM cteTally10 c1 CROSS JOIN cteTally10 c2
) x
WHERE tally BETWEEN 0 AND DATEDIFF(MONTH, @startDate, @endDate)
),
cteEntities AS (
SELECT DISTINCT e.PartyId, e.Name1, e.Code
FROM Entity e
join CustomerAccount ca on e.PartyId = ca.AgentId
WHERE ((@reportType = 'Agent' AND e.EntityTypeId = 3 AND e.Code NOT LIKE '%T%') OR (@reportType = 'Submitted By'))
AND (@pAgentList IS NULL OR e.PartyId IN (select * from @AgentsList))
AND (@pPortfolioRegionList IS NULL OR e.PortfolioRegionId IN (select * from @PortfolioRegionList))
AND ca.AccountCreateDate < DATEADD(MONTH, DATEDIFF(MONTH, 0, @endDate) + 1, 0)
AND (ca.ArchiveDate IS NULL OR ca.ArchiveDate >= DATEADD(MONTH, DATEDIFF(MONTH, 0, @endDate) + 1, 0))
)
SELECT
RT.*,
CONVERT(VARCHAR,DATEPART(MONTH,RT.PeriodDate))+'/'+CONVERT(VARCHAR,DATEPART(YEAR,RT.PeriodDate)) as Period,
DT.*,
RF.ReferralAmount,
dtGL.InterestIncome, dtGL.FeeIncome,
dtGLWO.NETWriteOffs ,
(
SELECT COUNT(*) as countAcitve
FROM Entity en
join CustomerAccount CuAc on en.PartyId = (CASE WHEN @reportType = 'Agent' THEN CuAc.AgentId
WHEN @reportType = 'Submitted By' THEN CuAc.SubmittedById
END)
WHERE
en.PartyId = RT.PartyId
AND ((@reportType = 'Agent' AND en.EntityTypeId = 3 AND en.Code NOT LIKE '%T%') OR (@reportType = 'Submitted By'))
AND CuAc.AccountCreateDate < DATEADD(MONTH, DATEDIFF(MONTH, 0, RT.PeriodDate) + 1, 0)
AND (CuAc.ArchiveDate IS NULL OR CuAc.ArchiveDate >= DATEADD(MONTH, DATEDIFF(MONTH, 0, RT.PeriodDate) + 1, 0))
AND (@pAgentList IS NULL OR en.PartyId IN (select * from @AgentsList))
AND (@pPortfolioRegionList IS NULL OR en.PortfolioRegionId IN (select * from @PortfolioRegionList))
) as ActiveLoans,
@costOfFunds / 100 as CostofFunds,
( SUM(CASE WHEN dtADB.WhenBooked < RT.PeriodDate THEN dtADB.Amount END)
+
SUM(CASE WHEN dtADB.WhenBooked < RT.PeriodEndDate THEN dtADB.Amount END)
) / 2 AS AvgDailyBalance
FROM
(
/* The cross join places every month against every partyid, to facilitate a subsequent left join */
SELECT
c.PartyId, c.Name1 as EntityName, c.Code,
DATEADD(MONTH, DATEDIFF(MONTH, 0, @startDate) + t.tally, 0) AS PeriodDate,
DATEADD(MONTH, DATEDIFF(MONTH, 0, @startDate) + t.tally + 1, 0) AS PeriodEndDate
FROM
cteTally100 t CROSS JOIN cteEntities c
) AS RT
LEFT JOIN
(
SELECT
en.PartyId,
t.WhenBooked,
ent.Amount
FROM
Entity en
JOIN CustomerAccount CuAc ON en.PartyId = (CASE WHEN @reportType = 'Agent' THEN CuAc.AgentId
WHEN @reportType = 'Submitted By' THEN CuAc.SubmittedById
END)
JOIN GlTransaction t ON t.PartyId = CuAc.PartyId
JOIN GlEntry ent ON ent.GlTransactionId = t.GlTransactionId
JOIN GlAccount a ON a.GlAccountId = ent.GlAccountId
WHERE a.AccountName = 'Accounts Receivable - Premiums'
AND ((@reportType = 'Agent' AND en.EntityTypeId = 3 AND en.Code NOT LIKE '%T%') OR (@reportType = 'Submitted By'))
AND t.WhenBooked < DATEADD(MONTH, DATEDIFF(MONTH, 0, @endDate) + 1, 0)
AND (@pAgentList IS NULL OR en.PartyId IN (select * from @AgentsList))
AND (@pPortfolioRegionList IS NULL OR en.PortfolioRegionId IN (select * from @PortfolioRegionList))
) AS dtADB ON RT.PartyId = dtADB.PartyId AND
dtADB.WhenBooked < RT.PeriodEndDate
LEFT JOIN
(
SELECT
e.PartyId as PartyId,
DATEADD(MONTH, DATEDIFF(MONTH, 0, PFL.CreatedOn), 0) as createMonth,
DATEADD(day, -1, DATEADD(month, DATEDIFF(month,0,PFL.CreatedOn) + 1 ,0)) as endMonth,
COUNT(PFL.PremiumFinanceLoanId) as AllLoansThisMonth,
SUM(PFL.AmountFinanced) as AllLoansAmountFinanced,
ROUND(AVG(CONVERT(FLOAT,(PFL.[DownPayment]-IP.[TotalEarnedTaxFees]-PFL.[EarnedBrokerFee])) /
(PFL.[TotalPremium]-IP.[TotalEarnedTaxFees]-PFL.[EarnedBrokerFee])) * 100,2)as AVGDownPaymentPercent,
AVG(PFL.APR) as AvgAPR,
ROUND((SUM(CASE WHEN CA.PrimaryAccountStatusId = 2 THEN 1 ELSE 0 END) /
CONVERT(FLOAT , COUNT(CA.PartyId))) * 100,2) as CancelRatio
FROM
Entity e
JOIN CustomerAccount CA ON e.PartyId = (CASE WHEN @reportType = 'Agent' THEN CA.AgentId
WHEN @reportType = 'Submitted By' THEN CA.SubmittedById
END)
JOIN PremiumFinanceLoan PFL ON PFL.CustomerAccountId = CA.PartyId
LEFT JOIN
(
SELECT InPo.PremiumFinanceLoanId,SUM(InPo.EarnedTaxesFees) as TotalEarnedTaxFees
FROM InsurancePolicy InPo
GROUP BY InPo.PremiumFinanceLoanId
) IP ON PFL.PremiumFinanceLoanId = IP.PremiumFinanceLoanId
WHERE
PFL.CreatedOn >= @startDate
AND PFL.CreatedOn < DATEADD(DAY, 1, @endDate)
AND ((@reportType = 'Agent' AND e.EntityTypeId = 3 AND e.Code NOT LIKE '%T%') OR (@reportType = 'Submitted By'))
AND (@pAgentList IS NULL OR e.PartyId IN (select * from @AgentsList))
AND (@pPortfolioRegionList IS NULL OR e.PortfolioRegionId IN (select * from @PortfolioRegionList))
GROUP BY
e.PartyId,
DATEADD(MONTH, DATEDIFF(MONTH, 0, PFL.CreatedOn), 0),
DATEADD(day, -1, DATEADD(month, DATEDIFF(month,0,PFL.CreatedOn) + 1 ,0))
) DT ON DT.PartyId = RT.PartyId AND
DT.createMonth >= RT.PeriodDate AND
DT.createMonth < RT.PeriodEndDate
LEFT JOIN
(
SELECT
e.PartyId, SUM(DISTINCT ReFe.Amount) as ReferralAmount, DATEADD(MONTH, DATEDIFF(MONTH, 0, ReFe.DatePaid), 0) as DatePaid
FROM
Entity e
JOIN CustomerAccount CuAc ON e.PartyId = (CASE WHEN @reportType = 'Agent' THEN CuAc.AgentId
WHEN @reportType = 'Submitted By' THEN CuAc.SubmittedById
END)
JOIN ReferralFee ReFe ON CuAc.PartyId = ReFe.CustomerAccountId
WHERE
--RF.ReferralFeeTypeId NOT IN (5,6) AND -- Probably needed, but is not specified in the requirements
ReFe.DatePaid >= @startDate
AND ReFe.DatePaid < DATEADD(DAY, 1, @endDate)
AND ((@reportType = 'Agent' AND e.EntityTypeId = 3 AND e.Code NOT LIKE '%T%') OR (@reportType = 'Submitted By'))
AND (@pAgentList IS NULL OR e.PartyId IN (select * from @AgentsList))
AND (@pPortfolioRegionList IS NULL OR e.PortfolioRegionId IN (select * from @PortfolioRegionList))
GROUP BY
e.PartyId, DATEADD(MONTH, DATEDIFF(MONTH, 0, ReFe.DatePaid), 0)
) RF ON RF.PartyId = RT.PartyId AND
RF.DatePaid >= RT.PeriodDate AND
RF.DatePaid < RT.PeriodEndDate
LEFT JOIN
(
SELECT
e.PartyId,
-- Interest Income
(
SUM(CASE WHEN m.GlAccountingMapTypeId = 1 -- Customer Accounts
AND GLe.GlAbstractAccountId = 1 -- Accrued Interest
AND GLe.GlAccountingEventId = 11 -- New Loan
THEN GLe.Amount ELSE 0
END) +
SUM(CASE WHEN m.GlAccountingMapTypeId = 1 -- Customer Accounts
AND GLe.GlAbstractAccountId = 4 -- Non-Refundable Fee
THEN GLe.Amount ELSE 0
END) +
SUM(CASE WHEN m.GlAccountingMapTypeId = 8 -- Interest Accrual / Refund
AND GLe.GlAbstractAccountId = 1 -- Accrued Interest
THEN GLe.Amount ELSE 0
END) +
SUM(CASE WHEN GLe.GlAbstractAccountId = 1000 -- Manual
AND GLe.GlAccountingEventId = 38 -- Manual Transaction
AND a.GlAccountTypeId = 4 -- Income
AND (a.AccountName ='Finance Charge Income' OR a.AccountName ='Interest Income')
THEN GLe.Amount ELSE 0
END)
) * -1
as InterestIncome,
-- Fee Income
(
SUM(CASE WHEN GLe.GlAccountingEventId IN (9,10) -- Late Fee Assessed / Late Fee Waived
AND m.GlAccountingMapTypeId = 4 -- Late Fees
AND m.GlAbstractTypeId = 7 -- Income
THEN GLe.Amount ELSE 0
END) +
SUM(CASE WHEN GLe.GlAccountingEventId IN (2,3) -- Cancellation Fee Assessed / Cancellation Fee Waived
AND m.GlAccountingMapTypeId = 5 -- Cancellation Fees
AND m.GlAbstractTypeId = 7 -- Income
THEN GLe.Amount ELSE 0
END) +
SUM(CASE WHEN GLe.GlAccountingEventId IN (36,37) -- Misc. Fee Assessed / Misc. Fee Waived
AND m.GlAccountingMapTypeId = 15 -- Misc. Fees
AND m.GlAbstractTypeId = 7 -- Income
THEN GLe.Amount ELSE 0
END) +
SUM(CASE WHEN GLe.GlAccountingEventId IN (16,17) -- Reinstatement Fee Assessed / Reinstatement Fee Waived
AND m.GlAccountingMapTypeId = 6 -- Reinstatement Fees
AND m.GlAbstractTypeId = 7 -- Income
THEN GLe.Amount ELSE 0
END) +
SUM(CASE WHEN GLe.GlAccountingEventId IN (19,20) -- Return Fee Assessed / Return Fee Waived
AND m.GlAccountingMapTypeId = 7 -- Return Fees
AND m.GlAbstractTypeId = 7 -- Income
THEN GLe.Amount ELSE 0
END)
) * -1
as FeeIncome,
DATEADD(month, DATEDIFF(month,0,GLt.WhenBooked),0) as WhenBooked
FROM
Entity e
JOIN CustomerAccount CuAc ON e.PartyId = (CASE WHEN @reportType = 'Agent' THEN CuAc.AgentId
WHEN @reportType = 'Submitted By' THEN CuAc.SubmittedById
END)
JOIN GlTransaction GLt ON GLt.PartyId = CuAc.PartyId
LEFT JOIN GlEntry GLe ON GLe.GlTransactionId = GLt.GlTransactionId
LEFT JOIN #tmpMap m ON GLe.GlAbstractAccountId = m.GlAbstractTypeId
LEFT JOIN GlAccount a on a.GlAccountId = GLe.GlAccountId
WHERE
GLt.WhenBooked >= @startDate
AND GLt.WhenBooked < DATEADD(DAY, 1, @endDate)
AND ((@reportType = 'Agent' AND e.EntityTypeId = 3 AND e.Code NOT LIKE '%T%') OR (@reportType = 'Submitted By'))
AND (@pAgentList IS NULL OR e.PartyId IN (select * from @AgentsList))
AND (@pPortfolioRegionList IS NULL OR e.PortfolioRegionId IN (select * from @PortfolioRegionList))
GROUP BY
e.PartyId, DATEADD(month, DATEDIFF(month,0,GLt.WhenBooked),0)
) dtGL ON dtGL.PartyId = RT.PartyId AND
dtGL.WhenBooked >= RT.PeriodDate AND
dtGL.WhenBooked < RT.PeriodEndDate
LEFT JOIN
(
SELECT
ent.PartyId, DATEADD(month, DATEDIFF(month,0,GLtr.WhenBooked),0) as WhenBooked,
-- NetWriteOffs
(
SUM(CASE WHEN (GLen.GlAccountingEventId = 15 -- Negative Values
AND GLen.GlAbstractAccountId = 8)
THEN GLen.Amount ELSE 0
END) +
SUM(CASE WHEN (GLen.GlAccountingEventId = 23 -- Positive Values
AND GLen.GlAbstractAccountId = 8)
THEN GLen.Amount ELSE 0
END)
)
as NetWriteOffs
FROM
Entity ent
JOIN CustomerAccount CusAcc ON ent.PartyId = (CASE WHEN @reportType = 'Agent' THEN CusAcc.AgentId
WHEN @reportType = 'Submitted By' THEN CusAcc.SubmittedById
END)
JOIN GlTransaction GLtr ON GLtr.PartyId = CusAcc.PartyId
left join GlEntry GLen ON GLen.GlTransactionId = GLtr.GlTransactionId
WHERE
GLtr.WhenBooked >= @startDate
AND GLtr.WhenBooked < DATEADD(DAY, 1, @endDate)
AND ((@reportType = 'Agent' AND ent.EntityTypeId = 3 AND ent.Code NOT LIKE '%T%') OR (@reportType = 'Submitted By'))
AND (@pAgentList IS NULL OR ent.PartyId IN (select * from @AgentsList))
AND (@pPortfolioRegionList IS NULL OR ent.PortfolioRegionId IN (select * from @PortfolioRegionList))
GROUP BY
ent.PartyId, DATEADD(month, DATEDIFF(month,0,GLtr.WhenBooked),0)
) dtGLWO ON dtGLWO.PartyId = RT.PartyId AND
dtGLWO.WhenBooked >= RT.PeriodDate AND
dtGLWO.WhenBooked < RT.PeriodEndDate
GROUP BY RT.PartyId, RT.EntityName, RT.Code, RT.PeriodDate, RT.PeriodEndDate,
DT.PartyId, DT.createMonth, DT.endMonth, DT.AllLoansThisMonth,
DT.AllLoansAmountFinanced, DT.AVGDownPaymentPercent, DT.AvgAPR, DT.CancelRatio,
RF.ReferralAmount,
dtGL.InterestIncome, dtGL.FeeIncome,
dtGLWO.NetWriteOffs
ORDER BY RT.EntityName, RT.PeriodDate
IF OBJECT_ID('tempdb..#tmpMap') IS NOT NULL
DROP TABLE #tmpMap
END
Microsoft SQL ServerMicrosoft SQL Server 2008SQL
Last Comment
pzozulka
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
DcpKing: what's a temp var?
Also, when you say are "indexed nicely", is it enough to create a primary key for the temp table? Like:
Create table #tmpTbl
(
RefID int identity(1,1) primary key,
PartyID int not null,
Etc.
)
Also, when you say are "indexed nicely", is it enough to create a primary key for the temp table? Like:
Create table #tmpTbl
(
RefID int identity(1,1) primary key,
PartyID int not null,
Etc.
)
Only watching for your correlated query, I would say immediately to get rid of the '*'
en.Code NOT LIKE '%T%' --> What are the possible values for en.Code?
(
SELECT COUNT(1) as countAcitve
FROM Entity en
join CustomerAccount CuAc on en.PartyId = (CASE WHEN @reportType = 'Agent' THEN CuAc.AgentId
WHEN @reportType = 'Submitted By' THEN CuAc.SubmittedById
END)
WHERE
en.PartyId = RT.PartyId
AND ((@reportType = 'Agent' AND en.EntityTypeId = 3 AND en.Code NOT LIKE '%T%') OR (@reportType = 'Submitted By'))
AND CuAc.AccountCreateDate < DATEADD(MONTH, DATEDIFF(MONTH, 0, RT.PeriodDate) + 1, 0)
AND (CuAc.ArchiveDate IS NULL OR CuAc.ArchiveDate >= DATEADD(MONTH, DATEDIFF(MONTH, 0, RT.PeriodDate) + 1, 0))
AND (@pAgentList IS NULL OR en.PartyId IN (select PartyID_ColumnNameHere from @AgentsList))
AND (@pPortfolioRegionList IS NULL OR en.PortfolioRegionId IN (select PortfolioRegionId_ColumnNameHere from @PortfolioRegionList))
) as ActiveLoans,
en.Code NOT LIKE '%T%' --> What are the possible values for en.Code?
ASKER
I'm doing that to improve performance to eliminate unwanted to hopefully shrink rows returned. E.code could be two things: T### or A###. Example : T001, T174, A037, etc.
ASKER
Vitor, can I replace it with: CHARINDEX('T', column) = 0
Ok, then you can increase the performance to telling to SQL Server engine to search only code not beginning with 'T' instead of having a 'T' in any position.
Like this ---> en.Code NOT LIKE 'T%'
Like this ---> en.Code NOT LIKE 'T%'
Avoid the use of functions on table columns. NOT LIKE will be a better option.
pzozulka:
"temp var" ... sorry - typo! I meant "table variable" but obviously my fingers were having trouble keeping up!
"Indexed nicely" meaning index the temp tables on the fields that they'll end up getting joined on. If you have an order table, for example, you can give it a primary key of OrderNumber but that won't help when you're joining it most of the time using the CustomerNumber field! Put a non-unique index there too!
Never use functions if where clauses if you can possibly help it - your function will be called once for every single row in the table you're looking at! Also, depending on the type of function (esp if it's a UDF), the query analyser may not evaluate it properly, so may not make a good plan for it ......resulting in even worse performance!
hth
Mike
"temp var" ... sorry - typo! I meant "table variable" but obviously my fingers were having trouble keeping up!
"Indexed nicely" meaning index the temp tables on the fields that they'll end up getting joined on. If you have an order table, for example, you can give it a primary key of OrderNumber but that won't help when you're joining it most of the time using the CustomerNumber field! Put a non-unique index there too!
Never use functions if where clauses if you can possibly help it - your function will be called once for every single row in the table you're looking at! Also, depending on the type of function (esp if it's a UDF), the query analyser may not evaluate it properly, so may not make a good plan for it ......resulting in even worse performance!
hth
Mike
>> If you have an order table, for example, you can give it a primary key of OrderNumber but that won't help when you're joining it most of the time using the CustomerNumber field! Put a non-unique index there too! <<
That's not best either.
In those types of situations, the CustomerNumber should be the clustered index, and the PK should be nonclus.
That's not best either.
In those types of situations, the CustomerNumber should be the clustered index, and the PK should be nonclus.
ASKER
ScottPletcher:
I tried implementing your approach, but am getting duplication of records:
Results of my correlated subquery:![Correlated Subquery Results]()
Left Join (Derived Table) Results:
Disregard formatting on one of the PartyIDs. I accidently chose ShortDate formatting in Excel![Left Join (Derived Table) Results]()
I tried implementing your approach, but am getting duplication of records:
Results of my correlated subquery:
Left Join (Derived Table) Results:Disregard formatting on one of the PartyIDs. I accidently chose ShortDate formatting in Excel
My suggestion is to split your one huge query into 6 smaller ones - one for each segment that you have linked by left outer joins, and put each into a temp table (not a temp var). make sure that the temp tables are indexed nicely to assist with the joins, and then run the thing that you have, where it'll look very small and simple because you'll have pre-processed most of the data into the temp tables.
You should find that doing the joins between well-indexed temp tables ends up far faster then the subqueries you're using right now. It'll probably take you about an hour to recode, but it should be well worth the time.
hth
Mike
(PS don't forget to comment what each temp table is for, and why you're doing it!)