Menu
▾
▴
Re: [d2rq-dev] Weird SQL query (d2rq: to exclusive)
|
From: Erwin B. <erw...@gm...> - 2009-05-14 16:09:10
|
Hi,
I have been experiencing the same performance problems. For example, a
simple SPARQL query like the following:
SELECT ?year ?title
WHERE
{ ?paper vocab:papers_Year ?year ;
vocab:papers_Title ?title .
}
translates into this SQL query:
SELECT DISTINCT `T2_papers`.`Title`, `T1_papers`.`Year`,
`T1_papers`.`PaperID` FROM `papers` AS `T2_papers`, `papers` AS
`T1_papers` WHERE `T1_papers`.`PaperID` = `T2_papers`.`PaperID`
I.e. a table is joined with itself on its primary key. A sufficiently
smart SQL query handler may realise that this is a NOOP, but in
practice we find that this is not always optimised out. I had assumed
that this was a known problem, but maybe not. The above query is
simple enough that performance is okay, but this is not the case for
all queries that we need to carry out. Some contain dozens of
unnecessary "self-joins" and performance is so poor that they cannot
be executed unfortunately :-(
Cheers,
Erwin
On Wed, May 13, 2009 at 11:16 AM, Alfredo Salvador Gonzalez wrote:
> Hi all,
>
> I solved my SQL driver problems but now im having problems with the
> performance of the queries, some of them take hours to resolve and most of
> them break the heap space limitation of 2GB. Watching the SQL Info logs i
> get a query like the following:
>
> SELECT DISTINCT `T2_NtpGps_measure`.`IP`, `T1_NtpGps_measure`.`Id` FROM
> `NtpGps_measure` AS `T2_NtpGps_measure`, `NtpGps_measure` AS
> `T1_NtpGps_measure` WHERE `T1_NtpGps_measure`.`Id` =
> `T2_NtpGps_measure`.`Id`
>
> Correct me if i'm wrong but this makes no sense! The product of the same
> table vs itself to retrieve two columns already in the table? something must
> be wrong configured or im getting something wrong.
>
> Thanks in advance for your time
>
> The mapping of the table its as follows:
>
> # Table NtpGps_measure
>
> map3:NtpGps_measure a d2rq:ClassMap;
>
> d2rq:dataStorage map3:database;
>
> d2rq:uriPattern "NtpGps_measure/@@NtpGps_measure.Id@@";
>
> d2rq:class data:Measurement;
>
> .
>
> map3:NtpGps_measure__label a d2rq:PropertyBridge;
>
> d2rq:belongsToClassMap map3:NtpGps_measure;
>
> d2rq:property rdfs:label;
>
> d2rq:pattern "Gps Measurement #@@NtpGps_measure.Id@@";
>
> .
>
> map3:NtpGps_measure_Id a d2rq:PropertyBridge;
>
> d2rq:belongsToClassMap map3:NtpGps_measure;
>
> d2rq:property vocab:NtpGps_measure_Id;
>
> d2rq:column "NtpGps_measure.Id";
>
> d2rq:datatype xsd:int;
>
> d2rq:valueRegex "\\d{4,8}";
>
> .
>
> map3:NtpGps_measure_IP a d2rq:PropertyBridge;
>
> d2rq:belongsToClassMap map3:NtpGps_measure;
>
> d2rq:property vocab:NtpGps_measure_IP;
>
> d2rq:column " NtpGps_measure.IP";
>
> d2rq:datatype xsd:int;
>
> d2rq:valueRegex "\\d{10}";
>
> .
>
> map3:NtpGps_measure_Timestamp a d2rq:PropertyBridge;
>
> d2rq:belongsToClassMap map3:NtpGps_measure;
>
> d2rq:property vocab:NtpGps_measure_Timestamp;
>
> d2rq:column "NtpGps_measure.Timestamp";
>
> d2rq:datatype xsd:int;
>
> d2rq:valueRegex "\\d{1,11}";
>
> .
>
> map3:NtpGps_measure_Dagoffset a d2rq:PropertyBridge;
>
> d2rq:belongsToClassMap map3:NtpGps_measure;
>
> d2rq:property vocab:NtpGps_measure_Dagoffset;
>
> d2rq:column "NtpGps_measure.Dagoffset";
>
> d2rq:datatype xsd:int;
>
> d2rq:valueRegex ".\\d{1,11}";
>
> .
>
> map3:NtpGps_measure_Ppsoffset a d2rq:PropertyBridge;
>
> d2rq:belongsToClassMap map3:NtpGps_measure;
>
> d2rq:property vocab:NtpGps_measure_Ppsoffset;
>
> d2rq:column "NtpGps_measure.Ppsoffset";
>
> d2rq:datatype xsd:int;
>
> d2rq:valueRegex ".\\d{1,11}";
>
> .
>
> map3:NtpGps_measure_Satellitecount a d2rq:PropertyBridge;
>
> d2rq:belongsToClassMap map3:NtpGps_measure;
>
> d2rq:property vocab:NtpGps_measure_Satellitecount;
>
> d2rq:column "NtpGps_measure.Satellitecount";
>
> d2rq:datatype xsd:int;
>
> d2rq:valueRegex "\\d{1,3}";
>
> .
>
>
> --
> Alfredo Salvador Gonzalez
> http://rincon.uam.es/dir?cw=350817871093750
>
> Si te caes siete veces levantate ocho.
> If you fall seven times, get up eight
> _________________________________
> B eing around with you is great dear
> E ach moments spend with you seem sweet and wonderful
> L oving you and longing for your touch from far
> I s real tough dear, but I will always love you
> E ven though I don't know what the future holds, but
> V alentine you will always be in my heart
> E verlasting with my love for you
>
> I am insane in loving you? You may ask
> N o. You'll always have a special place in my heart
>
> L asting with my love and devotion
> O n the hidden paradise deep inside my heart
> V ery special you will always be
> E ndless loves for you till the end my dear.
> _________________________________
>
> ------------------------------------------------------------------------------
> The NEW KODAK i700 Series Scanners deliver under ANY circumstances! Your
> production scanning environment may not be a perfect world - but thanks to
> Kodak, there's a perfect scanner to get the job done! With the NEW KODAK
> i700
> Series Scanner you'll get full speed at 300 dpi even with all image
> processing features enabled. http://p.sf.net/sfu/kodak-com
> _______________________________________________
> d2rq-map-devel mailing list
> d2r...@li...
> https://lists.sourceforge.net/lists/listinfo/d2rq-map-devel
>
>
|