<html>
<head>
<meta content="text/html; charset=ISO-8859-1"
http-equiv="Content-Type">
</head>
<body bgcolor="#FFFFFF" text="#000000">
<div class="moz-cite-prefix">Le 22/08/2012 07:46, Vishesh Handa a
écrit :<br>
</div>
<blockquote
cite="mid:CAOPTMKBFCc3=WoE8oYUsJjMhoVJswot95dRddPsPj3LX4HjmpA@mail.gmail.com"
type="cite">Hey everyone<br>
<br>
In 4.9, most the queries on large datasets are impossibly slow and
often cause virtuoso to completely lock up. So I've been going
through the common queries that are passed to Nepomuk from a user
perspective and been trying to optimize them.<br clear="all">
<br>
The most prevalent problem is that of the user visibility.<br>
<br>
Simple queries like listing all the tags seem to blow out of
proportion with the added "
<style type="text/css">p, li { white-space: pre-wrap; }</style>FILTER
EXISTS { ?r a [ nao:userVisible "true"^^xsd:boolean ] . }". If one
looks the the SQL that is being generated one can see a drastic
different<br>
<br>
"select ?r where { ?r a nao:Tag . }"<br>
<br>
SELECT __id2i ( "s_1_0-t0"."S" ) AS "r"<br>
FROM DB.DBA.RDF_QUAD AS "s_1_0-t0"<br>
WHERE "s_1_0-t0"."P" = __i2idn ( __bft( '<a moz-do-not-send="true"
href="http://www.w3.org/1999/02/22-rdf-syntax-ns#type">http://www.w3.org/1999/02/22-rdf-syntax-ns#type</a>'
, 1))<br>
AND isiri_id ( "s_1_0-t0"."O")<br>
AND "s_1_0-t0"."O" = __i2idn ( __bft( '<a
moz-do-not-send="true"
href="http://www.semanticdesktop.org/ontologies/2007/08/15/nao#Tag">http://www.semanticdesktop.org/ontologies/2007/08/15/nao#Tag</a>'
, 1))<br>
OPTION (QUIETCAST)<br>
<br>
<br>
"select ?r where { ?r a nao:Tag . FILTER EXISTS { ?r a [
nao:userVisible "true"^^xsd:boolean ] . } }"<br>
<br>
SELECT __id2i ( "s_1_0-t0"."S" ) AS "r"<br>
FROM DB.DBA.RDF_QUAD AS "s_1_0-t0"<br>
WHERE "s_1_0-t0"."P" = __i2idn ( __bft( '<a moz-do-not-send="true"
href="http://www.w3.org/1999/02/22-rdf-syntax-ns#type">http://www.w3.org/1999/02/22-rdf-syntax-ns#type</a>'
, 1))<br>
AND isiri_id ( "s_1_0-t0"."O")<br>
AND "s_1_0-t0"."O" = __i2idn ( __bft( '<a
moz-do-not-send="true"
href="http://www.semanticdesktop.org/ontologies/2007/08/15/nao#Tag">http://www.semanticdesktop.org/ontologies/2007/08/15/nao#Tag</a>'
, 1))<br>
AND EXISTS ( ( <br>
SELECT TOP 1 1 AS __ask_retval<br>
FROM DB.DBA.RDF_QUAD AS "s_1_4-t1"<br>
INNER JOIN DB.DBA.RDF_QUAD AS "s_1_4-t2"<br>
ON ( "s_1_4-t1"."S" = "s_1_4-t2"."O" )<br>
WHERE "s_1_4-t1"."P" = __i2idn ( __bft( '<a
moz-do-not-send="true"
href="http://www.semanticdesktop.org/ontologies/2007/08/15/nao#userVisible">http://www.semanticdesktop.org/ontologies/2007/08/15/nao#userVisible</a>'
, 1))<br>
AND (1 - isiri_id ( "s_1_4-t1"."O"))<br>
AND "s_1_4-t1"."O" = DB.DBA.RDF_OBJ_OF_SQLVAL ( 1)<br>
AND "s_1_4-t2"."P" = __i2idn ( __bft( '<a
moz-do-not-send="true"
href="http://www.w3.org/1999/02/22-rdf-syntax-ns#type">http://www.w3.org/1999/02/22-rdf-syntax-ns#type</a>'
, 1))<br>
AND isiri_id ( "s_1_4-t2"."O")<br>
AND "s_1_4-t2"."S" = "s_1_0-t0"."S" <br>
OPTION (QUIETCAST)<br>
))<br>
OPTION (QUIETCAST)<br>
<br>
The second query results in an added query on every single result,
and that additional query also contains an added join.<br>
<br>
On my system with 13k tags (yeah, I know), the system is
completely unusable. Virtuoso pops up to 200% and takes about 5
minutes to respond. While I don't expect anyone to have 13k tags,
people do have those many contacts or emails.<br>
<br>
Options on how to fix -<br>
<br>
1. Use graphs with a filter -<br>
<br>
select ?r where { graph ?g { ?r a nao:Tag . } FILTER NOT EXISTS {
?g a nrl:Ontology. } }<br>
_______________________________________________________________________________<br>
<br>
SELECT __id2i ( "s_1_1-t0"."S" ) AS "r"<br>
FROM DB.DBA.RDF_QUAD AS "s_1_1-t0"<br>
WHERE "s_1_1-t0"."P" = __i2idn ( __bft( '<a moz-do-not-send="true"
href="http://www.w3.org/1999/02/22-rdf-syntax-ns#type">http://www.w3.org/1999/02/22-rdf-syntax-ns#type</a>'
, 1))<br>
AND isiri_id ( "s_1_1-t0"."O")<br>
AND "s_1_1-t0"."O" = __i2idn ( __bft( '<a
moz-do-not-send="true"
href="http://www.semanticdesktop.org/ontologies/2007/08/15/nao#Tag">http://www.semanticdesktop.org/ontologies/2007/08/15/nao#Tag</a>'
, 1))<br>
AND not ( EXISTS ( ( <br>
SELECT TOP 1 1 AS __ask_retval<br>
FROM DB.DBA.RDF_QUAD AS "s_1_4-t1"<br>
WHERE "s_1_4-t1"."P" = __i2idn ( __bft( '<a
moz-do-not-send="true"
href="http://www.w3.org/1999/02/22-rdf-syntax-ns#type">http://www.w3.org/1999/02/22-rdf-syntax-ns#type</a>'
, 1))<br>
AND isiri_id ( "s_1_4-t1"."O")<br>
AND "s_1_4-t1"."O" = __i2idn ( __bft( '<a
moz-do-not-send="true"
href="http://www.semanticdesktop.org/ontologies/2007/08/15/nrl#Ontology">http://www.semanticdesktop.org/ontologies/2007/08/15/nrl#Ontology</a>'
, 1))<br>
AND "s_1_4-t1"."S" = "s_1_1-t0"."G" <br>
OPTION (QUIETCAST)<br>
)))<br>
OPTION (QUIETCAST)<br>
<br>
This also results in an additional SQL query per resource, but
it's still a LOT faster (no join in the exists query).<br>
<br>
2.) Use graphs via nao:maintainedBy<br>
<br>
select ?r where { graph ?g { ?r a nao:Tag . } ?g nao:maintainedBy
?app . }'<br>
_______________________________________________________________________________<br>
<br>
SELECT __id2i ( "s_1_1-t0"."S" ) AS "r"<br>
FROM DB.DBA.RDF_QUAD AS "s_1_1-t0"<br>
INNER JOIN DB.DBA.RDF_QUAD AS "s_1_0-t1"<br>
ON ( "s_1_0-t1"."S" = "s_1_1-t0"."G" )<br>
WHERE "s_1_1-t0"."P" = __i2idn ( __bft( '<a moz-do-not-send="true"
href="http://www.w3.org/1999/02/22-rdf-syntax-ns#type">http://www.w3.org/1999/02/22-rdf-syntax-ns#type</a>'
, 1))<br>
AND isiri_id ( "s_1_1-t0"."O")<br>
AND "s_1_1-t0"."O" = __i2idn ( __bft( '<a
moz-do-not-send="true"
href="http://www.semanticdesktop.org/ontologies/2007/08/15/nao#Tag">http://www.semanticdesktop.org/ontologies/2007/08/15/nao#Tag</a>'
, 1))<br>
AND ( "s_1_0-t1"."S" < min_bnode_iri_id ())<br>
AND "s_1_0-t1"."P" = __i2idn ( __bft( '<a
moz-do-not-send="true"
href="http://www.semanticdesktop.org/ontologies/2007/08/15/nao#maintainedBy">http://www.semanticdesktop.org/ontologies/2007/08/15/nao#maintainedBy</a>'
, 1))<br>
OPTION (QUIETCAST)<br>
<br>
This would be the ideal solution, however it will kill backward
compatibility cause all the graph don't have the nao:maintainedBy
clause.<br>
<br>
3.) Go SQL and add another column to our RDF_QUAD table which is
indexed. That way we can always filter statements on the basis of
visibility. Would be considerably faster than the join.<br>
<br>
I suggest we go with option 1 for 4.9, and option 2 for 4.10 and
get rid of all the user visible stuff.<br>
<br>
Any suggestions?<br>
<br>
-- <br>
<span style="color:rgb(192,192,192)">Vishesh Handa</span><br>
<br>
<br>
<fieldset class="mimeAttachmentHeader"></fieldset>
<br>
<pre wrap="">_______________________________________________
Nepomuk mailing list
<a class="moz-txt-link-abbreviated" href="mailto:Nepomuk@kde.org">Nepomuk@kde.org</a>
<a class="moz-txt-link-freetext" href="https://mail.kde.org/mailman/listinfo/nepomuk">https://mail.kde.org/mailman/listinfo/nepomuk</a>
</pre>
</blockquote>
<br>
Why not try 3 ? Looks like a simple and obvious solution from my
point of view. I'm not a sparql guy, but used to dealing with sql
and databases. I understand it's not the more elegant solution, but
it will scale much more and fast is never fast enough.<br>
<br>
My 2 cents :)<br>
Gaël<br>
</body>
</html>