Okay, folks: interesting theoretical question for you.
Okay, folks: interesting theoretical question for you.
If you were writing a C/S app in Delphi, and your back end were MS SQL Server, what data access technology would you use if your concern was purely speed and performance?
Again, I don't care about convenience, amount of code needed, features of the framework -- all I care about is performance.
And to taint the response a bit, I'm particularly interested in where you think raw ADO/MDAC fits into things.
If you were writing a C/S app in Delphi, and your back end were MS SQL Server, what data access technology would you use if your concern was purely speed and performance?
Again, I don't care about convenience, amount of code needed, features of the framework -- all I care about is performance.
And to taint the response a bit, I'm particularly interested in where you think raw ADO/MDAC fits into things.
Currently using "raw" ADO, but in the process of testing out migration to FireDAC. It will be some time before I can say something definitive about which one that perform better. We might not be able to tap all of FireDACs power, since the DB is modeled around using upsert procedures for saving data for various reasons.
ReplyDeleteNick Hodges and Lars Fosdal what do you mean by "raw" ADO? Do you mean the DbGo component wrapper or the pure COM/OLE objects?
ReplyDeleteA
Half-cooked in my case. We use the dbGo.
ReplyDeleteAndrea Raimondi The latter.
ReplyDeleteRaw speed? I'd use DevArt. They have components for native access (no intermediate layers) to most DB engines. In my speed tests on MySQL a few years back, DevArt MyDAC ran circles around the competition.
ReplyDeleteThe components are rock solid and the support is first rate.
"All I care is performance..." - I would put a layer between mssql and the client, with a protocol that optimizes performance based on knowledge about the application.
ReplyDeletedbGo sufficed in some apps I've worked on. There is one bug that never got fixed in all the years I used it though. The infamous Post on an Identity field would sometimes get the incorrect Id if there were triggers on the table that wrote to other tables with identity fields.
ReplyDeleteI agree with Kevin McCoy wrt DevArt as the Delphi DAC to use. However, I wouldn't go with a C/S solution. Better to at least have a middle tier / application service to broker requests from any type of client (not just Delphi) and deliver only what the client app needs.
ReplyDeleteThis offers more flexibility and consistent performance across clients, with reduced network traffic, as more of the "heavy lifting" resides on the server. It can also reduce client updates/rollouts when logic changes can be made in the application service, instead of the client application.
I suspect Nick Hodges has a specific thing in mind...
ReplyDeleteI have a legacy Delphi app that uses raw MDAC. I want to get off MDAC and on SQL Native Client, really, because I can't believe that raw MDAC is the way to go anymore. But whatever is fastest is what I'll look at.
ReplyDeleteI choose to stay with native client layer, no intermediates, I use ZeosDBO, open source library, good community support, can you ask more?
ReplyDeleteFireDAC uses the native protocol afaik.
ReplyDeleteHeinz Toskano I used ZeosLib before switching to DevArt. Zeos is very fast, but (at the time) had some quality issues and memory leaks. It also needs a DLL to connect to MySQL - and probably all the other engines it supports. DevArt is completely self contained, which is a big plus at deployment time.
ReplyDeleteKevin McCoy still using it, have no complains about. btw, it comes to its 7th version. the big advantage is that it is open source, if you found a bug and are willing, you can fix it.
ReplyDelete"speed AND performance"? :-)
ReplyDeleteIf you're already using dbGo & MDAC, switching to the Native Client is a pretty minor change -- just deploy the client and switch connection strings.
If you're thinking about hitting the raw APIs, don't forget that ODBC is MS SQL's One True Way(tm) now though. OLE DB is old and busted now.
My previous employer had licenses for DevArt's suite, but I didn't have a chance to benchmark it against dbGo for MS SQL. We were primarily a PgSQL shop, and dbGo -> ODBC -> PgSQL is just painful. That said, we were really happy with it's features and performance. I'd start by evaluating it.
Lars Fosdal For future reference: MERGE >> "upsert" sprocs.
Low level ADO is pretty fast (much faster than TADOQuery!) when you use the ADO "_Recordset" interfaces directly: 1000x single select takes about 20s (!) for TADOQuery, but 600ms for "_Recordset := ADOConnection1.Execute(sql)". I use this for our own ORM.
ReplyDeleteFireDAC (TADQuery) is much faster than TADOQuery: 1600ms but lower level TADConnection.ExecSQLScalar(sql) takes 500ms. And when I look at what FireDAC is doing internally (creating async interfaced objects, sql cursor animation, etc) I think you can get it much faster than 500ms.
By the way: FireDAC uses ODBC for MSSQL with native data types (so no variant casting!)
André Mussche FireDAC doesn't use the native client? Interesting.
ReplyDeleteNick Hodges I was under the impression that FireDac doesn't use the native client directly; it uses ODBC.
ReplyDeletePhillip Woon my understanding was that AnyDAC(the origin of FireDAC) used its own code to connect natively to the various RDBMes. I could be wrong though.
ReplyDeleteI researched it when I got FireDac for the first time in XE3, and discovered that we had to go through ODBC.
ReplyDelete