Why does some query become slower after some days, and updating statistics does not speed them up. The only way to have these queries fast as before is to download tables, drop tables and re-load tables. Why does this problem occur?(From 1851, version: DBMaker4.3)

~ 0 min
2016-01-12 03:33

This may be a special case about optimizer; DBMaker optimizer supposes the data distribution is Uniform in table. The optimizer can not find out merge join (the best execution plan) to QEP if the customer data distribution is skew, then it will adopt nested join to execute.
Because the data is skew, so after customer executing update statistics table_name simple=100 to get real statistic value, DBMaker optimizer also using nested join to execute.
Even create another index; the optimizer will take Primary Key as execution index.
But after unload and load, the default optimizer will use the default statistic value to make execution plan and it supposes the data distribution is uniform, so it will adopt the best execution plan.
This is not a bug; DBMaker has improved the optimizer formula to support all kinds of distributions of the data and to make the best execution plan.

Average rating 0 (0 Votes)

You cannot comment on this entry

Tags