GeneratedSQL

From RDB2RDF
Revision as of 08:48, 9 February 2010 by Jsequeda (Talk | contribs)

(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to: navigation, search

RDB2RDF HomePotential 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