ok. that's a reasonable answer (i've seen posts for pulling in 5 million records for the report)
well, it probably isn't sql server, since it would still be holding 1 million records in memory as a temp table (I would check if the speed difference is in SQL server by running the query or just in CR trying to process the output).
if it's not SQL Server(and I'll bet it isn't...though it will take a long time to display a million records, it will load it into a temp table is seconds) and you can create a stored proc (or maybe a view) I would try that route and let SQL Server filter your recordset instead of CR.
Standard statement...let SQL deal with the large sets of data, it's designed to that, tends to be on a more powerful box. Let CR deal with the display of the record set, and not the filtering of the data.
I do realize that not all report writers have the ability to create stored proc (either because they haven't done that before or because they don't have rights to), but when you can, I think that it is the way to go.
IP Logged
