So far I have always hesitated to take any SQL task. But currently I have no choice but have to take up task to optimize the most complicate store procedure in our project. I think this is a chance for me because I have a chance to work with a DBA and learned so much from him. I optimized the store procedure which takes about 6s to run, now it only takes 0.67s. Here is what I have changes
+ Remove all temporary tables. Because every time when it accesses temporary tables it needs go to the disk. So this will increase IO cost.
+ Use table in memory instead.
+ Remove recursive views and put in physical tables: because every time it calls those view, the view will be recalculated for its data.
+ Dont have function around index columns in query then it will break the index and scan all tables again.
+ Remove 2 loops which recursive into each row of two tables by a range query
No comments:
Post a Comment