Performance Issue with UNION ALL in View
-
Hi Guys,
I have a performance issue with one of the views when I join the view with a temp table
I have 2 Views - View1 and View2.
There is a third view - view_UNION where the
view_UNION =
SELECT * FROM View1
UNION ALL
SELECT * FROM View2
If I have a query like -
Select view_UNION.* FROM
view_UNION INNER JOIN #TMP ON #TMP.ID = view_UNION.ID
the execution is too slow.
But if I execute the views separately, I get good performance.
Can someone please help me to improve the performance of the view_Union
Thanks.
-
From your questions, it seems the #TMP table is causing the slowing down.
Is it indexed by ID?
You can create indexes on #temp tables.
Igor Micev,My blog: www.igormicev.com
-
I tried indexing the table as well. But that did not work. Moreover the slowness is there even if the temp table contains only one row.
-
Please post the actual code, otherwise it's impossible for us to help
-- Gianluca Sartori
-
Capture the execution plan for the query. I'd also capture the plans for the individual views.
Combining views, even through something as seemingly innocuous as a UNION ALL, is a common code smell. Remember, a view is not a table. It's just a query. Combining multiple queries by combining multiple views is putting a pretty taxing load on the optimizer. It can cause timeouts in the optimizer which can lead to poor plan choices. You're better off rewriting the queries.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
I will not be able to paste the entire query. But with the following query on AdventureWorks2012 database, I can demonstrate what is happening here.
Create VIEW View1
AS
SELECT BusinessEntityID, PersonType, FirstName, LastName FROM [Person].[Person] WHERE PersonType = 'SC'
GO
Create VIEW View2
AS
SELECT BusinessEntityID, PersonType, FirstName, LastName FROM [Person].[Person] WHERE PersonType = 'IN'
GO
Create VIEW view_UNION
AS
SELECT BusinessEntityID, PersonType, FirstName, LastName FROM View1
UNION ALL
SELECT BusinessEntityID, PersonType, FirstName, LastName FROM View2
GO
CREATE TABLE #TMP(BusinessEntityID INT)
INSERT INTO #TMP(BusinessEntityID) SELECT 8744
GO
SELECT * FROM
view_UNION
INNER JOIN #TMP ON #TMP.BusinessEntityID = view_UNION.BusinessEntityID
The query plan generated is as follows:
From the Query plan it seems that the entire view is executed first and then the join is applied. Hence the bad performance. The query above does not give the performance issue since the full view view_UNION itself does not take much time.
-
The execution plan doesn't seem to have made it. Plus, posting a picture of an execution plan is not posting an execution plan. The important parts of an execution plan are in the properties of the operators. You can right click a plan and save it as a .sqlplan file and attach that if you want to share it.
Looking at the query, why not simply have an OR statement instead of two separate queries? I'm assuming you're putting more than one value into that temp table because otherwise, you can just put that value into the WHERE clause and save yourself all the trouble of that JOIN and the temp table.
To tune, I'd look to eliminate the views and make it just a query and test adding an index to the temp table.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
Why don't you modify this, create a "new view" with the data you are going to need and then query that? It seems like you need a better view.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply