Don't let OLE DB slow you down
Out-of-the-box sounds good to most, but to a trained ear it screams "poor performance". It could be the root cause of users receiving a "timed out" response when searching, or sluggish response when scrolling through search results. A quick way to try and resolve this poor performance is to use a native driver.
When creating a dataset within the Content Manager Enterprise Studio we can either pick SQL Server or Oracle as our database platform. Making a selection tells the solution which flavor of SQL to use, but not which driver to use. You get to pick that on the connections tab, as shown below.
The very first thing written in that text box is the provider (driver) to be used when connecting to the database. You can see here I'm using the SQLOLEDB.1 driver. That's the default. If I click the blue KwikSelect icon I can see the datalink properties dialog.
Clicking onto the Provider tab will show all of the available drivers. The highlighted one is the currently selected one for the connection string. So that "SQLOLEDB.1" equals the "Microsoft OLE DB Provider for SQL Server". That's the generic one that comes with Windows. It works but won't contain the unique features and refinements of your SQL Server version.
Since I have the SQL Server Native Client available, I should be picking that!
Picking the native client forces me to click next and then re-enter my database details. That's because I'll have new options and features available for my connection. The interface changes slightly (and more options are available off the advanced tab). I'll just re-enter what I had before and click OK.
Now if I save and deploy all is well. If I have multiple workgroup servers I have to deploy the native client onto all of them. I also have to match the native client version to the SQL server instance build (you should not use mixed builds). Same goes for Oracle environments.
Don't take my word for it though. Try it in your development environment and see for yourself (use a load test tool to simulate DB activity).