SQLPLUS.net – an interview with Alan Hyneman

Introduction

If you are in search for a good ORM, keep reading. Alan Hyneman, the author of SQLPlus.net was very kind to give an interview and answer tough questions that a developer might ask. Feel free to ask more in the comments. For those in a hurry, the summary is at the bottom.

The interview

V: Tell us briefly about yourself and your passion for SQL Server.

A: I’ve been working as a software developer since the early ’90s, and the majority of my experience has been on the Microsoft Stack, which means I’ve been heavily involved with SQL for decades. That doesn’t make me a DBA, and I’m certainly not at your level, but I’m very competent with database design, as well as writing efficient SQL for CRUD operations.

V: How did you decide to write a new ORM? Have you needed one in your job and no existing ORM could fit the need?

A: Exactly. If you look at the options out there, you’re not limited to tools, but all the tools fall into one of two approaches.

1)      Tools that are what I refer to as full-featured, like Entity Framework or XPO. The developers of these tools thought it would be a good idea to remove SQL, or at least insulate the developer from having to use SQL. Now if you’re one of the millions of developers out there who have invested time to become proficient with SQL, these types of tools are certainly not working with you, or complementing your existing skills.

2)      Tools that are what I refer to as light-weights, like Dapper or DbConnect. These tools make using the native ADO stuff a little nicer, and typically perform better than the full-featured ones, but you have to write more code, and they introduce multiple points of maintenance. For instance, if you make a change to your database schema, those changes will go unnoticed until runtime. To fix your application, you typically have to change the query, as well as the objects that map parameters or consume result sets. So, while these tools get you closer to SQL, and perform better than the full-featured tools, you may be creating more work for yourself in the long run.

What I found, was with each of these tools, I had to make tradeoffs, and code wasn’t as clean or performant as myself or my colleagues would have liked. What I really wanted was the productivity of the full-featured tools, but the performance of the lighter-weight tools and that brings us to SQL+, which was designed from the ground up to complement the skills of SQL professionals. You start with a stored procedure, and by simply adding comments, you can generate an object-oriented class library. You get productivity on par with the full-featured tools and your generated class-library will outperform even the fastest light-weight tools.

Beyond that, additional tags allow you to add validation to the input parameters, set display properties enumerate return values, and you can even use them in tandem with resource files to create data services that support multiple languages. The object-oriented nature of the code gives you the ability to bind your input and output objects directly to UI components, streamlining the process down the development chain.

V: How simple is it to use? Can we have anyone from the street to read one page of instructions, and start using it? Ok, not anyone, a junior dotnet developer for example who knows to write simple procedures.

A: If a dotnet developer can write a stored procedure, they would be up and running in no time, but, what’s even more interesting, is any SQL developer, even if they have no experience with dotnet, can simply add comments to a stored procedure, and generate a dotnet class library. We even provide code snippets for Management Studio and Visual Studio to make the process of adding the comments fool-proof.

V: How fast it is? Can we do a “race” of several ORMs and see how they compare in terms of performance? For example, inserting (could also be update/del) a 1M rows: a rows/sec number is the measure of speed.

A: Yes, I did a video comparing Entity Framework, Dapper, and SQL+ that illustrates the level of effort to write the code, as well as a speed comparison test, and I invite you and your audience to have a look.

For simple CRUD operations we are about 400% faster than Entity Framework, and about 75% faster than Dapper. As the complexity of the CRUD operation goes up, so does the difference in speed.

The generated code uses native ADO and is mapped to concrete objects. It is as efficient as possible. In addition, the validation provided by the tags, is enforced in the service layer, making those services true gatekeepers of clean data, and preventing useless round trips to the database.

V: Will we have IntelliSense when using it?

A: Yes, input parameters are mapped to an input object with full support for IntelliSense. There is also a comment tag that you can place on input parameters which shows up in IntelliSense. The output objects are concrete classes, and the enumerated return values make it very easy to provide meaningful feedback to the users of the generated code.

