Fuzzy Search Performance using Full-Text indexes

[matma24]

Hello!

Unfortunately I've ran into some issues with performance of the fuzzy search. I'm running a fuzzy search on a table with 9-10m records, table is not partitioned, full-text indexes are present (TEXT data type so they are created by default) and fuzzy search index on is turned.

create column table company_data
(
id nvarchar(50),
company_name nvarchar(1000),
company_name_without_legal_type TEXT FUZZY SEARCH INDEX ON,
company_legal_type nvarchar(50) FUZZY SEARCH INDEX ON
)

select score,
	id,
	company_name
from (
	select top 10 to_decimal(max(score()),3,2) as score,
		cd.id,
		cd.company_name,
		row_number() over (partition by cd.id order by max(score()) desc) as row_num
	from company_data cd
	where contains(cd.company_name_without_legal_type, 'SAP', fuzzy(0.8, 'andSymmetric=on,andThreshold=0.75,bestMatchingTokenWeight=0.5,textSearch=compare,minTextScore=0.7'))
		and contains(cd.company_legal_type, 'SE', fuzzy(1, 'similarCalculationMode=compare,emptyScore=1'))
	group by cd.id,
		cd.company_name
	order by score desc, cd.id desc
) 
where row_num = 1 and score >= 0.85;

This is an example call.

Data in company_data contains all historical data of every company so row_number() is needed to get 10best unique companies that matched.

After this data is retrieved I'm running some more queries but only this part is causing slow runtime, for some calls I get runtime of over 700ms which is quite large and in my opinion should not be as that slow. Of course I know andSymmetric and andThreshold does influence performance a lot but not making it few times slower than without those two parameters.

On the 1000 companies that I've tested - I've got mean of 170ms, 90% of requests are below 430ms and max_time of over 800ms.

Would be really grateful for all hints and ideas how I can speed it up (ideal speed is 90% of requests below 250ms).

Best regards,

Mateusz

[lbreddemann]

Good job on having a relatively clear idea about the expected runtime - even though I don't know where the number 250ms comes from.

As with all performance issues, the question is "what is the time spent doing with?". So running a PlanViz and checking the thread-states for this statement might be helpful here.

Beyond that, I wonder if it would make a substantial difference if you replace the identical match for "SE" by a simple equal predicate (and contains(cd.company_legal_type,'SE', fuzzy(1,'similarCalculationMode=compare,emptyScore=1')))

[matma24]

Firstly, thank you very much for the response!

The time comes simply from project requirements. This procedure that I'm talking about will be called from rest service and response is expected in less than 300ms for 90% of the cases.

After analysing PlanViz generated by HANA Studio it seems that Basic Predicate (contains(cd.company_name_without_legal_form, 'SAP', fuzzy(0.8, 'andSymmetric=true,andThreshold=0.75,bestMatchingTokenWeight=0.5,textSearch=compare,minTextScore=0.7'))) is taking over most of the time for a procedure. There is this part from PlanViz for a call that took 230ms in total (please keep in mind that there are also another operations going on there)

What is quite weird is the fact that PlanViz shows 39records here but looking a bit further it shows :

Which leads to 39 (7,178??)

[lbreddemann]

Is that PlanViz based on the same query you've posted? The predicates for extracted_legal_type seem different.

I'm not sure what you think is weird with the 39 rows - that's the number of rows that have actually been returned from the POP where the arrows originate. The number in the brackets is what was expected before the query was executed.

Since the majority of the time seems to be spent in the bottom-most basic predicate, it may be a good idea to look into more details on that (i.e. check the details information). Also check, whether the runtime for this POP is usually around this number or if there are large variations.

[matma24]

This extracted_legal_type seems to be fine in my opinion. GMBH is the value i passed by but since there is also emptyScore=1 in my fuzzyOptions that get ran in 3nodes.

Now i understand - I was not sure what the number in the bracket means.

Yes there are larger ones - it look like 60-70% of whole time is spent in bottom-most predicate every time. As far as I've tested some cases it looks like that large runtime is coming from 'andSymmetric'. If I ran a query without it the runtime is about 4-5x faster but the results are worse In my case, so what I would like to do is to keep it but simply improve the runtime.

Edit:

To be fair documentation https://help.sap.com/viewer/691cb949c1034198800afde3e5be6570/2.0.05/en-US/3f622a55abcf42d79b6697988c... clearly states "The parameter influence the performance.". The question is if there is anything I can do to improve it.

[lbreddemann]

What the andSymmetric option does is basically a second type of matching in the same predicate. Not only are all records found for which the search term is at least a partial match, but also all those records that would be partial matches to the search term. Thus the name "symmetric".

In short, this is a lot more work to be done.

I don't see how you can improve on this operations performance (apart from using a faster processor/memory combination). Maybe you can improve other parts of the query to get closer to your performance goal.

[matma24]

That's what I thought as well but thank you very much for your help!

I'll wait one more day so maybe anyone will get any ideas, if not then I'll accept your comment as an answer.

Thanks again!

[matma24]

What is quite weird is 'andThreshold' parameter. Doesn't matter what value I put there PlanViz show exactly the same number of records that does match, even with value 0.7 vs 0.9. According to documentation https://help.sap.com/viewer/691cb949c1034198800afde3e5be6570/2.0.01/en-US/3f622a55abcf42d79b6697988c... it is possible to use them both together and andThreshold specifies the % of tokens that have to match so getting the same number of results for 0.7 and 0.9 seems at least odd.

[former_member600260]

Hello matma24,

I'm facing also performance issues when performing the FUZZY search, did you managed to get any solution?

BR,

André

[matma24]

Please post your code and some details about table DDL, data volume etc. Maybe I will be able to help.

[former_member600260]

Hey Mark,

-> How I created the FULLTEXT INDEX: CREATE FULLTEXT INDEX FZSR_FULL_NAME on "SAPDAT"."/SIE/FL_2BP_FZSR" ( "FULL_NAME" ) FUZZY SEARCH INDEX ON FAST PREPROCESS ON TEXT MINING OFF TEXT ANALYSIS OFF SYNC;

-> Table /SIE/FL_2BP_FZSR has 4.6M records

-> Field FULL_NAME has 160 chars

->SQL Sample:

SELECT DISTINCT SCORE() AS SCORE, MANDT, PARTNER, COUNTRY, NATION, BU_GROUP, BPEXT, ORGID, Z_ORGID_A, POST_CODE1, POST_CODE2, REGION, PO_BOX, FULL_NAME, LEGAL_FORM, NAME_MINUS_LFORM, FULL_STREET, CITY1, PO_BOX_LOC, TYPE, DUNS, XBLCK FROM "SAPDAT"."/SIE/FL_2BP_FZSR"

WHERE NATION = 'I'

AND COUNTRY = 'US'

AND( CONTAINS( FULL_NAME ,' MOHAMMADI ' ,FUZZY( 0.001 ,' interScriptMatching=on, similarCalculationMode=compare, textSearch=compare, spellCheckFactor=1.0, andThreshold=0.00, returnAll=on, andSymmetric=on '),WEIGHT( 1.00 ) ) )

AND SCORE() > '0.10 '

and MANDT = '100'

ORDER BY SCORE DESC , PARTNER ASC LIMIT 100

Normaly this query takes 4 seconds. That's too much for our mass processing processes.

Any tip to speed up this query?

Thank you.

BR,

André

[jerryjanda]

Hi, asousa84

Please stop posting new questions as answers to an existing question. The correct approach would have been to leave a comment or create a new question.

Since Mark has chosen to help you, we're going to leave this thread, but I ask that you post your responses as comments to your original "answer," not as new answers, so as not to confuse other members who might be searching for a solution to this problem (forcing them to scroll through a thread with a bunch of answers that aren't answers at all).

Kind regards,

--Jerry

Moderation Lead