Poker Copilot Database Improvements

Some Poker Copilot users have been having sporadic hangs or really slow refreshes. I suspect this is happening for users with loads of hands in their database. Like 1 million+. So I’ve been doing some profiling of Poker Copilot’s database queries.

I’ve stumbled upon a couple of full table scans taking place. To translate into English “full table scan” is database-speak for “dog slow”. A simple tweak or two fixed this, and reduced the time it takes to import a million hands by about 10%.

More aggressive caching has given a further 10% improvement. I knocked another 2-3% off the import time by removing some columns that were being populated but never used.

I’ve also created a new denormalised table for Position Summary and Stake Level Summary. To translate again, “denormalised table” is database-speak for “table that has unnecessary redundant information but goes at light-speed”. For big databases, this reduces the time taken for Position Summary and Stake Level Summary updates by two orders of magnitude. Two orders of magnitude? That’s right, what was taking 25 seconds now takes 0.25 seconds. I suspect this is mostly happening because the denormalised table is small enough to be entirely held in memory.

The downside of the new denormalised table is that it adds about 10% to the time taken to import a million hands. But I think it is a good trade-off. On my iMac, importing a million hands takes a little under two hours.

These changes have not been straight forward. I find a problem. I implement a trial solution. I run a full import of all my test data, which takes about two hours. I then measure the results, not just for the import but also for the various summaries and filters. Sometimes I find the trial solution has made things worse. So I have to undo the trial solution and go to plan B. Then I repeat the whole escapade.

This takes time. Mostly spent waiting. Which is why I’ve been spending time on the Mac Poker Software mini-site experiment. I hope that the database profiling pays off to give all users a smoother, faster experience.

Last night, while letting a million hands load, I played two tables simultaneously. The HUD continued to update within a couple of seconds after each hand for the full duration.

I hope to release a test version of this update for you to try out within a few days.