Solved

effect of index on temptable

Posted on 2013-10-24
9
173 Views
Last Modified: 2013-11-06
in the below code, even with the indexes on the temp table, it still took the same time to return the records as without the indexes.. can you review if the indexes can be made to be more effectual on the temp table? thanks.
0
Comment
Question by:25112
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
9 Comments
 
LVL 5

Author Comment

by:25112
ID: 39597383
the code is

---------------


SET NOCOUNT ON
GO
;WITH EMPHIST_CTE1
     AS (SELECT *
           FROM
                              (SELECT OFFICE_KEY,
                                          EMP_ID,
                                          EMP_FNAME,                                          EMP_LNAME,
                                          LOCATION_KEY,
                                          COUNT(*) OVER ( partition BY EMP_ID, EMPHIST_DATE) cnt,
                                          ROW_NUMBER() OVER ( partition BY EMP_ID, EMPHIST_DATE ORDER BY OFFICE_KEY) rn
                   FROM EMPHIST  
                              WHERE EMPHIST_DATE = '2013-10-01'  )  t1
          WHERE cnt in (2,3,4)),
    EMPHIST_CTE2
     AS (SELECT EMP_ID,
                MAX(CASE rn WHEN 1 THEN EMP_FNAME END) EMP_FNAME,
                        MAX(CASE rn WHEN 1 THEN EMP_LNAME END) EMP_LNAME,
                MAX(CASE rn WHEN 1 THEN OFFICE_KEY END) OFFICE_KEY1,
                MAX(CASE rn WHEN 2 THEN OFFICE_KEY END) OFFICE_KEY2,
                MAX(CASE rn WHEN 3 THEN OFFICE_KEY END) OFFICE_KEY3,
                MAX(CASE rn WHEN 4 THEN OFFICE_KEY END) OFFICE_KEY4,
                MAX(CASE rn WHEN 1 THEN LOCATION_KEY END) LOCATION_KEY1,
                MAX(CASE rn WHEN 2 THEN LOCATION_KEY END) LOCATION_KEY2,
                MAX(CASE rn WHEN 3 THEN LOCATION_KEY END) LOCATION_KEY3,
                MAX(CASE rn WHEN 4 THEN LOCATION_KEY END) LOCATION_KEY4
           FROM EMPHIST_CTE1
          GROUP BY EMP_ID)
         
         
          SELECT * into #EMPHIST from EMPHIST_CTE2   WHERE  (OFFICE_KEY1 = '43637' or OFFICE_KEY2 ='43637' or isnull(OFFICE_KEY3,0) = '43637' or isnull(OFFICE_KEY4,0) = '43637')
         
                        create nonclustered index ix_e1_OFFICE_KEY1 on #EMPHIST (OFFICE_KEY1 asc)
                create nonclustered index ix_e1_OFFICE_KEY2 on #EMPHIST  (OFFICE_KEY2 asc)
                create nonclustered index ix_e1_OFFICE_KEY3 on #EMPHIST  (OFFICE_KEY3 asc)
                create nonclustered index ix_e1_OFFICE_KEY4 on #EMPHIST (OFFICE_KEY4 asc)
               
                create nonclustered index ix_e1_LOCATION_KEY1 on #EMPHIST (LOCATION_KEY1 asc)
                create nonclustered index ix_e1_LOCATION_KEY2 on #EMPHIST (LOCATION_KEY2 asc)
                create nonclustered index ix_e1_LOCATION_KEY3 on #EMPHIST (LOCATION_KEY3 asc)
                create nonclustered index ix_e1_LOCATION_KEY4 on #EMPHIST (LOCATION_KEY4 asc)
               
 
          UPDATE #EMPHIST set OFFICE_KEY1 = OFFICE_KEY2, LOCATION_KEY1 = LOCATION_KEY2, OFFICE_KEY2 = OFFICE_KEY1, LOCATION_KEY2 = LOCATION_KEY1 WHERE OFFICE_KEY1 <> '43637' and OFFICE_KEY2 = '43637'
          UPDATE #EMPHIST set OFFICE_KEY1 = OFFICE_KEY3, LOCATION_KEY1 = LOCATION_KEY3, OFFICE_KEY3 = OFFICE_KEY1, LOCATION_KEY3 = LOCATION_KEY1 WHERE OFFICE_KEY1 <> '43637' and isnull(OFFICE_KEY3,0) = '43637'
          UPDATE #EMPHIST set OFFICE_KEY1 = OFFICE_KEY4, LOCATION_KEY1 = LOCATION_KEY4, OFFICE_KEY4 = OFFICE_KEY1, LOCATION_KEY4 = LOCATION_KEY1 WHERE OFFICE_KEY1 <> '43637' and isnull(OFFICE_KEY4,0) = '43637'
           
       
