A SQL developer came to me. There was a report timeout issue. The same report ran fine during the passed two years.

I looked at the stored procedure that generated the data, and noted there were only two parameters passed in, BeginDate and EndDate. These two parameters basically filtered the data to make sure that the returned data set would not be too large. However, the report finally got the timeout issue because the size of table grew, there were no indexes on these two columns, and a table scan was used by SQL engine.

The lesson is that the SQL index strategies are different between OLTP and OLAP. We should add proper additional indexes on OLAP if necessary, and cannot simply rely on the existing indexes on OLTP.

References:

  1. http://datawarehouse4u.info/OLTP-vs-OLAP.html
  2. Search sql oltp vs olap using search engine
Advertisements