Will LLBLGen Pro ever support OLEDB and ODBC

If you want to ask: will it ever support any database out there, using odbc / oledb, the answer is no.

The provider type is not that of an issue. It's the meta-data retrieval which is a problem, and the database specific sql on the other side. Both can't be solved by oledb/odbc, but with custom code, therefore per database a specific driver and DQE have to be written, no matter which .net provider type they use, unfortunately.

It would be interesting to see the performance advantages of simply calling the underlying ODBC or OLE-DB libraries directly - as opposed to routing through the additional .Net data access libraries. It may or may not warrant the effort, not that it's more than a couple days work for a good C++ coder. But if perf is an issue, you would surely see some solid increases in performance as well as reclaiming DB-specific features that you lose with ADO.Net.

 --Doug Hettinger

A colleague jsut said this is not good since it would require C++ code. The intent was to say "call the underlying ODBC or OLE-DB providers with C# client code". So, the solution would be all .Net code, just more direct to the underlying database - through a lower-level provider where you can control more.

--Doug Hettinger

db providers consists of a couple of layers. .NET providers replace parts of the OleDb layer stack, and talk to the bare metal of the actual db provider. If you put C++ in between, .NET has to marshall out to unmanaged code first, which doesn't have to be faster. OleDb also creates overhead you don't need in ADO.NET, because it's not an OleDb host.

It depends on what .Net data access route you are using. Naturally, for SQL Server, it is in MS's best interest to maximize performance and so they bypass some of the DB-independence focused code that resides in ODBC and OLE-DB - although many years ago, MS claimed that the ODBC driver was just as direct as DB-Lib (the native C API to SQL Server). In any event, it is true that the SQLClient represents an optimized route to a SQL Server, as it doesn't have to traverse multiple access layers.

However, in cases where a single data access layer that can targe multiple databases is desired (a very common requirement in large corporations), ODBC and OLE-DB represent the highest performance solutions - short of creating a common interface over wrappers over the underlying native C-API's. The native C-API is the fastest access route to every DB that I'm aware of - definitely SQL Server and Oracle. However, most business level IT shops don't want to code in C++ - and that's fair enough. However, DB focused libraries can definitely achieve the best performance through direct calls to this C-API or even a good ODBC or OLE-Db driver. I've always challenged Java and .Net guys to show me a case where their wrapper libraries were faster. It simply is not the case.

The bit about the marshalling of to unmanaged code is just a big red-herring (a distraction without substance). It simply is not the case that .Net data access is faster than the corresponding C++ access mechanism, whether it be native DB API, ODBC, or OLE-DB, in any cirucmstance.

The fact is that all MS Server product SDK's are defined in terms of the C programming language - not in .Net. The .Net access route to every product simply maps to the native access route, which is defined in term of a C-API and unmanaged code.

Nice try though - most readers probably bought the initial reply. I mean, it sounds technical. :-)

--Doug Hettinger

Let me first say that I don't disagree with you, I just rehashed what MS fed my brain to believe (and I learned from reflector-ing the various .NET providers). I worked with ADO and C++ in the past using OleDb with sqlserver, and it was very performant. My reply was more what I thought could be a reason why .NET providers are faster in some situations, at least from .NET to the db. If you're in C++, using the OleDb providers is a no-brainer, though when you're on .NET, using the raw OleDb provider doesn't make sense.

Doug_Hettinger wrote:

It depends on what .Net data access route you are using. Naturally, for SQL Server, it is in MS's best interest to maximize performance and so they bypass some of the DB-independence focused code that resides in ODBC and OLE-DB - although many years ago, MS claimed that the ODBC driver was just as direct as DB-Lib (the native C API to SQL Server). In any event, it is true that the SQLClient represents an optimized route to a SQL Server, as it doesn't have to traverse multiple access layers.

It's indeed a very thin layer around their unmanaged lib, which I believe is also called from the OleDb unmanaged driver (SQLOLEDB) but I'm not entirely sure of that. Like Oracle does in ODP.NET, they very soon after the data arrives at teh client move to managed code to process it further.

