Warning:
This wiki has been archived and is now read-only.
GeneratedSQL
From RDB2RDF
RDB2RDF Home • Potential SQL Issues
tool: FeDeRate
SPARQL:
SELECT ?emp1Name ?emp2Name ?emp3Name WHERE { ?emp1 emplP:lastName ?emp1Name OPTIONAL { ?emp1 emplP:birthday ?birthday } ?emp2 emplP:lastName ?emp2Name OPTIONAL { ?emp2 emplP:birthday ?birthday } ?emp3 emplP:lastName ?emp3Name . ?emp3 emplP:birthday ?birthday . ?emp4 emplP:lastName ?emp4Name . ?emp4 emplP:birthday ?birthday FILTER ( ?emp1Name < ?emp2Name && ?emp2Name < ?emp3Name && ?emp3Name < ?emp4Name) }
SQL:
SELECT R_emp1.lastName AS emp1Name, R_emp2.lastName AS emp2Name, R_emp3.lastName AS emp3Name FROM Employee AS R_emp1 LEFT OUTER JOIN ( SELECT R_emp1.birthday AS birthday, R_emp1.empid AS emp1, 1 AS _DISJOINT_ FROM Employee AS R_emp1 WHERE R_emp1.birthday IS NOT NULL AND R_emp1.empid IS NOT NULL ) AS G_opt1 ON G_opt1.emp1=R_emp1.empid INNER JOIN Employee AS R_emp2 LEFT OUTER JOIN ( SELECT R_emp2.birthday AS birthday, R_emp2.empid AS emp2, 3 AS _DISJOINT_ FROM Employee AS R_emp2 WHERE R_emp2.birthday IS NOT NULL AND R_emp2.empid IS NOT NULL ) AS G_opt3 ON (G_opt1._DISJOINT_ IS NULL OR G_opt3.birthday=G_opt1.birthday) AND G_opt3.emp2=R_emp2.empid INNER JOIN Employee AS R_emp3 INNER JOIN Employee AS R_emp4 WHERE (G_opt1._DISJOINT_ IS NULL OR R_emp3.birthday=G_opt1.birthday) AND (G_opt1._DISJOINT_ IS NULL OR R_emp4.birthday=G_opt1.birthday) AND R_emp1.lastName<R_emp2.lastName AND R_emp2.empid IS NOT NULL AND R_emp2.lastName<R_emp3.lastName AND R_emp3.empid IS NOT NULL AND R_emp3.lastName<R_emp4.lastName AND R_emp4.empid IS NOT NULL AND R_emp1.empid IS NOT NULL
tool: Ultrawrap
SPARQL:
SELECT ?productLabel ?review ?revTitle ?reviewer ?revName ?rating1 ?rating2 WHERE { Product981 product_label ?productLabel . ?review review_product Product981 . ?review review_person ?reviewer . ?reviewer person_name ?revName . ?review review_title ?revTitle . OPTIONAL { ?review review_rating1 ?rating1 . } OPTIONAL { ?review review_rating2 ?rating2 . } }
The Ultrawrap SPARQL2SQL compiler produces the following SQL, which is executed on the TripleView:
SELECT t1.oT1 AS productLabel, t9.sT9 AS review, t12.oT12 AS revTitle, t10.oT10 AS reviewer, t11.oT11 AS revName, t13.oT13 AS rating1, t14.oT14 AS rating2 FROM(SELECT opk as opkT1, o as oT1 FROM tripleview WHERE spk = 981 AND p = 'product_label') AS t1 INNER JOIN (SELECT spk as spkT9, s as sT9 FROM tripleview WHERE p = 'review_product' AND opk = 981) AS t9 INNER JOIN (SELECT spk as spkT10, s as sT10, opk as opkT10, o as oT10 FROM tripleview WHERE p = 'review_person') AS t10 ON (t10.spkT10 = t9.spkT9) INNER JOIN (SELECT spk as spkT11, s as sT11, opk as opkT11, o as oT11 FROM tripleview WHERE p = 'person_name') AS t11 ON (t11.spkT11 = t10.opkT10) INNER JOIN (SELECT spk as spkT12, s as sT12, opk as opkT12, o as oT12 FROM tripleview WHERE p = 'review_title') AS t12 ON (t12.spkT12 = t9.spkT9) LEFT OUTER JOIN (SELECT spk as spkT13, s as sT13, opk as opkT13, o as oT13 FROM tripleview WHERE p = 'review_rating1') AS t13 ON (t13.spkT13 = t9.spkT9) LEFT OUTER JOIN (SELECT spk as spkT14, s as sT14, opk as opkT14, o as oT14 FROM tripleview WHERE p = 'review_rating2') AS t14 ON (t14.spkT14 = t9.spkT9) ON (1=1)
The native query optimizer (in this case SQL Server) rewrites the previous query to the following query, which is then executed on the native relational data (disclaimer: this has been hand generated by looking at the explain)
SELECT t11.label, t12.reviewID, t12.rating2, t12.rating1, t12.name, t12.personID, t12.title, t12.productID FROM (select label, productID from product where productID = 981) as t11 INNER JOIN( SELECT t9.reviewID, t9.rating2, t10.rating1, t10.name, t10.personID, t10.title, t10.productID FROM (select reviewID, rating2 from review) aS t9 LEFT OUTER JOIN( SELECT t7.reviewID, t7.rating1, t8.name, t8.personID, t8.title, t8.productID FROM (select reviewID, rating1 from review) as t7 LEFT OUTER JOIN( SELECT t5.name, t6.reviewID, t6.personID, t6.title, t6.productID FROM (select personID, name from person) as t5 INNER JOIN( SELECT t3.reviewID, t3.personID, t4.title, t4.productID FROM (select personID, reviewID from review) AS t3 INNER JOIN( SELECT t2.reviewID, t2.title, t1.productID FROM (select reviewID, productID from review where productID = 981) as t1 INNER JOIN (select reviewID, title from review) as t2 ON (t1.reviewID = t2.reviewID) ) AS t4 ON (t3.reviewID = t4.reviewID) ) AS t6 ON(t5.personID = t6.personID) ) AS t8 ON(t7.reviewID = t8.reviewID) ) as t10 ON(t9.reviewID = t10.reviewID) ) AS t12 ON(t11.productID = t12.productID)
This query has 6 joins and it took 257 ms.
However, a semantically equivalent query, which would be the ideal and optimal one is the following:
SELECT product.label, review.reviewID, review.title, person.personID, person.name, review.rating1, review.rating2 FROM product, review, person WHERE product.productID = 981 AND product.productID = review.productID AND review.personID = person.personID;
This query only has 3 joins and took 36 ms