CODEWITHSUNDEEP
sql-server
Tom
Tom

Reputation: 8681

Missing index (Impact 97) : Create Non Clustered Index

I am trying to optimize my stored procedure. When I look at the query plan, I can see tablescan on tempcompany is showing 97 percent. I am also seeing the following message Missing index (Impact 97) : Create Non Clustered Index on #tempCompany I have already set non clustered indexes. Could somebody point out what the problem is 

 if object_id('tempdb..#tempCompany') is not null drop table #tempCompany else  

        select

            fp.companyId,fp.fiscalYear,fp.fiscalQuarter,fi.financialperiodid, fi.periodEndDate,
            fc.currencyId,fp.periodtypeid,ROW_NUMBER() OVER (PARTITION BY fp.companyId, 
            fp.fiscalYear, fp.fiscalQuarter ORDER BY fi.periodEndDate DESC) rowno   

        into #tempCompany               

        from 

            ciqFinPeriod fp

            inner join #companyId c on c.val = fp.companyId

            join ciqFinInstance fi on fi.financialperiodid = fp.financialperiodid

            join ciqFinInstanceToCollection ic on ic.financialInstanceId = fi.financialInstanceId

            left  join ciqFinCollection fc on fc.financialCollectionId = ic.financialCollectionId

            left join ciqFinCollectionData fd on fd.financialCollectionId = fc.financialCollectionId

        where 

            fp.periodTypeId = @periodtypeId

            and fi.periodenddate >= @date

            --and fp.companyId in (select val from @companyId)

            CREATE NONCLUSTERED INDEX id_companyId2 on #tempCompany(companyId,fiscalYear,fiscalQuarter,financialperiodid,periodEndDate,currencyId,periodtypeid,rowno)  



 if object_id('tempdb..#EstPeriodTbl') is not null drop table #EstPeriodTbl else    

    select

        companyId,fiscalYear,fiscalQuarter,financialPeriodId,periodenddate,currencyId,
        periodtypeid,rowno

    into #EstPeriodTbl

    from #tempCompany a

    where a.rowno = 1

    order by companyid, periodenddate

    CREATE NONCLUSTERED INDEX id_companyId3 on #EstPeriodTbl(companyId,periodenddate,fiscalYear,fiscalQuarter,currencyId,financialPeriodId,rowno)     

   Execution Plan

enter image description here

Upvotes: 1

Views: 4910

Answers (2)

MBurnham
MBurnham

Reputation: 381

Short answer: The index you provided, does not help SQL Server in the query you are doing. If you create another non-clustered index, and have rowno as the first column in the index, Sql Server will probably be able to use that index.

Long explination: The reason you have a problem, is because the index you have created isn't useful to SQL Server with this specific query. The order that records are sorted in on an index is determined by the order you specify them when creating an index.

(e.g. Your index orders your records by companyId first, and then orders the records with the same companyId by their fiscalYear, and then by their fiscalQuarter.)

Trying to use the provided index to find an item by just it's rowno value, would be like you trying to find the entries in the phone book based off of someone's phone number. The only way to locate all of the matching records is to search through every record in the book (i.e. a table scan).

In general, you can utalize nonclustered indexs only when the information you use in your where clause matches the first column in your index, (i.e. if you can provide a SARGable predicate for companyId in your where clause, you could probably use this index)

Using the phone book again: If I gave you a last name and a phone number, now you no longer need to do a full table scan on the phone book, you can do an index scan for the last name. Which would be more efficient. And if you were able to give a last name, and first name, and then middle initial and phone number, you could do an even more efficient table scan. But if I only provide you with a last name, middle initial and phone number; now I am back to scaning the index on just the value of last name.

So if you can narrow down your record set to use at least companyId (i.e. use companyID in your where clause) you can use the index you have provided.

Or, and I imagine this is what you will want to do, create an index that sorts by rowno, then companyId and periodendDate.

e.g.

CREATE NONCLUSTERED INDEX idx_temp_rowno ON #tempCompany(rowno, companyId, periodenddate)

Upvotes: 1

cloudsafe
cloudsafe

Reputation: 2506

You do not need to include everything in the #tempCompany index; just rowno:

CREATE NONCLUSTERED INDEX id_companyId2 on #tempCompany(rowno)

Upvotes: 1

Related Questions