SELECT  

        e1.EMP_FNAME+' '+e1.EMP_LNAME+' / '+e1.EMP_ID+ CHAR(10),
          'OfficeNo: '+cast(o2.OFFICE_KEY as varchar(50))+' OFFICE Desc: '+o2.Description+' County: '+o2.COUNTY+' Location Number: '+l2.Location_Number+' Location Name: '+l2.Location_Name + CHAR(10),
            CASE WHEN OFFICE_KEY3 IS NULL THEN '' ELSE  'OfficeNo: '+cast(o3.OFFICE_KEY as varchar(50))+' OFFICE Name: '+o3.Description+' County: '+o3.COUNTY+' Location Number: '+l3.Location_Number+' Location Name: '+l3.Location_Name + CHAR(10)  END ,
            CASE WHEN OFFICE_KEY4 IS NULL THEN '' ELSE  'OfficeNo: '+cast(o4.OFFICE_KEY as varchar(50))+' OFFICE Name: '+o4.Description+' County: '+o4.COUNTY+' Location Number: '+l4.Location_Number+' Location Name: '+l4.Location_Name END
 
        
  FROM #EMPHIST e1
       JOIN OFFICE o1
         ON e1.OFFICE_KEY1 = o1.OFFICE_KEY
       JOIN LOCATION l1
         ON e1.LOCATION_KEY1 = l1.LOCATION_KEY AND o1.OFFICE_KEY = l1.OFFICE_KEY
       JOIN OFFICE o2
         ON e1.OFFICE_KEY2 = o2.OFFICE_KEY
       JOIN LOCATION l2
         ON e1.LOCATION_KEY2 = l2.LOCATION_KEY   AND o2.OFFICE_KEY = l2.OFFICE_KEY
       LEFT JOIN OFFICE o3
         ON e1.OFFICE_KEY3 = o3.OFFICE_KEY
       LEFT JOIN LOCATION l3
         ON e1.LOCATION_KEY3 = l3.LOCATION_KEY   AND o3.OFFICE_KEY = l3.OFFICE_KEY
       LEFT JOIN OFFICE o4
         ON e1.OFFICE_KEY4 = o4.OFFICE_KEY
       LEFT JOIN LOCATION l4
         ON e1.LOCATION_KEY4 = l4.LOCATION_KEY   AND o4.OFFICE_KEY = l4.OFFICE_KEY
         
         DROP TABLE #EMPHIST
0
 
LVL 25

Assisted Solution

by:jogos
jogos earned 333 total points
ID: 39599529
To much unnecessary index-actions

You create indexes on each column -> only one index will be used
You update indexed columns -> indexes must be maintained
You use a function on a indexed column in where-clause -> index won't be used
and that function has no effect   "isnull(OFFICE_KEY4,0) = '43637'" when it is null you replace it by 0 , this is not a numerical so type conversion and that 0 cannot be equal to   '43637'
You don't use included columns so even for filtering your table has to be read


So get rid of all the indexes you use and replace the index and update block with this.  
 
create nonclustered index ix_e1_OFFICE_KEY2 
   on #EMPHIST  (OFFICE_KEY2 asc) 
  include (OFFICE_KEY1)
   where  OFFICE_KEY2 = '43637'  and OFFICE_KEY1 <> '43637' 
UPDATE #EMPHIST
  set OFFICE_KEY1 = OFFICE_KEY2, LOCATION_KEY1 = LOCATION_KEY2, OFFICE_KEY2 = OFFICE_KEY1, LOCATION_KEY2 = LOCATION_KEY1 
  WHERE OFFICE_KEY1 <> '43637' and OFFICE_KEY2 = '43637'

create nonclustered index ix_e1_OFFICE_KEY3  
  on #EMPHIST  (OFFICE_KEY3 asc) 
  include (OFFICE_KEY1)
  where OFFICE_KEY3 = '43637' and OFFICE_KEY1 <> '43637' 

UPDATE #EMPHIST 
  set OFFICE_KEY1 = OFFICE_KEY3, LOCATION_KEY1 = LOCATION_KEY3, OFFICE_KEY3 = OFFICE_KEY1, LOCATION_KEY3 = LOCATION_KEY1 
  WHERE OFFICE_KEY1 <> '43637' and OFFICE_KEY3 = '43637'

create nonclustered index ix_e1_OFFICE_KEY4 
  on #EMPHIST (OFFICE_KEY4 asc) 
  include (OFFICE_KEY1)
  where OFFICE_KEY3 = '43637' and OFFICE_KEY1 <> '43637' 
                
UPDATE #EMPHIST 
set OFFICE_KEY1 = OFFICE_KEY4, LOCATION_KEY1 = LOCATION_KEY4, OFFICE_KEY4 = OFFICE_KEY1, LOCATION_KEY4 = LOCATION_KEY1 
WHERE OFFICE_KEY1 <> '43637' and OFFICE_KEY4 = '43637'

Open in new window


And to see what effect an action has you must monitor your changes before and after (reads/writes , execution plan to see if index is used .....).  

But to create the index the whole table must be scanned so that is the same as the update actions would do. My guess is that no indexes before the update is better
0
 
