May 7, 2013 at 11:07 pm
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..
May 7, 2013 at 11:32 pm
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
May 7, 2013 at 11:55 pm
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
May 8, 2013 at 8:16 am
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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply