optimize query

This is a non-sargeable expression:

	AND (IF.Label LIKE '%DVD%'
			OR IF.Label LIKE '%HS%'
			OR IF.Label LIKE '%blu%ray%')

Select allOpen in new window

Thus no index can be used at all for this condition.

And it seems you're using a linked server:

FROM	Fact I (NOLOCK)
	INNER JOIN T_FactItems IF (NOLOCK) ON IF.FactId = F.FactId
	INNER JOIN LNK_SQL02.[VFR].[dbo].[Oper] O WITH(NOLOCK) ON O.OperationCode = F.OperationCode
	INNER JOIN LNK_SQL02.[VFR].[dbo].[T_Categories] C WITH(NOLOCK) ON O.CategoryId = C.Id

Select allOpen in new window

Which also reduces the possibilities. I would create this view with appropriate indices on your linked server:

SELECT	C.CategoryName,
		O.OperationCode
FROM	[VFR].[dbo].[Oper] O WITH(NOLOCK) 
	INNER JOIN [VFR].[dbo].[T_Categories] C WITH(NOLOCK) ON O.CategoryId = C.Id
WHERE	C.CategoryName IN ('J / JO','SPORTS','LIB', 'BABY');

Select allOpen in new window

When this is not possible, use this query to fill a temporary table and use the temporary table instead of the linked server tables.

Just a small observation: don't use "IF" as an alias it is a reserved word

Can you explain  This is a non-sargeable expression:

AND (IF.Label LIKE '%DVD%'
OR IF.Label LIKE '%HS%'
OR IF.Label LIKE '%blu%ray%')

•Where is the alias "F" declared --> I have modified select statement


•How is [dbo].[T_Facts] related to this query? --> I have modFIied the create index


•Is the query using one or more views? ---> only table


•Why can't you create an index? ---> it's a VLDB prod

SELECT F.ExpDate, FI.ReferenceId, C.CategoryName, FI.Label, FI.Quantity, FI.Amo*ABS(FI.Quantity) MHT, FI.TotalFactItemAmount*ABS(FI.Quantity) MontantTTC, ClientId
FROM Fact F (NOLOCK)
INNER JOIN T_FactItems FI (NOLOCK)
ON FI.FactId = F.FactId
INNER JOIN LNK_SQL02.[VFR].[dbo].[Oper] O WITH(NOLOCK)
ON O.OperationCode = F.OperationCode
INNER JOIN LNK_SQL02.[VFR].[dbo].[T_Categories] C WITH(NOLOCK)
ON O.CategoryId = C.Id
WHERE FI.FactItemTypeRefId = 4
AND FI.IsAccountedFor = 1
AND F.ExpDate >='20140101'
AND F.ExpDate < '20141001'
AND F.OperationCode NOT LIKE 'RZD[_]%'
AND F.SiteId = 1
AND C.CategoryName IN ('J / JO','SPORTS','LIB', 'BABY')
AND (FI.Label LIKE '%DVD%'
      OR FI.Label LIKE '%HS%'
      OR FI.Label LIKE '%blu%ray%')

CREATE NONCLUSTERED INDEX [IX_Fact_SITEID_EXPDATE]
  ON [dbo].[Fact] ([siteid], [expeditiondate])
  include ([FactId], [ClientId], [OperationCode])

Thanks

so how can I modify :
AND (IF.Label LIKE '%DVD%'
OR IF.Label LIKE '%HS%'
OR IF.Label LIKE '%blu%ray%')

We you need this kind of search: you cannot modify it as long as you use normal indices.

You may use a fulltext index, but this requires different steps to setup.

The only I see here: "Label" sounds like "Tag". So the question is why searching for patterns at all?

Improve the data so you avoid this type of filter.

Otherwise you could try full text indexing (but that's a lot of work/effort, and a big change).

Or, live with the performance.

There just is no magic bullet to make those double sided wildcards faster.