Jet vs. different database backends

[Travis]

Hi,

I am setting up plastic scm for the first time to help with a unity project.  We have just a small team of developers ( about 5-10 ) and 5 artists.  I'm looking for trade-offs and best practices for using jet as a backend vs using a RDBMS (i have team licenses, so mysql).  When does it make sense to use jet and when does it make sense to switch to a RDBMS.  Would you only evaluate with jet or use it for individuals and switch to mysql for a central server for the group?  Or can jet recommended for groups up to a certain size? Is the data safer from corruption using a RDBMS with multiple users?  Is one faster?

I'm sure there is documentation on this, but I can't find it.

Thanks for any help and suggestions.

[psantosl]

Hi Travis,

Short answer: you are good with Jet. It is the fastest.

 

Long answer: 

* For years we developed SQL-based backends. We developed a SQL Server layer, MySQL, SQL Server CE, Firebird, Firebird Embedded (was the default for years), SQLite (quite good performance in almost every scenario except heavy load), even Postgres (never optimized it) and Oracle (the connector we used was super bad and we ended up removing support for it, although the code is still there). Why we did this? Well, when we started coding Plastic back in 2005 we didn't think like reinventing the wheel. We were busy enough coding merge algorithms, GUIs, merge tools, diff tools and so on. And working on storage too was like too much. We performed tests, tweaked things here and there, and decided a stable database layer was going to be the way to go, building on top of rock solid software. Also, it was hard enough to convince companies they could trust a new version control player, so telling them their data was safe on a standard SQL storage seemed like a good idea.

 

* We never stopped optimizing database code. Things like bulk inserts in SQL Server, the equivalent on SQLite, better use of transactions, grouping inserts, temporary tables when doing many lookups... you know, everything we knew how to do or we learned in the way, we applied it.

 

* Years later we denormalized some data in the database. We stopped storing "trees" (directory structures) in tables as such but as blobs, like regular data. Doing 100k inserts to do a checkin of 100k files wasn't fast enough. Blobs were much better.

* Plastic was super fast already and we were able to consistently beat most version control competitors, specially under heavy load.

* But at one point we started thinking: hey, how long does it take to save 500k revisions to disk? Nothing. If you serialize the data correctly it takes nothing. And it was nothing compared to several seconds in a database. And what about reading? If we read using "pointers" (we do C#, but we use pointer-based code for this) we achieved super fast speeds. I mean, nothing new under the sun, of course reading/writing to disk is faster than doing the same on a database. But the database is reliable, rock solid, transaction aware, generic for all kinds of data...

* Finally some of our biggest customers in games had huge repos with 5-8TB in size, hosted in MySQL, and it started to say "enough". Some small issues here and there in MySQL, random slow downs.

* So we said: ok, we have been around for 11+ years, we covered the basics, we developed a bunch of tools, it is not 2005 anymore, we are not starting... let's go for our own ad-hoc storage. And we did it. And those 5-8TB repos were now handled like nothing. Literally nothing.

* Then we started moving all our major customers to Jet. Some of them have +1000 users on Plastic/Jet. They are much happier than when they used SQL Server. Easier admin, much faster performance, much faster backups.

* So we decided to make Jet the default.

 

So, did we discontinue SQL-based backends? No. We'll we do it in the future? Likely. Reducing the code base is always good, and focusing our performance efforts on Jet is better than trying to make 7 different database backends faster.

 

Ok, hope I answered your question ?

 

pablo

[Travis]

Great!  Thanks!  The history and roadmap give me a trajectory and a comfort level that your introductory documentation doesn't give.  So several TB repos with large user bases are just fine under Jet.  Cool!  I think it was the licensing structure that made me think the opposite.  As you go from single user to team to enterprise licensing your support for large back-end storage solutions increases, leading me to believe that those solutions were required.   Glad to know otherwise!