July 7, 2020 at 4:34 pm
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.
Again, what are you measuring the "51% worse" with? If you're comparing execution plans, that's NOT a measure of performance... like Grant tried to explain, the Execution Plan is just some math and even the actual execution plan is riddled with estimates instead of actual performance.
The only true measure of performance is how long did the code take to run, how much CPU time did it consume during the run, and what was the I/O during the run?
--Jeff Moden
Change is inevitable... Change for the better is not.
July 7, 2020 at 5:08 pm
zamani 41951 wrote: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.
Again, what are you measuring the "51% worse" with? If you're comparing execution plans, that's NOT a measure of performance... like Grant tried to explain, the Execution Plan is just some math and even the actual execution plan is riddled with estimates instead of actual performance.
The only true measure of performance is how long did the code take to run, how much CPU time did it consume during the run, and what was the I/O during the run?
Yes, to get the statistics just add the line
SET STATISTICS IO, TIME ON
at the top of the query then run it in SSMS.
July 7, 2020 at 7:39 pm
See if this is an improvement
With excludeRoles
As (
Select lr.legalID
From legalReference lr
Where lr.legalType = 1
And lr.RoleID = 20000
Union All
Select gr.RoleID
From legalReference lr
Inner Join Groups_Roles gr On gr.GroupID = lr.LegalID
Where lr.legalType = 2
And (lr.RoleID = 20000 Or lr.GroupID In (Select g.GroupID From Groups_Roles g Where g.RoleID = 20000))
Union All
Select lrg.legalID
From legalReference_Group lrg
Where lrg.legalType = 1
And lrg.GroupID In (Select g.GroupID From Groups_Roles g Where g.RoleID = 20000)
)
, includeRoles
As (
Select rr.RoleID
From Roles_Ex_Rights rr
Where rr.IsConfirm = 1
And rr.RightID In (7295)
Union All
Select gr.RoleID
From Groups_Roles gr
Inner Join Groups_Rights grr On grr.GroupID = gr.GroupID
Where gr.IsConfirm = 1
And gr.RightID = 7295
)
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
Where u.IsActive = 1
And r.IsActive = 1
And r.DepartmentID = 104
And r.CardTableStatus = 1
And Exists (Select * From includeRoles ir Where ir.RoleID = r.Role_ID)
And Not Exists (Select * From excludeRoles er Where er.RoleID = r.Role_ID)
Order By
MainLastName
, MainFirstName
, RoleName
, MainCode;
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
July 8, 2020 at 7:09 am
Jeff Moden wrote:zamani 41951 wrote: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.
Again, what are you measuring the "51% worse" with? If you're comparing execution plans, that's NOT a measure of performance... like Grant tried to explain, the Execution Plan is just some math and even the actual execution plan is riddled with estimates instead of actual performance.
The only true measure of performance is how long did the code take to run, how much CPU time did it consume during the run, and what was the I/O during the run?
Yes, to get the statistics just add the line
SET STATISTICS IO, TIME ONat the top of the query then run it in SSMS.
Attached file contains IO and TIME statistics result between general and your query.
first results belong to general query.
July 14, 2020 at 8:57 am
I've removed PK of LegalReferrence Table. Logical Read reduced for this table very good.
July 14, 2020 at 4:42 pm
I've removed PK of LegalReferrence Table. Logical Read reduced for this table very good.
Why would you remove the PK constraint from a table? That doesn't make any sense...
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
July 18, 2020 at 7:47 am
Friends, I've reached to a good improvement.
I destroyed fragmentation for table Groups_Roles Table. Result was very good. My goal procedure removed from SQL Serve Profiler with more than 3 seconds duration.
July 18, 2020 at 8:39 pm
I've removed PK of LegalReferrence Table. Logical Read reduced for this table very good.
I'm thinking the reason why that worked is simply because that caused a rebuild of related statistics which also caused a recompile.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 18, 2020 at 8:40 pm
Friends, I've reached to a good improvement.
I destroyed fragmentation for table Groups_Roles Table. Result was very good. My goal procedure removed from SQL Serve Profiler with more than 3 seconds duration.
So... prior to doing that, when was the last time you rebuilt statistics?
--Jeff Moden
Change is inevitable... Change for the better is not.
September 2, 2020 at 11:09 am
Hi
Finally Mission Accomplished. Thank you very mush for guide.
The below code made good improvement. (Execution Plan Attached)
some days more than 95% improvement vs old script.
still two problem remain:
1. spill to tempdb. Does change property "Minimum memory per query" helps to eleminate this problem?
2. Cardinality Estimation fauiler some times happen. does TVP or Local Temp Tables Helps to solve this problem?
;WITH LegalRecords (Rid) AS
(
Select [legalReference].[legalID] FROM [legalReference] WHERE [legalReference].[legalType] = 1
AND [legalReference].[RoleID] = 20534
UNION ALL
Select [Groups_Roles].[RoleID] From [legalReference] JOIN [Groups_Roles] ON
legalReference.legalID = Groups_Roles.GroupID WHERE [legalReference].[legalType]=2 AND
[legalReference].[RoleID] = 20534
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 = 20534
UNION ALL
Select [Groups_Roles].[RoleID] From [legalReference_Group] JOIN [Groups_Roles] ON
[legalReference_Group].[legalID] = Groups_Roles.GroupID JOIN Groups_Roles AS GP2 ON
legalReference_Group.GroupID = gp2.GroupID WHERE [legalReference_Group].[legalType]=2
AND gp2.RoleID = 20534 )
-- END With
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].[CardTableStatus] = 1 AND [Users].[IsActive] = 1 AND [Roles].[IsActive] = 1
AND NOT EXISTS (SELECT Rid FROM LegalRecords WHERE Rid = Roles.Role_ID)
AND EXISTS (SELECT Department_ID FROM Departments WHERE IsReference = 1 AND Roles.DepartmentID =
Department_ID)
AND EXISTS (Select [Roles_Ex_Rights].[RoleID] FROM [Roles_Ex_Rights] WHERE
[Roles_Ex_Rights].[RightID] IN (7873) and IsConfirm=1 AND Roles_Ex_Rights.RoleID =
Roles.Role_ID
UNION ALL
Select [Groups_Roles].[RoleID] From [Groups_Roles] JOIN Groups_Rights ON
Groups_Roles.GroupId = Groups_Rights.GroupID WHERE [Groups_Rights].[RightID] IN
(7873) AND Groups_Rights.IsConfirm=1 AND Groups_Roles.RoleID = Roles.Role_ID)
ORDER BY MainLastName , MainFirstName , RoleName,MainCode
Viewing 10 posts - 16 through 24 (of 24 total)
You must be logged in to reply to this topic. Login to reply