Monday, October 16, 2006

SQLserver performance tricks

I came across this post at Adam's website on using TOP and ORDER BY to achieve intermediate materialization of inline view.
I promised Adam in the comments I would pass it on and I have, via email to my DBA mates who I am sure haven't seen this technique and now here.

This is a very good technique, I was going to type trick however a trick is something which seems to conjue up an image of magic, once you use this trick yourself enough it will become a skill.

Adam's article whilst not mentioning it also shows the iterative processes of tuning, at times the steps can be large and seem weird to the outsider i.e. how did you go from here to there without all those intermediate steps.

I tried Adam's technique on MYSQL 5.0, maybe I am missing something with MySQL however it couldn't get MySQL to use the derived table on the join... sigh.
I noticed the documentation (MySQL calls them derived tables not inline views) mentions the fact they are always evaluated for optimisation!?!



Simple join

mysql> explain select a.userid,count(*) from ratings a
-> join ratings b on a.movieid = b.movieid
-> group by a.userid;
+----+-------------+-------+--------------------+--------------------+---------+------------------+--------
| id | type | possible_keys | key | key_len | ref | rows | Extra
+----+-------------+-------+--------------------+--------------------+---------+------------------+--------
| 1 | index | ix_ratings_movieid | ix_ratings_userid | 5 | NULL | 100165 | |
| 1 | ref | ix_ratings_movieid | ix_ratings_movieid | 5 | movie1.a.movieid | 28 | Using where;
Using index|
+----+-------------+-------+-------+--------------------+--------------------+---------+------------------+

2 rows in set (0.00 sec)

Using INLINE view

mysql> explain select a.userid,b.theCount from ratings a
-> join
-> (select movieid,count(*) theCount from ratings b group by movieid) b on a.movieid = b.movieid
-> ;
+----+-------------+------------+-------+--------------------+--------------------+---------+--------
| id | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+-------+--------------------+--------------------+---------+--------
| 1 | | NULL | NULL | NULL | NULL | 1682 | |
| 1 | ref | ix_ratings_movieid | ix_ratings_movieid | 5 | b.movieid | 28 | Using where |
| 2 | index | NULL | ix_ratings_movieid | 5 | NULL | 100165 | Using index |
+----+-------------+------------+-------+--------------------+--------------------+---------+--------
3 rows in set (0.11 sec)


Using INLINE with ORDER BY n LIMIT m

mysql> explain select a.userid,b.theCount from ratings a
-> join
-> (select movieid,count(*) theCount
-> from ratings group by movieid order by movieid limit 2147483647) b on a.movieid = b.movieid
-> ;
+----+-------------+------------+-------+--------------------+--------------------+---------+---------
| id | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+-------+--------------------+--------------------+---------+---------
| 1 | NULL | NULL | NULL | NULL | 1682 | |
| 1 | ref | ix_ratings_movieid | ix_ratings_movieid | 5 | b.movieid | 28 | Using where |
| 2 | index | NULL | ix_ratings_movieid | 5 | NULL | 100165 | Using index |
+----+-------------+------------+-------+--------------------+--------------------+---------+--------
3 rows in set (0.11 sec)



Have Fun