Hi Lars,
Do you see any warnings generated by D2R Server?
What version of Oracle and what version of the JDBC driver are you using?
What's happening apparently is that D2RQ can't properly interpret the date
values returned by the JDBC driver. This is likely a bug and we need the
information above to track it down (DATE in Oracle works fine on the versions
we've tested; the code is aware of the fact that Oracle DATE includes a time).
What happens next is that D2RQ treats the value that it can't interpret as
NULL. Since a NULL here doesn't match the query pattern, no result will be
generated.
(Since you have a LIMIT 10, D2RQ will walk through all 2.8M rows trying to find
some results. In the queries that work, D2RQ just needs to look at the first
few rows of your table to get 10 results. This explains the difference in
runtime.)
(Regarding the suboptimal SQL query, are you running D2R Server with --fast?)
Best,
Richard
On 2 May 2012, at 12:23, Lars Marius Garshol wrote:
>
> I've set up D2RQ against an Oracle database, and for some queries I get good
> results, but the query I actually need takes about 8 minutes and then
> produces nothing. Investigation has lead me to believe that the problem may
> be how D2RQ treats Oracle DATE values (which really are date+time).
>
> Let's go through my reasoning. This query works fine, yielding instant
> results:
> SELECT * WHERE { ?s a nav:Person } LIMIT 10
>
> This query takes 8 minutes to do nothing:
> SELECT * WHERE { ?s a nav:Person; nav:updated ?t } LIMIT 10
>
> Checking the database I see that there are 2.8 million rows in the PERSON
> table, and every single one has a value in the MOD_DATO column.
>
> Looking at the console we find:
>
> 11:49:57 INFO SPARQL :: Query: SELECT * WHERE { ?s a
> <http://psi.
> nav.no/2012/mod/Person>; <http://psi.nav.no/2012/mod/updated> ?t } limit 10
> 11:49:57 DEBUG QueryExecutionIterator :: SELECT DISTINCT
> "T1_PERSON"."PERSON_ID"
> , "T2_PERSON"."MOD_DATO" FROM "PERSON" "T1_PERSON", "PERSON" "T2_PERSON"
> WHERE "
> T1_PERSON"."PERSON_ID" = "T2_PERSON"."PERSON_ID"
>
> So this is not mapped to optimal SQL, but it looks correct to me.
>
> I tried simplifying the SPARQL query to just
> SELECT * WHERE { ?s nav:updated ?t } LIMIT 10
>
> This time we get:
>
> 11:12:17 INFO SPARQL :: Query: SELECT * WHERE { ?s
> <http://psi.na
> v.no/2012/mod/updated> ?t } limit 10
> 11:12:17 DEBUG QueryExecutionIterator :: SELECT DISTINCT "PERSON"."MOD_DATO",
> "P
> ERSON"."PERSON_ID" FROM "PERSON"
> 11:20:06 INFO SPARQL :: OK/select: SELECT * WHERE { ?s
> <http://ps
> i.nav.no/2012/mod/updated> ?t } limit 10
>
> Again the SQL looks slow but right, but I get no results. Running the SQL
> query directly gives me results that look perfectly fine.
>
> The relevant part of my mapping file is:
>
> map:Person a d2rq:ClassMap;
> d2rq:dataStorage _:Database1;
> d2rq:class nav:Person;
> d2rq:uriPattern
> "http://psi.nav.no/2012/mod/arena/person/@@PERSON.PERSON_ID@@";
> .
> _:Oppdatert a d2rq:PropertyBridge;
> d2rq:belongsToClassMap map:Person;
> d2rq:property nav:updated;
> d2rq:column "PERSON.MOD_DATO";
> d2rq:datatype xsd:dateTime;
> .
>
> There's also a column ETTERNAVN mapped to foaf:familyName (xsd:string) in the
> same way. If I query for that with
> SELECT * WHERE { ?s a nav:Person>; foaf:familyName ?f } limit 10
>
> I get correct results instantly.
>
>
> This led me to suspect that the declaration of MOD_DATO as xsd:dateTime might
> be the problem. I changed the mapping to xsd:string and tried querying for
> nav:updated/MOD_DATO again, and now I get instant results. Sadly, however,
> they are just dates represented as strings, and I really do need the full
> date and time.
>
> Investigation of the schema shows that MOD_DATO is declared as DATE, but SQL
> queries on it return full date/time values like 2010-05-28 01:59:04.0.
> Further investigation shows that Oracle DATE values actually do include the
> time:
> http://docs.oracle.com/cd/B19306_01/server.102/b14220/datatype.htm#i1847
>
> So... is this a bug in D2RQ, in the sense that you've mapped the Oracle DATE
> type to xsd:date when it should in fact map to xsd:dateTime?
>
> --Lars M.
> http://www.garshol.priv.no/tmphoto/
> http://www.garshol.priv.no/blog/
>
>
> ------------------------------------------------------------------------------
> Live Security Virtual Conference
> Exclusive live event will cover all the ways today's security and
> threat landscape has changed and how IT managers can respond. Discussions
> will include endpoint security, mobile security and the latest in malware
> threats. http://www.accelacomm.com/jaw/sfrnl04242012/114/50122263/
> _______________________________________________
> d2rq-map-devel mailing list
> [email protected]
> https://lists.sourceforge.net/lists/listinfo/d2rq-map-devel
------------------------------------------------------------------------------
Live Security Virtual Conference
Exclusive live event will cover all the ways today's security and
threat landscape has changed and how IT managers can respond. Discussions
will include endpoint security, mobile security and the latest in malware
threats. http://www.accelacomm.com/jaw/sfrnl04242012/114/50122263/
_______________________________________________
d2rq-map-devel mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/d2rq-map-devel
