Optimizing BizTalk SQL Adapter performance

On your receive port, there is a property called 'Poll While Data Found' , set this to True. This will ensure that the adapter will be polling the SQL DB continuously to retrieve data until there are no more rows (at that point it will actually switch over to the sleep mode for the duration of the polling interval). This will greatly help if you are planning to scale to handle large volumes.

Use stored procedures instead of direct SQLs to confine the Application Logic to the DB and for easier maintenance & security

Limit your maximum rows that are returned to a particular number by using the 'select top' e.g. select top 5 from orders where orderValue > 5000 order by orderValue - this eliminates accidental out of memory errors.