[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 78
  • Last Modified:

optimize query

Hello,

How can I optimize this query :
Hello,

How can I optimize this query :
SELECT F.ExpDate, IF.ReferenceId, C.CategoryName, IF.Label, IF.Quantity, IF.Amo*ABS(IF.Quantity) MHT, IF.TotalFactItemAmount*ABS(IF.Quantity) MontantTTC, ClientId
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
WHERE IF.FactItemTypeRefId = 4
AND IF.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 (IF.Label LIKE '%DVD%'
      OR IF.Label LIKE '%HS%'
      OR IF.Label LIKE '%blu%ray%')

Without creating the missing index :

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

go

Thanks

Regards
0
bibi92
Asked:
bibi92
  • 3
  • 3
  • 2
1 Solution
 
ste5anSenior DeveloperCommented:
This is a non-sargeable expression:

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

Open 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

Open 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');

Open 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.
0
 
PortletPaulCommented:
Just a small observation: don't use "IF" as an alias it is a reserved word

Where is the alias "F" declared?
How is [dbo].[T_Facts] related to this query?
Is the query using one or more views?
Why can't you create an index?
0
 
bibi92Author Commented:
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%')
0
Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

 
bibi92Author Commented:
•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
0
 
PortletPaulCommented:
SARGABLE
a predicate is "sargable" if a dbms can use index(es) to help with execution efficiency

When searching for strings with wildcards on both sides it is not possible to use indexes; therefore it is not sargable

Those double sided wildcards cause a full scan of the text fields.

so, the following is a cause of slowness:

AND (IF.Label LIKE '%DVD%'
OR IF.Label LIKE '%HS%'
OR IF.Label LIKE '%blu%ray%')
0
 
bibi92Author Commented:
so how can I modify :
AND (IF.Label LIKE '%DVD%'
OR IF.Label LIKE '%HS%'
OR IF.Label LIKE '%blu%ray%')
0
 
ste5anSenior DeveloperCommented:
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?
0
 
PortletPaulCommented:
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.
0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 3
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now