Hi Anca, Great analysis. This gives a lot of good information to make the right choice for XWiki Watch.
I suggest we run with 120000 articles to see the trend of query time for all three methods. You should run the tests again with indexes on feed_feedentry because this can bring good improvements. The optimized query for clicking on "All" seems to be still slow (it could be bad with more data). We should also test a text search and a tag or keyword search Ludovic Guillaume Lerouge wrote: > Hi, > > I've summarized Anca's findings below (great testing btw ;-) , I bet we > should do this more often) : > > [snip] > > | Standard SQL | Lucene | Optimized SQL | Winner > initial loading of the articles, in a newly started server | 30-40 seconds > | up to 20 (15-16) | around 10 | OSQL > initial load of the interface, in a non-newly started server | ~15 seconds | > ~4-5 on average (but can go up to 10) | 7 | Lucene > click on the All group | around 7-8-9 seconds | 1 second | 5 on average, > from 3 to 7 | Lucene > click on a feed with 1023 articles | 3 seconds | from under a second to a > couple seconds | under a second (0.7-0.8) | OSQL > pagination navigation | 2-3 seconds | a second on average | 2-3 on average | > Lucene > > As we can see Lucene still has the edge a majority of times, but Optimized > SQL comes close in most cases. As far as my understanding of this issue > goes, I'd advise going for SQL optimization instead of Lucene for the > following reasons : > > - It is better suited to handle the highly structured data coming from > XWiki Watch > - It already offers a good performance and could deliver even more if > fully implemented > - It goes in the right way in terms of making the XWiki Watch > distribution on par with other XWiki products (such as XWS & XEM) in terms > of code organization (client-side / server-side) > - The Lucene indexing engine integration with XWiki is still error-prone > - Lucene doesn't work for real-time actions that are used a lot in XWiki > Watch > > Which is why, on the whole, SQL Optimization seems better than Lucene to me. > > Please tell me if I've missed something. > > Guillaume > > -- Ludovic Dubost Blog: http://blog.ludovic.org/ XWiki: http://www.xwiki.com Skype: ldubost GTalk: ldubost _______________________________________________ devs mailing list [email protected] http://lists.xwiki.org/mailman/listinfo/devs
