{"id":230,"date":"2011-05-25T11:36:57","date_gmt":"2011-05-25T11:36:57","guid":{"rendered":"http:\/\/www.sitekickr.com\/blog\/?p=230"},"modified":"2011-05-25T11:36:57","modified_gmt":"2011-05-25T11:36:57","slug":"chunky-sql-querie","status":"publish","type":"post","link":"https:\/\/www.sitekickr.com\/blog\/chunky-sql-querie\/","title":{"rendered":"Make those SQL queries chunky"},"content":{"rendered":"
I'm by no means saying that my tests below are evidence enough to support the case for "chunky" SQL queries. By chunky, I mean queries that leverage subqueries (like the first example below) to bring in more data.<\/div>\n
 <\/div>\n
The execution times below however, supports chunky queries in every way.<\/div>\n
 <\/div>\n
    \n
  • Faster – surprisingly
    \n\t\tI would have expected the extra level of complexity to add some execution time. Quite the opposite.<\/li>\n
  • Fewer queries<\/li>\n
  • Simplified script code to process the queries<\/li>\n<\/ul>\n

    What amazed me most about the execution times below is that the chunky query actually ran faster than the subquery within it.<\/p>\n

     <\/div>\n

    Chunky Query (with subquery)<\/h3>\n
    SELECT<\/span> id<\/span>,<\/span> firstname<\/span>,<\/span> lastname<\/span>,<\/span> company<\/span><\/span>,
    \n\t<\/code><\/div>\n
      (SELECT<\/span> sum<\/span>(<\/span> price<\/span> *<\/span> quantity<\/span> )<\/span>
    \n\t   FROM<\/span> order_items<\/span>
    \n\t   WHERE<\/span> orders_id<\/span> =<\/span> id<\/span> <\/span>)<\/span> AS<\/span> subtotal<\/span><\/span>
    \n\tFROM<\/span> orders<\/span>
    \n\t
    \n\t<\/span> <\/code><\/div>\n
    Query took 0.0255 sec<\/div>\n
     <\/div>\n

    Two Queries to achieve the same data<\/h3>\n
    SELECT<\/span> id<\/span>,<\/span> firstname<\/span>,<\/span> lastname<\/span>,<\/span> company<\/span><\/span>
    \n\tFROM<\/span> orders<\/span><\/code><\/div>\n
     <\/div>\n
    Query took 0.0007 sec<\/div>\n
     <\/div>\n
     <\/div>\n
    SELECT<\/span> sum<\/span>(<\/span> price<\/span> *<\/span> quantity<\/span> )<\/span>
    \n\tFROM<\/span> order_items<\/span>
    \n\t<\/span><\/code><\/div>\n

    \n\t<\/code><\/div>\n
    Query took 0.0259 sec<\/div>\n
     <\/div>\n
    Total: 0.0266 sec<\/div>\n","protected":false},"excerpt":{"rendered":"

    I'm by no means saying that my tests below are evidence enough to support the case for "chunky" SQL queries. By chunky, I mean queries…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"amp_status":""},"categories":[41,13],"tags":[],"_links":{"self":[{"href":"https:\/\/www.sitekickr.com\/blog\/wp-json\/wp\/v2\/posts\/230"}],"collection":[{"href":"https:\/\/www.sitekickr.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sitekickr.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sitekickr.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sitekickr.com\/blog\/wp-json\/wp\/v2\/comments?post=230"}],"version-history":[{"count":1,"href":"https:\/\/www.sitekickr.com\/blog\/wp-json\/wp\/v2\/posts\/230\/revisions"}],"predecessor-version":[{"id":231,"href":"https:\/\/www.sitekickr.com\/blog\/wp-json\/wp\/v2\/posts\/230\/revisions\/231"}],"wp:attachment":[{"href":"https:\/\/www.sitekickr.com\/blog\/wp-json\/wp\/v2\/media?parent=230"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sitekickr.com\/blog\/wp-json\/wp\/v2\/categories?post=230"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sitekickr.com\/blog\/wp-json\/wp\/v2\/tags?post=230"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}