Index Question

Question

Index Question

abcim

SSC Eights!

Points: 879
More actions
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%'??

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

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

grahamc SSCertifiable Points: 6762 More actions · Answer 1

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

abcim SSC Eights! Points: 879 More actions · Answer 2

For detail of tables see this thread

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

grahamc SSCertifiable Points: 6762 More actions · Answer 3

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

abcim SSC Eights! Points: 879 More actions · Answer 4

abcim

SSC Eights!

Points: 879

October 1, 2010 at 6:01 am

#1230133

But the sql is different

Grant Fritchey SSC Guru Points: 398886 More actions · Answer 5

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 Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Paul White SSC Guru Points: 150467 More actions · Answer 6

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

abcim SSC Eights! Points: 879 More actions · Answer 7

abcim

SSC Eights!

Points: 879

October 4, 2010 at 4:51 am

#1230898

Thanks I will take a closer look in my Indexes