Hi All,
I have a query it's inserting around 9 lakh of records into the database in per hit, our SQL server exists on the remote location. Due to this query, performance client is not happy. So trying to optimize this bellowed mentioned query with the help of query optimizer (Query Execution Plan), During analysis through execution plan we got one warning per this warning I created non-cluster Index on temp table Transection column but after that, we have not seen any good result.
INSERT INTO FeeAnalysis (FeeAnalysis, Transactions, Matters, MatterID, ShortDesc, AreaOfLaw, MatterType, ClientSort, Professionals, Initials, Office, ProfClass, ProfDept, ProfType, ProfName, TaskCodes, CodeId, Transactions.MattersProjects, Project_Description, MattersProjects.ProjectAmount, ActivityCodeId , ActivityCodeDesc, MTDWorkedUnits, MTDWorkedBUnits, MTDWorkedAUnits, MTDWorkedExtAmt, MTDWorkedBExtAmt, MTDWorkedAExtAmt, MTDWorkedValue, MTDWorkedBValue, MTDWorkedAValue, MTDBilledUnits, MTDBilledExtAmt, MTDBilledValue, MTDBilledDiscount, MTDBilledWUD, YTDWorkedUnits, YTDWorkedBUnits, YTDWorkedAUnits, YTDWorkedExtAmt, YTDWorkedBExtAmt, YTDWorkedAExtAmt, YTDWorkedValue, YTDWorkedBValue, YTDWorkedAValue, YTDBilledUnits, YTDBilledExtAmt, YTDBilledValue, YTDBilledDiscount, YTDBilledWUD, FYTDWorkedUnits, FYTDWorkedBUnits, FYTDWorkedAUnits, FYTDWorkedExtAmt, FYTDWorkedBExtAmt, FYTDWorkedAExtAmt, FYTDWorkedValue, FYTDWorkedBValue, FYTDWorkedAValue, FYTDBilledUnits, FYTDBilledExtAmt, FYTDBilledValue, FYTDBilledDiscount, FYTDBilledWUD , CurrentProfessionals, ReportId, ReportDateTime ) SELECT NewID(), Transactions.Transactions, Matters.Matters, Matters.MatterID, Matters.ShortDesc, Matters.AreaOfLaw, Matters.MatterType, Matters.ClientSort, Professionals.Professionals, Professionals.Initials, Professionals.Office, Professionals.ProfClass, Professionals.ProfDept, Professionals.ProfType, Professionals.ProfName, TaskCodes.TaskCodes, TaskCodes.CodeId, Transactions.MattersProjects, MattersProjects.ProjectDesc, MattersProjects.ProjectAmount, ActivityCodes.ActivityCodeId , ActivityCodes.ActivityCodeDesc, dMTDWorkedUnits, dMTDWorkedBUnits, dMTDWorkedAUnits, dMTDWorkedExtAmt, dMTDWorkedBExtAmt, dMTDWorkedAExtAmt, dMTDWorkedValue, dMTDWorkedBValue, dMTDWorkedAValue, dMTDBilledUnits, dMTDBilledExtAmt, dMTDBilledValue, dMTDBilledDiscount, dMTDBilledWUD, dYTDWorkedUnits, dYTDWorkedBUnits, dYTDWorkedAUnits, dYTDWorkedExtAmt, dYTDWorkedBExtAmt, dYTDWorkedAExtAmt, dYTDWorkedValue, dYTDWorkedBValue, dYTDWorkedAValue, dYTDBilledUnits, dYTDBilledExtAmt, dYTDBilledValue, dYTDBilledDiscount, dYTDBilledWUD, dFYTDWorkedUnits, dFYTDWorkedBUnits, dFYTDWorkedAUnits, dFYTDWorkedExtAmt, dFYTDWorkedBExtAmt, dFYTDWorkedAExtAmt, dFYTDWorkedValue, dFYTDWorkedBValue, dFYTDWorkedAValue, dFYTDBilledUnits, dFYTDBilledExtAmt, dFYTDBilledValue, dFYTDBilledDiscount, dFYTDBilledWUD , '07138f0d-55d8-4aaf-bf84-a6dbf1251d98', '9604d85c-5edc-46e1-95a7-35eca286e41c', GetDate() FROM #FeeAnalysis INNER JOIN Transactions ON Transactions.Transactions = #FeeAnalysis.Transactions LEFT OUTER JOIN MattersProjects ON Transactions.MattersProjects = MattersProjects.MattersProjects LEFT OUTER JOIN ActivityCodes ON Transactions.ActivityCodes = ActivityCodes.ActivityCodes LEFT OUTER JOIN Professionals ON Transactions.Professionals = Professionals.Professionals LEFT OUTER JOIN TaskCodes ON Transactions.TaskCodes = TaskCodes.TaskCodes LEFT OUTER JOIN StmnLedger ON Transactions.StmnLedger = StmnLedger.StmnLedger INNER JOIN Matters ON Transactions.Matters = Matters.Matters INNER JOIN SecurityILSKey ON SecurityILSKey.ILSKey = Matters.ILSKey AND SecurityILSKey.Professionals = '07138f0d-55d8-4aaf-bf84-a6dbf1251d98' LEFT OUTER JOIN MatterTypes ON Matters.MatterType = MatterTypes.MatterTypesDesc INNER JOIN Components ON Transactions.Components = Components.Components, (SELECT 1 AS AssignedPercent) AS Thing
So I just want to know is there any way to optimize the time of my query or any suggested step so that I found specific area where we will do some updating and then got accepted result with in minimum seconds.
Another thing after inserting records through this query when we will go for deleting records then It’s also take too much time with simple delete query.
delete FROM AssignedProfs WHERE (ReportId = '9604d85c-5edc-46e1-95a7-35eca286e41c') OR (CurrentProfessionals = '07138f0d-55d8-4aaf-bf84-a6dbf1251d98' AND DateDiff (hh, ReportDateTime, GetDate()) > 60)
Please help me or share your experience with me. Execution plan is attached with post.