However, in cases where a single data access layer that can targe multiple databases is desired (a very common requirement in large corporations), ODBC and OLE-DB represent the highest performance solutions - short of creating a common interface over wrappers over the underlying native C-API's.

Not ODBC, which is a layer on top of OleDb.

The native C-API is the fastest access route to every DB that I'm aware of - definitely SQL Server and Oracle. However, most business level IT shops don't want to code in C++ - and that's fair enough. However, DB focused libraries can definitely achieve the best performance through direct calls to this C-API or even a good ODBC or OLE-Db driver. I've always challenged Java and .Net guys to show me a case where their wrapper libraries were faster. It simply is not the case.

It depends on where you are. If you're in C# land, using a C-lib to access the db is slower. Try it . If you're in C++ land, OleDb is your friend. If you're undecided what to use, it might be C++ is faster, it also might be C# is faster (or VB.NET), due to the runtime jit-ing of code. I have to agree though that the initial promisses of managed code being faster than native code hasn't been a reality since java was introduced more than 10 years ago.

The bit about the marshalling of to unmanaged code is just a big red-herring (a distraction without substance). It simply is not the case that .Net data access is faster than the corresponding C++ access mechanism, whether it be native DB API, ODBC, or OLE-DB, in any cirucmstance.

yeah, you're right about that, except for ODBC. ODBC is slower. ODBC is also phased out.

The fact is that all MS Server product SDK's are defined in terms of the C programming language - not in .Net. The .Net access route to every product simply maps to the native access route, which is defined in term of a C-API and unmanaged code.

Not necessarily. With databases for example, on the client all you need to do is produce a network stream with commands and data and consume a network stream and propagate it upwards. Managed code can be faster when the JIT does its job properly, or better: if the code at hand is suitable for runtime-optimization, so the information, only available at runtime, can be fully used to optimize the code further. This can be utilized to optimize the managed providers over C++ ones.

For MS SqlServer, .NET is becoming a main access point with sqlserver 2005. The enterprise manager successor for example is also written in .NET, SQLDMO is gone. (not a good example though, as IMHO the new sqlserver 2005 management console is significantly slower ).

IMHO, if you look at the whole stack, thus from ado recordset to network stream on teh C++ side and from datareader to network stream, I don't think the NET stack is that inefficient, as the ADO stack is pretty deep. It has more features and some advantages, but as the ado designer once said: it became unmaintainable.

Otis,

Let me start by saying your customers are very fortunate to have you tend to this forum as meticulously as you do. And I'm getting the hang of the product now and believe it represents a very solid value - especially considering the price of many of the competitor tools in the marketplace.

However, I'm such a stickler for accuracy of information, i felt compelled to follow-up on this thread with just one more post. And let me also qualify that I am not recommending that everyone drop their LLBL Gen Pro and start coding in C++ or even C# to ODBC, OLE-DB, or even the native DB API. I personally believe the .Net approach, in conjunciton with LLBL Gen Pro of course :-) , peforms well enough for the vast majority of apps.

Anyway, a few observations and clarifications:

OTIS Wrote: "If you're in C++, using the OleDb providers is a no-brainer, though when you're on .NET, using the raw OleDb provider doesn't make sense."

Doug Responds: While accessing the "raw OLE-Db provider" does not make sense, but it makes alot of sense to alot of large corporations that employ multiple DB platfforms to use the .Net OLE-Db services. This should be obvious.

OTIS Wrote: "It's indeed a very thin layer [the SqlClient classes] around their unmanaged lib, which I believe is also called from the OleDb unmanaged driver (SQLOLEDB) but I'm not entirely sure of that. Like Oracle does in ODP.NET, they very soon after the data arrives at teh client move to managed code to process it further"

Doug Responds: This may just be due to the English translation (and I do compliment you on your language skills, I took French and German in high school and know how difficult it is to learn second languages), but the accurate depiction of the architecture is that both the .Net SqlClient and the raw OLE-DB driver simply call the native SQL Server API (dblib), which is a C-language API, as most all native API's are (the Oracle OCI is the counterpart).

