Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

filtere index benefit Expand / Collapse
Author
Message
Posted Tuesday, May 07, 2013 11:07 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Saturday, February 22, 2014 6:35 AM
Points: 257, Visits: 1,678
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..
Post #1450400
Posted Tuesday, May 07, 2013 11:32 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 12:20 PM
Points: 41,529, Visits: 34,445
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 2008, MVP
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

Post #1450401
Posted Tuesday, May 07, 2013 11:55 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Saturday, February 22, 2014 6:35 AM
Points: 257, Visits: 1,678
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
Post #1450410
Posted Wednesday, May 08, 2013 8:16 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 12:20 PM
Points: 41,529, Visits: 34,445
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 2008, MVP
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

Post #1450595
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse