Timeline for How to improve performance of this Scalar Function for search with ranking by word
Current License: CC BY-SA 4.0
26 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| S Jan 24, 2023 at 15:01 | history | bounty ended | CommunityBot | ||
| S Jan 24, 2023 at 15:01 | history | notice removed | CommunityBot | ||
| Jan 22, 2023 at 21:52 | answer | added | stecyk | timeline score: 0 | |
| Jan 22, 2023 at 20:42 | answer | added | stecyk | timeline score: 1 | |
| Jan 16, 2023 at 21:00 | history | tweeted | twitter.com/StackDBAs/status/1615091526056509440 | ||
| Jan 16, 2023 at 17:44 | comment | added | Martin Smith | But as you have bountied it I wouldn't accept an answer yet or award the bounty and see what other people come up with! | |
| Jan 16, 2023 at 17:43 | vote | accept | ttugates | ||
| Jan 16, 2023 at 17:43 | comment | added | Martin Smith | Hopefully the revised version in the answer chips away at that timing a bit more | |
| Jan 16, 2023 at 17:42 | answer | added | Martin Smith | timeline score: 4 | |
| Jan 16, 2023 at 16:33 | comment | added | ttugates | @MartinSmith - That is indeed much faster than I have been able to achieve on my real world data! That gets me sub 1 sec on a slow Azure Db. Please make an answer so I can accept. +1 for this being a direct query, no function needed. | |
| Jan 16, 2023 at 16:18 | comment | added | Martin Smith |
A possible different approach would be dbfiddle.uk/Xj8aRxTz - this avoids running STRING_SPLIT on @Query multiple times and it may be quicker just to do multiple searches on Data rather than split that and do a count distinct
|
|
| Jan 16, 2023 at 15:46 | history | edited | ttugates | CC BY-SA 4.0 |
Additional behavior definition
|
| Jan 16, 2023 at 15:43 | comment | added | ttugates | Yes, I would say 4. Else there would be a cartesian explosion problem. It can be assumed the server can parse the query into optional word params | |
| Jan 16, 2023 at 15:38 | comment | added | Martin Smith | Can a maximum number of query elements be assumed? If so what is it? | |
| Jan 16, 2023 at 13:55 | history | edited | ttugates | CC BY-SA 4.0 |
Explain search definition
|
| S Jan 16, 2023 at 13:47 | history | bounty started | ttugates | ||
| S Jan 16, 2023 at 13:47 | history | notice added | ttugates | Draw attention | |
| Jan 13, 2023 at 1:13 | comment | added | J.D. | Also separate thought. 75,000 rows is a tiny amount of data. At that point I'd return it all to the consuming app, and let the app handle paging (since you'll have the entirety of the results ranked as a static dataset on the app side). Most UI controls these days offer paging out of the box. | |
| Jan 12, 2023 at 23:52 | comment | added | J.D. | Sure, it's possible to still implement paging and a deterministic sort, despite unioning multiple datasets together. As long as your sort and ranking expressions for the source tables are deterministic, and the ranking function for the final page of data on top of that is deterministic, the results will always be deterministic. | |
| Jan 12, 2023 at 22:52 | comment | added | ttugates | Union-ing the results of queries is problematic because of paging. Consider the case of, the user is looking at a HTML table and they go to the next page.. I now execute a query that needs a deterministic ordered Union where I take the "Next 15". | |
| Jan 12, 2023 at 22:43 | comment | added | J.D. |
Ok that makes more sense now. (Having worked in manufacturing, I've never heard of anyone with part numbers that are named with words and spaces.) But your goal is to just offer a global search to the end user, essentially. Having a denormalized search table probably complicates things and hurts performance more than it helps you, to be honest. You'd likely be better off unioning the results of custom queries for each table of data, with a search predicate tailored to that table. E.g. when searching on part numbers, you don't need to worry about full text search, since they're single words.
|
|
| Jan 12, 2023 at 21:55 | history | edited | ttugates | CC BY-SA 4.0 |
Alternative attempt with measured perf
|
| Jan 12, 2023 at 21:22 | comment | added | ttugates | A cross cutting, alternate example of business case with more detail: Client lists a table of Invoices. There is a single search input above it should accept Customer Name or Vehicle Description in addition to the Invoice Number which narrows the results of Invoices in the Table, which has paging(Skip / Take) and thus ordering.. I left all this out to keep question focused on the piece I need assistance with. | |
| Jan 12, 2023 at 21:11 | comment | added | ttugates | Business case is we want the users to be able to input manufacturer name and or type and or part number and or description in a single unstructured intput string and return a ranked list of Items that match. I have a search table that is populated with Mfr Names, Item Types, Part Number and it has a computed column that stores the distinct words. | |
| Jan 12, 2023 at 21:02 | comment | added | J.D. | "I have read that Scalar Valued Functions are slow(er) / Single threaded" - Yes, and multi-statement table valued functions (such as yours) also force serialized zones in the execution plans they're a part of. Not as bad as (non-inlinable) scalar functions but that means your function itself will always be single threaded. Could you provide more context on your use case from a business logic perspective? Your part numbers have a nomenclature that involves space delimited words? | |
| Jan 12, 2023 at 20:14 | history | asked | ttugates | CC BY-SA 4.0 |