Solved

effect of index on temptable

Posted on 2013-10-24
9
167 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
  • 5
  • 3
9 Comments
 
LVL 5

Author Comment

by:25112
Comment Utility
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
Comment Utility
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
Comment Utility
Guess that problem lies in CTE that is not always that fast and maybe indexes on your real table.
0
 
LVL 5

Author Comment

by:25112
Comment Utility
>>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
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 
LVL 39

Assisted Solution

by:lcohan
lcohan earned 167 total points
Comment Utility
"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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
temp tables and   (OFFICE_KEY4 is null or OFFICE_KEY = '43637')  option helped. thx!
0

Featured Post

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
In this article I will describe the Copy Database Wizard 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.
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

771 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now