SQL resources and performance

Post Reply
KBleivik
Site Admin
Posts: 184
Joined: Tue Sep 29, 2009 6:25 pm
Location: Moss Norway
Contact:

SQL resources and performance

Post by KBleivik »

1. Resources

Tutorials:

http://www.w3schools.com/sql/

Forums:

W3Schools: http://w3schools.invisionzone.com/index ... owforum=32

Sitepoint: http://www.sitepoint.com/forums/forumdi ... -amp-MySQL

Litterature:

There are many books written on SQL. Like

http://www.amazon.com/dp/1565927443/

http://shop.oreilly.com/product/9780596523084.do

One of my favourites is

Rudy Limeback's

book: http://simply-sql.com/ from sitepoint http://www.sitepoint.com/books/sql1/

business site: http://r937.com/

personal site: http://rudy.ca/

See also: http://www.sqllessons.com/categories.html

It is a clear and pedagogical book that teach you most of what you may know of SQL as a web developer. I also have a more advanced book, Sams Teach Yourself Transact-SQL in 21 Days that I find as a good complementary book, even if it is some years old. It handles more advanced topics like indexes in greater detail. In addition it has own chapters on stored procedures and triggers.

If you need a more recent book, go to amazon and search for
  • beginning sql
  • introduction to sql
  • sql tutorials
  • sql for dummies
  • sql teach yourself
  • advanced sql
and variations thereof. If you want the most recent books listed first, sort by: publication day

2. Performance

When you use a database system, there may be many bottlenecks that slow down your data driven application. Here are some:
  • Bad relational integrity or database model.
  • Application programming where the database has a faster solution. See 3. below for an example.
  • Inefficient queries.
  • Bad use of data types.
There are many ways to overcome these bottlenecks where some of the more advanced are indexes, stored procedures and triggers.

PostgreSQL 9.2.4 Performance Tips

PostgreSQL 9.2.4 performance related search

PostgreSQL general performance related search

http://www.esvon.com/pg/services/performance/

http://www.xaprb.com/blog/2006/07/04/ho ... mizations/

3. Application programming versus database manipulation - an example

The following example is taken from Rudy Limeback's book Simply SQL.

Code: Select all

/*  Chapter 8: The ORDER BY Clause  */

/*  Detail and Total rows  */

SELECT
  * 
FROM (
  SELECT
    customers.name    AS customer
  , carts.id          AS cart
  , items.name        AS item
  , cartitems.qty
  , items.price
  , cartitems.qty
        * items.price AS total
  FROM
    customers
      INNER JOIN carts
        ON carts.customer_id = customers.id
      INNER JOIN cartitems
        ON cartitems.cart_id = carts.id
      INNER JOIN items
        ON items.id = cartitems.item_id
        
  UNION ALL
       
  SELECT
    customers.name                  AS customer
  , NULL                            AS cart
  , CAST(COUNT(items.name) AS CHAR) AS item
  , NULL                            AS qty
  , NULL                            AS price
  , SUM(cartitems.qty  
        * items.price)              AS total
  FROM
    customers 
      INNER JOIN carts 
        ON carts.customer_id = customers.id
      INNER JOIN cartitems 
        ON cartitems.cart_id = carts.id 
      INNER JOIN items
        ON items.id = cartitems.item_id
  GROUP BY
    customers.name 
  ) AS dt
ORDER BY
  customer
, cart
, item
;
/* Results should look like this --   
   customer  cart  item            qty   price    total             
   A. Jones        2                              72.55
   A. Jones  2131  gimmick           3    9.37    28.11
   A. Jones  2131  thingum           2   22.22    44.44
   B. Smith        3                             287.51
   B. Smith  2921  dingus            3   29.37    88.11
   B. Smith  2921  whatsis           2   93.70   187.40
   B. Smith  3002  doohickey         1   12.00    12.00
   C. Brown        4                             342.94
   C. Brown  2937  thingum           1   22.22    22.22
   C. Brown  3001  thingamabob       3   22.22    66.66
   C. Brown  3001  whatsis           2   93.70   187.40
   C. Brown  3937  thingum           3   2.22     66.66
   D. White        1                              59.95
   D. White  3197  whatchamacallit   1   59.95    59.95
   E. Baker        2                              69.00
   E. Baker  2461  doohickey         2   12.00    24.00
   E. Baker  2461  whatnot           3   15.00    45.00
   G. Scott        1                             179.85
   G. Scott  3321  whatchamacallit   3   59.95   179.85
   H. Clark        2                             132.55
   H. Clark  3081  dingus            3   29.37    88.11
   H. Clark  3081  thingum           2   22.22    44.44
*/
Exercise:
  1. Try to figure out how you would have done the same with application code.
  2. Comment on the following statement (page 181 in the book)
    The UNION query that produces totals as well as details, and then interleaves them, is more complex than the simple detail query, but nowhere near as complex as the application programming required to achieve the same effect.

Post Reply

Who is online

Users browsing this forum: No registered users and 4 guests