GameZone/CrticalPath
CS290F Fall 2006 - UCSB Computer Science - Thorsten von Eicken
Home | Overview | Architecture | CriticalPath | Optimizations and Analysis | Performance on multiple servers | Caching | Conclusion
[edit]
Critical Paths
Tested on AWS with 10000+ records in the database.
- Anonymous User Flow ( 25% of users)
- Home -> Shop Online(Default Sorted by Top Rating) -> Paginate -> View Product -> View Product Specification -> Back to Shop Online Page
SQL (0.236302) SELECT count(*) AS count_all FROM products Product Load (0.000751) SELECT * FROM products LIMIT 0, 10 Product Load (0.000357) SELECT * FROM products WHERE (products.id = '1') LIMIT 1 Product Load (0.000368) SELECT * FROM products WHERE (products.id = '1') LIMIT 1 Specification Load (0.000562) SELECT * FROM specifications WHERE (specifications.`catalogid` = 1990962391 ) LIMIT 1 SQL (0.236292) SELECT count(*) AS count_all FROM products Product Load (0.000750) SELECT * FROM products LIMIT 0, 10
Comment*** as our specs table ref catalogid field and not "auto inc" ruby id field :)
- Home -> (Search by Title) -> Paginate -> View Product -> View Product Specification -> Back to Shop Online Page (25% of users)
SQL (0.236948) SELECT count(*) AS count_all FROM products Product Load (0.000757) SELECT * FROM products LIMIT 0, 10 Product Load (0.316527) SELECT * FROM products WHERE productname LIKE %Microsoft% Product Load (0.000594) SELECT * FROM products WHERE (products.id = '2') LIMIT 1 Product Load (0.000347) SELECT * FROM products WHERE (products.id = '2') LIMIT 1 Specification Load (0.000545) SELECT * FROM specifications WHERE (specifications.`catalogid` = 1994221835 ) LIMIT 1 SQL (0.236576) SELECT count(*) AS count_all FROM products Product Load (0.000756) SELECT * FROM products LIMIT 0, 10
Comments*** this is the search routine.. We are planning to optimise it using MyISAM search method and bring the request time down. We also plan to limit the search to first N results.
- Home -> Shop Online -> View Reviews -> Paginate -> Back to Product Details (10% of users)
SQL (0.225341) SELECT count(*) AS count_all FROM products Product Load (0.000789) SELECT * FROM products LIMIT 0, 10 Product Load (0.000359) SELECT * FROM products WHERE (products.id = '2') LIMIT 1 Review Load (0.000617) SELECT * FROM reviews WHERE (reviews.`catalogid` = 1994221835 ) Product Load (0.000347) SELECT * FROM products WHERE (products.id = '2') LIMIT 1
Comment***: looks like this flow is pretty OK :)
- Registered User Flow (We Provide Sign In Option at all Pages)
- Home -> Login -> (Search by Title) / (Browse by Category) -> Paginate -> View Product -> Back to Shop Online Page -> Logout (10% of users)
User Load (0.000218)^[[0m ^[[0;1mSELECT * FROM users WHERE (users.`login` = 'keshav' ) LIMIT 1^[[0m Order Load (0.000363)^[[0m ^[[0;1mSELECT * FROM orders WHERE (status = "In-Cart") AND (orders.`user_id` = 1 ) LIMIT 1^[[0m SQL (0.225009)^[[0m ^[[0;1mSELECT count(*) AS count_all FROM products ^[[0m Product Load (0.000755)^[[0m ^[[0mSELECT * FROM products LIMIT 0, 10^[[0m User Load (0.000340)^[[0m ^[[0mSELECT * FROM users WHERE (users.`id` = 1 ) LIMIT 1^[[0m Product Load (0.338043)^[[0m ^[[0;1mSELECT * FROM products WHERE productname LIKE '%Microsoft%'^[[0m User Load (0.000315)^[[0m ^[[0mSELECT * FROM users WHERE (users.`id` = 1 ) LIMIT 1^[[0m Product Load (0.000592)^[[0m ^[[0;1mSELECT * FROM products WHERE (products.id = '197') LIMIT 1^[[0m User Load (0.000218)^[[0m ^[[0mSELECT * FROM users WHERE (users.`id` = 1 ) LIMIT 1^[[0m User Load (0.000227)^[[0m ^[[0mSELECT * FROM users WHERE (users.`id` = 1 ) LIMIT 1^[[0m User Update (0.011250)^[[0m ^[[0mUPDATE users SET `created_at` = '2006-11-16 20:20:45', `login` = 'keshav', `crypted_password` = '... SQL (0.236398)^[[0m ^[[0;1mSELECT count(*) AS count_all FROM products ^[[0m Product Load (0.000764)^[[0m ^[[0mSELECT * FROM products LIMIT 0, 10^[[0m
- Home -> Login -> View Product -> View Reviews -> Paginate -> Back to Product Details -> Logout (10% of users)
[4;36;1mUser Load (0.000334)^[[0m ^[[0;1mSELECT * FROM users WHERE (users.`login` = 'keshav' ) LIMIT 1^[[0m [4;36;1mOrder Load (0.000355)^[[0m ^[[0;1mSELECT * FROM orders WHERE (status = "In-Cart") AND (orders.`user_id` = 1 ) LIMIT 1^[[0m [4;36;1mSQL (0.236925)^[[0m ^[[0;1mSELECT count(*) AS count_all FROM products ^[[0m [4;35;1mProduct Load (0.000750)^[[0m ^[[0mSELECT * FROM products LIMIT 0, 10^[[0m [4;35;1mUser Load (0.000345)^[[0m ^[[0mSELECT * FROM users WHERE (users.`id` = 1 ) LIMIT 1^[[0m [4;36;1mProduct Load (0.000346)^[[0m ^[[0;1mSELECT * FROM products WHERE (products.id = '4') LIMIT 1^[[0m [4;35;1mUser Load (0.000224)^[[0m ^[[0mSELECT * FROM users WHERE (users.`id` = 1 ) LIMIT 1^[[0m [4;36;1mProduct Load (0.000368)^[[0m ^[[0;1mSELECT * FROM products WHERE (products.id = '4') LIMIT 1^[[0m [4;35;1mReview Load (0.000671)^[[0m ^[[0mSELECT * FROM reviews WHERE (reviews.`catalogid` = 1990612300 ) ^[[0m [4;35;1mUser Load (0.000215)^[[0m ^[[0mSELECT * FROM users WHERE (users.`id` = 1 ) LIMIT 1^[[0m [4;35;1mSQL (0.236288)^[[0m ^[[0mSELECT count(*) AS count_all FROM products ^[[0m [4;36;1mProduct Load (0.000778)^[[0m ^[[0;1mSELECT * FROM products LIMIT 0, 10^[[0m [4;36;1mUser Load (0.000333)^[[0m ^[[0;1mSELECT * FROM users WHERE (users.`id` = 1 ) LIMIT 1^[[0m [4;36;1mUser Load (0.000225)^[[0m ^[[0;1mSELECT * FROM users WHERE (users.`id` = 1 ) LIMIT 1^[[0m [4;35;1mSQL (0.000049)^[[0m ^[[0mBEGIN^[[0m [4;36;1mUser Update (0.000206)^[[0m ^[[0;1mUPDATE users SET `created_at` = '2006-11-16 20:20:45', `login` = 'keshav',... [4;35;1mSQL (0.236400)^[[0m ^[[0mSELECT count(*) AS count_all FROM products ^[[0m [4;36;1mProduct Load (0.000737)^[[0m ^[[0;1mSELECT * FROM products LIMIT 0, 10^[[0m
Comments*** We want to investigate storing user data in the session to reduce some queries per page.
- Shopping Cart Checkout Flow
- Home -> Login -> Shop Online -> (Sort by Top Price/Rating) / (Search by Title) / (Browse by Category) -> Paginate -> View Product -> View Product Specification -> -> Add to cart -> Checkout -> Logout. (20% of users)
SELECT count(*) AS count_all FROM products
SELECT * FROM products LIMIT 0, 10
SELECT * FROM products WHERE productname LIKE '%microsoft%'
SELECT * FROM products WHERE (products.id = '2') LIMIT 1
SELECT * FROM products WHERE (products.id = '2') LIMIT 1
SELECT * FROM specifications WHERE (specifications.`catalogid` = 1994221835 ) LIMIT 1
SELECT * FROM products WHERE (products.id = '2') LIMIT 1
SELECT * FROM products WHERE (products.id = '2') LIMIT 1
SELECT * FROM users WHERE (users.`login` = 'keshav' ) LIMIT 1
SELECT * FROM orders WHERE (status = "In-Cart") AND (orders.`user_id` = 2 ) LIMIT 1
SELECT * FROM users WHERE (users.`id` = 2 ) LIMIT 1
SELECT * FROM users WHERE (users.id = 2) LIMIT 1
SELECT * FROM users WHERE (users.`id` = 2 ) LIMIT 1
SELECT * FROM users WHERE (users.`id` = 2 ) LIMIT 1
SELECT * FROM orders WHERE (status = "In-Cart") AND (orders.`user_id` = 2 ) LIMIT 1
INSERT INTO orders (`status`, `date`, `pay_type`, `shiptoname`, `user_id`, `shippingaddress`)
VALUES('Shipping', '2006-10-09', 'Credit card','keshav',2, 'ejfhewf')
INSERT INTO ordered_items (`order_id`, `total_price`, `product_id`, `quantity`) VALUES(16, 48, 1994221835, 1)
SELECT count(*) AS count_all FROM products
SELECT * FROM products LIMIT 0, 10
SELECT * FROM users WHERE (users.`id` = 2 ) LIMIT 1
[edit]
Atomic Transaction
Product.transaction do
fail = 0
product_soldout = ""
for cart_product in @cart.items
if cart_product.product.numberavailable < cart_product.quantity
fail=1
product_soldout=cart_product.product.productname
break
end
end
if fail == 0
if @order.save
for cart_product in @cart.items
cart_product.product.numberavailable = cart_product.product.numberavailable - cart_product.quantity
cart_product.product.save
end
session[:cart] = nil
redirect_to_index("Thank you for your order")
else
render :action => :checkout
end
else
redirect_to_index("Oops!! Not enough "+product_soldout +" available , Please try later!")
end
end
During check out if the number of games ordered is greater than the number currently available (numberavailable) then the order is not completed. None of the games in the cart get ordered!.
