Hello Dear Reader! I had recently written a blog for the PFE blog, "How Many Tables Can I Have In SQL Azure DB & SQL Server". It was a fun blog that I had written during a very long layover when traveling home. It covers the maximum number of objects that you can create in SQL Server.
In this blog I had written up a demo to create the maximum number of tables you can in a database. I also explained that based on table creation rates it would take around 64 days for me to reach that limit. As a point of interest I tried creating In-Memory OLTP/Hekaton tables vs. native T-SQL tables. There I noticed something very interesting. Creating In-Memory OLTP tables was slower than creating T-SQL tables. A lot slower.
To be crystal clear this is specifically on the creation of tables nothing more. This has nothing to do with the insert, deletion, or reading of data. My blog was about how many OBJECTS could a database hold, and I had a tight loop creating tables over and over again. I explored different options to get as many tables created as quickly as possible.
"So Balls", you say, "You said this was slow. How slow?"
Great point Dear Reader, back to the question!
I was able to create 388 native T-SQL tables per second and only 3 In-Memory OLTP tables. On the book of faces someone asked about the results very quickly.
I reached out to my colleague Robert Dorr (Blog) from the SQL Team. He and Bob Ward, from the SQL Tiger Team, (@BobWardMS | Blog) are running a great blog called Bob SQL, https://blogs.msdn.microsoft.com/bobsql/ , you should check this out.
He was kind enough to write up a reply that covered how things work under the covers. To read his blog click here.
QUICK SUMMARY
Each In-Memory table is managed compiled code. By making In-Memory objects compiled this allows us to save CPU cycles and allows us to skip some of the traditional over head of native T-SQL objects.
When we issue the DDL to create an In-Memory table we follow the same processes as a native T-SQL table. We also do two additional steps we create the Access DDL code and we compile the Access DDL code.
These steps give us a one time hit on creation of an object that give us long term benefits as we execute against our In-Memory OLTP/Hekaton tables.
WRAP UP
There are nothing but great questions out there Dear Reader. The worst kind of question is the one that is not asked. In this case the question pushed me to look for an answer that I already wanted to know.
In this case a demo that I almost didn't write, a question from a place that I normally do not look for questions, are all that we needed to get an answer. My thanks again to Robert Dorr for taking the time to give us the answer.
I love it when a plan comes together. Until next time Thanks for stopping by.
Thanks,
Bradley Ball