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