4

I've been running through some Classic ASP code recently and was wondering if there was a performance difference between connecting using:

Provider=SQLOLEDB;server=NTSERVERNAME\SQLEXPRESS;database=DBNAME;uid=USER;pwd=PASSWORD;

Or

Provider=SQLNCLI;server=NTSERVERNAME\SQLEXPRESS;database=DBNAME;uid=USER;pwd=PASSWORD;

I seem to recall that there is a performance impact with some provider options (mostly from the days of SQL Server 2000 and earlier), but I've not been able to find if that is the case with these two providers.

The only thing I've been able to find are articles explaining that the latter will let me use some of the new features in SQL Server 2005 (XML data type, etc.) and some other feature support issues - but nothing about performance.

5

I talked to Erland Sommarskog about this and here is his response.

I attended a presentation as PASS 2005 about SQL Native Client. The then-PM for SQL Server Client said that for ODBC they had achieved a 20% perf improvement, but for OLE DB, it was about even.

But since SQLNCLI provides more features, that is probably the provider to use. Then again, with old ADO a lot of the features are unavailable anyway.

HTH

| improve this answer | |
  • That helps. I'll probably go ahead and start using the SQLNCLI. – AnonJr May 25 '11 at 14:06
0

According to microsoft SQLNCLI is deprecated and MSOLEDBSQL should be preferred.

But I had a case where I had to use SQLNCLI in SSIS: I unexpectedly got some 'The OLE DB provider used by the OLE DB adapter cannot convert between types "DT_DBTIMESTAMPOFFSET" and "DT_WSTR" for ...' messages in SSIS dataflows.

After some debugging I found out that using MSOLEDBSQL, some datatypes do not get mapped correctly. A datetime2 or a datetimeoffset would be mapped to the SSIS datatype DT_WSTR instead of DT_DBTIMESTAMP2 and DT_DBTIMESTAMPOFFSET respectively.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.