CODEWITHSUNDEEP
t-sqlviewleft-join
Francois
Francois

Reputation: 10978

TSQL: left join on view very slow

in some complex stored procedure we use view and left join on this view. It takes 40 sec to execute.

Now, if we create TABLE variable and store in it result of view, then do the left join on this variable and not on the view, it takes 3 sec...

What can explain this behavior?

Upvotes: 0

Views: 1868

Answers (2)

James Baloni
James Baloni

Reputation: 147

Courtesy of Alden W. This response for one of my questions like yours by Alden W.

You may be able to get better performance by specifying the VIEW ALGORITHM as MERGE. With MERGE MySQL will combine the view with your outside SELECT's WHERE statement, and then come up with an optimized execution plan.

To do this however you would have to remove the GROUP BY statement from your VIEW. As it is, if a GROUP BY statement is included in your view, MySQL will choose the TEMPLATE algorithm. A temporary table is being created of the entire view first, before being filtered by your WHERE statement.

If the MERGE algorithm cannot be used, a temporary table must be used instead. MERGE cannot be used if the view contains any of the following constructs:

Aggregate functions (SUM(), MIN(), MAX(), COUNT(), and so forth)

DISTINCT

GROUP BY

HAVING

LIMIT

UNION or UNION ALL

Subquery in the select list

Refers only to literal values (in this case, there is no underlying table)

Here is the link with more info. http://dev.mysql.com/doc/refman/5.0/en/view-algorithms.html

If you can change your view to not include the GROUP BY statement, to specify the view's algorithm the syntax is:

CREATE ALGORITHM = MERGE VIEW...

Upvotes: -1

gbn
gbn

Reputation: 432261

The view expands into the main query. S

So if you have 5 tables in the view, these expand with the extra table into one big query plan with 6 tables. The performance difference will most likely be caused by added complexity and permutations of the extra table you left join with.

Another potential issue: Do you then left join on a column that has some processing on it? This will further kill performance.

Upvotes: 3

Related Questions