ADO Recordset::sort help

[nalan]

I have a disconnected ADO recordset, which I want sorted by more than one filed..
The follwoing is the sort i tried
m_qData.getRecordsetPtr()->Sort = L"TagName,DateTime,wwVersion DESC";

This works but the problem I have is that the memory consumption increases drastically, with the number of records in the recordset, and I am talking of millions of rows.
On one occasion, all my memory was used up and ADO produced an exception.
What alternatives do I have. SQL server sorting is better, and does not eat memory, which means I have to dump the Recordset into a sql server table , sort it and then get it back into the recordset..
Any suggestions??

[raghupathys]

Just curious,
but could'nt you retrieve the records in sorted order while retrieving the data from the db in the first place?

[nalan]

dear raghupathy,
sorry, I did not give full details. This recordset is a disconnected one which is created by merging two different recordsets. I have a merge function which will do this.
After merge I have to sort this. A simple sort by one column is fast, but when I sort by 3 different columns , ADO starts crying for memory, takes up almost 2 Gigs of memory and causes an exception.

nalan

[antares686]

Originally posted by nalan
I have a disconnected ADO recordset, which I want sorted by more than one filed..
The follwoing is the sort i tried
m_qData.getRecordsetPtr()->Sort = L"TagName,DateTime,wwVersion DESC";

This works but the problem I have is that the memory consumption increases drastically, with the number of records in the recordset, and I am talking of millions of rows.
On one occasion, all my memory was used up and ADO produced an exception.
What alternatives do I have. SQL server sorting is better, and does not eat memory, which means I have to dump the Recordset into a sql server table , sort it and then get it back into the recordset..
Any suggestions??

SQL sorting does use memory and tempdb space to sort the data. Just happens on the server and SQL may already have the memory allocated. Large disconnected recordsets are not usually a good idea unless you have the resources to cover them.

[nalan]

antares686,

You are right.
Since what I have is an ADO recordset and ADO does a very bad job sorting it, I had to find an alternative
I now dump the recordset into a global temp table in sql server,
and retrieve it out, sorted (Order by clause) from the same table using a recordset
Any better way ??

[antares686]

Are your data sources on the same server or coming from various locations. If the same then create a view on the server and save soem time and the temp table resource can be eliminated.

If remote sources you could potentially create linked servers on the SQL server and access the data thru a view still that way.

Can you layout your sources for us a bit (oracle, sql, access, etc)?

[nalan]

data sources are custom flat files, exposed by a OLEDB provider.
and these are from various locations..
I do have views.

I am not sure whether I have expressed well enough.. My problems is with my program, and not access to data. I posted this under this thread because this was related to ADO, but this is not a databse issue per say.
Do you think this should have gone under some other thread, like C++, or ADO.NET, but I am not using ADO.NET. If there was a thread for just ADO, I would have posted it there.

All I want is to sort an ADO recordset with millions of rows in it.
Since ADO sorting is bad , what is the best alternative??