LVL 25

Expert Comment

by:jogos
ID: 39599547
Guess that problem lies in CTE that is not always that fast and maybe indexes on your real table.
0
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
LVL 5

Author Comment

by:25112
ID: 39600598
>>So get rid of all the indexes you use and replace the index and update block with this.  

appreciate that idea.. but 100s of different parameters may call in, so it is not feasible, as these update statements will differ each time (1st time it will be '43637', next time totally different and so forth, 100s of times over)..

>>Guess that problem lies in CTE that is not always that fast
can you suggest a different syntax but still does what CTE in the above logic will do?

thanks jogos!
0
 
LVL 40

Assisted Solution

by:lcohan
lcohan earned 167 total points
ID: 39601110
"but 100s of different parameters may call in, so it is not feasible, as these update statements will differ each time "

You must find THE one - the most significant to reduce to the minimum ALL record sets involved in your queries - and make it MANDATORY and at the BEGINING or FIRST in any WHERE or JOIN clauses. This way you can add an index on that column (or a group of max 2-3 columns) to significantly reduce all record sets to be processed.

SQL (as many other databases)  makes use at the best/most of 2-3 columns matching your WHERE/JOIN clauses and SQL in particular MUST have them in the exact same order (if more than 1) as the matching index. Same thing I imposed at my work place to deal with similar performance issues to what you have and even worse considering the code was generated via LLBLGen.

And I agree with jogos that CTE are great but may be a huge pain especially dealing with large record sets. Disaster performance I found if you mix them with temp tables similar to mixing temp and @tables.
0
 
LVL 25

Expert Comment

by:jogos
ID: 39601363
So if the value differs then it won t be a filtered index. But still create at most only the 3 indexes i showed with the included column and important not creating the index before yiu will use it so it will not be updated.

And as i said trace and execution plan will learn you and us if problem is in the cte, temp table or the join
0
 
LVL 25

Accepted Solution

by:
jogos earned 333 total points
ID: 39603804
Without CTE ,

create view vw_emphist as
SELECT * 
           FROM 
                              (SELECT OFFICE_KEY, 
                                          EMP_ID, 
                                          EMP_FNAME,                                          EMP_LNAME, 
                                          LOCATION_KEY, EMPHIST_DATE,
                                          COUNT(*) OVER ( partition BY EMP_ID, EMPHIST_DATE) cnt, 
                                          ROW_NUMBER() OVER ( partition BY EMP_ID, EMPHIST_DATE ORDER BY OFFICE_KEY) rn 
                   FROM EMPHIST   )  t1
          WHERE cnt in (2,3,4);

Open in new window


And then direct filling the temp-table
 
SELECT EMP_ID,
                MAX(CASE rn WHEN 1 THEN EMP_FNAME END) EMP_FNAME,
                        MAX(CASE rn WHEN 1 THEN EMP_LNAME END) EMP_LNAME,
                MAX(CASE rn WHEN 1 THEN OFFICE_KEY END) OFFICE_KEY1,
                MAX(CASE rn WHEN 2 THEN OFFICE_KEY END) OFFICE_KEY2,
                MAX(CASE rn WHEN 3 THEN OFFICE_KEY END) OFFICE_KEY3,
                MAX(CASE rn WHEN 4 THEN OFFICE_KEY END) OFFICE_KEY4,
                MAX(CASE rn WHEN 1 THEN LOCATION_KEY END) LOCATION_KEY1,
                MAX(CASE rn WHEN 2 THEN LOCATION_KEY END) LOCATION_KEY2,
                MAX(CASE rn WHEN 3 THEN LOCATION_KEY END) LOCATION_KEY3,
                MAX(CASE rn WHEN 4 THEN LOCATION_KEY END) LOCATION_KEY4
into #EMPHIST 
FROM vw_emphist  
WHERE    EMPHIST_DATE = '2013-10-01'
GROUP BY EMP_ID
Having (MAX(CASE rn WHEN 1 THEN OFFICE_KEY END) = '43637'
            OR MAX(CASE rn WHEN 2 THEN OFFICE_KEY END) ='43637'
            OR isnull(max(CASE rn WHEN 3 THEN OFFICE_KEY END),'') = '43637'
            OR isnull( MAX(CASE rn WHEN 4 THEN OFFICE_KEY END),'') = '43637'
        )

Open in new window

0
 
LVL 25

Expert Comment

by:jogos
ID: 39603807
About: and that function has no effect   "isnull(OFFICE_KEY4,0) = '43637'"

That was not correct, it naturaly prevents that when no record with rn=4 is found you still get a  result ..... but if you want index on that column being used you must write it as
  (OFFICE_KEY4 is null or OFFICE_KEY = '43637')
0
 
LVL 5

Author Comment

by:25112
ID: 39627641
temp tables and   (OFFICE_KEY4 is null or OFFICE_KEY = '43637')  option helped. thx!
0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question