OLE vs ODBC?

[#1]

I have a report driver class that all of my reports go through. The class exports the report in some format, then closes and dispose of the Report object. My question is about the Driver...

(Microsoft SQL Server 2008 R2 is my DB.)

What would be the best Report Driver to use for the reports I am creating to run in this scenario? I have been using the "OLE DB (ADO)" driver.

In the VS 2010 Integrated report designer, the "ODBC (RDO)" selection doesn't seem to be an option (when I choose "Set Datasource location" there is no SQL Server driver listed). Obviously, I just don't have the ODBC driver installed and can easily be remedied....Although, if I could get the "ODBC (RDO)" to work as a DataSource, is there any reason to use it or some other method? Or, should I just stick with "OLE DB (ADO)"?

[former_member11696 · Accepted solution]

Hi Shayne,

MS SQL Server 2008 does not install their updated Client Tools by default. To get the MS SQL Server Native 10 driver you have to install it on the PC where your application is being run either by using the MS CD or if you search on MS's site you can download the 2008 Client Tools MSI and simply run that on your app server or local Desktop PC.

As for OLE DB and ODBC, MS SQL Servers client native driver uses the OLE DB driver. So if you use ODBC what you are doing is adding a layer on top of OLE DB.

CR to ODBC to OLE DB client engine versus CR to OLE DB client engine. So OLE DB should be faster.

The biggest improvement you can do though for either driver is to be sure to use indexes on your key fields in the database and be sure to use those fields in CR when designing your reports. Check with your DBA on how to create and how to optimize your database using Indexes.

Thank you

Don

[Former Member · Accepted solution]

Gotcha!

Thanks for the advice Don. I also happen to be the DBA and do have all the report parameters/sort fields and group fields defined with ther own indexes in the DB. It is amazing the performance difference.

I see no compelling to switch from OLE, looks like I made the right decision and it seems to be working great!

Shayne

[Former Member]

Hi,

We are using OLE DB instead of ODBC for our reports, main reason is simplicity.

You have to configure an ODBC connection on each computer that uses the report, an OLE DB connection is configured in the report and therefore doesn't require client side configuration.

Also to note, when you use an OLE DB connection, you can easy change the report database in your .Net application. You can find more information here on the forum.

Hope this helped!

Regards,

Cyrus

[Former Member]

Thanks for the feedback, Cyrus!

As I noted in my post, I am exporting all of my reports so there wouldn't we an issue with client setup.

I have read about people experiencing preformance issues with OLE, that would be my only concern and one of the reasons I decided to ask for feedback.

Thanks again!

Shayne

[Former Member]

Hello Shayne

Performance is one of those things best tested by the developer as there are way too many variables; DB client versions, DB client author (e,g,; there is an OLE DB driver for Oracle from MS as well as from Oracle), the database (Oracle vs. say MS SQL), etc., etc. A blanket statement that performance decreases is probably questionable.

Performance will also depend on the report structure (for example the more subreports, the worse the performance), OS, hardware, etc., etc.

And then of course a performance hit is in the eyes of the beholder. A report that takes 1/2 a second to complete with one connection type, may take 3/4 of a second or even on second to complete using another connection method. Is that significant? Perhaps. Perhaps not.

Ludek