Discussion between ttugates and Martin Smith

Feeds

19:00

A: How to improve performance of this Scalar Function for search with ranking by word

For the following example data INSERT INTO MyTable VALUES ('A CAR'), ('A BIKE'), ('CAR WASH'), ('CAR BIKE RACK'), ('A HOUSE'); INSERT INTO MyTable SELECT TOP 75000 REPLACE(NEWID(), '-'...

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)

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

@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

ttugates

Thats exactly what I did. Replace QSplit is what I meant by saying replacing the CTE

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

ttugates

You are absolutely correct. I assumed I didn't state the requirement. So many thanks

Martin Smith

19:00

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.

ttugates

Hi Martin. I have never used chat before. I am working on solving the issue. It appears to be caused by the code where I replaced QSplit. I will share with you what I learned, what I did wrong when I solve.

Martin Smith

Have you got it working now?

ttugates

Not in the sense that I string build the QSplit. I don't mind working through.

Martin Smith

yeah feel free to paste some code and I'll take a look

ttugates

I assume my issue is I have not used "VALUES" before other than in an insert. Thus my CROSS JOIN Line is the culprit in the following.

dbfiddle.uk/NFyaUDKN

Note the first word I passed was "AR" and it matched Data A CAR

Martin Smith

19:13

dbfiddle.uk/LMa8MIMO

This is why in the answer it had " ' ' + [1]"

etc.

ttugates

Got it

Wow, thank you for all your help

Martin Smith

Also your DB Fiddle has lost the COLLATE Latin1_General_100_BIN2 - this made a big improvement speedup for me

So dbfiddle.uk/XlusIaOD

using the binary collation sped things up from 500ms to 100ms on my dummy data

ttugates

19:37

Getting errors sending chat. Not sure you will see. Researching updating Collation on Table column now.. Otherwise in my DB I get no results. Its presently SQL_Latin1_General_CP1_CI_AS

Martin Smith

You will get results if you add the UPPER back in

ttugates

You are correct :)

Martin Smith

If the data can be stored in Upper case in the first place then you can remove the UPPER there. And similarly if the parameter values being searched for can be upper cased first then you can remove those Upper too. The binary collate clause is quicker because it just looks at a match at the binary level so won't do any case insensitive comparisons etc

ttugates

I did gather that, and UPPER can be guranteed.

Testing if I can ALTER TABLE dbo._SearchInventory
ALTER COLUMN ComputedSearchable VARCHAR(50) COLLATE Latin1_General_100_BIN2 on a large table at the moment

Martin Smith

It doesn't need to be done to the column it can just be specified in the query

ttugates

19:46

Oh, awesomeness

Thats a 37% speed increase for my data.

Cool, thanks again for everything

last day (15 days later) »

all rooms