here's one possibility:
if it's not parameter sniffing as mentioned above, which uses a bad execution plan so it's slow,
it might actually be the data and the time it takes to move the data over the wire (or wireless)
i believe if i select 10 billion rows from a table with QA or SSMS, it doesn't really fetch all 10 billion rows...it executes the query, but really grabs the top few hundred rows, and as i scroll down, it fetches more and more rows...
it doesn't do it all at once.
but if i try to move those ten billion rows to a dataset, it's gotta move all ten billion rows over the network instead of the top few hundred, and then the local disk has to allocate some space in memory for the dataset, which might get paged to the harddisk because there;s not enough ram....
how big is the recordset you are grabbing.
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!