I have kept silent for quite a while, mostly because I was so busy test-driving Hekaton that I didn’t have much time for other things. And as Hekaton was under NDA for a long while I just couldn’t tell you anything about it. Well, that’s over now… And here is my first post on the topic:
As you might have heard by now Hekaton does not support large objects. (The limitation states that a row is not allowed to exceed 8000 bytes.) Microsofts comment on that is that normally large objects and in memory technology don’t go well together anyway, and you should maybe leave the BLOB in a relational table while putting the rest of the data in memory.
Well, … I disagree… There are some areas where having a BLOB in a latch-free, highspeed environment just makes a lot of sense. Dilema? No… Because there is an easy way to work around the limitation in the Hekaton engine. (Which by the way is the reason I didn’t oppose this limitation a lot harder…) The key idea is to split the BLOB on storing and reunite it on retrieval. Sounds a little stupid, but actually works like a charm, and even hell fast…So… Now that you know the concept you can for sure implement it yourself.
For those of you that are too lazy for that, or just don’t know what the hell I am talking about, here is the POC. It supports Store, Update and Retrieve of a BLOB in Hekaton, based on an nvarchar key. The way I implement this in my projects is to replace the original BLOB field with a reference key field (e.g. a GUID) and then use the sprocs in my script to actually handle the BLOB. It’s not as elegant as I would have liked it to be, but it works for all applications I came across so far.
Please bear in mind that this is a POC script only, it lakes error handling in most cases and for sure doesn’t cover everything. But it should get you a good idea of how it’s done. Therefore I take no warranty whatsoever for the script and what happens to your system by using it.
If you have questions about it, or about anything else in the Hekaton space for that matter please feel free to ping me.
Great post, and I loved seeing the gains you guys made at bwin it's interesting stuff.
ReplyDeleteCan you comment on what durability settings you use in your production environment that gave you such large gains and whether you see considerable performance increases when using DURABILITY = SCHEMA_AND_DATA ?