Tuesday Sep 18

This morning is as regular; my husband came home around 7 in the morning. I told him our little one has low temperature last night. But he was OK in the morning; we thought that he is teething. Poor baby!

 My husband needs to work five days this week, so I have to take care of our two boys three week nights by myself which I am not happy. I feel stress sometime when it comes to week like this. If I don’t have to work overtime, I have no problem. Just I never know when production issues come and I have to work late. Then I don’t know what I am going to tell my boss because I can’t work with two kids like that.

Anyway, the situation was improved compare to before he had to work in North Florida and just came home once a week. I think I should be happy for that instead of complaining. I know things will get better from time to time. I just need to preserve. And I believe God is blessing us.

Idea at work to optimize one store procedure

What is the goal we are trying to optimize here?  Are we expecting the average time to run this store procedure is under 1s? 

I have an idea to optimize this store procedure.Here is the input it needs to calculate availability:

+ Operation hours

+ Calendar hours

+Holidays

+ Scheduled limit days for services, service group

+ Existing appointments

And the way we store our data is that if the branch inherits operation hours/calendar hours/holidays from its parent. We only store data for its parent. That’s why we need lot of recursive queries to get data for specific branch. 

In reality, I think we rarely change operation hours/calendar hours/holidays for a branch, so we can create a separate thread which is run every 15 minutes to calculate all the above data for all the branches and populate into one table. 

So it is time to get availability timeslot, we can query data from that table and merge with existing appointments to get the final timeslots. With this way, we can reduce tremendous amount of calculation we need to do in that store procedure. I believe this approach will speed up the performance.