Using Triggers
-
I have an area on our website where users can customize what data fields they want to see displayed from a list of about 50 different fields. I have a query that has about 50 subqueries to pull all this info and it has just got to be extemely slow...sometimes even times out. I need to pull alot of data from different tables and the only solution i can think of is to create triggers on the different tables that i want to pull and store that info in another table and then just run the query of that table so that there are not 50 - 60 subqueries. Is this a correct solution or does anyone have any other suggestions? Any help in this area would be greatly appreciated. P.S.. I've never worked with triggers before but did read up on it a bit.
-
For future reference, please do not post the same question in multiple forums.
I do not like the idea of duplicating all of this data using triggers - it goes against the grain of why relational databases were invented.
Can you give an example of the sort of query that is causing performance issues? Perhaps there will be another way of constructing the query that will perform better. Or maybe you can just create a view that contains all of the possible fields and select from that. Have you looked at your indexes - perhaps some of your subqueries are causing performance issues because they are not using a suitable index.
Regards
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
I can't think of a query that would absolutely require the use of 50 subqueries... I'd like to see one though
. -
Here's the query...it's pretty big and the reason i have such a long query is because our website is for project managers to track projects from start to finish so it's mainly data entry. Different PM's are interested in viewing different info about there projects and hence i'm pulling all the info in a view thats saved and then letting them pick and choose what colums they want to see from that view. The view takes about 4-5 mins to run and sometimes doesn't even run. Also....all the data needs to be real time for the users....they have to see the updated changes immediately and so i can't use a table with data stored from a query that's ran a few times during the day.
SELECT dbo.tblProjectData.ProjectID, dbo.tblProjectData.NewCostCtr, dbo.tblProjectData.ProjName, dbo.qryLastSubmit.LastSubmit AS LastSRID,
dbo.tblStatusReports.Status, ISNULL(tblRYGStatus_2.RYGStatusName, N'NA') AS RYGStatusName, dbo.tblProjectData.Program,
dbo.tblProjectData.ProjInitiative, dbo.tblProjectData.ProjType, RIGHT(CONVERT(varchar, dbo.tblStatusReports.SubmitTS, 106), 8) AS [Output],
dbo.tblStatusReports.SubmitTS, dbo.tblPhase.phaseid, dbo.tblPhase.CurPhase, dbo.tblProjectData.ProjStart AS ProjStartSort,
dbo.tblProjectData.ProjFinish AS ProjFinishSort, CONVERT(varchar, dbo.tblProjectData.ProjStart, 1) AS ProjStart, CONVERT(varchar,
dbo.tblProjectData.ProjFinish, 1) AS ProjFinish, dbo.tblProjectData.SubProject, dbo.tblProjectData.Active, dbo.tblProjectData.ProjNoShow,
ISNULL(dbo.qryIssueHigh.HighCount, 0) AS hcount, ISNULL(dbo.qryIssueOpen.OpenCount, 0) AS ocount,
ISNULL(dbo.qryMilestoneCount.MileCountTotal, 0) AS mcount, ISNULL(dbo.qryMilestoneCountComplete.MileCount, 0) AS mcomp,
dbo.tblProjectData.TempStatus, dbo.qryMaxPMUpdateStatus.MaxPMStatus, dbo.qryMaxPMUpdateStatus.WeekStatusProjID,
dbo.qryMaxPMUpdateStatus.WeekStatusPost, dbo.qryMaxPMUpdateStatusDetail.WeekStatusPostTS, dbo.tblProjectData.StateID,
dbo.tblState.StateName, dbo.tblState.StateDispName, dbo.tblProjectData.CostType, dbo.tblProjectData.ProjNameShort,
dbo.qryCorpISCostFormat.PTDActual, ISNULL('$' + CONVERT(varchar, dbo.qryCorpISCostFormat.PTDActualShort)
+ dbo.qryCorpISCostFormat.PTDActualShortL, '$0') AS PTDActualShort, dbo.qryCorpISCostFormat.PTDActualCorp, ISNULL('$' + CONVERT(varchar,
dbo.qryCorpISCostFormat.PTDActualCorpShort) + dbo.qryCorpISCostFormat.PTDActualCorpShortL, '$0') AS PTDActualCorpShort,
dbo.qryCorpISCostFormat.PTDActualIS, ISNULL('$' + CONVERT(varchar, dbo.qryCorpISCostFormat.PTDActualISShort)
+ dbo.qryCorpISCostFormat.PTDActualISShortL, '$0') AS PTDActualISShort, dbo.qryCorpISCostFormat.PTDPlan, ISNULL('$' + CONVERT(varchar,
dbo.qryCorpISCostFormat.PTDPlanShort) + dbo.qryCorpISCostFormat.PTDPlanShortL, '$0') AS PTDPlanShort, dbo.qryCorpISCostFormat.PTDPlanCorp,
ISNULL('$' + CONVERT(varchar, dbo.qryCorpISCostFormat.PTDPlanCorpShort) + dbo.qryCorpISCostFormat.PTDPlanCorpShortL, '$0')
AS PTDPlanCorpShort, dbo.qryCorpISCostFormat.PTDPlanIS, ISNULL('$' + CONVERT(varchar, dbo.qryCorpISCostFormat.PTDPlanISShort)
+ dbo.qryCorpISCostFormat.PTDPlanISShortL, '$0') AS PTDPlanISShort, dbo.qryCorpISCostFormat.YTDActual, ISNULL('$' + CONVERT(varchar,
dbo.qryCorpISCostFormat.YTDActualShort) + dbo.qryCorpISCostFormat.YTDActualShortL, '$0') AS YTDActualShort, dbo.qryCorpISForecast.Forecast,
'$' + CONVERT(varchar, dbo.qryCorpISForecast.FCast) + dbo.qryCorpISForecast.Forecastletter AS ForecastShort,
dbo.qryCorpISForecastPTD.ForecastPTDCorp, '$' + CONVERT(varchar, dbo.qryCorpISForecastPTD.ForecastPTDCorpShort)
+ dbo.qryCorpISForecastPTD.ForecastPTDCorpShortL AS ForecastPTDCorpShort, dbo.qryCorpISForecastPTD.ForecastPTDIS, '$' + CONVERT(varchar,
dbo.qryCorpISForecastPTD.ForecastPTDISShort) + dbo.qryCorpISForecastPTD.ForecastPTDISShortL AS ForecastPTDISShort,
dbo.qryCorpISForecastPTD.ForecastPTD, '$' + CONVERT(varchar, dbo.qryCorpISForecastPTD.ForecastPTDShort)
+ dbo.qryCorpISForecastPTD.ForecastPTDShortL AS ForecastPTDShort, dbo.qryGovernanceApprovalsFormat.ApprCorp, ISNULL('$' + CONVERT(varchar,
dbo.qryGovernanceApprovalsFormat.ApprCorpShort) + dbo.qryGovernanceApprovalsFormat.ApprCorpShortL, '$0') AS ApprCorpShort,
dbo.qryGovernanceApprovalsFormat.ApprIS, ISNULL('$' + CONVERT(varchar, dbo.qryGovernanceApprovalsFormat.ApprISShort)
+ dbo.qryGovernanceApprovalsFormat.ApprISShortL, '$0') AS ApprISShort, dbo.qryGovernanceApprovalsFormat.ApprTotal,
ISNULL('$' + CONVERT(varchar, dbo.qryGovernanceApprovalsFormat.ApprTotalShort) + dbo.qryGovernanceApprovalsFormat.ApprTotalShortL, '$0')
AS ApprTotalShort, dbo.qryGovernanceApprovalsVarianceFormat.ApprVariance, '$' + CONVERT(varchar,
dbo.qryGovernanceApprovalsVarianceFormat.ApprVarianceShort)
+ dbo.qryGovernanceApprovalsVarianceFormat.ApprVarianceletter AS ApprPTDVarianceShort, dbo.qryCorpISCurMth.CurMthActual,
dbo.qryCorpISCurMth.CurMthPlan, '$' + CONVERT(varchar, dbo.qryCorpISCurMth.CurMthActualShort)
+ dbo.qryCorpISCurMth.CurMthActualletter AS CurMthActualShort, '$' + CONVERT(varchar, dbo.qryCorpISCurMth.CurMthPlanShort)
+ dbo.qryCorpISCurMth.CurMthPlanletter AS CurMthPlanShort, ISNULL(dbo.qryProjMgr.ProjMgr, N'NA') AS ProjManager, dbo.qryProjMgr.email,
dbo.qryDCFCostBen.maxDCF, dbo.qryDCFCostBen.DCFTPC, dbo.qryDCFCostBen.DCFBenefits, '$' + CONVERT(varchar,
dbo.qryDCFCostBen.DCFTPCShort) + dbo.qryDCFCostBen.DCFTPCletter AS DCFTPCShort, '$' + CONVERT(varchar,
dbo.qryDCFCostBen.DCFBenefitsShort) + dbo.qryDCFCostBen.DCFBenefitsletter AS DCFBenefitsShort,
dbo.qryDCFBenYr1Format.PDCFYear AS DCFYr1, dbo.qryDCFBenYr1Format.DCFBenYr1, '$' + CONVERT(varchar,
dbo.qryDCFBenYr1Format.DCFBen1Short) + dbo.qryDCFBenYr1Format.DCFBenYr1letter AS DCFBenYr1Short,
dbo.qryDCFBenYr2Format.PDCFYear AS DCFYr2, dbo.qryDCFBenYr2Format.DCFBenYr2, '$' + CONVERT(varchar,
dbo.qryDCFBenYr2Format.DCFBen2Short) + dbo.qryDCFBenYr2Format.DCFBenYr2letter AS DCFBenYr2Short,
ISNULL(dbo.qryDeliverableCountComplete.DelCountComp, 0) AS Delcount1, ISNULL(dbo.qryDeliverableCount.DelCountTot, 0) AS Delcount2,
tblProjectGroup_1.GroupName AS BusGroupName, tblProjectGroup_2.GroupName AS BusParentGroupName,
tblProjectGroup_4.GroupName AS TechGroupName, tblProjectGroup_3.GroupName AS TechParentGroupName, dbo.tblProjectData.ProjPMOID,
dbo.tblPMO.PMOShortName, dbo.tblProjectType.ProjTypeName, dbo.tblProjectCategory.ProjCatName, dbo.tblProjectData.ProjCat,
dbo.tblCostType.CTypeName, dbo.tblProjectData.GovID, dbo.tblGovernanceGroup.GovName, dbo.qryDCFCostBen.lastdcfdate,
dbo.tblProjectData.StrategicProcess, dbo.tblProjectData.Portfolio, dbo.tblProjectData.TechLayer1, dbo.tblProjectData.TechLayer2,
dbo.qrySchedClosingDates.SchPhsBE, dbo.qrySchedClosingDates.SchPhsRE, dbo.qrySchedClosingDates.SchPhsVarianceSort,
dbo.qrySchedClosingDates.SchPhsBESort, dbo.qrySchedClosingDates.SchPhsRESort, dbo.qryDCFCostBen.lastdcfdatesort,
dbo.qryLastGovReqMeetDate.GovReqMeetDateSort, dbo.qryLastGovReqMeetDate.GovReqMeetDate, dbo.tblProjectData.ProjSRReport,
dbo.tblStatusReports.[Month], dbo.tblStatusReports.[Year]
FROM dbo.qryCorpISForecast RIGHT OUTER JOIN
dbo.qrySchedClosingDates RIGHT OUTER JOIN
dbo.qryLastGovReqMeetDate RIGHT OUTER JOIN
dbo.tblProjectData LEFT OUTER JOIN
dbo.qryCorpISForecastPTD ON dbo.tblProjectData.ProjectID = dbo.qryCorpISForecastPTD.ProjectID ON
dbo.qryLastGovReqMeetDate.GovReqProjID = dbo.tblProjectData.ProjectID ON
dbo.qrySchedClosingDates.SchPhsProjID = dbo.tblProjectData.ProjectID LEFT OUTER JOIN
dbo.tblGovernanceGroup ON dbo.tblProjectData.GovID = dbo.tblGovernanceGroup.GovID LEFT OUTER JOIN
dbo.tblCostType ON dbo.tblProjectData.CostType = dbo.tblCostType.CTypeID LEFT OUTER JOIN
dbo.tblProjectCategory ON dbo.tblProjectData.ProjCat = dbo.tblProjectCategory.ProjCatID LEFT OUTER JOIN
dbo.tblProjectType ON dbo.tblProjectData.ProjType = dbo.tblProjectType.ProjTypeID LEFT OUTER JOIN
dbo.tblPMO ON dbo.tblProjectData.ProjPMOID = dbo.tblPMO.PMOID LEFT OUTER JOIN
dbo.tblProjectGroup tblProjectGroup_3 RIGHT OUTER JOIN
dbo.tblProjectGroup tblProjectGroup_4 ON tblProjectGroup_3.GroupID = tblProjectGroup_4.GroupRecursiveID ON
dbo.tblProjectData.TechLayer2 = tblProjectGroup_4.GroupID LEFT OUTER JOIN
dbo.tblProjectGroup tblProjectGroup_1 LEFT OUTER JOIN
dbo.tblProjectGroup tblProjectGroup_2 ON tblProjectGroup_1.GroupRecursiveID = tblProjectGroup_2.GroupID ON
dbo.tblProjectData.Portfolio = tblProjectGroup_1.GroupID LEFT OUTER JOIN
dbo.qryDeliverableCount ON dbo.tblProjectData.ProjectID = dbo.qryDeliverableCount.DelivProjID LEFT OUTER JOIN
dbo.qryDeliverableCountComplete ON dbo.tblProjectData.ProjectID = dbo.qryDeliverableCountComplete.DelivProjID LEFT OUTER JOIN
dbo.qryDCFBenYr2Format ON dbo.tblProjectData.ProjectID = dbo.qryDCFBenYr2Format.DCFProjID LEFT OUTER JOIN
dbo.qryDCFBenYr1Format ON dbo.tblProjectData.ProjectID = dbo.qryDCFBenYr1Format.DCFProjID LEFT OUTER JOIN
dbo.qryGovernanceApprovalsVarianceFormat ON
dbo.tblProjectData.ProjectID = dbo.qryGovernanceApprovalsVarianceFormat.ProjectID LEFT OUTER JOIN
dbo.qryGovernanceApprovalsFormat ON dbo.tblProjectData.ProjectID = dbo.qryGovernanceApprovalsFormat.GovReqProjID LEFT OUTER JOIN
dbo.qryDCFCostBen ON dbo.tblProjectData.ProjectID = dbo.qryDCFCostBen.DCFProjID LEFT OUTER JOIN
dbo.qryProjMgr ON dbo.tblProjectData.ProjectID = dbo.qryProjMgr.projectid LEFT OUTER JOIN
dbo.qryCorpISCurMth ON dbo.tblProjectData.ProjectID = dbo.qryCorpISCurMth.ProjectID ON
dbo.qryCorpISForecast.ProjectID = dbo.tblProjectData.ProjectID LEFT OUTER JOIN
dbo.qryCorpISCostFormat ON dbo.tblProjectData.ProjectID = dbo.qryCorpISCostFormat.ProjectID LEFT OUTER JOIN
dbo.tblState ON dbo.tblProjectData.StateID = dbo.tblState.StateID LEFT OUTER JOIN
dbo.qryMaxPMUpdateStatus LEFT OUTER JOIN
dbo.qryMaxPMUpdateStatusDetail ON dbo.qryMaxPMUpdateStatus.MaxPMStatus = dbo.qryMaxPMUpdateStatusDetail.WeekStatusID ON
dbo.tblProjectData.ProjectID = dbo.qryMaxPMUpdateStatus.WeekStatusProjID LEFT OUTER JOIN
dbo.qryMilestoneCountComplete ON dbo.tblProjectData.ProjectID = dbo.qryMilestoneCountComplete.MileProjID LEFT OUTER JOIN
dbo.qryMilestoneCount ON dbo.tblProjectData.ProjectID = dbo.qryMilestoneCount.MileProjID LEFT OUTER JOIN
dbo.qryIssueHigh ON dbo.tblProjectData.ProjectID = dbo.qryIssueHigh.ProjID LEFT OUTER JOIN
dbo.qryIssueOpen ON dbo.tblProjectData.ProjectID = dbo.qryIssueOpen.ProjID FULL OUTER JOIN
dbo.tblStatusReports RIGHT OUTER JOIN
dbo.qryLastSubmit LEFT OUTER JOIN
dbo.tblPhase RIGHT OUTER JOIN
dbo.qrySchedulePhase ON dbo.tblPhase.phaseid = dbo.qrySchedulePhase.MinOfSchPhasePMGID ON
dbo.qryLastSubmit.LastSubmit = dbo.qrySchedulePhase.SchPhaseSRID ON
dbo.tblStatusReports.StatusPrimaryID = dbo.qryLastSubmit.LastSubmit FULL OUTER JOIN
dbo.tblRYGStatus tblRYGStatus_2 ON dbo.tblStatusReports.Status = tblRYGStatus_2.RYGStatusID ON
dbo.tblProjectData.ProjectID = dbo.qryLastSubmit.ProjID
WHERE (dbo.tblProjectData.StateID IN (1, 2, 3, 4, 5, 7, 13, 14, 15, 16, 17, 18)) AND (dbo.tblProjectData.ProjNoShow = 0) AND
(dbo.tblProjectData.StrategicProcess <> 0) AND (dbo.tblProjectData.PMGProject = 0) OR
(dbo.tblProjectData.StateID IN (1, 2, 3, 4, 5, 7, 13, 14, 15, 16, 17, 18)) AND (dbo.tblProjectData.ProjNoShow = 0) AND (dbo.tblProjectData.PMGProject = 0)
AND (dbo.tblProjectData.Portfolio <> 0) OR
(dbo.tblProjectData.StateID IN (1, 2, 3, 4, 5, 7, 13, 14, 15, 16, 17, 18)) AND (dbo.tblProjectData.ProjNoShow = 0) AND (dbo.tblProjectData.PMGProject = 0)
AND (dbo.tblProjectData.TechLayer1 <> 0) OR
(dbo.tblProjectData.StateID IN (1, 2, 3, 4, 5, 7, 13, 14, 15, 16, 17, 18)) AND (dbo.tblProjectData.ProjNoShow = 0) AND (dbo.tblProjectData.PMGProject = 0)
AND (dbo.tblProjectData.TechLayer2 <> 0)
-
Maybe it's just me, but I don't see a single subquery in there. Also I would definitly run the index tuning wizard just to make sure you're not missing any index in there... but then again I don't think it would make a huge difference.
One solution to this problem would be to run this query pediodically into a perm table. And then have the managers query that table instead of the query. They would get their data instantaneously and you might be able to work around the timeout problem (no limit if you ran this as a job). The only downside is that there would be a X minutes delay between that data and the actual data. But that sure beats the hell out of waiting 5-15 minutes to see your data.
-
I'm sorry Remi....i guess i'm not using the right words here. What i posted previously was a view that pulls from about 50 other views since all the data is scattered in different tables. I meant view instead of subqueries.
I thought about your solution too regarding running it into a perm table but the only issue to that is the delay in the data....i was hoping to find a solution that did not require a delay in data. Is using triggers my only option and if so...then which one of these options would be the best:
a) Use triggers on alot of different tables to replicate the info i need in another table OR
b) Use the method you suggested about the perm table and deal with a 15 minute or so delay in data
-
If you are using 50 views to make this, I would strongly suggest you try to use only the base tables as you might see a big improvement from that. Then optimize the indexes and see where it goes from there.
The 15 minutes is an arbitrary number. Maybe you could rerun the query every 5 minutes... But that would still cause some delay in the data. I don't know how acceptable this may be in your situation.
Another thing that could help would be to make a stored proc out of this where only a single project would be displayed at once, hence greatly reducing the number of rows to return and giving the results back much faster.
-
Let me give you an example which might give you an idea of how the user is going to access the data:
Based on there rights...each user will see a different group. Let's say I see 10 groups and I select the HR group. Once i've selected HR what is displayed to me is the data for all the fields that i selected to see for all the projects in the HR Group. The reason i can't use base tables much is because based on the type of project i pull different info from different tables which is done using CASE Statements. For example if it's a Functional Project...then I pull the cost from table A + table B, if it's a Cost Center Project then i pull the info from table C + table A. And this is done for pretty much all the data fields pulled.
I can't pull just one single project since a group may have 5 projects or 500 projects.
-
Let's start by simplifying where possible. Consider the WHERE clauses (reformatted for clarity):
WHERE (dbo.tblProjectData.StateID IN (1, 2, 3, 4, 5, 7, 13, 14, 15, 16, 17, 18))
AND (dbo.tblProjectData.ProjNoShow = 0)
AND (dbo.tblProjectData.StrategicProcess 0)
AND (dbo.tblProjectData.PMGProject = 0)
OR (dbo.tblProjectData.StateID IN (1, 2, 3, 4, 5, 7, 13, 14, 15, 16, 17, 18))
AND (dbo.tblProjectData.ProjNoShow = 0)
AND (dbo.tblProjectData.PMGProject = 0)
AND (dbo.tblProjectData.Portfolio 0)
OR (dbo.tblProjectData.StateID IN (1, 2, 3, 4, 5, 7, 13, 14, 15, 16, 17, 18))
AND (dbo.tblProjectData.ProjNoShow = 0)
AND (dbo.tblProjectData.PMGProject = 0)
AND (dbo.tblProjectData.TechLayer1 0)
OR (dbo.tblProjectData.StateID IN (1, 2, 3, 4, 5, 7, 13, 14, 15, 16, 17, 18))
AND (dbo.tblProjectData.ProjNoShow = 0)
AND (dbo.tblProjectData.PMGProject = 0)
AND (dbo.tblProjectData.TechLayer2 0)
It appears that you were attempting to include different conditional combinations, but this is not what happened - the parentheses are in the wrong locations. Cleaning this up, we end up with the (probable) WHERE clause:
WHERE (dbo.tblProjectData.StateID IN (1, 2, 3, 4, 5, 7, 13, 14, 15, 16, 17, 18))
AND (dbo.tblProjectData.ProjNoShow = 0)
AND (dbo.tblProjectData.StrategicProcess 0)
AND ( (dbo.tblProjectData.PMGProject = 0)
OR (dbo.tblProjectData.Portfolio 0)
OR (dbo.tblProjectData.TechLayer1 0)
OR (dbo.tblProjectData.TechLayer2 0)
)
-
Hmm, then I think that they either have to wait for the most current data or just accept the 5-10 minutes delay... I don't know any workaround in this situation.
-
This is just how the view wizard optimizes the where conditions. As wrong as it seems, it's often better for performance to have the query written that way. But I can't be sure without running some test on that query
. -
Thank alot for all your help remi. I think your solution might be the best option....using a perm table and inserting data from the view to the table and they'll just have to deal with the data delay.
Thank again for all your help.
-
HT almost help
-
I know that what I am going to say you are not going to like but I feel imperative to be said because in the long run may make your life a lot easier.
When you find yoursefl writing extremelly long and convoluted queries, don't fight with the query, have a look at the DESIGN!
You will be better off planning a design change (if at all possible)
... from a person that has been in contact ( a lot) with this error:
"Could not allocate ancilliary table for view or function resolution"
* Noel -
Since when are you repeating yourself Noeld??
[Edited]
Much better
.
Viewing 15 posts - 1 through 15 (of 31 total)
You must be logged in to reply to this topic. Login to reply