Timeline for How to improve performance of this Scalar Function for search with ranking by word
Current License: CC BY-SA 4.0
15 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Jan 24, 2023 at 15:01 | history | bounty ended | CommunityBot | ||
| Jan 17, 2023 at 19:00 | comment | added | ttugates | Let us continue this discussion in chat. | |
| Jan 17, 2023 at 18:58 | comment | added | Martin Smith |
So for all the CHARINDEX calls both the expression being searched for and the string being searched have been left padded out with a single space and so this should work out the same as the LIKE b.[value] + '%' logic after splitting on space.
|
|
| Jan 17, 2023 at 18:33 | comment | added | ttugates | You are absolutely correct. I assumed I didn't state the requirement. So many thanks | |
| Jan 17, 2023 at 18:30 | comment | added | Martin Smith |
@ttugates - the query in my answer works fine., from your comment above it looks like you dropped the CONCAT(' ', when rewriting it., This was the purpose of that
|
|
| Jan 17, 2023 at 1:05 | history | edited | Martin Smith | CC BY-SA 4.0 |
added 132 characters in body
|
| Jan 17, 2023 at 0:54 | history | edited | Martin Smith | CC BY-SA 4.0 |
added 685 characters in body
|
| Jan 16, 2023 at 21:44 | comment | added | ttugates |
Thats exactly what I did. Replace QSplit is what I meant by saying replacing the CTE
|
|
| Jan 16, 2023 at 18:32 | comment | added | Martin Smith |
@ttugates - I just noticed what you said about SELECT 'car' AS [0] - in that case you don't need QSplit at all. You can just start the query at SELECT m.*, Rank and instead of CHARINDEX([1],SpacePadded) do CHARINDEX(' ' + @Param1,SpacePadded) etc - and pass the parameter values as upper cased individual values
|
|
| Jan 16, 2023 at 18:11 | comment | added | Martin Smith |
@ttugates - in that case you can get rid of UPPER(Data) and just use Data as the UPPER won't do anything there and will take some CPU time
|
|
| Jan 16, 2023 at 18:09 | comment | added | ttugates |
Thank you for all your time, immensly! Some additional detail I didn't include. The Data column will only be all Upper, numbers and these '(', ')', '|', '-'. I am string building in C# and replacing the CTE with for ex: (SELECT 'car' AS [0], 'oil' AS [1]) and this allows me to also string build the correct number of IIF(CHARINDEX([1],SpacePadded) > 0,1,0)
|
|
| Jan 16, 2023 at 18:05 | history | edited | Martin Smith | CC BY-SA 4.0 |
added 196 characters in body
|
| Jan 16, 2023 at 18:00 | history | edited | Martin Smith | CC BY-SA 4.0 |
added 196 characters in body
|
| Jan 16, 2023 at 17:43 | vote | accept | ttugates | ||
| Jan 16, 2023 at 17:42 | history | answered | Martin Smith | CC BY-SA 4.0 |