OTIS Wrote: "Not ODBC, which is a layer on top of OleDb."

Doug Reponds: I might have let the whole thread die of natural causes until I read this. ODBC is in no way based on, or a layer on top of OLE-DB. Absolutely false. ODBC and OLE-DB are simply API specifications published by MS with token industry support. Having said that, they are both very good. However, they are just interface specs. ODBC was published over ten years ago and represents a C-language interface spec. About five years later, OLE-DB was published to fit in during the v-table interface days of COM and CORBA object technologies and added some of the flavors du jour. Hoowever, for most DB platforms, the ODBC driver was already written, and the OLE-Db provider simply mapped calls to the C code that implemented the already-written ODBC driver - but the better OLE-DB drivers were written fresh and called the underlying native C-API to the underlying DB directly. I've written ODBC drivers as well as OLE-DB drivers and have observed this lazy approach to OLE-Db driver development in commercial bundles of drivers. It is very common for the ODBC driver to out-perform the OLE-Db driver on a platform.

OTIS Wrote: "It depends on where you are. If you're in C# land, using a C-lib to access the db is slower. Try it . If you're undecided what to use, it might be C++ is faster, it also might be C# is faster (or VB.NET), due to the runtime jit-ing of code. I have to agree though that the initial promisses of managed code being faster than native code hasn't been a reality since java was introduced more than 10 years ago."

Doug Responds: Unless the developer writing the interaction with the C-Lib is a high school intern who only has a semester of VB under his belt, the C library will always be faster. But the .Net providers work fine for most business apps. It's good to see you admit that the optimization of code bit is total BS. Every site on microsoft.com that deals with real numbers and is not written by a marketing team always says managed code performs at 85%-90% of unmanaged code. My own observation leads me to call this optimisitic, but I'll accept those numbers.

OTIS Wrote: "yeah, you're right about that, except for ODBC. ODBC is slower. ODBC is also phased out"

Doug Responds: See a previous paragraph in this post. ODBC is usually faster, altohugh the best OLE-DB drivers (SQL Server has a good one) are written from scratch and perform as well as the ODBC driver. But in most cases, the ODBC driver performs better. Go to microsoft.com and see if ODBC is phased out. This is just the fluff they tell corporate IT shops to get them to migrate to .Net. The same story goes for MFC - and go to that site on microsoft.com. Too many ISV's have huge code bases using this stuff for MS to phase it out. You see, MS has two story-lines, one for corporate IT shops and one for ISV's. .Net ensures that customers don't leapfrog versions of Windows OS, while VC++, MFC, ODBC, and OLE-DB help to ensure that ISV's need not have to entirely re-write code-bases.

OTIS Replied: "Not Necessarily" to Doug's observation that "The fact is that all MS Server product SDK's are defined in terms of the C programming language - not in .Net."

Doug Reponds: Yes, maybe not necessarily, but that's the way Microsoft has, and continues to do things, even three years after the release of .Net. VS.Net is a replacement for VB as the RAD approach to development, not the choice of commercial software developers (ISV's, as opposed to corporate IT shops). All MS products have their defintive interfaces defined in terms of the C programming language (with C++ V-table definitions peppered among these header files). IIS, SQL Server, Exchange, MSMQ, AD, and all the other products.

Keep up the good work with the tool, your presence on the forum is a great aid to customers. I know you've answered alot of my questions. You obviosuly have a deep technical background.

 --Doug Hettinger

Doug_Hettinger wrote:

Let me start by saying your customers are very fortunate to have you tend to this forum as meticulously as you do. And I'm getting the hang of the product now and believe it represents a very solid value - especially considering the price of many of the competitor tools in the marketplace.

Thank you

However, I'm such a stickler for accuracy of information, i felt compelled to follow-up on this thread with just one more post. And let me also qualify that I am not recommending that everyone drop their LLBL Gen Pro and start coding in C++ or even C# to ODBC, OLE-DB, or even the native DB API. I personally believe the .Net approach, in conjunciton with LLBL Gen Pro of course :-) , peforms well enough for the vast majority of apps.

