The same block of operations takes a long time in two fairly similar queries
I have two requests:
SELECT *
FROM ActionMessage am
JOIN vTasks vt ON am.TasksSeq = vt.TasksSeq
JOIN Tasks t2 ON t2.TasksSeq = vt.UltimateParent
WHERE vt.UltimateParent = 1225
SELECT *
FROM ActionMessage am
JOIN vTasks vt ON am.TasksSeq = vt.TasksSeq
JOIN Tasks t2 ON t2.TasksSeq = vt.UltimateParent
WHERE t2.TasksSeq = 1225 -- NOTE: this is the difference between
vTasks
is a view that, using left self-joins, can go up to 4 levels, computes the topmost parent for a given task. When I run queries, the first one takes less than a second and the second one takes 15 seconds.
I then reviewed their execution plans, which I have attached, if necessary, a link to an image.
If you look at the image, both of them have an index lookup operation.
This will take 30% of the total query execution time according to the first plan.
The first plan refers to a query with a longer execution time.
So I can conclude that the index lookup will take 5 seconds for query 1.
We have the same operation in plan 2, but it takes less than a second. I have looked at the details of this operation in both plans, but the statistics and information look the same.
My question is , since the 2 queries are about the same, I at least expect the index lookup in both cases to do the same operation. So why is their execution time different from this?
Here's a broader look at Plan 2:
source to share
In your first query, you have JOIN
one that tells the DBMS to do a cluster index scan on an indexed entity, which is the table Tasks
to be joined, checking the related parts with the table Tasks
as alias t2
in WHERE
, so you only have one cluster index scan.
But when in the second query you try to validate your view, the DBMS will reload the data Tasks
that your view is using in a new memory location and perform another clustered index scan on that newly loaded data.
I suggest that you optimize the table ActionMessage
that does more effects in your query by adding Index seek (Non-Clustered)
.
source to share