on mine, user are able to multiple keyword search separate by comma by simply looping on the keyword search field and doing multiple like on the appended string.
Your like queries aren’t going to be fast - ever, because they don’t use the index. Is there a chance that you can change to a query that uses an index?
I have a similar situation where I’m search for text in my application. The like query makes this dog slow. I’m looking into an add-on to Lucene called Elasticsearch. This has the benefit of a REST api that Xojo can talk to via CURL. It’s a Java app so it needs to be packaged. But Elasticsearch is optimized for search queries like you have.
it benefits from the fact that its in memory.
its not slow though, the users don’t complain about it at the moment
[quote=111663:@Paul Stevenson]OK, limited test results time (very rough guides as I am no expert and don’t know the correct methods so went old school and used a stopwatch) :
Time to execute SQL as above (no optimisation) with 61 records returned -
In SQL Admin 6.9 seconds
In Xojo 6.5 seconds
Difference will be my reaction times and different software displaying the results but gives a bit more info. The times are roughly the same (in SQL admin ADN Xojo app) even with a search that returns 9000+ rows.
[/quote]
This suggests to me that Pats original focus on optimizing the query is probably the right thing to attack first.
I’d run you query in SQL Admin with EXPLAIN so we can see what the SQL Server has decided to do with your query
I suspect Pat’s right about count & all the leading wildcards being problematic as indexes won’t be very useful