SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


filtere index benefit


filtere index benefit

Author
Message
IT researcher
 IT researcher
Mr or Mrs. 500
Mr or Mrs. 500 (586 reputation)Mr or Mrs. 500 (586 reputation)Mr or Mrs. 500 (586 reputation)Mr or Mrs. 500 (586 reputation)Mr or Mrs. 500 (586 reputation)Mr or Mrs. 500 (586 reputation)Mr or Mrs. 500 (586 reputation)Mr or Mrs. 500 (586 reputation)

Group: General Forum Members
Points: 586 Visits: 1798
i need to create a query on a table where the condition is such that we select the rows where the value of a particular column (IS NULL OR =' ').
please help me with the idea if the filtered index concept can be of any benefit here ...i tried using it but i got no improvement in the execution plan..
GilaMonster
GilaMonster
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87005 Visits: 45267
It might be. Can you post the query, the table definition and the index that you tried?

Why must it be a filtered index?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


IT researcher
 IT researcher
Mr or Mrs. 500
Mr or Mrs. 500 (586 reputation)Mr or Mrs. 500 (586 reputation)Mr or Mrs. 500 (586 reputation)Mr or Mrs. 500 (586 reputation)Mr or Mrs. 500 (586 reputation)Mr or Mrs. 500 (586 reputation)Mr or Mrs. 500 (586 reputation)Mr or Mrs. 500 (586 reputation)

Group: General Forum Members
Points: 586 Visits: 1798
this is the query :
SELECT [DesignChanges].* FROM [Checklists].dbo.[DesignChanges]
WHERE
(
Charindex('jyo',[Checklists].dbo.[DesignChanges].[AllotedTo])>0 AND
(
([Checklists].dbo.[DesignChanges].[WorkImplemented] Is Null OR [Checklists].dbo.[DesignChanges].[WorkImplemented] = '' )
OR
(
([Checklists].dbo.[DesignChanges].[WorkImplemented] Is Not Null AND [Checklists].dbo.[DesignChanges].[WorkImplemented]<>'')
AND([Checklists].dbo.[DesignChanges].[DocumentReqd] <> 'NA/' or [Checklists].dbo.[DesignChanges].[DocumentReqd] is null )
AND([Checklists].dbo.[DesignChanges].[DocumentDoneBy] Is Null OR [Checklists].dbo.[DesignChanges].[DocumentDoneBy] = '')
)
)
)
AND ([Checklists].dbo.[DesignChanges].[Pending] Is Null OR [Checklists].dbo.[DesignChanges].[Pending] = '' )
AND Charindex('Queries',[Checklists].dbo.[DesignChanges].[TypeOfWork])>0
AND (Charindex('~!',[Checklists].dbo.[DesignChanges].[F6Row])=0 or [Checklists].dbo.[DesignChanges].[F6Row] Is NULL )
the table is:
Sl_no int

Date date

Operation nvarchar(MAX)

AllotedTo varchar(50)

UserApproval xml

[By] varchar(50)

DbName varchar(255)

TableName xml

Remarks varchar(5000)

WorkImportance int

F6Row varchar(200)

F6Givenby nvarchar(MAX)

F8Row varchar(1000)

F8Givenby varchar(1000)

ListCol nvarchar(255)

pro_remarks nvarchar(MAX)

WorkImplemented varchar(50)

WorkTime xml

DetailedPlan varchar(255)

UserChecked xml

TestedBy xml

Priority tinyint

TasklistId varchar(50)

Discussed xml

Permitted_By varchar(50)

PrelimPlan varchar(255)

[User] varchar(255)

ApprovalReady varchar(50)

Sup_Approval xml

ManagerApproval xml

Pending varchar(50)

IncReady varchar(50)

IncReadyDate date

IncApproval varchar(50)

[Inc By] varchar(50)

[Inc Date] date

DocumentReqd varchar(255)

AfterInc_Check xml

TBFill_To varchar(255)

Documents varchar(255)

DocumentDoneBy varchar(50)

DocumentIncBy varchar(50)

SupChecked xml

ManagerChecked xml

JobTitle xml

DoneBy_PreTest xml

TestRecId varchar(50)

TypeOfWork varchar(50)

Bgd_test varchar(MAX)

bgd_Ph varchar(MAX)
i have use the filtered index
:
CREATE NONCLUSTERED INDEX [Checklists]
ON DesignChanges(Pending)
WHERE (Pending IS NULL AND Pending =' ')
GO
GilaMonster
GilaMonster
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87005 Visits: 45267
The index isn't covering and doesn't even support most of the where clause. Unless a very, very, very, very small portion of the table qualifies for that filtered index, it's extremely likely that SQL will pick a different index, one that supports more of the where clause or one that covers the query, even if it means scanning the table.

Plus the predicate in the query and the predicate in the index don't match.

Query:
AND ([Checklists].dbo.[DesignChanges].[Pending] Is Null OR [Checklists].dbo.[DesignChanges].[Pending] = '' )


Index
CREATE NONCLUSTERED INDEX [Checklists]
ON DesignChanges(Pending)
WHERE (Pending IS NULL AND Pending =' ')


In short, the chances of SQL using a single column nonclustered index (that it has to scan, there's no seekable predicate) followed by key lookups for all the rows, followed by a secondary filter for that query is pretty much zero.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search