Optimize query that is using like statements
-
I currently have a large query which is taking over an hour to run, and am attempting to optimize. Unfortunately, I do not have permissions to see the execution plan, but believe I have narrowed the issue down to several like statements that are being used (shown below).
Select 1.A, 1.B, 1.Cfrom Table1 1
left join Table2 2 on 2.id = 1.id
left join Table3 3 on 2.id = 3.id and 3.seq = '1'Where 2.date between 201501 and 201707
AND (3.Desc Like '%red%'
OR 3.Desc like '%blue%'
OR 3.Desc like '%Teal%'
OR 3.Desc like '%yellow black%'
OR 3.Desc like '%brown%'
OR 1.Class = 'english')Is it possible to move the like statements to a join to make it more efficient?
I attempted to use a temporary table as shown below but it did not work. I am thinking it was because I added the 1.Class to the inner join. I am looking to return items from Table 1 that have a Class equal to English or a Desc from Table 3 like red, blue, Teal, yellow black, or brown.
CREATE TABLE #HH (Desc VARCHAR(60),
);insert into #HH
select Desc.Desc
from Table3 Desc
Where Desc.Desc Like '%red%'
OR Desc.Desc like '%blue%'
OR Desc.Desc like '%Teal%'
OR Desc.Desc like '%yellow black%'
OR Desc.Desc like '%brown%'group by Desc.Desc
Select 1.A, 1.B, 1.C
from Table1 1
left join Table2 2 on 2.id = 1.id
left join Table3 3 on 2.id = 3.id and 3.seq = '1'
--------Added----
inner join on #HH HH on 3.Desc = HH.Desc or 1.Class = 'english'Where 2.date between 201501 and 201707
] -
It's likely to be a table scan caused by the non-sargable LIKE operators that are causing you pain, by forcing a scan of the whole table. I imagine that if you don't even have access to see the execution plan, creating a persisted column or an indexed view is out of the question?
John
-
I am not sure as I have never worked with persisted column or indexed views before. Guess I would need to determine how to use each and attempt to see if it makes an improvement.
-
I meant do you even have permission to create one? If you do, you'd do it something like this:
ALTER TABLE Table3
ADD DescColour AS (
CASE
WHEN Desc LIKE '%blue%' THEN 'blue'
WHEN Desc LIKE '%Teal%' THEN 'Teal'
WHEN Desc LIKE '%yellow black%' THEN 'yellow black'
WHEN Desc LIKE '%brown%' THEN 'brown'
ELSE ''
END
)You can then index the column and use it in your view. You'd have to test to see whether it actually gets used by the query optimizer - with such a low number of values, it's not very selective.
The other alternative is to write your query as an indexed view, so that the whole thing is persisted in the database and doesn't have to be calculated on the fly. Of course, if you only run the query once a week, for example, it's probably not worth the overheads associated with maintaining it.
John
-
It does appear I do not have permissions to create a view. So your saying the only other option is to write the query as an indexed view?
-
LIKEs are tough on the optimizer, as are all those ORs.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service -
That is why I was hoping that I could put the values from the LIKEs in a temp table, and then incorporate it into the join as the values versus using the LIKEs. I am open to any thoughts.
-
Since using actual words, what about Full Text Search?
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service - ollyjolly - Monday, August 21, 2017 9:21 AM
Yes. I answered this earlier today . Here's a similar but more simple but less dynamic way to tackle this:
First some sample data:
CREATE TABLE dbo.sometable
(
someId int identity primary key clustered,
col1 varchar(100) not null
);INSERT dbo.sometable(col1)
VALUES ('xxx blue'),('ggg red gg'),('xxx'),('fff pink h'),('I''m yellow!!!');Next for our indexed view:
CREATE VIEW dbo.vwSomeTable
WITH SCHEMABINDING AS
SELECT someId, col1
FROM dbo.sometable
WHERE col1 LIKE '%blue%'
OR col1 LIKE '%pink%';
GOCREATE UNIQUE CLUSTERED INDEX uq_cl_vwSomeTable ON dbo.vwSomeTable(someId);
Now, if you run the query below (just as an example) you'll notice that the optimizer is able to perform an index seek against the index on my view even without me referencing it in my query.
SELECT someId, col1
FROM dbo.sometable
WHERE
(
col1 LIKE '%blue%' OR
col1 LIKE '%pink%'
)
AND someId < 4;
The execution plan:
This is one of those very cool but rarely mentioned benefits of indexed views.
You could one or more add nonclustered indexes to your view to speed things up even further. As is the case with all Indexes, however, Indexed views do not come without the usual overhead so you'll have to test for yourself to see if the improved performance is worth the added overhead. There's also no guarantee that the optimizer will always chose your indexed view which is why you can consider referencing it directly along with a NOEXPAND query hint. I generally don't recommend query hints but will say that I've had great success with NOEXPAND (after plenty of testing 😉) On the topic of NOEXPAND - note this great article by Paul White: Another Reason to Use NOEXPAND hints in Enterprise Edition
"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."-- Itzik Ben-Gan 2001
- ollyjolly - Monday, August 21, 2017 10:04 AM
No, an indexed view is a view. If you don't have DDL permissions on your database, you won't be able to create the index view or add the persisted column. If you can prove that it works, perhaps you could persuade the owner of the database that it's worth doing?
John
- John Mitchell-245523 - Tuesday, August 22, 2017 2:04 AM
I missed this and agree that there's a case to get permission to create a view as that would be the best solution imho. That said, a computed column could work too.
CREATE TABLE dbo.sometable
(
someId int identity primary key clustered,
col1 varchar(100) not null
);INSERT dbo.sometable(col1)
VALUES ('xxx blue'),('ggg red gg'),('xxx'),('fff pink h'),('I''m yellow black!!!');ALTER TABLE dbo.sometable
ADD hasThatColor AS
(
cast(sign(
charindex('red', col1)+
charindex('blue', col1)+
charindex('teal', col1)+
charindex('yellow black', col1)+
charindex('brown', col1)) as bit)
) persisted;CREATE NONCLUSTERED INDEX nc_sometable_hasThatColor on dbo.sometable(hasThatColor)
INCLUDE (someId, col1);Using my sample data above, the following query will get you a nonclustered covering index seek which is what I like to see.
SELECT *
FROM dbo.sometable
WHERE hasThatColor = 1"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."-- Itzik Ben-Gan 2001
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply