search

fidoandfido / blackswan

Blackswan Stock Game
Other
2 stars 0 forks source link

Adding trader level requirement - performance penalty #28

Closed fidoandfido closed 13 years ago

fidoandfido commented 13 years ago

Adding a trader level requirement means that a trader should only ever see things available to their level. This manifests itself as performance problem when retrieving the latest news or rumours. The old query to retrieve news events (ie sector announcements) was like this: select from quarterAnouncement where dataAvailable < Date(now) orderBy dataAvailable desc limit 10

This has been replaced with: select from quarterAnouncement where dataAvailable < Date(now) _and quarterAnnouncement.company.exchange.level < trader.getLevel() _ orderBy dataAvailable desc limit 10

This means that for every announcement we are joining to a company AND AN exchange, just to get the level. This is not ideal.

Possibly the simplest solution will be to denormalise the database - add a column containing the level to the quarterAnnouncement. Alternatively, some query optimisation could be undertaken, not sure how easy that would be in practice.

I will do some experimenting to see if query optimisations help; but adding the extra column probably won't be too difficult.

fidoandfido commented 13 years ago

Query optimisation didnt really help; it would still take a long time to do all the joining.

Denormalising the database is not too bad; on object creation the period quarter gets the value from the company, so it is fairly cheap and logical.