Creating a search function with relavence

Question

User1034446946 posted

Hi

I am trying to build a search facility and have a basic idea on sort of maybe not, but how do i go about my sorting by relavence?

I know I have to give a weight to the certain search parameters?

Answer 1

User1120430333 posted

Maybe, you should look into MS SQL Server Service Broker. It was used as an async search engine service used by an ASP.NET Web forms solution used by the USAF at the time I was working the Web application. SB works with the .NET CLR so you can write SQL Server code in C# or VB,  and it works with the ADO.NET Entity Framework too.

https://technet.microsoft.com/en-us/library/ms345108(v=sql.90).aspx

Answer 2

User1034446946 posted

Thanks for the reply, but I don't see how that helps, after reading the link I didn't see anything that helps me return a relavant search.

Answer 3

User753101303 posted

Hi,

Which kind of search? The SQL Server full text search returns a rank value. Try https://docs.microsoft.com/en-us/sql/relational-databases/search/limit-search-results-with-rank?view=sql-server-2017 (can't find it right now but they explain somewhere how it is computed you have some details at the end about how it is computed).

If not (you are using LIKE ?) you are on your own to compute this kind of value. Or depending on which kind of features you are looking for it could be even a 3rd party specialized search product. We would need some more context to better help you.

Answer 4

User1034446946 posted

My theory at the moment is to have a search table away from the main entity used specifically to search, i know it will need carefull consideration to maintain, but it means i can flatter the complex crud models.

I am thinking it around an ecommerce site as thats the most complex i can think of, eg it has barcodes, product codes,  price, features, text, text can be split into brand, features, description, title, tags,etc,etc

when I do a  crud operation is inserted or update, i will populate the search table and based on what field it is it will be given a fixed score value, then based on the total score will give me a relavance score.

however if I have a field for each property I have no idea how to do a sum of all the different score fields.

My other options is to have a base table for the product then a list of search paramaters with a score next to them, and I could sum the score from the paramaters table for a relavent count, however i am not sure this is a good option for indexing.

One last issue I have is how to I validate string having more value than a single word, not the value part but adding relavent strings from a full description.

Any suggestions would be appriciated.

Answer 5

User753101303 posted

Unclear. Start with basic stuff.

You'll just search in multiple fields with a LIKE query and the score is depending in which fields you'll find the value ? Are you sure the score will be significant enough for your users ?

Or maybe you should consider using a  https://en.wikipedia.org/wiki/Faceted_search ? In short you are "grouping" the result on multiple columns so that you can see how many rows you have for each column value (or ranges for prices or dates etc...) and then the user can use that to further refine the result.

Answer 6

User1034446946 posted

the faceted approach seems like my second option, but it doesn't seem the ideal for optomising search results, things like indexing.

Which then leads me to believe i would then need to seperate this further into a search lookup using 1 design to return the search results, then another design (lookup) to assign a relevence figure.

I don't think it would be overly hard to assign relavance values, eg prices are a bool value, but things like brand would hold a grater value than a value in the description.

Answer 7

User753101303 posted

Or you could perhaps generate a query including columns created from expressions such as CASE WHEN Value LIKE '%'+Term+'%' THEN 1 ELSE 0 END column to produce  0/1 columns depending on wether a field is found inside a column or not.
Then on top of this you could compute a final ranking depending based on those values.

Answer 8

User1120430333 posted

Thanks for the reply, but I don't see how that helps, after reading the link I didn't see anything that helps me return a relavant search.

Well, you would have to get a Service Broker book to understand how to use Service Broker. And I am telling you that a SB service can be used as a search engine that can be used against a local database that it is running on,  but SB can also communicate with other MS SQL Server databases in a cluster or go over the Internet to communicate with MS SQL Server databases as endpoints, just as like one is using a WCF service and its endpoints. Millions, millions , millions, millions and millions of records were being searched throughout the day  across the DoD used by the social networking application  developed for the USAF and DoD with all military branches searching for things on the social network. 

Answer 9

User1034446946 posted

Well, you would have to get a Service Broker book to understand how to use Service Broker.

I am not sure i was supposed to figure that out, without knowing what you just posted. I don't suppose you have a link with a full description without having to find a book I know the name of.

I do appriciate the insight though, will keep it in mind although i think its way over my head at this moment.

Answer 10

User1034446946 posted

Or you could perhaps generate a query including columns created from expressions such as CASE WHEN Value LIKE '%'+Term+'%' THEN 1 ELSE 0 END column to produce  0/1 columns depending on wether a field is found inside a column or not.
Then on top of this you could compute a final ranking depending based on those values.

I think that could work, but one issue that would work for text, but how could i get it to work for say price is greater than?or would i need a field for each data type?

Oh an can I do that with linq, preferably lamda?

Answer 11

User1120430333 posted

I am not sure i was supposed to figure that out, without knowing what you just posted. I don't suppose you have a link with a full description without having to find a book I know the name of.

https://www.apress.com/us/book/9781590599990?gclid=CjwKCAjw6djYBRB8EiwAoAF6oYfwMFaOqAEIO-rlY5bvHo4l7jHKEMJFZGhgPowYirD545MY2aKebRoCOUMQAvD_BwE#otherversion=9781484220399

I saw your posting about caching too in the MVC forum. :)

https://www.apriorit.com/dev-blog/386-caching-in-dot-net

<copied>

Built upon the Service Broker infrastructure, query notifications allow applications to be notified when data has changed. This feature is particularly useful for applications that provide a cache of information from a database, such as Web applications, and need to be notified when the source data is changed.

<end>

Service Broker is top-gun technology. :)

http://blog.maskalik.com/sql-server-service-broker/scalable-webservice-calls-from-database/

Answer 12

