I’ve been evaluating some of the database programming alternatives for my webapp. The results below suggest that a well built ORM may take twice as long as programming with SQL. A poorly built ORM could take 5-6 times longer.
These tests were done on the desktop because the BK activerecord doesn’t support CubeSQL. I didn’t have time to see if adding that support was trivial or not. From earlier tests it appears that the SQL times are fairly comparable from desktop to CubeSQL:
However the poorly built ORM does much worse on CubeSQL than on the desktop:
I was under the impression that ActiveRecord built Xojo classes that interact with the database. If that’s the case, I wouldn’t expect much difference between it and straight sql. Now, an ORM built using introspection, I certainly would expect a lot of overhead.
BK ActiveRecord uses introspection. I don’t think I revealed anything that folks didn’t expect. You trade some speed for convenient programming metaphors. In most desktop apps I doubt it would be perceptible.
ActiveRecord uses Introspection when registering the classes so it can check to see if the class is missing fields from the table and to figure out if data types match. Otherwise it’s standard Xojo database calls.
When AR really falls down is if you only need 1 field out of a record because it will load all fields off the bat. This is overkill for simple operations or in records that might contain an image (this is easy to workaround by putting images into a separate table). It also falls short with related table data in that you have to go out of your way to get that data (we do this via lazy loading).
FWIW, if CubeSQL is using the standard Xojo database class it should be trivial to add to ActiveRecord. There’s only a handful of places where it matters what database it is and that’s usually in making the PreparedStatement (does CubeSQL support prepared statements?) and doing the initial registration process.
Having autocomplete with table/field names and the compiler squawk with datatype mismatches are our two big reasons for using it. No fat fingering of table/field names or guessing what the field datatype is. It also forces us to put all of our db code in one spot rather than spaghetti code all over the project. YMMV.
Although I’d be much happier if Xojo would fix the (@#*& autocomplete with shared methods that return values. Doesn’t seem to work (at all) with namespace objects.
cubeSQL does not use the standard Xojo database prepared statements mechanism. They have their own class (CubeSQLVM). I actually extended AR to support cubeSQL. It was not terribly difficult. Re-jiggering the prepared statements in the subclassed databaseAdapter was the bulk of the work.
We decided against ORM for the project we were working on before I finished it, so the code is in need of some cleanup and further testing, but if anyone wants it, please let me know, and I’ll be happy to send it to you as-is…
Please send it in. We’ll clean it up and add it to the next release. We’re hoping to get ARGen update in the next week or two for Oracle and MS SQL Server. What’s another DB (other than one more to support)?
I would love to see Studio Stable Database. It uses the database class and is basically SQLite with a network component.
And I can say that ARGen is a lifesaver for $10. I have used it soo many times and it has cut hours and hours of time out since I didnt have to manually code the objects. I would feel the same way about the tool even if it were double the cost.
[quote=38035:@scott boss]I would love to see Studio Stable Database. It uses the database class and is basically SQLite with a network component.
[/quote]
I wouldn’t use SSD with a Web Edition application. Just use a local SQLite database with a connection for each Session. Your WE app is already doing all the “server” work that SSD Server does.
to date, I only have one WE app that is an API server that only uses sqlite for anytype of DBase. I have started playing with SSD for desktop apps. But because of Bob’s great implementation of AR, I find it hard to use DBs without it. Hence why I would like it added.
Done right (*), many developers would be shocked at how well SQLite does for web applications. Pretty much the only two reasons I wouldn’t use it as first choice now are if I needed multiple app servers or there was pre-existing data I needed to get to.
(*) All standard and imaginable disclaimers apply.
