Query Execution Plan

Question

Query Execution Plan

Maverick100877

SSCertifiable

Points: 5240
More actions
June 28, 2006 at 6:44 am

#60825

Hi All

I have a table Calllist which has customer details.Now this table is queried on the basis of saledates,dispotypes,phonenumber and one more field I3_rowid.
Now in this table I have I3_rowid as Unique Clsuetered index
and nonclustered index on the other 3 fields.
Now when I'm querying the table it is showing this execution plan

|--Bookmark Lookup(BOOKMARK[Bmk1000]), OBJECT[AACleansing].[dbo].[calllist]) WITH PREFETCH)
       |--Hash Match(Inner Join, HASH[Bmk1000])=([Bmk1000]), RESIDUAL[Bmk1000]=[Bmk1000]))
            |--Index Scan(OBJECT[AACleansing].[dbo].[calllist].[Ind_dispotype]), WHEREConvert([calllist].[DispoType])=Convert([@1])))
            |--Index Seek(OBJECT[AACleansing].[dbo].[calllist].[Ind_Saletime]), SEEK[calllist].[SaleTime] >= Convert([@2]) AND [calllist].[SaleTime] <= Convert([@3])) ORDERED FORWARD)

Now I'm a bit confused with this.
How do I go about interpreting this execution plan.It is still doing a Index scan in this which I think is not a good thing.
So how do I interpret this and avoid the index scan which is happening.
July 3, 2006 at 8:00 am

This was removed by the editor as SPAM

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

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

Philip Yale-193937 SSCarpal Tunnel Points: 4603 More actions · Answer 1

Arun,

Can you please post all table and index structures, together with the actual query you are running? An indication of the approx number of rows in the table(s) would be a big help, too.

skeane SSC Eights! Points: 877 More actions · Answer 2

As Philip requested, posting your DDL and SQL will enable us to give you more specific advice.

That said, what jumps out at me is the CONVERT() function shown for the DispoType scan. The index contains the unconverted values, and SQL cannot do an index seek here. It will scan the entire index, converting the values of each entry to do it's compare. Are you explicitly doing the convert in your query?

Maverick100877 SSCertifiable Points: 5240 More actions · Answer 3

CREATE TABLE [dbo].[calllist] (

[I3_RowId] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,

[PhoneNumber] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[Zone] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[Status] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[CampaignName] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[CampaignNumber] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[AAMemberShipNumber] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[Avanti_Id] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[Title] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[FName] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[MName] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[LName] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[OPhoneNumber] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[Address1] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[Address2] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[Address3] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[Address4] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[PostCode] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[AgentName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[SaleTime] [datetime] NULL ,

[SaleDate] [datetime] NULL ,

[Attempts] [int] NULL ,

[Counter] [int] NULL ,

[DispoType] [char] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[num_calls] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[Notes] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[CallId] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[Comments] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[LOADDATE] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[CompCategory] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[Renewal_Date] [datetime] NULL ,

[Lead_RefNum] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[Leadgen_Name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[MotorLead] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[HomeLead_Contents] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[HomeLead_Buildings] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[DOB] [datetime] NULL ,

[DateofBirth] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[Leadgendt] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[ITGDispo] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[Appreciation] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[NewScenario] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[Complaint] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[SpecialMotor] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[SpecialBuilding] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[SpecialContent] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[SpecialMotorRisk] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[SpecialBuildingRisk] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[SpecialContentRisk] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL

) ON [PRIMARY]

GO

ALTER TABLE [dbo].[calllist] WITH NOCHECK ADD

CONSTRAINT [PK_calllist] PRIMARY KEY CLUSTERED

(

[I3_RowId]

&nbsp WITH FILLFACTOR = 90 ON [PRIMARY]

GO

CREATE INDEX [Ind_Saletime] ON [dbo].[calllist]([SaleTime]) WITH FILLFACTOR = 90 ON [PRIMARY]

GO

CREATE INDEX [Ind_dispotype] ON [dbo].[calllist]([DispoType]) WITH FILLFACTOR = 90 ON [PRIMARY]

GO

This is the table definition.

I'm trying to query for a particular dipostye for a date.

The query which I'm giving is

select * from calllist where saletime between '2006-06-30 00:00:00.000' and '2006-06-30 23:59:59.000' and dispotype='984'

David Burrows SSC Guru Points: 65141 More actions · Answer 4

What version of SQL are you using?

On SQL2k I get the following plan

Filter(WHERE[calllist].[DispoType]='984'))

|--Bookmark Lookup(BOOKMARK[Bmk1000]), OBJECT[Customer].[dbo].[calllist]))

|--Index Seek(OBJECT[Customer].[dbo].[calllist].[Ind_Saletime]), SEEK[calllist].[SaleTime] >= Convert([@1]) AND [calllist].[SaleTime] <= Convert([@2])) ORDERED FORWARD)

One way to get a single Index Seek is to create another index on [SaleTime],[DispoType],[I3_RowId]

However this means an increase in disk usage and processing for inserts and may affect other queries

Far away is close at hand in the images of elsewhere.
Anon.

Philip Yale-193937 SSCarpal Tunnel Points: 4603 More actions · Answer 5

An indication of the number of table rows would help, but let's assume there are a lot.

Now, your PK:

Firstly, it's potentially very large (varchar(50)), and I really wouldn't recommend using something like that as a PK.

Secondly, it's clustered. This has 2 disadvantages -

1) your varchar(50) column will be added automatically to every NC index, greatly reducing their efficiency, and

2) you're wasting your clustered index on a column that's only ever going to return 1 value at a time.

I'd be sorely tempted to drop the clustered PK, and recreate it as NON-clustered. If you have the luxury of being able to add a surrogate key to the table and making that your PK instead, even better.

Next, I'd create a CLUSTERED index on (saletime) and a NON-clustered index on (dispotype). The non-clustered index will also automatically include the saletime column because this is the clustered key.

Depending on how your data is distributed, different indexes will be used.

If you have many instances of dispotypes, but only a few entries for each on a given day, I'd expect it to use a clustered index seek to get the required date, and then scan that list of records to find the required dispotypes. (You could cluster on saletime and dispotype, but I think this will introduce too much randomness into where your new inserts will go, causing page splits)

If you have only a few dispotypes, then I'd expect it to use the NON-clustered index, which would also allow it to restrict on the saletime before having to access the main table.

BTW - do you actually need to do a SELECT * , or could you be more specific about the columns you need? This will reduce your network traffic, and will also give you more scope to use a covering index if appropriate.

My suggestion of placing a CLUSTERED index on the saletime column, BTW, can have its own problems. If new saletime values being added are not necessarily greater than the existing ones, then they could cause the new rows to be inserted into the middle of the table. This will ultimately lead to page splits if you don't take corrective action. You'll need to have a lower fillfactor than you do at present (say, 70%, or even less), and will need to rebuild your clustered index on a regular basis. Just how frequently you need to rebuild it depends on how often you insert random saletimes.