• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 168
  • Last Modified:

Issue with performance of a SQL query

I have a SQL Query that doesn't perform well.  It takes about 20 minutes to run the attached query and I feel that it could probably run faster, but I'm not exactly sure how to accomplish that.  Can someone give some suggest on what I can do to increase performance?  If it includes creating view or indexes, please include the SQL query to do so as I have never created either.

SELECT e.EmpName Timekeeper, Sum(f.FSPWorkedHrsEntered) [Hrs Worked], Sum(f.FSPNonBilHrsEntered) [NonBillable Hrs], Sum(f.FSPBilHrsEntered) [Billable Hrs],
  Round((Sum(f.FSPBilHrsEntered) / 12) * 12, 2) [Annualized Billable Hrs], Sum(f.FSPHrsBilled) [Hrs Billed], Sum(f.FSPFeeBldActualAmt) [Fees Billed],
  Round(Sum(f.FSPFeeBldActualAmt) / Sum(f.FSPHrsBilled), 2) [Avg Billed Rate], Sum(f.FSPFeeReceived) [Fees Received], u.[Unbilled Amount], e.Goal, (((e.Percent1 * 11192186.41) / 1000000) +
  ((e.Percent2 * 0) / 1000000) + ((e.Percent3 * 0) / 1000000) + ((e.Percent4 * 0) / 1000000)) [Taxable Income], ((100000 / 12) * 12) Cost,
  (Sum(f.FSPFeeBldActualAmt) - (((e.Percent1 * 11192186.41) / 1000000) + ((e.Percent2 * 0) / 1000000) + ((e.Percent3 * 0) / 1000000) + ((e.Percent4 * 0) / 1000000)) - ((100000 /
  12) * 12)) [Profit/Loss]
FROM (SELECT e1.EmpSysNbr Timekeeper, Sum(u1.UTAmount) [Unbilled Amount]
    FROM dbo.UnbilledTime u1
      INNER JOIN dbo.Employee e1 ON u1.UTTkpr = e1.EmpSysNbr
    GROUP BY e1.EmpSysNbr) u
    INNER JOIN dbo.Employee e ON u.Timekeeper = e.EmpSysNbr
    INNER JOIN dbo.FeeSumByPrd f ON e.EmpSysNbr = f.FSPTkpr
  WHERE f.FSPPrdYear = 2009 AND f.FSPPrdNbr BETWEEN 1 AND 12 AND e.EmpSysNbr NOT IN (103, 109, 56, 68, 88, 105, 44, 34, 174, 147, 46, 29, 37, 201, 148, 69, 36, 45)
    AND e.EmpPrsTyp = '01'
  GROUP BY e.EmpName, u.[Unbilled Amount], e.Goal, e.EmpPrsTyp, e.Percent1, e.Percent2, e.Percent3, e.Percent4
    HAVING Sum(f.FSPWorkedHrsEntered) > 0
    ORDER BY e.EmpName, e.EmpPrsTyp

Open in new window

0
TPBPIT
Asked:
TPBPIT
  • 6
  • 3
  • 3
3 Solutions
 
rajvjaCommented:
try indexing the table on columns which are used in where clause.

See the execution plan and find if there are any index scan...
0
 
TPBPITAuthor Commented:
1) How do I create the index?  I have never done it before.
2) There are two tables being used.  Do I index both?
0
 
rajvjaCommented:
See the link below on query performance and indexing

http://www.sql-server-performance.com/tips/optimizing_indexes_general_p1.aspx
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
Patrick MatthewsCommented:
Hello TPBPIT,

Generally speaking, you want to have indexes on any columns used in joins, and as WHERE criteria.  Also,
Try not to use IN unless you have to: using IN for e.EmpSysNbr may interfere with SQL Server leveraging
an index on that column, thus resulting in a full table scan (bad!).

Regards,

Patrick
0
 
TPBPITAuthor Commented:
matthew, how can I change this to not use the in, but keep that same function?  Also, on creating the indexes: a)do I only add those columns to the index which is being used for joins and where and b) can I create the new indexes just by going to the management console, selecting the table and adding it under indexes?  THis is SQL 2005.  Lastly, how do I know whether it should be unique or non-unique and clustered or non-clustered?
0
 
Patrick MatthewsCommented:
TPBPIT,

At least two ways:

1) Instead of NOT IN, use <>:

  WHERE f.FSPPrdYear = 2009 AND f.FSPPrdNbr BETWEEN 1 AND 12 AND e.EmpSysNbr <>103 AND e.EmpSysNbr <>109
 AND e.EmpSysNbr <>56 AND e.EmpSysNbr <>68 AND e.EmpSysNbr <>88 AND e.EmpSysNbr <>105 AND e.EmpSysNbr <>44
 AND e.EmpSysNbr <>34 AND e.EmpSysNbr <>174 AND e.EmpSysNbr <>147 AND e.EmpSysNbr <>46 AND e.EmpSysNbr <>29
 AND e.EmpSysNbr <>37 AND e.EmpSysNbr <>201 AND e.EmpSysNbr <>148 AND e.EmpSysNbr <>69 AND e.EmpSysNbr <>36
 AND e.EmpSysNbr <>45

2) Have some sort of column on the Employee table indicating include/exclude:

  WHERE f.FSPPrdYear = 2009 AND f.FSPPrdNbr BETWEEN 1 AND 12 AND e.Include = 1



Patrick
0
 
TPBPITAuthor Commented:
First sorry about calling you Matthew.  Second, what do you mean in #2?
0
 
rajvjaCommented:
NOT IN is used because, values to be checked might be more than 1
<> is used to check for 1 value

Am I right TPBPIT?
0
 
TPBPITAuthor Commented:
If you mean that the value may appear in more than one row, then correct.

Damian
0
 
Patrick MatthewsCommented:
rajvja,

I understand why NOT IN is used.  All I am saying is that it can be suboptimal, and I outlined an alternative
to using it.

Patrick
0
 
TPBPITAuthor Commented:
Additional information.  There was already index for FeeSumByPrd with FSPPrdYear and FSPPrdNbr as key columns and  Non-Clustered Non-Unique.  Then there was one for UnbilledTime with the column Uttkpr as the same as above.  I added an index for Employee with columns of EmpSysNbr and EmpPrsTyp with the same settings.

Does I do the correct thing?
0
 
TPBPITAuthor Commented:
Thanks guys for the help.  I made the suggested query changes and created that one index and teh run time went from 20+ minutes to under 3 minutes.  I still have a long way to go on understandind indexes, but I have gained some knowledge today.

Thanks again,
Damian
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

  • 6
  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now