SQL ODBC Connection query running longer than expected

I have  a SQL query that I want to run against MS SQL from cache ensemble using the SQL outbound adapter. If I run this query direct from MS SQL Studio it take about 7 seconds MAX and returns about half a million rows. The row only contains one column it is a number all same size and if I run this query in the production in ensemble it takes for ever the production finishes without getting the response back. The same query run in cache outside the ensemble environment writing results to a file it returns results taking about 3minutes to complete. But if I load the results into  a list or Record map it runs for ever is there a limit to the numbers of rows of data you can load to a record map or a list and what is it that I missing that might be causing this to happen? The file returned by this query is about 5.7mb