I must say, that some years ago, I thought C++ and ADO (oledb) was horrible to work with, and without smart pointers it really is a pain. Though once you get the smartpointers generated by visualstudio, it's a breeze. The performance was also really great. So, to clarify: I'm not against that combination. What I think is that it depends on the platform you're on: if it's native win32 and C++, oledb is the obvious choice. If you're in .NET, marshalling out to oledb on a high level is not performant (due to .NET).

Anyway, a few observations and clarifications: OTIS Wrote: "If you're in C++, using the OleDb providers is a no-brainer, though when you're on .NET, using the raw OleDb provider doesn't make sense."

Doug Responds: While accessing the "raw OLE-Db provider" does not make sense, but it makes alot of sense to alot of large corporations that employ multiple DB platfforms to use the .Net OLE-Db services. This should be obvious.

I disagree, if there's a native .NET provider for that db. Using the .NET Oledb provider for sqlserver for example is slower than using the native .NET provider for Sqlserver (SqlClient), as it has lesser layers to wade through. I don't see why the choice for platform X influences the db provider to use on .NET. Use what's best on that platform, not what's best on another platform, just because some toolkit uses the native oledb driver because it's written in C++.

OleDb is just a wrapper around the native client library, as the .NET provider is too.

OTIS Wrote: "It's indeed a very thin layer [the SqlClient classes] around their unmanaged lib, which I believe is also called from the OleDb unmanaged driver (SQLOLEDB) but I'm not entirely sure of that. Like Oracle does in ODP.NET, they very soon after the data arrives at teh client move to managed code to process it further"

Doug Responds: This may just be due to the English translation (and I do compliment you on your language skills, I took French and German in high school and know how difficult it is to learn second languages), but the accurate depiction of the architecture is that both the .Net SqlClient and the raw OLE-DB driver simply call the native SQL Server API (dblib), which is a C-language API, as most all native API's are (the Oracle OCI is the counterpart).

Correct.

OTIS Wrote: "Not ODBC, which is a layer on top of OleDb."

Doug Reponds: I might have let the whole thread die of natural causes until I read this. ODBC is in no way based on, or a layer on top of OLE-DB. Absolutely false. ODBC and OLE-DB are simply API specifications published by MS with token industry support. Having said that, they are both very good. However, they are just interface specs. ODBC was published over ten years ago and represents a C-language interface spec. About five years later, OLE-DB was published to fit in during the v-table interface days of COM and CORBA object technologies and added some of the flavors du jour. Hoowever, for most DB platforms, the ODBC driver was already written, and the OLE-Db provider simply mapped calls to the C code that implemented the already-written ODBC driver - but the better OLE-DB drivers were written fresh and called the underlying native C-API to the underlying DB directly. I've written ODBC drivers as well as OLE-DB drivers and have observed this lazy approach to OLE-Db driver development in commercial bundles of drivers. It is very common for the ODBC driver to out-perform the OLE-Db driver on a platform.

Ok, thanks for correcting me. I looked it up in MSDN and indeed, it says odbc is writing directly to the network stack, not using dblib. It was my understanding that odbc was a layer on top of oledb.

Anyway, ODBC is in QuickFixEngineering (QFE) mode at Microsoft for some time now, which means the plug is pulled, only fixes are added to it, no more new features, no more new things.

OTIS Wrote: "It depends on where you are. If you're in C# land, using a C-lib to access the db is slower. Try it . If you're undecided what to use, it might be C++ is faster, it also might be C# is faster (or VB.NET), due to the runtime jit-ing of code. I have to agree though that the initial promisses of managed code being faster than native code hasn't been a reality since java was introduced more than 10 years ago."

Doug Responds: Unless the developer writing the interaction with the C-Lib is a high school intern who only has a semester of VB under his belt, the C library will always be faster. But the .Net providers work fine for most business apps. It's good to see you admit that the optimization of code bit is total BS. Every site on microsoft.com that deals with real numbers and is not written by a marketing team always says managed code performs at 85%-90% of unmanaged code. My own observation leads me to call this optimisitic, but I'll accept those numbers.

