- 论坛徽章:
- 0
|
Rails sql延迟加载和自带缓存
Ruby代码
- color_lot_manuallies = color_lot.color_lot_manuallies
- if color_lot_manuallies.size == 1
- end
复制代码 引用- SELECT count(*) AS count_all FROM `color_lot_manuallies` WHERE (`color_lot_manuallies`.color_lot_id = 237)
复制代码 当有A has many B时,a.bs.size并不是检索出所有a.bs再求出size,而是直接用select count(*)来计算结果。这应该是延迟加载,有点类似named_scope,但是并不是named_scope,named_scope的结果类是ActiveRecord::NamedScope::Scope,但是A.bs.class的结果是Array,这部分看了下源码但是并没有看懂。
所以当我这样写的时候:
Ruby代码- color_lot_manuallies = color_lot.color_lot_manuallies
- if color_lot_manuallies.size == 1
- end
- color_lot.color_lot_manuallies.each do |i|
- puts i.id
- end
复制代码 引用- SQL (0.6ms) SELECT count(*) AS count_all FROM `color_lot_manuallies` WHERE (`color_lot_manuallies`.color_lot_id = 200)
- ColorLotManually Load (0.1ms) SELECT * FROM `color_lot_manuallies` WHERE (`color_lot_manuallies`.color_lot_id = 200)
复制代码 这样的话rails使用了延迟加载造成两个sql不同,所以后面。color_lot.color_lot_manuallies再次使用时,不会使用缓存的sql。
改成这样:
Ruby代码- color_lot_manuallies = color_lot.color_lot_manuallies.all
- if color_lot_manuallies.size == 1
- end
- color_lot.color_lot_manuallies.each do |i|
- puts i.id
- end
复制代码 引用- ColorLotManually Load (0.1ms) SELECT * FROM `color_lot_manuallies` WHERE (`color_lot_manuallies`.color_lot_id = 198)
- CACHE (0.0ms) SELECT * FROM `color_lot_manuallies` WHERE (`color_lot_manuallies`.color_lot_id = 198)
复制代码 加一个all,color_lot.color_lot_manuallies.all,这样的话,后面再次调用时,会使用rails的sql缓存。
Ruby代码- #PurchaseOrder
- has_one :purchase_order_marketing, :dependent => :destroy
- has_many :purchase_invoices, :dependent => :destroy,:through => :purchase_order_marketing
- #PurchaseOrderMarketing
- has_many :purchase_invoices,:dependent => :destroy
-
- PurchaseOrder.first.purchase_invoices.all(:select => 'purchase_invoices.id')
复制代码 引用- PurchaseOrder Load (0.2ms) SELECT * FROM `purchase_orders` LIMIT 1
- PurchaseInvoice Load (0.8ms) SELECT purchase_invoices.id FROM `purchase_invoices` INNER JOIN `purchase_order_marketings` ON `purchase_invoices`.purchase_order_marketing_id = `purchase_order_marketings`.id WHERE ((`purchase_order_marketings`.purchase_order_id = 13))
复制代码 一条sql就完成了,真强大。
Ruby代码- #article
- f sizes
- sizes = []
- art = self
- sf = art.article_secondary_feature
- size_group = SizeGroup.find_by_id(sf.size_groupid) if sf
- sizes = size_group.sizes if size_group
- return sizes
- end
复制代码 Ruby代码- ArticleMarketing.first.article.sizes.all(:select => 'id')
复制代码 引用- ArticleMarketing Load (0.3ms) SELECT * FROM `article_marketings` LIMIT 1
- Article Load (0.8ms) SELECT * FROM `articles` WHERE (`articles`.`id` = 43)
- ArticleSecondaryFeature Load (0.7ms) SELECT `article_secondary_features`.* FROM `article_secondary_features` WHERE (`article_secondary_features`.article_id = 43)
- SizeGroup Load (0.6ms) SELECT * FROM `size_groups` WHERE (`size_groups`.`id` = 2) LIMIT 1
- Size Load (1.0ms) SELECT id FROM `sizes` WHERE (`sizes`.size_group_id = 2)
复制代码 find和find_by_id
Ruby代码- params[:root].classify.constantize.find_by_id(params[:id])
- params[:root].classify.constantize.find(params[:id])
复制代码 引用- Opportunity Load (0.1ms) SELECT * FROM `opportunities` WHERE (`opportunities`.`id` = '126') LIMIT 1
- Opportunity Load (0.1ms) SELECT * FROM `opportunities` WHERE (`opportunities`.`id` = 126)
复制代码 find_by_id的SQL多了一行limit 1,对于sql语句而言,有一点性能上的提高。不过有时候我们需要find来捕捉异常。如果能用find_by_id最好了。
都改成find_by_id
引用- Opportunity Load (0.1ms) SELECT * FROM `opportunities` WHERE (`opportunities`.`id` = '126') LIMIT 1
- CACHE (0.0ms) SELECT * FROM `opportunities` WHERE (`opportunities`.`id` = '126') LIMIT 1
复制代码 这样都有缓存了。rails的自带的缓存是很脆弱的,B.find_by_id(a.id)方法变成a.b时,这个缓存就不会用上。同样前面的例子里改成
Ruby代码- color_lot_manuallies = color_lot.color_lot_manuallies.all
- ColorLotManually.find_all_by_color_lot_id(color_lot.id)
复制代码 rails自带的缓存也不会用上。
关于||=缓存,参考
http://fuliang.javaeye.com/blog/827321
http://www.javaeye.com/topic/810957
但是有一点,||=不会自动清除或者更新,所以使用的时候还是要注意点,可能会引起取值错误,而且不会报错。
Ruby代码- class PortOfDischage < ActiveRecord::Base
- def _name
- @_name ||= self.city
- end
- end
复制代码 Ruby代码- Reloading...
- => true
- >> p=PortOfDischage.first
- SQL (0.2ms) SET SQL_AUTO_IS_NULL=0
- PortOfDischage Load (21.2ms) SELECT * FROM `port_of_dischages` LIMIT 1
- PortOfDischage Columns (1.7ms) SHOW FIELDS FROM `port_of_dischages`
- +----+------------+-----------------+---------+---------+--------------------------------+--------------------------------+
- | id | ap_list_id | ap_marketing_id | city | country | created_at | updated_at |
- +----+------------+-----------------+---------+---------+--------------------------------+--------------------------------+
- | 1 | 2 | 2 | Piraeus | Greece | Thu Oct 07 07:10:21 +0800 2010 | Thu Oct 07 07:10:21 +0800 2010 |
- +----+------------+-----------------+---------+---------+--------------------------------+--------------------------------+
- 1 row in set
- >> p._name
- => "Piraeus"
- >> p.update_attribute(:city,'p')
- SQL (0.2ms) BEGIN
- ApMarketing Columns (43.0ms) SHOW FIELDS FROM `ap_marketings`
- ApMarketing Load (18.6ms) SELECT * FROM `ap_marketings` WHERE (`ap_marketings`.`id` = 2)
- PortOfDischage Update (45.5ms) UPDATE `port_of_dischages` SET `updated_at` = '2010-12-30 13:08:47', `city` = 'p' WHERE `id` = 1
- SQL (55.5ms) COMMIT
- => true
- >> p._name
- => "Piraeus"
- >> p.city
- => "p"
- >> p.reload
- PortOfDischage Load (0.7ms) SELECT * FROM `port_of_dischages` WHERE (`port_of_dischages`.`id` = 1)
- +----+------------+-----------------+------+---------+--------------------------------+--------------------------------+
- | id | ap_list_id | ap_marketing_id | city | country | created_at | updated_at |
- +----+------------+-----------------+------+---------+--------------------------------+--------------------------------+
- | 1 | 2 | 2 | p | Greece | Thu Oct 07 07:10:21 +0800 2010 | Thu Dec 30 13:08:47 +0800 2010 |
- +----+------------+-----------------+------+---------+--------------------------------+--------------------------------+
- 1 row in set
- >> p._name
- => "Piraeus"
复制代码 |
|