V: What else can you say positive, why would you recommend this ORM over eg. Dapper or some other, what is the advantage, in short?

A: It’s a much better workflow for SQL developers, a group that the makers of the other tools have been neglecting for years. Write your SQL, add comments, and generate your code, and every piece of code down the development chain points back to a single source of truth. In addition, it is the only tool that facilitates the management of transient errors, something that will be more important as companies move their databases to the cloud.

V: Does it support table-valued parameters? That is quite handy for efficient, set-based processing.

A: Yes, it fully supports table-values parameters and your table type will be represented in the class-library as a concrete class with columns represented as properties.

V: If an architect decides all changes will go through stored procedures but wants SELECT queries to be stored intermingled with dotnet code, is that achievable? That means SQL code is not only stored as stored procedures in the database but also as ad-hoc queries from the app side. Having both options would be beneficial since the stored procedure is quite stiff when choosing the smallest set of columns to return that is needed for particular app code. The procedure returns always the biggest set of columns needed by potential callers.

A: At this time, we do not support ad hoc queries for the reasons I’ve mentioned above. Also, as developers get comfortable with SQL+, they find creative ways to avoid writing ad hoc queries so they don’t have to roll their own POCO’s, that said if enough people request a feature, the team will consider adding it. Also, there is a really nice way to handle multiple result sets, so if you wanted to create a procedure that returns say an Order, along with OrderDetails, you could do that and bind the output directly to your view and access the output.Order, output.OrderDetails and it’s very clean. (Note: see “future plans” question for a pleasant surprise)

V: Will the app break if the schema changes? Eg. a column is added to a table, a parameter (with default value) to a procedure, and procedure returns one extra column, and the app is not refreshed/compiled/changed in any way – will the app break in ORM calls?

A: It the stored procedure is broken, obviously, the service is broken. It the procedure signature changes, or result set(s) changes, yes you have to rebuild the class library. Right-click on the project, run the builder, your back in business.

V: Is there a trial, so anyone can try it for free how well it fits their need?

A: Yes, 30 days on the house, no credit card is required but we do need a valid email address. This allows us to communicate new features, any planned outages, etc.

V: What are the plans for the future, do you plan to add some interesting features or improvements?

A: Lots of new features coming to make SQL+ a complete ecosystem for SQL professionals who need to build robust data services. Things like custom validators and resource files, with a web-based UI to allow you to quickly assemble things that are tailored to your exact needs and make end-to-end software development easier and more efficient. We will support ad hoc queries from the app, in addition to stored procedures. I’m also looking into a Certification Program where I would vet SQL Professionals and list them on the site as resources to help companies implement services with SQL+.

Summary

SqlPlus.net is a stored-procedure driven ORM for SQL Server, which has a lot of positive sides. In addition it supports:

• Table-Valued Parameters – for fast, set-based processing, this is a life saver. Much faster than RBAR (row-by-row) processing. In some measurements around 1000x faster.
• Automatic retry – in case of transient errors, eg. during failover of cluster or AG, most other ORMs will throw an error and the user request is lost together with data. This ORM retries long enough to bridge common failover scenarios without any error. User will just see it as a “glitch” taking longer than usual, but data will get through without loss. If you plan a zero data loss app, this is a must, and here built-in by default.
• Stored procedure is the “source of truth” – add a few special TSQL comments, and the SP is visible in your dotnet app, together with intellisense and description of parameters.
• Ad-hoc queries – support for writing SQL from the app is planned for the near future releases. Developers will have freedom to choose will they use SPs, ad hoc SQL, or both.
• Validation at compile time – if a dotnet code fetches something that does not exist in DB (or makes other similar “sin” as not giving a value for a SP’s required parameter), you do not have to wait for QA to test, developer will know immediately at compile time. Results in higher code quality and less roundtrips between QA and devs, faster time to market.
• Performance is much faster than Entity Framework, and about the same or a bit faster than Dapper.
• Not free, but inexpensive for good support you get, and there is a 30d free trial and a money-back guarantee.