1

The following query is taking 5 seconds to return 9692 rows:

SELECT 
    Project.CalculateSequencialProject,
    Project.CreateDate,
    Project.OpeningDate,
    Project.ForecastClosingDate,
    Project.RealClosingDate,
    Project.ProjectStatus,
    Unit.Name AS UnitName,
    UTE.Name AS UTEName,
    Process.Name AS Process 
FROM 
    Project
        LEFT JOIN Unit ON Project.Unit_Id = Unit.Id AND Project.Company_Id = Unit.Company_Id
        LEFT JOIN UTE ON Project.UTE_Id = UTE.Id
        LEFT JOIN Process ON Project.Process_Id = Process.Id
WHERE 
    Project.Company_Id = '????????????????'

The execution plan is like this: ExecutionPlan

And the most "expensive" part of it is detailed here: Detail

As you may se in the tooltip image, the Clustered Index Scan with 75% of cost is using the table primary key instead of one of the following indexes.

CREATE NONCLUSTERED INDEX [IX_Company_Id] ON [dbo].[Project]
(
    [Company_Id] ASC
)

CREATE NONCLUSTERED INDEX [IX_Project_02] ON [dbo].[Project]
(
    [ProjectType_Id] ASC,
    [ProjectStatus] ASC,
    [Unit_Id] ASC,
    [Id] ASC,
    [ProjectDirector_Id] ASC,
    [UTE_Id] ASC,
    [Company_Id] ASC,
    [LostType_Id] ASC,
    [Pillar_Id] ASC,
    [ProjectPhase_Id] ASC
)
INCLUDE (   [CalculateSequencialProject],
    [DescriptionTittle],
    [OpeningDate],
    [ForecastClosingDate],
    [RealClosingDate])

Why?

  • It seems to me that index on Company_Id is not selective enough, and optimizer decides that full table scan (clustered index scan) is cheaper than index range scan + lookup. – a1ex07 Oct 7 '15 at 19:05
  • What do you suggest me to change so I can improve this query? The Project table has only 27k rows, I don't understand why such query is taking 5 seconds to run. – Juliano Nunes Silva Oliveira Oct 7 '15 at 20:20
  • What columns exactly does the PK consist of? – Jeffrey Van Laethem Oct 7 '15 at 20:35
  • Just the [Id] column. – Juliano Nunes Silva Oliveira Oct 7 '15 at 20:36
3

None of your indexes are good for this query

  • IX_Company_Id works only for the WHERE
  • IX_Project_02 has not overlap with any part of the query because the leading columns do not match a JOIN or WHERE

This should be better because it matches the JOIN and the WHERE although iIt does rely on a matching (Company_Id, Unit_Id) index on Unit

CREATE NONCLUSTERED INDEX [IX_Project_02] ON [dbo].[Project]
(
    [Company_Id] , --can swap these 2 to see what happens if no index on Unit
    [Unit_Id] ,

    [UTE_Id] ,

    ProcessID

)
INCLUDE
    CalculateSequencialProject,
    CreateDate,
    OpeningDate,
    ForecastClosingDate,
    RealClosingDate,
    ProjectStatus
  • If I drop all my indexes and create a new one like the one you suggested, and I have another query in my application that doesn't need Company_id, but needs Unit_Id, will it use this index? – Juliano Nunes Silva Oliveira Oct 8 '15 at 16:49
  • Probably not, but I did not say drop any existing indexes. And any query with Unit_Id won't use the current indexes either for the same reasons I gave – gbn Oct 8 '15 at 19:54
  • I was saying to drop existing indexes because they seem to be duplicated or even not used at all. – Juliano Nunes Silva Oliveira Oct 9 '15 at 12:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.