This HTML5 document contains 35 embedded RDF statements represented using HTML+Microdata notation.

The embedded RDF content will be recognized by any processor of HTML5 Microdata.

Subject Item

n4:this

foaf:made

n2:VirtTipsAndTricksGuideDataRangeIndexFilter

Subject Item

n12:this

sioc:creator_of

n2:VirtTipsAndTricksGuideDataRangeIndexFilter

Subject Item

n13:item

n14:services_of

n2:VirtTipsAndTricksGuideDataRangeIndexFilter

Subject Item

n11:this

sioc:creator_of

n2:VirtTipsAndTricksGuideDataRangeIndexFilter

Subject Item

n8:VOS

sioc:container_of

n2:VirtTipsAndTricksGuideDataRangeIndexFilter

atom:entry

n2:VirtTipsAndTricksGuideDataRangeIndexFilter

atom:contains

n2:VirtTipsAndTricksGuideDataRangeIndexFilter

Subject Item

n2:VirtTipsAndTricksGuideDataRangeIndexFilter

rdf:type

atom:Entry sioct:Comment

dcterms:created

2017-06-13T05:37:04.850780

dcterms:modified

2017-06-13T05:37:04.850780

rdfs:label

VirtTipsAndTricksGuideDataRangeIndexFilter

foaf:maker

n4:this n15:this

dc:title

VirtTipsAndTricksGuideDataRangeIndexFilter

opl:isDescribedUsing

n19:rdf

sioc:has_creator

n11:this n12:this

sioc:content

%META:TOPICPARENT{name="VirtTipsAndTricksGuide"}% ---+How to optimize bif:dateadd in SPARQL query using selective index-friendly filter? ---++What? Index-friendly filter for Date range ( bif:dateadd ) optimization within SPARQL query. ---++Why? Achieve fast results and better performance. ---++How? Assume the following SPARQL query: <verbatim> SELECT ?wiki, ?dbp, bif:datediff('second', xsd:DateTime(?extracted), now()) AS ?secondsAgo FROM <http://nl.dbpedia.org> WHERE { ?wiki foaf:primaryTopic ?dbp . ?dbp dcterms:modified ?extracted . FILTER ( bif:datediff('minute', now(), xsd:DateTime(?extracted)) <= 10 ) } ORDER BY DESC(?extracted) LIMIT 30 </verbatim> Let's take a look at the calculation of: <verbatim> FILTER ( bif:datediff('minute', now(), xsd:DateTime(?extracted)) <= 10 ) . </verbatim> For each "is modified" triple we: 1 Convert <code>?extracted</code> to <code>xsd:dateTime</code> , 1 Calculate <code>datediff</code>, 1 Make a comparison and know whether we hit or miss 10 minutes interval. Written so, this will lead to a potentially long loop, because even if the optimizer will realize that the filter is selective, it can't discover <code>_why_</code> is it so selective. Now let's change the filter to: <verbatim> FILTER ( ?extracted > bif:dateadd('minute', -10, now())) . </verbatim> <code>now()</code> can be calculated once at the very beginning of the query execution because it does not depend on any rows in a given table. Then <code>bif:dateadd</code> has all arguments known and thus the whole <code>bif:dateadd('minute', -10, now())</code> can be calculated only once and produce some value. Therefor <code>FILTER ( ?extracted > <nowiki>some_known_value</nowiki> )</code> can be represented as a single search step: look at index and get triples with known P, known G, O greater than the given one and any S. That's pretty fast and predictable step, good for both optimizer and the runtime. We can rephrase the query to filter index-friendly: <verbatim> SELECT ?wiki, ?dbp, bif:datediff('second', xsd:DateTime(?extracted) , now()) AS ?secondsAgo FROM <http://nl.dbpedia.org> WHERE { ?wiki foaf:primaryTopic ?dbp . ?dbp dcterms:modified ?extracted . FILTER ( ?extracted > bif:dateadd('minute', -10, now())) } ORDER BY DESC (?extracted) LIMIT 30 </verbatim> In this case the presence or the absence of the order by does not matter too much, because the query is way more straightforward: selective index-friendly filter first, and the selection could be ordered naturally via hot index used by the filter. Note also that if you know the datatype of an object literal, there's no need to write a cast like xsd:dateTime --- it can make an expression index-unfriendly even if it will always return the argument unchanged on your specific data. * [[http://bit.ly/13xefMl][View the SPARQL Query Definition via SPARQL Protocol URL]] * [[http://bit.ly/18rI9VA][View the SPARQL Query Results via SPARQL Protocol URL]] ---++Related * [[VirtTipsAndTricksGuideDataRangeQueries][How can I manage Date Range SPARQL queries?]] * [[VirtTipsAndTricksGuide][Virtuoso Tips and Tricks Collection]] * [[http://docs.openlinksw.com/virtuoso/rdfsparql.html][Virtuoso Documentation]]

sioc:id

40bf1f762754fdff78ab22db7373c3f3

sioc:link

n2:VirtTipsAndTricksGuideDataRangeIndexFilter

sioc:has_container

n8:VOS

n14:has_services

n13:item

atom:title

VirtTipsAndTricksGuideDataRangeIndexFilter

sioc:links_to

n6:VirtTipsAndTricksGuideDataRangeQueries n6:VirtTipsAndTricksGuide n10:13xefMl n10:18rI9VA n20:html

atom:source

n8:VOS

atom:author

n4:this

atom:published

2017-06-13T05:37:04Z

atom:updated

2017-06-13T05:37:04Z

sioc:topic

n8:VOS

Subject Item

n2:VirtTipsAndTricksGuideDataRangeQueries

sioc:links_to

n2:VirtTipsAndTricksGuideDataRangeIndexFilter