Post reply

Index Question

  • October 1, 2010 at 1:48 am

    #241206

    1.

    Select top 501 subnaics.[SubCatInd] , bdf.[Business ID], bdf.[Business ID Number], bdf.[Business Name] ,

    bdf.[Address] , bdf.City , bdf.[State], bdf.ZIP,

    bdf.[ZIP+4], bdf.Phone, bdf.Contact, bdf.Gender, bdf.Title,

    bdf.[SIC Code], bdf.[SIC Description], bdf.[NAICS Code], bdf.[NAICS Description] ,

    bdf.[Location Type], bdf.[Year of 1st Appearance], bdf.[Number of Local Employees],

    bdf.[Total Number of Employees], bdf.[Local Annual Sales], bdf.[Total Annual Sales],

    bdf.[Latitude], bdf.[Longitude] ,

    bdf.[Fax] , bdf.CityID , bdf.StateID , bdf.CountryID , bdf.Country

    ,subnaics.SubcategoryID,subnaics.SubCategoryName,subnaics.CategoryId,subnaics.CategoryName,

    subnaics.IndustryID,subnaics.IndustryName from

    BusinessData bdf ,SubCategoryNAICSCode subnaics

    where bdf.[NAICS Code]=subnaics.NAICSCode

    and bdf.[State] like 't%'

    actualplan1 is not seleting IX_BusinessData_NAICSCodeBnAddrCityStCityStZipZip4Phone index

    and that's why query response is very slow on the other hand following query

    2.

    Select top 501 subnaics.[SubCatInd] , bdf.[Business ID], bdf.[Business ID Number], bdf.[Business Name] ,

    bdf.[Address] , bdf.City , bdf.[State], bdf.ZIP,

    bdf.[ZIP+4], bdf.Phone, bdf.Contact, bdf.Gender, bdf.Title,

    bdf.[SIC Code], bdf.[SIC Description], bdf.[NAICS Code], bdf.[NAICS Description] ,

    bdf.[Location Type], bdf.[Year of 1st Appearance], bdf.[Number of Local Employees],

    bdf.[Total Number of Employees], bdf.[Local Annual Sales], bdf.[Total Annual Sales],

    bdf.[Latitude], bdf.[Longitude] ,

    bdf.[Fax] , bdf.CityID , bdf.StateID , bdf.CountryID , bdf.Country

    ,subnaics.SubcategoryID,subnaics.SubCategoryName,subnaics.CategoryId,subnaics.CategoryName,

    subnaics.IndustryID,subnaics.IndustryName from

    BusinessData bdf ,SubCategoryNAICSCode subnaics

    where bdf.[NAICS Code]=subnaics.NAICSCode

    and bdf.[State] like 'a%'

    actualplan2 is returning fast response because it select

    IX_BusinessData_NAICSCodeBnAddrCityStCityStZipZip4Phone index

    why actual plan1 is not selecting the required index even though i have just change 'a%' with 't%'??

  • October 1, 2010 at 3:10 am

    #1230094

    whats the percentage of records returned by both selects (without the top) with regards to the total number of records in the table?

  • October 1, 2010 at 4:18 am

    #1230102

    For detail of tables see this thread

    http://qa.sqlservercentral.com/Forums/Topic988717-360-1.aspx

  • October 1, 2010 at 5:57 am

    #1230131

    then the response is the same as that thread... no need for a second thread

  • October 1, 2010 at 6:01 am

    #1230133

    But the sql is different

  • October 1, 2010 at 6:04 am

    #1230136

    The number of rows being returned is different, so the second plan is getting a seek and a key lookup instead of a scan. Personally, I wouldn't be happy with either plan in terms of performance. I'd try to get rid of the key lookup as well. Possibly add the columns to the index using the INCLUDE statement as a solution.

    ----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

  • October 3, 2010 at 5:38 am

    #1230764

    Grant Fritchey (10/1/2010)

    Personally, I wouldn't be happy with either plan in terms of performance. I'd try to get rid of the key lookup as well. Possibly add the columns to the index using the INCLUDE statement as a solution.

    There are already nine columns on the index IX_BusinessData_NAICSCodeStBnAddrCityCityStZipZip4Phone and the key lookup returns a further twenty. I'm not sure I would recommend an index over 29 columns, included or not.

    abcim, would you please try the following rewrite to see how it performs in both cases please?

    This code uses one of your old indexes, not the new IX_BusinessData_NAICSCodeStBnAddrCityCityStZipZip4Phone index, because I don't think it adds much value here.

    select top (501)

    subnaics.[SubCatInd] ,

    bdf.[Business ID],

    bdf.[Business ID Number],

    bdf.[Business Name] ,

    bdf.[Address] ,

    bdf.City ,

    bdf.[State],

    bdf.ZIP,

    bdf.[ZIP+4],

    bdf.Phone,

    bdf.Contact,

    bdf.Gender,

    bdf.Title,

    bdf.[SIC Code],

    bdf.[SIC Description],

    bdf.[NAICS Code],

    bdf.[NAICS Description] ,

    bdf.[Location Type],

    bdf.[Year of 1st Appearance],

    bdf.[Number of Local Employees],

    bdf.[Total Number of Employees],

    bdf.[Local Annual Sales],

    bdf.[Total Annual Sales],

    bdf.[Latitude],

    bdf.[Longitude] ,

    bdf.[Fax] ,

    bdf.CityID ,

    bdf.StateID ,

    bdf.CountryID ,

    bdf.Country,

    subnaics.SubcategoryID,

    subnaics.SubCategoryName,

    subnaics.CategoryId,

    subnaics.CategoryName,

    subnaics.IndustryID,

    subnaics.IndustryName

    from

    SubCategoryNAICSCode subnaics

    inner

    hash

    join

    BusinessData bdf with (index(IX_BusinessDataState)) on

    bdf.[NAICS Code] = subnaics.NAICSCode

    where

    bdf.[State] like 't%';

    Actual execution plans for both a% and t% queries would be appreciated, thanks.

    Paul White
    SQLPerformance.com
    SQLkiwi blog
    @SQL_Kiwi

  • October 4, 2010 at 4:51 am

    #1230898

    Thanks I will take a closer look in my Indexes

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply