spill to tempdb problem

Question

spill to tempdb problem

Viewing 10 posts - 16 through 24 (of 24 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 1

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?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jonathan AC Roberts SSCoach Points: 18263 More actions · Answer 2

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 ON

at the top of the query then run it in SSMS.

Jeffrey Williams SSC Guru Points: 90252 More actions · Answer 3

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

zamani 41951 Old Hand Points: 325 More actions · Answer 4

Jonathan AC Roberts wrote:

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 ON
at 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.

Attachments:

You must be logged in to view attached files.

zamani 41951 Old Hand Points: 325 More actions · Answer 5

I've removed PK of LegalReferrence Table. Logical Read reduced for this table very good.

Jeffrey Williams SSC Guru Points: 90252 More actions · Answer 6

zamani 41951 wrote:

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

zamani 41951 Old Hand Points: 325 More actions · Answer 7

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.

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 8

zamani 41951 wrote:

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 9

zamani 41951 wrote:

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

zamani 41951 Old Hand Points: 325 More actions · Answer 10

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

Attachments:

You must be logged in to view attached files.