two things: 1) the .NET provider, as we agreed on, is also a wrapper around the same code, dblib, as oledb. So 'faster' is only subjective: using the .net provider from native C++ is probably not a good idea and vice versa 2) managed code can be faster than native code, namely in those situations where execution path information at runtime is essential to make code optimizations. The .NET compilers at the moment do very little code analysis, they compile almost no JIT hints into the IL (I'm not sure about .NET 2.0, as it runs much faster). The philosophy is that any compile-time analysis can hurt runtime analysis. We all learned in compiler classes at the uni that's not true: a compiler can perform big compile-time code analysis to optimize code paths already. It though can be that at runtime, code which is taken into account by the compiler, is never called, so the JIT can make a big shortcut. This is theory at the moment, as the JIT simply has too less time to make this analysis at runtime. This is why managed code isn't faster than unmanaged code at the moment. Though I really wonder why they never let teh compiler fully analyze the code, and compile hints into the IL so the JIT has a much easier job at runtime, which MAY be make the JIT be able to actually perform these kind of analysis at runtime and be able to make the proper shortcuts. (It's now on a method basis, with inlining of methods as the basic optimization.). If I'm not mistaken, sun's Hotspot did this.

OTIS Wrote: "yeah, you're right about that, except for ODBC. ODBC is slower. ODBC is also phased out"

Doug Responds: See a previous paragraph in this post. ODBC is usually faster, altohugh the best OLE-DB drivers (SQL Server has a good one) are written from scratch and perform as well as the ODBC driver. But in most cases, the ODBC driver performs better. Go to microsoft.com and see if ODBC is phased out. This is just the fluff they tell corporate IT shops to get them to migrate to .Net. The same story goes for MFC - and go to that site on microsoft.com. Too many ISV's have huge code bases using this stuff for MS to phase it out. You see, MS has two story-lines, one for corporate IT shops and one for ISV's. .Net ensures that customers don't leapfrog versions of Windows OS, while VC++, MFC, ODBC, and OLE-DB help to ensure that ISV's need not have to entirely re-write code-bases.

Well, all I know is that ODBC is in QFE mode, (which I read some time ago, it might have been reversed now, I doubt it though). QFE means that it has no future. I'm not aware of a QFE for MFC, but I doubt any new projects are created in MFC, as they're not updated (if I'm not mistaken), though ATL is. (at least in vs.net 2003). Though why would a typical application be developed in MFC or even ATL today? It only will take more time to develop, more bugs (as you have more control over memory management, although smart pointers will help you here).

OTIS Replied: "Not Necessarily" to Doug's observation that "The fact is that all MS Server product SDK's are defined in terms of the C programming language - not in .Net."

Doug Reponds: Yes, maybe not necessarily, but that's the way Microsoft has, and continues to do things, even three years after the release of .Net. VS.Net is a replacement for VB as the RAD approach to development, not the choice of commercial software developers (ISV's, as opposed to corporate IT shops). All MS products have their defintive interfaces defined in terms of the C programming language (with C++ V-table definitions peppered among these header files). IIS, SQL Server, Exchange, MSMQ, AD, and all the other products.

That's changing now, slowly. For example SqlServer's DMO lib is gone, it's .NET now. I'm not sure if they now require C++ apps to write managed C++ to actually config sqlserver now, perhaps I missed something. Everything which had a native COM interface is slowly moved to .NET. I mean 'slowly' as it's not a process that's done in a year but takes longer.

Though I also have to say: if they don't get winforms up to speed within a year or so, interest in .NET will drop. At least that's my expectation. The main gripe I have with .NET at the moment is the utterly slow .NET winforms experience. It's so dogslow, it's not even funny anymore. I mean, when I open an entity in llblgen pro, it takes 3, 4 seconds to open the tab with the controls. (the first time). That's very slow. Just to load teh controls, compile them to native code etc and let the controls draw their grids. It's sluggish at best. I can't remember any of my VB5/6 interfaces was ever sluggish.

Keep up the good work with the tool, your presence on the forum is a great aid to customers. I know you've answered alot of my questions. You obviosuly have a deep technical background.

.