Hi Hugh,
On 08/29/2015 12:29 AM, Hugh Williams wrote:
Hi Núria,
So you increased from 9000 to 9000000 , which is excessive considering
the query takes 144secs to complete ?
well, this is the computer time, my crono said 2'25''
Can you provide the query being executed as well as the query profile
by running “set profile on;” from isql before running the query.
The query is:
SELECT count(DISTINCT ?gene) as ?genes ?disease ?diseaseName ?orphanet
count(DISTINCT ?phenotype) as ?phenotypes FROM <http://rdf.disgenet.org>
WHERE {
?gda sio:SIO_000628 ?disease,?gene .
?disease skos:exactMatch ?orphanet .
?orphanet sio:SIO_000341 ?phenotype .
FILTER regex(?gene, 'ncbigene')
FILTER regex(?disease, 'umls/id')
?disease dcterms:title ?diseaseName .
FILTER regex(?orphanet, 'identifiers.org/orphanet/86893')
?orphanet dcterms:title ?orphanetName .
?phenotype dcterms:title ?phenotypeName
}
GROUP BY ?disease ?diseaseName ?orphanet
After running "set profile on;" :
SQL> set profile on;
SQL> sparql SELECT count(DISTINCT ?gene) as ?genes ?disease ?diseaseName
?orphanet count(DISTINCT ?phenotype) as ?phenotypes FROM
<http://rdf.disgenet.org> WHERE {
?gda sio:SIO_000628 ?disease,?gene .
?disease skos:exactMatch ?orphanet .
?orphanet sio:SIO_000341 ?phenotype .
FILTER regex(?gene, 'ncbigene')
FILTER regex(?disease, 'umls/id')
?disease dcterms:title ?diseaseName .
FILTER regex(?orphanet, 'identifiers.org/orphanet/86893')
?orphanet dcterms:title ?orphanetName .
?phenotype dcterms:title ?phenotypeName
}
GROUP BY ?disease ?diseaseName ?orphanet
Type the rest of statement, end with a semicolon (;)> Type the rest of
statement, end with a semicolon (;)> Type the rest of statement, end
with a semicolon (;)> Type the rest of statement, end with a semicolon
(;)> Type the rest of statement, end with a semicolon (;)> Type the rest
of statement, end with a semicolon (;)> Type the rest of statement, end
with a semicolon (;)> Type the rest of statement, end with a semicolon
(;)> Type the rest of statement, end with a semicolon (;)> Type the rest
of statement, end with a semicolon (;)> Type the rest of statement, end
with a semicolon (;)> Type the rest of statement, end with a semicolon
(;)> Type the rest of statement, end with a semicolon (;)> ;
result
LONG VARCHAR
_______________________________________________________________________________
12 http://linkedlifedata.com/resource/umls/id/C1334968 Nodular
Lymphocyte Predominant Hodgkin Lymphoma
http://identifiers.org/orphanet/86893 11
1 http://linkedlifedata.com/resource/umls/id/C2239290 Hodgkin
lymphoma, nodular lymphocyte predominance
http://identifiers.org/orphanet/86893 11
{
time 4e-06% fanout 1 input 1 rows
Subquery 27
{
time 2.9e-06% fanout 1 input 1 rows
{ fork
time 0.14% fanout 2.03747e+06 input 1 rows
RDF_QUAD 2.8e+05 rows(s_14_7_t0.O, s_14_7_t0.S)
inlined P = #/SIO_000628 G = #/rdf.disgenet.org
time 6.8% fanout 0.5 input 2.03747e+06 rows
END Node
After test:
0: __ro2sq := Call __ro2sq (s_14_7_t0.O)
5: _cvt := Call _cvt (<constant>, __ro2sq)
10: if (_cvt LIKE <c %umls/id%>) then 14 else 15 unkn 15
14: BReturn 1
15: BReturn 0
time 2.3% fanout 2 input 1.01874e+06 rows
Precode:
0: __ro2sq := Call __ro2sq (s_14_7_t0.O)
5: BReturn 0
RDF_QUAD 3.5 rows(s_14_7_t1.O)
inlined P = #/SIO_000628 , S = k_s_14_7_t0.S G = #/rdf.disgenet.org
time 6.7% fanout 0.5 input 2.03747e+06 rows
END Node
After test:
0: __ro2sq := Call __ro2sq (s_14_7_t1.O)
5: _cvt := Call _cvt (<constant>, __ro2sq)
10: if (_cvt LIKE <c %ncbigene%>) then 14 else 15 unkn 15
14: BReturn 1
15: BReturn 0
time 1% fanout 1 input 1.01874e+06 rows
RDF_QUAD 5 rows(s_14_7_t4.S, s_14_7_t4.O)
inlined P = #/title , S = k_s_14_7_t0.O G = #/rdf.disgenet.org
time 4.5% fanout 14.4808 input 1.01874e+06 rows
Precode:
0: __ro2sq := Call __ro2sq (s_14_7_t4.O)
5: BReturn 0
RDF_QUAD 4.6 rows(s_14_7_t2.O)
inlined P = ##exactMatch , S = k_s_14_7_t0.O G = #/rdf.disgenet.org
time 79% fanout 8.81231e-07 input 1.47521e+07 rows
END Node
After test:
0: __ro2sq := Call __ro2sq (s_14_7_t2.O)
5: rdf_regex_impl := Call rdf_regex_impl (__ro2sq, <c
identifiers.org/orphanet/86893>)
10: if ( 0 = rdf_regex_impl) then 15 else 14 unkn 15
14: BReturn 1
15: BReturn 0
time 0.0006% fanout 11 input 13 rows
Precode:
0: __ro2sq := Call __ro2sq (s_14_7_t2.O)
5: BReturn 0
RDF_QUAD 86 rows(s_14_7_t3.O, s_14_7_t3.S)
inlined P = #/SIO_000341 , S = k_s_14_7_t2.O G = #/rdf.disgenet.org
time 0.00054% fanout 1 input 143 rows
RDF_QUAD 5 rows()
inlined P = #/title , S = cast G = #/rdf.disgenet.org
time 0.00021% fanout 2 input 143 rows
RDF_QUAD 5 rows()
inlined P = #/title , S = k_s_14_7_t2.O G = #/rdf.disgenet.org
time 0.0021% fanout 0 input 286 rows
Sort (s_14_7_t0.O, s_14_7_t4.O, s_14_7_t2.O) -> (inc, inc, __ro2sq,
__ro2sq, __ro2sq)
}
time 7.5e-05% fanout 2 input 1 rows
group by read node
(s_14_7_t0.O, s_14_7_t4.O, s_14_7_t2.O, aggregate, aggregate, __ro2sq,
__ro2sq, __ro2sq)
After code:
0: genes := := artm aggregate
4: disease := := artm __ro2sq
8: diseaseName := := artm __ro2sq
12: orphanet := := artm __ro2sq
16: phenotypes := := artm aggregate
20: BReturn 0
time 1.4e-06% fanout 0 input 2 rows
Subquery Select(genes, disease, diseaseName, orphanet, phenotypes)
}
After code:
0: genes := Call __ro2sq (genes)
5: disease := Call __ro2sq (disease)
10: diseaseName := Call __ro2sq (diseaseName)
15: orphanet := Call __ro2sq (orphanet)
20: phenotypes := Call __ro2sq (phenotypes)
25: BReturn 0
time 8e-07% fanout 0 input 2 rows
Select (genes, disease, diseaseName, orphanet, phenotypes)
}
103934 msec 99% cpu, 4.05894e+06 rnd 1.7155e+07 seq 70.6155% same
seg 13.7565% same pg
Compilation: 35 msec 0 reads 0% read 0 messages 0% clw
4 Rows. -- 103972 msec.
What is the current triple count on the server, which can be obtained
with:
sparql select count(*) where {?s ?p ?o};
24914558 triples
Also what is the status of the server after running the query multiple
times, which can be obtained with the command:
status();
SQL> status();
result
LONG VARCHAR
_______________________________________________________________________________
OpenLink Virtuoso Server
Version 07.10.3211-pthreads for Linux as of Nov 4 2014
Started on: 2015-08-19 10:02 GMT+2
Database Status:
File size 0, 1073664 pages, 474141 free.
5450000 buffers, 535311 used, 2 dirty 0 wired down, repl age 0 0 w.
io 0 w/crsr.
Disk Usage: 526645 reads avg 0 msec, 0% r 0% w last 1604 s, 495790
writes flush 26.55 MB,
2677 read ahead, batch = 191. Autocompact 133309 in 100660 out,
24% saved col ac: 25228 in 22% saved.
Gate: 9239 2nd in reads, 0 gate write waits, 0 in while read 0 busy scrap.
Log =
/usr/local/virtuoso-development-04112014/var/lib/virtuoso/db/virtuoso.trx,
5872 bytes
599446 pages have been changed since last backup (in checkpoint state)
Current backup timestamp: 0x0000-0x00-0x00
Last backup date: unknown
Clients: 16 connects, max 4 concurrent
RPC: 688 calls, -12 pending, 2 max until now, 0 queued, 0 burst reads
(0%), 0 second 2M large, 417M max
Checkpoint Remap 0 pages, 0 mapped back. 464 s atomic time.
DB master 1073664 total 474141 free 0 remap 0 mapped back
temp 13312 total 13307 free
{
Precode:
0: <none> := Call status ()
5: BReturn 0
END Node
}
264 msec 99% cpu, 0 rnd 0 seq 0% same
seg 0% same pg
Compilation: 0 msec 0 reads 0% read 0 messages 0% clw
4 Rows. -- 266 msec.
I hope this is the info you requested. Thanks again!
Best,
Núria
Best Regards
Hugh Williams
Professional Services
OpenLink Software, Inc. // http://www.openlinksw.com/
Weblog -- http://www.openlinksw.com/blogs/
LinkedIn -- http://www.linkedin.com/company/openlink-software/
Twitter -- http://twitter.com/OpenLink
Google+ -- http://plus.google.com/100570109519069333827/
Facebook -- http://www.facebook.com/OpenLinkSoftware
Universal Data Access, Integration, and Management Technology Providers
On 28 Aug 2015, at 18:08, Nuria Queralt Rosinach <nquer...@imim.es
<mailto:nquer...@imim.es>> wrote:
Hi Hugh,
On 08/28/2015 06:46 PM, Hugh Williams wrote:
Hi Núria,
What was the 'MaxQueryCostEstimationTime’ parameter set to
previously and what did you increase it to , as the
“ERR_CONTENT_DECODING_FAILED” error seem odd for a timeout parameter ?
I increased from 9x10**3 to 9x10**6
I got directly the virtuoso error message executing the query from
our endpoint but specifying the virtuoso port in the browser, in
order to avoid the apache server. then the message error from
virtuoso was that the estimation execution time was too short. the
error message "ERR_CONTENT_DECODING_FAILED" come from apache i guess,
as it does not know how to serve the error message from virtuoso?
Also, when you ran the query from isql how long does it take to
execute and return results ?
144552 msec
best,
n
Best Regards
Hugh Williams
Professional Services
OpenLink Software, Inc. // http://www.openlinksw.com/
Weblog -- http://www.openlinksw.com/blogs/LinkedIn --
http://www.linkedin.com/company/openlink-software/Twitter --
http://twitter.com/OpenLinkGoogle+ --
http://plus.google.com/100570109519069333827/Facebook --
http://www.facebook.com/OpenLinkSoftware
Universal Data Access, Integration, and Management Technology Providers
On 28 Aug 2015, at 15:51, Nuria Queralt Rosinach <nquer...@imim.es
<mailto:nquer...@imim.es>> wrote:
Hi Hugh,
Problem solved! just increasing a bit the
'MaxQueryCostEstimationTime' parameter in the configuration of the
SPARQL service and the query works perfectly fine :)
Thank you very much for your always fast replies.
Best,
Núria
On 08/28/2015 04:30 PM, Nuria Queralt Rosinach wrote:
Hi Hugh,
On 08/28/2015 02:56 PM, Hugh Williams wrote:
HI Núria,
You reported the same error running a SPARQL-FED query last year,
was this resolved then as it is unclear from the thread ?
Good memory! i managed to solve the problem in that case. This
time it is not a FED.
What is the query currently being run is it a SPARQL-FED query
also or a query against the local instance ?
it is about a query against local data. Issues raised via the
sparql endpoint when using DISTINCT in a SELECT query, or GROUP BY
modifier: the same query gives output without using DISTINCT or
GROUP BY in the SELECT query.
If the query runs from isql which executes queries as the “dba”
(or whatever user you login as) and fails via the HTTP , which I
assume is the /sparql endpoint, which runs as user “SPARQL”,
yes
then most probably the roles assigned to both differ, thus you
should check was is assigned to both in the “System Admin -> User
Accounts” section of the Conductor and ensure the same SPARQL
roles are assigned ie SPARQL_SELECT, SPARQL_UPDATE,
SPARQL_SPONGE, SPARQL_LOAD_SERVICE_DATA , see:
http://docs.openlinksw.com/virtuoso/rdfsparql.html#rdfsupportedprotocolendpointuri
http://virtuoso.openlinksw.com/dataspace/dav/wiki/Main/VirtTipsAndTricksDiscoverSPARQFedCapabilities
I checked, they differ: SPARQL user has the 'SPARQL_SELECT' role
assigned, whereas the dba user does not have any role assigned...
Thanks!
Núria
Best Regards
Hugh Williams
Professional Services
OpenLink Software, Inc. // http://www.openlinksw.com/
Weblog -- http://www.openlinksw.com/blogs/
LinkedIn -- http://www.linkedin.com/company/openlink-software/
Twitter -- http://twitter.com/OpenLink
Google+ -- http://plus.google.com/100570109519069333827/
Facebook -- http://www.facebook.com/OpenLinkSoftware
Universal Data Access, Integration, and Management Technology
Providers
On 28 Aug 2015, at 12:33, Nuria Queralt Rosinach
<nquer...@imim.es <mailto:nquer...@imim.es>> wrote:
Dear all,
I have a problem with a query that perfectly works via ISQL, but
it does not via Web. The error i got is:Error code:
ERR_CONTENT_DECODING_FAILED
Virtuoso version implemented:
OpenLink Virtuoso Server
Version 07.10.3211-pthreads for Linux as of Nov 4 2014
Could you shed light on that problem? Many thanks in advance.
Best regards,
Núria
--
********************************************************************
Núria Queralt Rosinach
Research Programme on Biomedical Informatics (GRIB)
Department of Experimental and Health Sciences
Universitat Pompeu Fabra
IMIM (Hospital del Mar Medical Research Institute)
C/Dr. Aiguader 88, 08003
Barcelona, Spain
Tel.: +34 93 316 0536 (1536)
E-mail:nquer...@imim.es
Skype IM: nuriaqr76
http://ibi.imim.es/
********************************************************************
------------------------------------------------------------------------------
_______________________________________________
Virtuoso-users mailing list
Virtuoso-users@lists.sourceforge.net
<mailto:Virtuoso-users@lists.sourceforge.net>
https://lists.sourceforge.net/lists/listinfo/virtuoso-users
--
********************************************************************
Núria Queralt Rosinach
Research Programme on Biomedical Informatics (GRIB)
Department of Experimental and Health Sciences
Universitat Pompeu Fabra
IMIM (Hospital del Mar Medical Research Institute)
C/Dr. Aiguader 88, 08003
Barcelona, Spain
Tel.: +34 93 316 0536 (1536)
E-mail:nquer...@imim.es
Skype IM: nuriaqr76
http://ibi.imim.es/
********************************************************************
--
********************************************************************
Núria Queralt Rosinach
Research Programme on Biomedical Informatics (GRIB)
Department of Experimental and Health Sciences
Universitat Pompeu Fabra
IMIM (Hospital del Mar Medical Research Institute)
C/Dr. Aiguader 88, 08003
Barcelona, Spain
Tel.: +34 93 316 0536 (1536)
E-mail:nquer...@imim.es
Skype IM: nuriaqr76
http://ibi.imim.es/
********************************************************************
------------------------------------------------------------------------------
_______________________________________________
Virtuoso-users mailing list
Virtuoso-users@lists.sourceforge.net
<mailto:Virtuoso-users@lists.sourceforge.net>
https://lists.sourceforge.net/lists/listinfo/virtuoso-users
--
********************************************************************
Núria Queralt Rosinach
Research Programme on Biomedical Informatics (GRIB)
Department of Experimental and Health Sciences
Universitat Pompeu Fabra
IMIM (Hospital del Mar Medical Research Institute)
C/Dr. Aiguader 88, 08003
Barcelona, Spain
Tel.: +34 93 316 0536 (1536)
E-mail:nquer...@imim.es
Skype IM: nuriaqr76
http://ibi.imim.es/
********************************************************************
------------------------------------------------------------------------------
_______________________________________________
Virtuoso-users mailing list
Virtuoso-users@lists.sourceforge.net
<mailto:Virtuoso-users@lists.sourceforge.net>
https://lists.sourceforge.net/lists/listinfo/virtuoso-users
--
********************************************************************
Núria Queralt Rosinach
Research Programme on Biomedical Informatics (GRIB)
Department of Experimental and Health Sciences
Universitat Pompeu Fabra
IMIM (Hospital del Mar Medical Research Institute)
C/Dr. Aiguader 88, 08003
Barcelona, Spain
Tel.: +34 93 316 0536 (1536)
E-mail: nquer...@imim.es
Skype IM: nuriaqr76
http://ibi.imim.es/
********************************************************************
------------------------------------------------------------------------------
_______________________________________________
Virtuoso-users mailing list
Virtuoso-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/virtuoso-users
