Sometimes you can find little gems in unexpected places, like the SharePoint Designer DataSource functionality.
For my current customer I was developing an itemdetail application where they had a mix of own data (stored in SharePoint) and a few tables from a SQL-SERVER.
They have a SP2010 Standard Edition. The solution was a homepage where I can search between 30 000 items and a link to a custom page with the details.
The link was build with a dataview webpart where I added a unique identifier as a querystring parameter.
At first I was using BCS: making 4 external Content Types, 4 external lists. They can be placed on the page and connected to the querystring parameter to filter the item. That goes great when you have a few items, but when you are looking at 30K items, things are becoming quite slow.
In Standard Edition SharePoint, you don’t have the BCS Webparts, so you cannot use parameters to query the exact item you need. I only found a way to get the item via a load of all items, and then a filter of those items.
That is when I discovered SharePoint Designer DataSource.
You can create a new datasource very easy, just provide a sql server, username and password and you can create your query. You have the possibility to add parameters (like querystring parameters).
When you have created that datasource, you can just add it via a dataview webpart. Easy as pie!
My page now loads in about 1 second. Easy to say I am a happy hippo!
More info on SharePoint Designer DataSources:
Datasources
Issues and workarounds
I actually think this is a brilliant conclusion. BCS can be an annoying middle man at times.
For instance, right now I have to write a timer job as a workaround to a limitation in the BCS's ability to sync People Field types from a database, (Managers for User Profiles from a LOB). Not supported through BCS.