August 16, 2017 at 1:05 pm
ChrisM@Work - Wednesday, August 16, 2017 1:44 AMffarouqi - Tuesday, August 15, 2017 3:37 PMJonathan AC Roberts - Tuesday, August 15, 2017 1:52 PMffarouqi - Tuesday, August 15, 2017 1:36 PMCan you suggest what index would benefit here on the temp table to have it fetch quickly when doing a join. Can we do something on the joining columns only or do we need to include other columns as well.Can you provide a list with the table name and how many rows are in each table?
I have attached the list of tables with the rows in each. By the way I was asking about the temp table as well. Based on the message the table is holding 2181 records in temp table from that function.
Here's what I suggest you do:
Run the results of the function into a temp table and reference the temp table in your query instead of the function, as already mentioned. Save and post here an actual execution plan from the modified query.
Post up the definitions of all of the views referenced by the query.
Cheers
Okay. I have attached the query plan and the script for the views referenced after making the said modification.
August 16, 2017 at 2:17 pm
Try adding the following indexesCREATE INDEX IX_#ReportDealCore_1 ON #ReportDealCore(DealID)
CREATE INDEX IX_Activities_1 ON Activities(DealID,Type)
CREATE INDEX IX_DealProperties_1 ON DealProperties(DealID,IsMainProperty,DateRemoved)
CREATE INDEX IX_Regions_1 ON Regions(CityID)
CREATE INDEX IX_DealsToClients_1 ON (DealID,isPrimary,MemberType)
CREATE INDEX IX_CacheClients_1 ON CacheClients(ClientID)
CREATE INDEX IX_DealsToEmployees_1 ON DealsToEmployees(DealID,MemberType,isPrimary)
Then check the query plan to see which indexes were used. (Drop the new ones that aren't used)
Also, click the display estimated execution plan button in SSMS and see if that suggests any indexes.
August 16, 2017 at 2:50 pm
Jonathan AC Roberts - Wednesday, August 16, 2017 2:17 PMTry adding the following indexesCREATE INDEX IX_#ReportDealCore_1 ON #ReportDealCore(DealID)
CREATE INDEX IX_Activities_1 ON Activities(DealID,Type)
CREATE INDEX IX_DealProperties_1 ON DealProperties(DealID,IsMainProperty,DateRemoved)
CREATE INDEX IX_Regions_1 ON Regions(CityID)
CREATE INDEX IX_DealsToClients_1 ON (DealID,isPrimary,MemberType)
CREATE INDEX IX_CacheClients_1 ON CacheClients(ClientID)
CREATE INDEX IX_DealsToEmployees_1 ON DealsToEmployees(DealID,MemberType,isPrimary)
Then check the query plan to see which indexes were used. (Drop the new ones that aren't used)
Also, click the display estimated execution plan button in SSMS and see if that suggests any indexes.
Jonathan - For the index IX_Regions_1 on REGIONS(CityID) - Column name CityID is not valid. Did you meant City table or RegionID colum in Regions table
August 16, 2017 at 4:14 pm
I'm not sure, I didn't mean Regions( RegionID) as that's already the PK.
Just ignore that index for now.
August 16, 2017 at 6:04 pm
Jonathan AC Roberts - Tuesday, August 15, 2017 11:52 AMWith these type of query you can sometimes get a lot of success from breaking it up into several queries inserting the data into temporary tables, adding indexes to the temporary tables and then querying the temporary tables to get the results. You could start off by inserting the results of he function into a temporary table:IF OBJECT_ID('temdb..#ReportDealCore','U') IS NOT NULL
DROP TABLE #ReportDealCoreSELECT FDC.DealID,
FDC.StageID,
FDC.PlatformID
INTO #ReportDealCore
FROM report.fnReportDealCore(@StartDate, @EndDate, @Type) AS FDC
Then query the able #ReportDealCore instead of calling the function in the query.Then just select several tables from the query join them together and insert the results into another temporary table, then build up the whole query from joins to the temporary tables,
Preach it, Brother! 😉 A whole lot of people don't understand that "set based" doesn't actually mean "all in one query" especially when a DISTICT has to be used to overcome the accidental many-to-many joins formed by such monsters.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 17, 2017 at 2:05 am
-- Here's an alternative to vwReportMemberTypeResults_Employee
-- which reads DealsToEmployees and Employees only once, instead of four times.
-- Test it, when you're satisfied that it works, sub it into your query.
CREATE VIEW [dbo].[vwReportMemberTypeResults_Employee_V2]
AS
SELECT
D.DealID,
D.DealName,
x.InitialLead,
x.Analyst,
x.Producer,
x.CrossProducer,
x.InitialLeadInitals,
x.AnalystInitials,
x.ProducerInitials,
x.CrossProducerInitials
FROM dbo.Deals AS D
OUTER APPLY (
SELECT
InitialLead = MAX(CASE WHEN de.MemberType = 2 THEN e.FirstName + ' ' + e.LastName END),
Analyst = MAX(CASE WHEN de.MemberType = 3 THEN e.FirstName + ' ' + e.LastName END),
Producer = MAX(CASE WHEN de.MemberType = 1 THEN e.FirstName + ' ' + e.LastName END),
CrossProducer = MAX(CASE WHEN de.MemberType = 9 THEN e.FirstName + ' ' + e.LastName END),
InitialLeadInitals = MAX(CASE WHEN de.MemberType = 2 THEN LEFT(e.FirstName, 1) + ISNULL(LEFT(e.MiddleName, 1), '') + LEFT(e.LastName, 1) END),
AnalystInitials = MAX(CASE WHEN de.MemberType = 3 THEN LEFT(e.FirstName, 1) + ISNULL(LEFT(e.MiddleName, 1), '') + LEFT(e.LastName, 1) END),
ProducerInitials = MAX(CASE WHEN de.MemberType = 1 THEN LEFT(e.FirstName, 1) + ISNULL(LEFT(e.MiddleName, 1), '') + LEFT(e.LastName, 1) END),
CrossProducerInitials = MAX(CASE WHEN de.MemberType = 9 THEN LEFT(e.FirstName, 1) + ISNULL(LEFT(e.MiddleName, 1), '') + LEFT(e.LastName, 1) END)
FROM dbo.DealsToEmployees de
INNER JOIN dbo.Employees e
ON e.EmployeeID = de.EmployeeID
WHERE de.isPrimary = 1
AND de.MemberType IN (1,2,3,9)
AND de.DealID = d.DealID -- outer reference
) xFor fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 17, 2017 at 2:13 am
ffarouqi - Wednesday, August 16, 2017 1:05 PMChrisM@Work - Wednesday, August 16, 2017 1:44 AMffarouqi - Tuesday, August 15, 2017 3:37 PMJonathan AC Roberts - Tuesday, August 15, 2017 1:52 PMffarouqi - Tuesday, August 15, 2017 1:36 PMCan you suggest what index would benefit here on the temp table to have it fetch quickly when doing a join. Can we do something on the joining columns only or do we need to include other columns as well.Can you provide a list with the table name and how many rows are in each table?
I have attached the list of tables with the rows in each. By the way I was asking about the temp table as well. Based on the message the table is holding 2181 records in temp table from that function.
Here's what I suggest you do:
Run the results of the function into a temp table and reference the temp table in your query instead of the function, as already mentioned. Save and post here an actual execution plan from the modified query.
Post up the definitions of all of the views referenced by the query.
CheersOkay. I have attached the query plan and the script for the views referenced after making the said modification.
Oh good grief.
The view vw_Deals is one of the finest examples of cowboy coding I've ever seen.
It's going to take some time to refactor it to work properly.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 17, 2017 at 3:51 pm
Jonathan AC Roberts - Wednesday, August 16, 2017 4:14 PMI'm not sure, I didn't mean Regions( RegionID) as that's already the PK.
Just ignore that index for now.
Hi Jonathan,
As per your suggestion I created the indexes but it hasn't really helped the query execution time a lot and only a couple of indexes are being used. However, the execution time is still around 4 odd minutes. I have also attached the query plan for further review.
Indexes used by the query
IX_Activities_1 (You will notice that it is IX_Activities_5 as I already had one with that number)
IX_DealsToClients_1
Kindly, let me know what else can we do to improve it even further to reduce the execution time to 2 odd minutes...the way you've helped me out before.
Thanks again.
August 18, 2017 at 4:30 am
-- Here's an attempt to remove the complex view vwDeals from the query. Run it and compare with the original.
-- Note that I can't find [AccountingDate] in the view. You will have to track this down.
-- I can't find which table [HFFTotalFee] is from, the reference isn't fully qualified in vwDeals so you will have to find out where this is from too.
-- There are some other changes in the FROM list.For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 18, 2017 at 6:16 am
ffarouqi - Thursday, August 17, 2017 3:51 PMJonathan AC Roberts - Wednesday, August 16, 2017 4:14 PMI'm not sure, I didn't mean Regions( RegionID) as that's already the PK.
Just ignore that index for now.Hi Jonathan,
As per your suggestion I created the indexes but it hasn't really helped the query execution time a lot and only a couple of indexes are being used. However, the execution time is still around 4 odd minutes. I have also attached the query plan for further review.
Indexes used by the query
IX_Activities_1 (You will notice that it is IX_Activities_5 as I already had one with that number)
IX_DealsToClients_1Kindly, let me know what else can we do to improve it even further to reduce the execution time to 2 odd minutes...the way you've helped me out before.
Thanks again.
If an equivalent index already exists then drop the new index.
My idea is that you try splitting the query by populating temporary tables. For example I would start something like this:SELECT FDC.DealID,
FDC.StageID,
FDC.PlatformID
INTO #ReportDealCore
FROM report.fnReportDealCore(@StartDate, @EndDate, @Type) AS FDC
CREATE INDEX IX_#ReportDealCore_1 ON #ReportDealCore(DeailID)
SELECT D.DealID,
D.DealName,
D.Amount,
D.Description,
D.HFFTotalFee
INTO #vwDeals
FROM report.vwDeals D
WHERE (D.AccountingDate BETWEEN @StartDate AND @EndDate)
AND EXISTS(SELECT * FROM #ReportDealCore r WHERE r.DealID = D.DealID)
CREATE INDEX IX_#vwDeals_1 ON #ReportDealCore(DealID)
CREATE INDEX IX_#vwDeals_2 ON #ReportDealCore(AssetID)
SELECT C.City,
C.State,
A.AssetId
INTO #AssetCity
FROM Assets AS A
LEFT JOIN Cities AS C
ON C.CityID = A.CityID
WHERE EXISTS(SELECT * FROM #ReportDealCore rdc WHERE rdc.AssetID = A.AssetID)
CREATE INDEX IX_#AssetCity_1 ON #ReportDealCore(AssetID)
And then update the main query to replace permanent tables with the temporary tables.
If you started with the above temporary tables the joins would look like this (you'd also have to change the aliases in the select to match)FROM #ReportDealCore AS FDC
INNER JOIN DealProperties AS DP
ON DP.DealID = FDC.DealID
AND DP.IsMainProperty = 1
AND DP.DateRemoved IS NULL
INNER JOIN #vwDeals AS D
ON D.DealID = FDC.DealID
INNER JOIN #AssetCity A
ON DP.AssetID = A.AssetID
LEFT JOIN Activities AS ACT
ON ACT.DealID = FDC.DealID
AND ACT.Type >= 2
AND ACT.Type <= 7
LEFT JOIN ActivityTypes AS AT
ON AT.ActivityTypeID = ACT.Type
LEFT JOIN DealStages AS DS
ON DS.StageID = FDC.StageID
LEFT JOIN DealTypes AS DT
ON DT.DealTypeID = D.DealTypeID
LEFT JOIN DealPlatforms AS DPF
ON DPF.PlatformID = FDC.PlatformID
LEFT JOIN PropertyTypes AS PT
ON PT.PropertyTypeID = DP.PropertyTypeID
LEFT JOIN States AS S
ON S.StateID = C.StateID
LEFT JOIN Regions AS R
ON isnull(C.RCARegionID, isnull(S.RCARegionID, S.RegionID)) = R.RegionID
LEFT JOIN Offices AS O
ON O.OfficeID = D.OfficeID
LEFT JOIN vwReportMemberTypeResults_Employee AS MTRE
ON MTRE.DealID = D.DealID
LEFT JOIN vwReportMemberTypeResults_Client AS MTRC
ON MTRC.DealID = D.DealID
--LEFT JOIN vwReportLosingOffers AS LO ON LO.DealID = D.DealID
LEFT JOIN vwReportOnlyLoserOffers AS LO
ON LO.DealID = D.DealID
WHERE(LO.isForeign = 1)
ORDER BY D.DealID ASC, LO.InvestorID ASC, AT.ActivityTypeID DESC
If you take this approach it's very useful to have some sort of logging between each "select" to log a timestamp and a message. After the procedure has run you can then view a log and identify which parts took longest then tackle them. One way of doing this is to crate a new logging table and a stored procedure that you call with some parameters (e.g StoredProcedureName and Section of SP you are in) that inserts a row with a timestamp into your new table. Then just call this stored procedure after each "select into" statement and the final select that produces the report.
Also you should try ChrisM's suggestion of rewriting the views so they are faster.
Viewing 10 posts - 16 through 24 (of 24 total)
You must be logged in to reply to this topic. Login to reply