User1034446946 posted

I am not sure i was supposed to figure that out, without knowing what you just posted. I don't suppose you have a link with a full description without having to find a book I know the name of.

https://www.apress.com/us/book/9781590599990?gclid=CjwKCAjw6djYBRB8EiwAoAF6oYfwMFaOqAEIO-rlY5bvHo4l7jHKEMJFZGhgPowYirD545MY2aKebRoCOUMQAvD_BwE#otherversion=9781484220399

I saw your posting about caching too in the MVC forum. :)

https://www.apriorit.com/dev-blog/386-caching-in-dot-net

<copied>

Built upon the Service Broker infrastructure, query notifications allow applications to be notified when data has changed. This feature is particularly useful for applications that provide a cache of information from a database, such as Web applications, and need to be notified when the source data is changed.

<end>

Service Broker is top-gun technology. :)

http://blog.maskalik.com/sql-server-service-broker/scalable-webservice-calls-from-database/

Thanks for all the info, really appriciate it, I have a couple of issues, 1, i would like to get something up and running quickly and think Server Broker is more complex than the other offerings at present, but something i have bookmarked and may look into later, 2, it seems to use SQL Server which has a high price which i am trying to stay away from.

Answer 13

User753101303 posted

IMO relevance is only for the full text search. If you have additional criteria(s) in addition to that, it won't change the relevance as it will match just exactly what the user asked for.

Or the UI could show a slider that would allows to select values falling within a range (but once again it doesn't change anything to the relevance as it will select exactly what the user asked for unlike a full text search which can match one or multiple keywords, have keywords being close or not etc...

Edit: for example something such as https://www.w3schools.com/jquerymobile/tryit.asp?filename=tryjqmob_forms_slider_range

Answer 14

User1120430333 posted

Thanks for all the info, really appriciate it, I have a couple of issues, 1, i would like to get something up and running quickly and think Server Broker is more complex than the other offerings at present, but something i have bookmarked and may look into later, 2, it seems to use SQL Server which has a high price which i am trying to stay away from.

Service Broker runs on MS SQL Server Express too, which is free to use.

Answer 15

User1034446946 posted

Thanks for all the info, really appriciate it, I have a couple of issues, 1, i would like to get something up and running quickly and think Server Broker is more complex than the other offerings at present, but something i have bookmarked and may look into later, 2, it seems to use SQL Server which has a high price which i am trying to stay away from.

Service Broker runs on MS SQL Server Express too, which is free to use.

That is interesting but express will only last for so long, and i would be forced to move to a very expensive option, and its still seems harder for me to get right into.

Answer 16

User1034446946 posted

IMO relevance is only for the full text search. If you have additional criteria(s) in addition to that, it won't change the relevance as it will match just exactly what the user asked for.

That is an excellent point, i guess now since the search is becoming more complicated i need to figure out if and how to cache it to speed things up

Answer 17

User1120430333 posted

DA924

Thanks for all the info, really appriciate it, I have a couple of issues, 1, i would like to get something up and running quickly and think Server Broker is more complex than the other offerings at present, but something i have bookmarked and may look into later, 2, it seems to use SQL Server which has a high price which i am trying to stay away from.

Service Broker runs on MS SQL Server Express too, which is free to use.

That is interesting but express will only last for so long, and i would be forced to move to a very expensive option, and its still seems harder for me to get right into.

Express can be used as a training tool Service Broker.  No one is saying get right into Service Broker, assuming that your solution is scalable. 

But on the other hand, why use a database if it is not an enterprise level database for what I will assume is an enterprise level solution? 

Answer 18

User753101303 posted

Depends on which scale you are doing that.  You have also specialized products doing that for you... IMO don't try to optimize earlier. You can always cache or precolmpute things later and having a search that works is already a bit of design work.

Your underlying db is ? (or you are the one who discussed not using any db but having all loaded in memory and saved to XML files when the application ends ?)

Answer 19

User1034446946 posted

EnenDaveyBoy

DA924

Thanks for all the info, really appriciate it, I have a couple of issues, 1, i would like to get something up and running quickly and think Server Broker is more complex than the other offerings at present, but something i have bookmarked and may look into later, 2, it seems to use SQL Server which has a high price which i am trying to stay away from.

Service Broker runs on MS SQL Server Express too, which is free to use.

That is interesting but express will only last for so long, and i would be forced to move to a very expensive option, and its still seems harder for me to get right into.

Express can be used as a training tool Service Broker.  No one is saying get right into Service Broker, assuming that your solution is scalable. 

But on the other hand, why use a database if it is not an enterprise level database for what I will assume is an enterprise level solution? 

Its a fair point however I am still in early stages, I will be what i call  live testing in about 8 weeks, but I expect that to be very different to the industry definition.

Enterprise level stuff hasn't been decided, but if i put all my eggs into Service Broker it would be decided for me and I don't want at this time, when there are other options I can consider, and happy to change it later as this will always be in development.

Answer 20

User1034446946 posted

Depends on which scale you are doing that.  You have also specialized products doing that for you... IMO don't try to optimize earlier. You can always cache or precolmpute things later and having a search that works is already a bit of design work.

Your underlying db is ? (or you are the one who discussed not using any db but having all loaded in memory and saved to XML files when the application ends ?)

Yes this will be db driven (wasn't me doing none db stuff), the db I am using at the moment is express, at present I don't need anything bigger, and do more research well before I do (open to opinions now tbh)

I am trying to use as few 3rd party stuff as possible, or least use open source stable free things until i can build or get built something, that way i keep costs down, get exactly what i want, and have the most flexability.

After sorting this bit out which is almost done, I will be force to more onto other bits so progress, but it will give me things to think about and finialise while i build other bits.