?
Solved

effect of index on temptable

Posted on 2013-10-24
9
Medium Priority
?
175 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 1332 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
Get real performance insights from real users

Key features:
- Total Pages Views and Load times
- Top Pages Viewed and Load Times
- Real Time Site Page Build Performance
- Users’ Browser and Platform Performance
- Geographic User Breakdown
- And more

 
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 668 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 1332 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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

770 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