<br><br><div class="gmail_quote">On Mon, Mar 7, 2011 at 10:07 AM, Sebastian Trüg <span dir="ltr"><<a href="mailto:trueg@kde.org">trueg@kde.org</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex;">
<div><div></div><div class="h5">On 03/06/2011 02:33 PM, Ignacio Serantes wrote:<br>
> A better visibility check directly on the resource. This also requires a<br>
> nao:userVisible property on all resources which is done by a new class.<br>
><br>
><br>
> Great!, precisely this is the big problem I found with your queries and<br>
> you resolved adding a flag. Please, check the following simple queries<br>
> with one string tests. Strings are selected to obtain result sets with<br>
> different size.<br>
><br>
> Test #01 [ API_H1, (dorama)]: 93 results in 0.06073117<br>
> seconds<br>
> Test #02 [ API_H2, (dorama)]: 93 results in 1.84762883<br>
> seconds<br>
> Test #03 [ API_H3, (dorama)]: 93 results in 2.01492691 seconds<br>
> Test #04 [ API_H4, (dorama)]: 93 results in 1.91813993 seconds<br>
> Test #05 [ API_H5, (dorama)]: 93 results in 1.60597801 seconds<br>
> Test #06 [ API, (dorama)]: 93 results in 2.23234606 seconds<br>
> Test #07 [ API_H1, (ha ji won)]: 2 results in 0.01688004<br>
> seconds<br>
> Test #08 [ API_H2, (ha ji won)]: 2 results in 5.00310612 seconds<br>
> Test #09 [ API_H3, (ha ji won)]: 2 results in 4.11244702 seconds<br>
> Test #10 [ API_H4, (ha ji won)]: 2 results in 3.95332003 seconds<br>
> Test #11 [ API_H5, (ha ji won)]: 2 results in 3.90592694 seconds<br>
> Test #12 [ API, (ha ji won)]: 2 results in 11.18835807 seconds<br>
> Test #13 [ API_H1, (music)]: 5434 results in 1.39107108 seconds<br>
> Test #14 [ API_H2, (music)]: 5420 results in 4.74685478 seconds<br>
> Test #15 [ API_H3, (music)]: 5420 results in 5.27211499 seconds<br>
> Test #16 [ API_H4, (music)]: 5420 results in 5.14297509 seconds<br>
> Test #17 [ API_H5, (music)]: 5420 results in 4.99993420 seconds<br>
> Test #18 [ API, (music)]: 5420 results in 9.88414216 seconds<br>
><br>
> The query is the same but I applied minor changes:<br>
> API_H1: is the same query without visibility inner join so easy to<br>
> wonder where is the main performance problem.<br>
> API_H2: is the query using a subquery and not an inner join. Query is<br>
> equivalent (A U B) X C = (A X C) U (B X C). Because subquery is the same<br>
> could be optimized by query optimizer and, because intermediate joins<br>
> result sets are small union is fast. This is easy to see with a simple<br>
> SELECT DISTINCT * in both queries and comparing result sets.<br>
> API_H3: is like H1 without using optional to obtain columns.<br>
> API_H4: is API_H3 with a different filter construction method.<br>
> API_H5: is API_H3 with another different filter construction method.<br>
> API: is the query created by the API so no additional explanation is<br>
> needed. It is the last one to give it cache advantage over other queries.<br>
> Note: API_H1 results number differs in "music" case because this query<br>
> is not equivalent to the other five.<br>
<br>
</div></div>As a first hacky query optimization you might try to use<br>
<br>
?r a ?y .<br>
?y nao:userVisible ?visiblity .<br>
filter(regex(?visibility),'true')) .<br>
<br>
looks weird but should do wonders because almost all resources are visible.<br></blockquote><div><br></div><div><meta http-equiv="content-type" content="text/html; charset=utf-8"><div>I'm not following you I'm in my job so I can't test this but are you told me that your query is faster than this one:</div>
<div><br></div><div>?r a ?y . </div><div>?y nao:userVisible "true"^^<<a href="http://www.w3.org/2001/XMLSchema#boolean">http://www.w3.org/2001/XMLSchema#boolean</a>></div><div><br></div><div>Looks really weird :).</div>
<div> </div></div><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex;">
<br>
The optimization I was talking about is inference on client level. (As I<br>
mentioned I am not doing much on the DB level yet.) It creates a<br>
nao:userVisible value for each resource. Thus, one can test the<br>
visibility directly on the resource. That combined with the filter above<br>
is a big performance improvement. Obviously it is hacky and a view as<br>
you mention seems a much better solution.<br></blockquote><div><br></div><div>The view system could be a better solution but only several test will demonstrate it.</div><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex;">
<br>
Actually there is no dedicated index for nao:userVisible yet. The DB is<br>
totally generic. We are only beginning with the optimization. Your input<br>
is very very helpful.</blockquote><div><br></div><div>And you don't need to create it because boolean indexes are a lost of time and even could degrade db performance. You must check this in Virtuoso documentation.</div>
<div> </div><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex;">
<br>
Cheers,<br>
Sebastian<br>
<div><div></div><div class="h5"><br>
> This is an initial test and with my current db knowledge caution is<br>
> mandatory but here are some initial conclusions:<br>
> 1) As in SQL, subqueries increment performance<br>
> over indiscriminated inner joins in large results sets. I'm not sure if<br>
> this is general to all tripletstore dbms or only to Virtuoso that is a<br>
> rdbms with added tripletstore functionally. Seems like search API must<br>
> build queries use less joins and more subqueries.<br>
> 2) Optional seems haven't performance impact extracting column values<br>
> but queries are less easy to read and you must write more characters :).<br>
> 3) API_H3, API_H4 and API_H5 has similar times so without a profiling<br>
> tool and more test is difficult to wonder what is the best. Probably<br>
> query optimizer is doing it's job and, in fact, there is no differences<br>
> at all.<br>
><br>
> In "sparql_test.spql" attach there are the queries.<br>
><br>
> I go back to stored procedures because this problem could be solved<br>
> using Virtuoso. You have the need to filter for an value always so you<br>
> construct a relation any time you need to filter for this value. The<br>
> problem with this approach is that any time you do a query you must<br>
> build again this relation and this is time consuming. And the problem is<br>
> more and more serious when data grows.<br>
><br>
> In my db there are 23.766 results to userVisible = True and doing all<br>
> queries with a join with a table like this is not a good idea and we<br>
> can't trust that query optimizers do all your job without any<br>
> help. There are some solutions to this problem at db level:<br>
><br>
> 1) The (probably) ideal one, create a view in the dbms using your query<br>
> and use always this to filter your data. If query is very simple and<br>
> dbms is good this is automaintained and fast because view don't exists<br>
> and is only a different representation of data in your db.<br>
><br>
> 2) If the first approach don't works, view is slow and general db<br>
> performance is degrading, you must create a table to store this data and<br>
> use stored procedures and triggers to maintain this table. A view from<br>
> first case and a physical table is the same at query language level so,<br>
> if we tried the first case, most of the work is done and your queries<br>
> need only minor changes or even none.<br>
><br>
> 3) The easy one, add a property with and index to do the filter. You<br>
> told that this is your approach and works but you must be cautious<br>
> because now is visibility, later is user restriction, later devices<br>
> restriction and so on, and you can't solve all your filter problems with<br>
> properties as you can't solve all your query problems with joins.<br>
> Increase your size register and add so many indexes could have a<br>
> performance penalty when your data grows but note that this differs from<br>
> one dbms to other so there is no general rules.<br>
><br>
> You can solve this problem in application layer too but, if the problem<br>
> is db related must be solved at db level if if possible. Obviously this<br>
> is a relaxed rule.<br>
><br>
> Because, sadly, there is no perfect solution you must try<br>
> different approaches and use the solution that better suits to any case<br>
> and, here, profiling tools are your friends. About this, I will try to<br>
> activate Virtuoso web interface to activate profiling but I can't. I<br>
> download and compile Virtuoso and with my instance web interface is<br>
> available but I can't imagine how activate it in Nepomuk's Virtuoso<br>
> instance. I can copying the db and use it in my Virtuoso instance but<br>
> this is uncomfortably if I need doing changes in data using Nepomuk.<br>
><br>
> I found many errors in soprano-virtuoso db log. I created a db from<br>
> scratch and this errors persists: no method of name existsNode,<br>
> getClobVal, getNumVal, getSchemaURL etc... I'm informing you because I<br>
> don't know if this errors are relevant or not.<br>
><br>
> I'm sorry if I'm not much help but I'm doing my best. I never worked<br>
> before with Sparql and tripletstore but I'm learning.<br>
><br>
><br>
> Cheers,<br>
> Sebastian<br>
> _______________________________________________<br>
> Nepomuk mailing list<br>
</div></div>> <a href="mailto:Nepomuk@kde.org">Nepomuk@kde.org</a> <mailto:<a href="mailto:Nepomuk@kde.org">Nepomuk@kde.org</a>><br>
<div><div></div><div class="h5">> <a href="https://mail.kde.org/mailman/listinfo/nepomuk" target="_blank">https://mail.kde.org/mailman/listinfo/nepomuk</a><br>
><br>
><br>
><br>
><br>
> --<br>
> Cheers,<br>
> Ignacio<br>
><br>
><br>
><br>
><br>
> _______________________________________________<br>
> Nepomuk mailing list<br>
> <a href="mailto:Nepomuk@kde.org">Nepomuk@kde.org</a><br>
> <a href="https://mail.kde.org/mailman/listinfo/nepomuk" target="_blank">https://mail.kde.org/mailman/listinfo/nepomuk</a><br>
_______________________________________________<br>
Nepomuk mailing list<br>
<a href="mailto:Nepomuk@kde.org">Nepomuk@kde.org</a><br>
<a href="https://mail.kde.org/mailman/listinfo/nepomuk" target="_blank">https://mail.kde.org/mailman/listinfo/nepomuk</a><br>
</div></div></blockquote></div><br><br clear="all"><br>-- <br>Cheers,<div>Ignacio</div><div><br></div><br>