Monthly Archives: September 2021

SQL optimization – long tail affecting performance

It often happens that you need join multiple tables (more than two) into one and do some computation. the you will find many instances are long tails. All other instances complete, long tail instances still running. For example, normal instances may 1 hour completed but long tail may more than 24 hours even worse it halt there.

For example, write a sql to do text information retrieval, ie, a query document table to match a target document database, with constrain of only selected query and target document pair are needed to calculate score.

Let saying 3 tables are query, target, candidate. Normally, you can join 3 at one time, like

Select other-score ……
from query q
Join target t
On q.word=t.word
Join candidate c
On q.id=c.query_id
And t.id=c.target_id
……

In big data, query, target and candidate maybe millions, which is normal. But word distribution along document is very skew, following 80-20 rule or Pareto principle. It will cause above join operation having serious long tail issue.

In my practice, some normal instances completed about a few hours, but long tails running more than 1 day and still halting there without any progress.

After analyzing, change 3 join at one time into first join query and candidate, and their result join target. Only this logic change, the task complete at 1 hour without any long tail, data skew. Comparing with before , the task cannot successfully completed even after running 24 hours.

It is amazing performance improvement.

Electric vs non-electric pressure cooker

In my kitchen, I have a set of pressure cooker, a 4.5L and 2L. It is non-electric.

Electric pressure cooker has more functions you can select on menu. But frankly, most of time, we use default setting. But I like non-electric pressure cooker. I do not want to worry the fault of sensor. (I have bad experiences of buying rice cooker. Just after 2 weeks, sensor is fault and water leaks into panel.)

When I wandered the Shaw center shopping mall many years ago, I found pressure cooker promotion. It is Fissler brand. About 700 Sgd for 2 set package. It is heavy, thick steel. Looks good. This was first time I bought so expensive cookware. But it is worthy.

It is safe and easy to use. I used them often, cooking Chinese braised beef with soy sauce, cooking soup, cooking Chinese congee. I used more than 5 years, it still work and there is no any fault like electric cooker.