This thread looks to be a little on the old side and therefore may no longer be relevant. Please see if there is a newer thread on the subject and ensure you're using the most recent build of any software if your question regards a particular product.
This thread has been locked and is no longer accepting new posts, if you have a question regarding this topic please email us at support@mindscape.co.nz
|
I have an issue with the lucene search and wonder if there is a solution that I'm not seeing. I want to be able to filter the full text results by a database flag currently I'm doing the following:
Query query = new Query{
SearchQuery = q, QueryExpression = Entity.Attribute("flag") == flag,
ViewName = "ArticleSearch"
};
What this does is that search finds all the ids of the rows that match and does an in statement in the sql to find those that match. The QueryExpression is added to this where statement so that it's where flag=value and id in (... etc The problem is that if there are 1000 rows in the table and 900 match the full text query then there are 900 ids in the in statement but if only 100 had the flag value then currently the in statement is a lot bigger than it needs to be. If I could use Lucene to match the flag as well then the performance would be greatly increased. The issue is that Lightspeed puts all the index properties into one Lucene field called data. Lucene supports more than one searchable field but I can't see how I can get Lightspeed to do it. My own search implimentation won't work as the data field is generated by lightspeed before ISearchEngine The only solution I can think of it to add a GUID to the indexed columns and filter by that in the full text search by adding it to my query. This however is not a good solution and can only work for one flag. Please can you offer some suggestions? |
|
|
|
|
Hi, Just to clarify, your issues are:
The size of the SQL query shouldn't really be an issue. I can appreciate that you could see that the query could be more efficient, but the reverse could be true for others (perhaps filtering by the flag first would generate more results than the ID range provided). Of course we can't predict which is going to be more efficient. Generally the query optimizer in your database engine will know which part of the query it should run first for performance reasons (be it the flag check or the IN() query) and you may find that the query plan generated in both orderings is exactly the same). The search API for LightSpeed is not built for Lucene only nor is Lucene a supported database back end which would let you do full querying of different fields. It is built to be a fairly generic full text search capability that will work with any database, not a database replacement. We could perhaps look at enhancing this in a future version of LightSpeed but we also don't want to make the search API unnecessarily complex for simple search providers. Is this a critical issue for you or just a nice to have? What sort of performance issues are you encountering? |
|
|
|
|
In the scenario I'm testing the performance difference is large. I'm doing a site search with google style suggestions. There are about 2500 rows in the database but only 15 are flagged to be searched (in my test). Using the sql flag filtering the ajax request takes consitently around 2.5 seconds for the first query (then it's quicker as sql server caches the query). If I filter the search by the flag in the index the first query is between 3ms and 60ms. So in other words the large query is slow because of it's size the first time it's attempted. It isn't that the database doesn't do the best job it can, it's that there are 2000 + ids that don't need to be added to the where clause. When there is only 15 it is quicker. So if Lucene can return 15 the whole thing would be better. I understand Lucene isn't a database but I've used it before and you can have more than one field to filter by - in fact you're filtering by "scope" which is how you limit which table is searched. I think it is a Lightspeed issue as it combines all indexed fields into one field. If I added another search implimentation I'd have the same issue unless it was sql server full text I guess. As far as the importance goes I'd say it's fairly important. At the moment the options are to have a seperate searchable view for each flag or if I've filtereing by an ID add a GUID to the index and add the corresponding GUID to the search query. These options are not ideal by any means as I've got extra properties and extra views kicking about just for searching. Lightspeed is great I know that it can't serve every secnario but this seems to me to be a fairly common scenario? |
|
|
|
