Price of Indirection
time to read 2 min | 366 words

How goes that old saying?

Anything in computers can be solved by adding another layer of indirection, except too many levels of indirection.

I find myself contemplating that a lot lately. I'm fighting performace troubles quite a bit lately. And right now my pet devil is this creature and its friends:

CREATE VIEW LastBusinessDayTransactions

AS

      SELECT * FROM Transactions

      WHERE TransactionDate = (SELECT max(TradeDate) FROM TradeDates

            WHERE TradeDate < (SELECT max(TradeDate) FROM TradeDates))

This is supposed to be a view that will return the transactions from the lats business days (assuming that the max trade date is the current date).

The issue is that doing anything on this view is slow. Selecting isn't bad, but inserting / updating is very slow. I'm pretty sure that it's considered bad manners to update / insert to views, though.

What interest me is that even selecting speed can be significantly improved by pre-calculating the last trade date value outside the select statement. So far I haven't found a way to do this using a view (and it has got to stay a view, unfortantely.

Tweet Share Share 0 comments
Tags: