How do I turn this off?
I have a view that calculates real-time timings, however I notice that because of lookups the view gets queried once for itself but then also for every lookup. There are many performance issues with this:
- Why the OR statements and not the actual pre-filter, they tend to be less performant than the exists clause in the pre-filter.
- Why all lookups are based on the base-table with a left join? Why not directly lookup on the lookup table?
This setup is really killing me. Any advice here on how to optimize this situation?
declare @handle int;
exec sp_prepexec @handle output, N'@p0 int, @p1 int, @p2 int, @p3 int, @p4 int, @p5 int, @p6 int, @p7 int, @p8 int', N'SELECT t1.[process_id], t1.[process_task_id], t1.[tenant_id], t2.[description] AS [lookup_636502171]
FROM [process_task_view] t1
LEFT OUTER JOIN [tenant_definition_status] t2
ON t1.[tenant_id] = t2.[tenant_id]
AND t1.[process_status_id] = t2.[domain_status_id]
WHERE (t1.tenant_id = convert(int,SESSION_CONTEXT(N'tenant_id'))
AND ((t1.[process_id] = @p0 AND t1.[process_task_id] = @p1 AND t1.[tenant_id] = @p2) OR (t1.[process_id] = @p3 AND t1.[process_task_id] = @p4 AND t1.[tenant_id] = @p5) OR (t1.[process_id] = @p6 AND t1.[process_task_id] = @p7 AND t1.[tenant_id] = @p8))', @p0=3087, @p1=143, @p2=1, @p3=3081, @p4=116, @p5=1, @p6=3083, @p7=126, @p8=1
Best answer by Anne BuitView original