Ping?<br><br>4.9.2 is out. And we definitely need to fix this in time for 4.10.<br><br>I can experiment with the extra column approach, though it seems pretty unclean to me. (Unclean = not rdf based)<br><br><div class="gmail_quote">
On Wed, Aug 22, 2012 at 11:16 AM, Vishesh Handa <span dir="ltr"><<a href="mailto:me@vhanda.in" target="_blank">me@vhanda.in</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
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 "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 href="http://www.w3.org/1999/02/22-rdf-syntax-ns#type" target="_blank">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 href="http://www.semanticdesktop.org/ontologies/2007/08/15/nao#Tag" target="_blank">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 href="http://www.w3.org/1999/02/22-rdf-syntax-ns#type" target="_blank">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 href="http://www.semanticdesktop.org/ontologies/2007/08/15/nao#Tag" target="_blank">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 href="http://www.semanticdesktop.org/ontologies/2007/08/15/nao#userVisible" target="_blank">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 href="http://www.w3.org/1999/02/22-rdf-syntax-ns#type" target="_blank">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 href="http://www.w3.org/1999/02/22-rdf-syntax-ns#type" target="_blank">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 href="http://www.semanticdesktop.org/ontologies/2007/08/15/nao#Tag" target="_blank">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 href="http://www.w3.org/1999/02/22-rdf-syntax-ns#type" target="_blank">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 href="http://www.semanticdesktop.org/ontologies/2007/08/15/nrl#Ontology" target="_blank">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 href="http://www.w3.org/1999/02/22-rdf-syntax-ns#type" target="_blank">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 href="http://www.semanticdesktop.org/ontologies/2007/08/15/nao#Tag" target="_blank">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 href="http://www.semanticdesktop.org/ontologies/2007/08/15/nao#maintainedBy" target="_blank">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?<span class="HOEnZb"><font color="#888888"><br><br>-- <br><span style="color:rgb(192,192,192)">Vishesh Handa</span><br>
<br>
</font></span></blockquote></div><br><br clear="all"><br>-- <br><span style="color:rgb(192,192,192)">Vishesh Handa</span><br><br>