October 4, 2018 at 2:52 am
Below query taking fourteen seconds to give result.Is there any way to optimize this query.
SELECT DISTINCT L.F_T_CODE AS F_T_CODE, PHT.F_PHRAS AS F_PHRAS FROM PHRASE_LINK L INNER JOIN PHRAS_TR PHT ON L.F_P_ID = PHT.F_P_ID WHERE PHT.F_PHRAS LIKE '%a%' AND PHT.F_LAN = 'EN' AND L.F_D_CODE != L.F_T_CODE ORDER BY PHT.F_PHRASOFFSET 100000 ROWS FETCH NEXT 10 ROWS ONLY;
index is available for PHT.F_P_ID BUT NOT AVAILABLE FOR L.F_P_ID on Join condition index is available for PHT.F_PHRAS column in like operator index is available for L.F_D_CODE AND L.F_T_Code columns
October 4, 2018 at 3:28 am
Almost certainly. Please post the actual (not estimated) execution plan. And when posting code, try to look as if you care about it - if you don't then it's likely that others won't. Here's a way to make it much easier to read:
SELECT DISTINCT
L.F_T_CODE AS F_T_CODE
, PHT.F_PHRAS AS F_PHRAS
FROM
PHRASE_LINK L
INNER JOIN
PHRAS_TR PHT
ON
L.F_P_ID = PHT.F_P_ID
WHERE
PHT.F_PHRAS LIKE '%a%'
AND
PHT.F_LAN = 'EN'
AND
L.F_D_CODE != L.F_T_CODE
ORDER BY
PHT.F_PHRAS
OFFSET
100000 ROWS FETCH NEXT 10 ROWS ONLY;
John
October 4, 2018 at 4:05 am
Thank you.How to attach file?.I want to attach execution plan
October 4, 2018 at 6:43 am
jkramprakash - Thursday, October 4, 2018 2:52 AMBelow query taking fourteen seconds to give result.Is there any way to optimize this query.
SELECT DISTINCT L.F_T_CODE AS F_T_CODE, PHT.F_PHRAS AS F_PHRAS FROM PHRASE_LINK L INNER JOIN PHRAS_TR PHT ON L.F_P_ID = PHT.F_P_ID WHERE PHT.F_PHRAS LIKE '%a%' AND PHT.F_LAN = 'EN' AND L.F_D_CODE != L.F_T_CODE ORDER BY PHT.F_PHRASOFFSET 100000 ROWS FETCH NEXT 10 ROWS ONLY;index is available for PHT.F_P_ID BUT NOT AVAILABLE FOR L.F_P_ID on Join condition index is available for PHT.F_PHRAS column in like operator index is available for L.F_D_CODE AND L.F_T_Code columns
I'm not convinced you can improve the query much without changing it's functionality. The problem here is most likely the LIKE '%a%' which is not "SARGABLE". That kind of expression CAN NOT use an index. It's just not possible. Indexes don't provide a means to search a string for some sequence of characters that is somewhere in the middle of it. You can use an index if you want a column value to start with a given character sequence, but not when you are searching either the middle or end of the string. Nothing you do is going to solve that problem.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply