OLEDB vs ODBC

system · February 17, 2003, 11:13am

I’m moving into a SQL Server environment for the first time in the near future.

What are the advantages of having OLEDB connections compared to ODBC?
What’s the easiest way of maintaining client connectivity for say 30 clients (i.e. the equivalent to TNSNAMES)?
Are there any common pitfalls that I should be aware of?

Many thanks.

anorak (BOB member since 2002-09-13)

system · February 17, 2003, 11:31am

One thing if I recall correctly that came up a couple of times is to use Array Fetch Size of 1 when using MS SQL server (otherwise you might get only partial results from the MS SQL Server DB without any warning).

Andreas (BOB member since 2002-06-20)

system · February 17, 2003, 2:28pm

Andreas is correct about Array Fetch Size. It must be set to 1 or it will not work correctly.

I have no proof, but I believe OLE DB to be faster than ODBC.

To get around SQL Servers limitations on outer joins, change these lines in the .prm file to look like this:

LEFT_OUTER=
RIGHT_OUTER=
OUTERJOINS_GENERATION=ANSI_92

Lee Drake (BOB member since 2002-08-15)

system · February 17, 2003, 5:14pm

OUTERJOINS_COMPLEX=Y may be a good idea too. Also, assuming you set the ANSI_92 option, I believe the descriptions for the cardinalities of your joins will be incorrect in Designer unless you set EXT_JOIN_INVERT=YES. These things are discussed in the BO SQL Server data access manual (accept you won’t see ANSI_92 mentioned, but this option does work).

Steve Nicoll (BOB member since 2002-08-16)

system · February 17, 2003, 8:15pm

I don’t know why, but I can confirm that OLEDB connections do perform better over the WAN. I have seen situations where universes took 10 minutes to import using ODBC and 10 seconds using OLEDB.

Steve Krandel (BOB member since 2002-06-25)

system · February 21, 2003, 1:55am

Hi,

I already use MS SQL 2000 with ODBC.

I hadn’t given oldeb a thought…

I would be interested to here what is the optimum solution for use with MS database products in conjuntion with both Webi and Full CLient. Oh, and Full Client on Citrix Metaframe too.

scott.thornton (BOB member since 2002-09-17)

system · May 13, 2003, 9:42pm

Very interesting. I remember posting a large post to the former BUSOB-L a couple of years ago detailing exactly that after I had a situation where rows were being dropped with an Array Fetch Size > 1 using an OLE DB connection to MS SQL Server 7.

Thinking about ODBC vs. OLE DB, and factoring this in causes two thoughts to arise:

[1] Might OLE DB connections choose to ignore an Array Fetch Size = 1 parameter and do it a smarter/better way automatically? I mention this because there doesn’t seem to be a huge performance hit with this setting (although that would make a good test in a WAN enviro)

[2] Would ODBC outperform OLE DB on medium to large data sets, across slow (lag) to medium slow environment due to the Array Fetch Size parameter? Might this be mitigated by OLE DB somehow just knowing how to do it better/smarter (think of SKrandel’s example above).

Anyone have any experience with this?

Thanks,

Dan Lelovic

dl_toronto (BOB member since 2002-08-28)

system · October 15, 2003, 2:24pm

Question:
Do I need a specific BusObjects Data Access Pack for MS SQL Server to use OLEDB?
I assume with the Generic ODBC Data Access Pack that I have installed right now I can only use ODBC and not OLEDB to MS SQL server, correct?

Andreas (BOB member since 2002-06-20)

system · October 15, 2003, 2:25pm

Actually, you shouldn’t use Generic ODBC with SQL Server either.

If you own the SQL Server connectivity, you can use OLEDB.

Steve Krandel (BOB member since 2002-06-25)

system · October 15, 2003, 2:33pm

Thank you, Steve:
I understand that, but my question is in order to use OLEDB do I need the MS SQL Data Access Pack (or maybe it is called OLE DB Data Access Pack) from BO (I have the three Data Access Packs installed: Oracle, DB2, and Generic ODBC)?

Andreas (BOB member since 2002-06-20)

system · October 15, 2003, 3:28pm

I think that BO 6.0 and 6.1 dont support OLEDB connections; Is this right?

Schuster (BOB member since 2002-08-29)

system · October 15, 2003, 3:41pm

Well, I am on BO v5.1.6

Andreas (BOB member since 2002-06-20)

system · October 15, 2003, 3:56pm

Then dont upgrade to 6.X; besides I think that 5.1.6 or 5.1.7 are more stable

Schuster (BOB member since 2002-08-29)

system · October 15, 2003, 4:01pm

That’s not the point (we might implement BO v6.x sometime in 2004):

My question is what exactly do I need on the BusObjects side to use OLE DB as a universe connection? Do I need a Data Access Pack for it? If so which one (SQL Server Data Access Pack or OLE DB Data Access Pack)

Andreas (BOB member since 2002-06-20)

system · October 15, 2003, 4:13pm

When we started using 5.1.X we didnt have access to SQL Server, then we decided to use SQL Server and we received a new data access key from BO to use also SQL Server (and OLEDB connections); maybe your license key just doesnt include access to SQL

Schuster (BOB member since 2002-08-29)

system · October 15, 2003, 4:17pm

Andreas,

Yes - You need the MS Access Pack in order to use OLE DB with SQL Server. Otherwise, only the generic ODBC interface is available. It works, (I have used it), but I would assume there is a reason why the Krandel says not to do so.

Note, that under the Microsoft Data Access Pack, you have the following features:

In there, you can see the ODBC and OLE DB for SQL Server. If you have a choice between a SQL Server Access Pack, and an OLE DB Access Pack, and the former is more expensive, it is likely because it comes with everything you see there, plus the MS Access ODBC Driver (which is cutoff in the picture). The latter might only include OLE DB.

Regards,

Dan Lelovic

dl_toronto (BOB member since 2002-08-28)

system · October 15, 2003, 4:23pm

Another thought on Generic ODBC vs. OLE DB -OR- MS ODBC for SQL Server:

Generic ODBC may select results correctly, but would be un-tuneable for SQL Server, whereas MS ODBC and MS OLE DB would each be tuneable, but OLE DB omits some layers in the stack over ODBC (along with the other known advantages such as not needing a DSN).

Someone jump in here and correct me if I’m wrong.

All that said, I believe you could get rolling with Generic ODBC on SQL Server provided you weren’t using complex joins, or other advanced techniques that might not work out correctly. For advanced access / tuning / performance, MS ODBC or MS OLE DB would be the way to go. Note that it is true that OLE DB is not supported in v6.1 however, so maybe MS ODBC is the way to go, which will allow an easier upgrade later.

Regards,

Dan Lelovic

dl_toronto (BOB member since 2002-08-28)

system · January 16, 2004, 2:37pm

Hi,

I have been testing to change the connection from ODBC to OLEDB (changed all connection in Supervisor + recreated the BOMain key using OLEDB).
When running full client the performance is improved a lot on a WAN network. But running ZABO on a WAN network the performance is much better using ODBC then using OLEDB.

How is that possible? Any ideas?

We are running Business Objects 5.1.4, SQL server 2000 and Windows 2000 Server…
/Tomas

sebjorkt (BOB member since 2004-01-16)