spill to tempdb problem
-
Hello
I have a problem with spill to tempdb. I've Update Statistics and try to create suitable index. But problem remain.
this is my script:
SELECT [Roles_Ex_Rights].[RoleID] FROM [Roles_Ex_Rights] WHERE [Roles_Ex_Rights].[RightID] IN (7295) and IsConfirm = 1 Union Select [Groups_Roles].[RoleID] From [Groups_Roles] Where [Groups_Roles].[GroupID] IN (SELECT [Groups_Rights].[GroupID] FROM [Groups_Rights] WHERE [Groups_Rights].[RightID] IN (7295) and IsConfirm=1)
-
If you could zip the execution plan and post it instead of the picture, we might be able to help. All the good stuff on an execution plan is in the properties of the operators, not the picture. The picture doesn't tell us a darned thing.
Also, the UNION operator is an aggregation operator. So, you're getting a sort, and the subsequent spill because it's trying to find a distinct set of values. UNION ALL might be a better way to go, if each of the result sets is in fact unique. Otherwise, restructuring the query is the approach I'd take.What's the relationship between roles_ex_rights and group_rights? Clearly there's something common due to the matching RightID. See if you can do a JOIN instead.
"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 -
Hi.
I've attached execution plan.
I've checked Union ALL and it is better and Eliminate Spill Data . Also I've use join for Groups_Roles And Groups_Rights.
Now, I have another problem, When I use this subquery in another query, spill data comes back again.
-
This is old and new script:
I've attached execution plan of below code.
SELECT [Roles_Ex_Rights].[RoleID] FROM [Roles_Ex_Rights] WHERE [Roles_Ex_Rights].[RightID] IN (7295) and IsConfirm = 1 Union Select [Groups_Roles].[RoleID] From [Groups_Roles] Where [Groups_Roles].[GroupID] IN (SELECT [Groups_Rights].[GroupID] FROM [Groups_Rights] WHERE [Groups_Rights].[RightID] IN (7295) and IsConfirm=1)
GO
SELECT [Roles_Ex_Rights].[RoleID] FROM [Roles_Ex_Rights] WHERE [Roles_Ex_Rights].[RightID] IN (7295) and IsConfirm = 1 Union all Select [Groups_Roles].[RoleID] From [Groups_Roles] join Groups_Rights ON Groups_Roles.GroupID = Groups_Rights.GroupID
Where Groups_Rights.RightID = 7295 and Groups_Rights.IsConfirm=1 -
and this is my target script. I edit subquery in Master query but spill data occured.
I've also attach execution plan.
Select [Roles].[Role_ID] AS "legalRoleID" , [Roles].[UserID] AS MainUserID , [Users].[FirstName] AS MainFirstName, [Users].[LastName] AS MainLastName, [Roles].[RoleName] ,[Roles].[Code] AS MainCode, [Users].[IsOnline],[Users].[PersonnelID] , [Roles].[DepartmentID],[Users].[PersonnelID] From [Roles] , [Users] Where [Roles].[UserID] = [Users].[User_ID] AND [Roles].[DepartmentID] = 104 AND [Roles].[CardTableStatus] = 1 AND [Users].[IsActive] = 1 AND [Roles].[IsActive] = 1
AND [Roles].[Role_ID] NOT IN ( Select [legalReference].[legalID] FROM [legalReference] WHERE [legalReference].[legalType] = 1 AND [legalReference].[RoleID] = 20000)
AND Role_ID IN (SELECT [Roles_Ex_Rights].[RoleID] FROM [Roles_Ex_Rights] WHERE [Roles_Ex_Rights].[RightID] IN (7295) and IsConfirm = 1 Union all Select [Groups_Roles].[RoleID] From [Groups_Roles] join Groups_Rights ON Groups_Roles.GroupID = Groups_Rights.GroupID
Where Groups_Rights.RightID = 7295 and Groups_Rights.IsConfirm=1
)
AND [Roles].[Role_ID] NOT IN ( Select [Groups_Roles].[RoleID] From [legalReference], [Groups_Roles] WHERE [legalReference].[legalID] = [Groups_Roles].[GroupID] AND [legalReference].[legalType]=2 AND [legalReference].[RoleID] = 20000)
AND [Roles].[Role_ID] NOT IN ( Select [legalReference_Group].[legalID] FROM [legalReference_Group] WHERE [legalReference_Group].[legalType] = 1 AND [legalReference_Group].[GroupID] in(select GroupID from Groups_Roles where RoleID= 20000 ) )
AND [Roles].[Role_ID] NOT IN ( Select [Groups_Roles].[RoleID] From [legalReference_Group], [Groups_Roles] WHERE [legalReference_Group].[legalID] = [Groups_Roles].[GroupID] AND [legalReference_Group].[legalType]=2 AND [legalReference_Group].[GroupID] in(select GroupID from Groups_Roles where RoleID= 20000 ) )
ORDER BY MainLastName , MainFirstName , RoleName,MainCode -
Here's a formatted copy of the query for anyone who wants to look:
SELECT [Roles].[Role_ID] AS "legalRoleID",
[Roles].[UserID] AS MainUserID,
[Users].[FirstName] AS MainFirstName,
[Users].[LastName] AS MainLastName,
[Roles].[RoleName],
[Roles].[Code] AS MainCode,
[Users].[IsOnline],
[Users].[PersonnelID],
[Roles].[DepartmentID],
[Users].[PersonnelID]
FROM [Roles],
[Users]
WHERE [Roles].[UserID] = [Users].[User_ID]
AND [Roles].[DepartmentID] = 104
AND [Roles].[CardTableStatus] = 1
AND [Users].[IsActive] = 1
AND [Roles].[IsActive] = 1
AND [Roles].[Role_ID] NOT IN
(
SELECT [legalReference].[legalID]
FROM [legalReference]
WHERE [legalReference].[legalType] = 1
AND [legalReference].[RoleID] = 20000
)
AND Role_ID IN
(
SELECT [Roles_Ex_Rights].[RoleID]
FROM [Roles_Ex_Rights]
WHERE [Roles_Ex_Rights].[RightID] IN ( 7295 )
AND IsConfirm = 1
UNION ALL
SELECT [Groups_Roles].[RoleID]
FROM [Groups_Roles]
JOIN Groups_Rights
ON Groups_Roles.GroupID = Groups_Rights.GroupID
WHERE Groups_Rights.RightID = 7295
AND Groups_Rights.IsConfirm = 1
)
AND [Roles].[Role_ID] NOT IN
(
SELECT [Groups_Roles].[RoleID]
FROM [legalReference],
[Groups_Roles]
WHERE [legalReference].[legalID] = [Groups_Roles].[GroupID]
AND [legalReference].[legalType] = 2
AND [legalReference].[RoleID] = 20000
)
AND [Roles].[Role_ID] NOT IN
(
SELECT [legalReference_Group].[legalID]
FROM [legalReference_Group]
WHERE [legalReference_Group].[legalType] = 1
AND [legalReference_Group].[GroupID] IN
(
SELECT GroupID FROM Groups_Roles WHERE RoleID = 20000
)
)
AND [Roles].[Role_ID] NOT IN
(
SELECT [Groups_Roles].[RoleID]
FROM [legalReference_Group],
[Groups_Roles]
WHERE [legalReference_Group].[legalID] = [Groups_Roles].[GroupID]
AND [legalReference_Group].[legalType] = 2
AND [legalReference_Group].[GroupID] IN
(
SELECT GroupID FROM Groups_Roles WHERE RoleID = 20000
)
)
ORDER BY MainLastName,
MainFirstName,
RoleName,
MainCode;"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 -
Right. So.
You're very focused on that spill. You actually have a couple. However, they're not the core of the issue. There's a lot to unpack here. If i had to tune this query myself, I'd take down to the core & rebuild it. All those sub-selects in the WHERE clause, probably, should be JOINs. Some of them maybe should be NOT EXISTS rather than NOT IN (maybe, don't glom onto that as magic). You're not using standard join syntax in the queries, making this much more difficult to read. However, you're repeatedly joining to tables, over & over. Your row estimates are wildly off, hence the spills, however, there's much worse than the spills going on.
When the optimizer thinks it's returning 6 rows, it picks a Loops Join. But, you're returning 4,772 rows, a disparity of 79533%. That's the core of your issue. Yes, in part, it could be statistics. However, the more likely answer is all the nested tables, nested, and nested again, is just overwhelming what the optimizer can return. In fact, the execution plan represents a Timeout, meaning the optimizer gave up trying to find a better plan.
Taking this table as an example: [DB_OAS].[dbo].[Groups_Roles].[IX_Groups_Roles_GroupId]. The table has 70,000 rows. The statistics are suggesting to the optimizer that it's going to return 90 rows. In fact, it returns 20,000 rows. That wild disparity is fundamental to the issues.
I can't rebuild all this. It's going to take a lot of time. One suggestion, figure out how to do joins in order to do a lot of this. Referencing on column, repeatedly, with NOT IN calls, to then reference the same tables in those, all of it screams out for restructuring.
Tear it all down. Start with two tables. Get the joins right. Add filtering. See if the row estimates are correct there. Then, starting with the first IN, see if you just using a JOIN for all the tables to retrieve the data. If that doesn't work, then try using a sub-select in the JOIN again. Focus on joins, not more and more of these sub-queries.
Others might suggest additional changes.
"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 -
Hi
Thanks for formmating my code.
Also for good suggestions.
I'm going to tune my code based of your guides. And tell you about results.
-
Hi
I used join and a covered index. I could reach 1% Improvement. I couldn't use 'Not Exists' instead 'Not IN'.
I've attached last execution plan.
--set statistics io on
--set statistics time on
SELECT [Roles].[Role_ID] AS "legalRoleID" , [Roles].[UserID] AS MainUserID ,
[Users].[FirstName] AS MainFirstName, [Users].[LastName] AS MainLastName,
[Roles].[RoleName] ,[Roles].[Code] AS MainCode, [Users].[IsOnline],
[Users].[PersonnelID] , [Roles].[DepartmentID],[Users].[PersonnelID]
FROM Roles JOIN Users ON Roles.UserID = Users.[User_ID]
WHERE [Roles].[DepartmentID] = 104 AND [Roles].[CardTableStatus] = 1 AND
[Roles].[IsActive] = 1 AND [Users].[IsActive] = 1 AND
[Roles].[Role_ID] NOT IN (
SELECT [legalReference].[legalID] FROM [legalReference]
WHERE [legalReference].[legalType] = 1 AND
[legalReference].[RoleID] = 20000)
AND Role_ID IN (
SELECT [Roles_Ex_Rights].[RoleID] FROM [Roles_Ex_Rights]
WHERE [Roles_Ex_Rights].[RightID] IN (7295) ANDIsConfirm = 1
UNION ALL SELECT [Groups_Roles].[RoleID] FROM
[Groups_Roles] JOIN Groups_Rights
ON Groups_Roles.GroupID = Groups_Rights.GroupID
WHERE RightID = 7295 ANDGroups_Rights.IsConfirm = 1)
AND [Roles].[Role_ID] NOT IN
(SELECT [Groups_Roles].[RoleID] FROM [legalReference] JOIN [Groups_Roles]
ON legalReference.legalID = Groups_Roles.GroupID
WHERE [legalReference].[legalType]=2 AND
[legalReference].[RoleID] = 20000)
AND [Roles].[Role_ID] NOT IN
(Select [legalReference_Group].[legalID] FROM
[legalReference_Group] JOIN Groups_Roles ON
legalReference_Group.GroupID = Groups_Roles.GroupID
WHERE [legalReference_Group].[legalType] = 1 AND Groups_Roles.RoleID= 20000 )
AND [Roles].[Role_ID] NOT IN
(SELECT [Groups_Roles].[RoleID] FROM [LegalReference_Group] JOIN [Groups_Roles]
ON LegalReference_Group.GroupID = Groups_Roles.GroupID
WHERE LegalReference_Group.legalID =Groups_Roles.GroupID AND [legalReference_Group].[legalType] = 2 AND Groups_Roles.RoleID= 20000)
ORDER BY MainLastName,
MainFirstName,
RoleName,
MainCode
GO -
I've rewritten your query to use EXISTS instead of IN. Removed the UNION ALL and change the comma separate table names to INNER JOIN. I think it's a better starting point to look at the query with a view to optimising it.
SELECT r.[Role_ID] AS [legalRoleID],
r.[UserID] AS [MainUserID],
u.[FirstName] AS [MainFirstName],
u.[LastName] AS [MainLastName],
r.[RoleName],
r.[Code] AS [MainCode],
u.[IsOnline],
u.[PersonnelID],
r.[DepartmentID],
u.[PersonnelID]
FROM [Roles] r
INNER JOIN [Users] u
ON u.[User_ID] = r.[UserID]
AND u.[IsActive] = 1
WHERE r.[DepartmentID] = 104
AND r.[CardTableStatus] = 1
AND r.[IsActive] = 1
AND NOT EXISTS(SELECT *
FROM [legalReference] lr
WHERE lr.[legalType] = 1
AND lr.[RoleID] = 20000
AND lr.[legalID] = r.Role_ID)
AND (EXISTS(SELECT *
FROM [Roles_Ex_Rights] rex
WHERE rex.[RightID] = 7295
AND rex.IsConfirm = 1
AND rex.[RoleID] = r.Role_ID)
OR EXISTS(SELECT *
FROM [Groups_Roles] gr
INNER JOIN Groups_Rights grt
ON grt.GroupID = gr.GroupID
AND grt.RightID = 7295
AND grt.IsConfirm = 1
WHERE gr.[RoleID] = r.Role_ID)
)
AND NOT EXISTS (SELECT *
FROM [legalReference] lr
INNER JOIN [Groups_Roles] gr
ON gr.[GroupID] = lr.[legalID]
AND gr.[RoleID] = r.[Role_ID]
WHERE lr.[legalType] = 2
AND lr.[RoleID] = 20000)
AND NOT EXISTS(SELECT *
FROM [legalReference_Group] lrg
WHERE lrg.[legalID] = r.[Role_ID]
AND lrg.[legalType] = 1
AND EXISTS (SELECT *
FROM [Groups_Roles] gr
WHERE gr.RoleID = 20000
AND gr.GroupID = lrg.[GroupID]))
AND NOT EXISTS(SELECT *
FROM [legalReference_Group] lrg
INNER JOIN Groups_Roles gr
ON gr.[GroupID] = lrg.[legalID]
AND gr.[RoleID] = r.[Role_ID]
WHERE lrg.[legalType] = 2
AND EXISTS(SELECT *
FROM [Groups_Roles] gr2
WHERE gr2.RoleID = 20000
AND gr2.GroupID = lrg.[GroupID]))
ORDER BY MainLastName, MainFirstName, RoleName, MainCode; -
Hi
Thanks a lot.
I've run your edited query. but its cost was 51% against last improved query that I sent in my last post. And Logical Read grows up for Table Groups_Roles.
I going to send Execution plan ASAP.
- zamani 41951 wrote:
Hi
Thanks a lot.
I've run your edited query. but its cost was 51% against last improved query that I sent in my last post. And Logical Read grows up for Table Groups_Roles.
I going to send Execution plan ASAP.
The query I rewrote was your initial query. Is the query I rewrote 51% better or worse? It was really just a starting point for improving the performance. Are there any missing indexes shown in the execution plan?
-
Hang on a second. Don't look at the estimated costs of the plans as a measure of performance. It absolutely is not. It's only a measure of the mathematics that the optimizer goes through. A plan can have a higher cost and yet run faster than the one with a lower cost. Measure performance using Extended Events or statistics time & I/O.
"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 -
It's hard to optimize without having access to the database, but this is my try:
WITH exclusions
AS
(SELECT
[legalReference].[legalID] AS [Role_ID]
FROM [legalReference]
WHERE [legalReference].[legalType] = 1
AND [legalReference].[RoleID] = 20000
UNION ALL
SELECT
[Groups_Roles].[RoleID]
FROM [legalReference]
JOIN [Groups_Roles]
ON legalReference.legalID = Groups_Roles.GroupID
WHERE [legalReference].[legalType] = 2
AND [legalReference].[RoleID] = 20000
UNION ALL
SELECT
[legalReference_Group].[legalID]
FROM [legalReference_Group]
JOIN Groups_Roles
ON legalReference_Group.GroupID = Groups_Roles.GroupID
WHERE [legalReference_Group].[legalType] = 1
AND Groups_Roles.RoleID = 20000
UNION ALL
SELECT
[Groups_Roles].[RoleID]
FROM [LegalReference_Group]
JOIN [Groups_Roles]
ON LegalReference_Group.GroupID = Groups_Roles.GroupID
WHERE LegalReference_Group.legalID = Groups_Roles.GroupID
AND [legalReference_Group].[legalType] = 2
AND Groups_Roles.RoleID = 20000),
inclusions
AS
(SELECT
[Roles_Ex_Rights].[RoleID]
FROM [Roles_Ex_Rights]
WHERE [Roles_Ex_Rights].[RightID] IN (7295)
AND IsConfirm = 1
UNION ALL
SELECT
[Groups_Roles].[RoleID]
FROM [Groups_Roles]
JOIN Groups_Rights
ON Groups_Roles.GroupID = Groups_Rights.GroupID
WHERE RightID = 7295
AND Groups_Rights.IsConfirm = 1)
SELECT
[Roles].[Role_ID] AS "legalRoleID"
,[Roles].[UserID] AS MainUserID
,[Users].[FirstName] AS MainFirstName
,[Users].[LastName] AS MainLastName
,[Roles].[RoleName]
,[Roles].[Code] AS MainCode
,[Users].[IsOnline]
,[Users].[PersonnelID]
,[Roles].[DepartmentID]
,[Users].[PersonnelID]
FROM Roles
JOIN Users
ON Roles.UserID = Users.[user_id]
JOIN inclusions i
ON i.[RoleID] = Roles.Role_ID
WHERE [Roles].[DepartmentID] = 104
AND [Roles].[CardTableStatus] = 1
AND [Roles].[IsActive] = 1
AND [Users].[IsActive] = 1
AND NOT EXISTS (SELECT
*
FROM exclusions
WHERE exclusions.Role_ID = [Roles].[Role_ID])
ORDER BY MainLastName,
MainFirstName,
RoleName,
MainCodeFrom my experience multiple IN/EXISTS statements in the same query often gives very bad performance.
- Jonathan AC Roberts wrote:zamani 41951 wrote:
Hi
Thanks a lot.
I've run your edited query. but its cost was 51% against last improved query that I sent in my last post. And Logical Read grows up for Table Groups_Roles.
I going to send Execution plan ASAP.
The query I rewrote was your initial query. Is the query I rewrote 51% better or worse? It was really just a starting point for improving the performance. Are there any missing indexes shown in the execution plan?
Hi
It's 51% worse.
Execution plan desn’t exists missing index.
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply