jtreher
asked on
bubble or LIFO or some sort of recordset sort
Hi Experts,
I was going along smoothly until I hit a rut. I've got the SQL guys giving me some SQL code, but this will require a huge rewrite of a CFC and reduce overall application efficiency.
I'd like to sort a recordset BEFORE outputting it. This cannot be done with SQL as I'm using a QoQ and it lacks any functions to handle such a task.
Here is my query
select *
from tbl_myTable
where tableID in (342,34,532,24,463,14,12,4
I need that order preserved, but of course, since they are IDs, they are returned as a numerical sort and my list order is lost.
I tried to do a bubble sort (I think if I can remember proper terms from the college days) but it took forever.
->copy current pointer row into temp struct, find correct row and move to current, replace found row with tempstruct data.
Maybe this was sloppy, but it took 2.5 seconds to run.
What I need to do is basically reorder those query results back to the original list order. Any high performance suggestions?
I was going along smoothly until I hit a rut. I've got the SQL guys giving me some SQL code, but this will require a huge rewrite of a CFC and reduce overall application efficiency.
I'd like to sort a recordset BEFORE outputting it. This cannot be done with SQL as I'm using a QoQ and it lacks any functions to handle such a task.
Here is my query
select *
from tbl_myTable
where tableID in (342,34,532,24,463,14,12,4
I need that order preserved, but of course, since they are IDs, they are returned as a numerical sort and my list order is lost.
I tried to do a bubble sort (I think if I can remember proper terms from the college days) but it took forever.
->copy current pointer row into temp struct, find correct row and move to current, replace found row with tempstruct data.
Maybe this was sloppy, but it took 2.5 seconds to run.
What I need to do is basically reorder those query results back to the original list order. Any high performance suggestions?
ASKER
Hey Trail,
I do indeed want it to remain sorted by the order of the IN list, however, it is currently returning them in numerical order - 4,12,1,24,34,342,463,532,6
So - I'm feeding this query a list of IDs ordered as I want and it is giving me back the list reordered numerically. Therefore, I need to sort them before I can output them. Indeed, this is a master-detail and all I have are sorted IDs which will give me access to a slew of details, but I need those rows to stay correctly ordered!
thanks
I do indeed want it to remain sorted by the order of the IN list, however, it is currently returning them in numerical order - 4,12,1,24,34,342,463,532,6
So - I'm feeding this query a list of IDs ordered as I want and it is giving me back the list reordered numerically. Therefore, I need to sort them before I can output them. Indeed, this is a master-detail and all I have are sorted IDs which will give me access to a slew of details, but I need those rows to stay correctly ordered!
thanks
You can do this....
<cfset orderList="342,34,532,24,4
<cfset Values=''>
<cfloop list="#orderList#" index="i">
<cfquery name="qry" datasource="testDB">
select *
from tbl_myTable
WHERE tableID IN(#i#)
</cfquery>
<cfset Values=ListAppend(Values,"
</cfloop>
<cfloop from="1" to="#ListLen(orderList)#" index="j">
<cfset ListValue=ListGetAt(Values
<cfoutput>#j#: </cfoutput>
<cfloop from="1" to="#ListLen(ListValue,'~'
<cfoutput>
#ListGetAt(ListValue,idx,'
</cfoutput>
</cfloop>
<br>
</cfloop>
yes this isn't all within the query... but it works and probably faster than the way you may be doing it now... maybe =)
give that a try and test the performance
<cfset orderList="342,34,532,24,4
<cfset Values=''>
<cfloop list="#orderList#" index="i">
<cfquery name="qry" datasource="testDB">
select *
from tbl_myTable
WHERE tableID IN(#i#)
</cfquery>
<cfset Values=ListAppend(Values,"
</cfloop>
<cfloop from="1" to="#ListLen(orderList)#" index="j">
<cfset ListValue=ListGetAt(Values
<cfoutput>#j#: </cfoutput>
<cfloop from="1" to="#ListLen(ListValue,'~'
<cfoutput>
#ListGetAt(ListValue,idx,'
</cfoutput>
</cfloop>
<br>
</cfloop>
yes this isn't all within the query... but it works and probably faster than the way you may be doing it now... maybe =)
give that a try and test the performance
ASKER
Roger.
So am I to take it that it is impossiible to sort a recordset post query?
Here is a big catch...I only want to this to run as one option. Sometimes I will be using an order by statement.
In case you are wondering, yes, this is part of my search engine and the list contains IDs from the search tag. I need the order of relevance preserved, which it isn't, but I caught before production. However, sometimes I will order by date, title, DESC or ASC and that works just peachy, performance is right on. I just never caught that my 'relevance' list was being reordered.
The thing about using loops is that could be a lot of queries, almost 500 for some searches.
So am I to take it that it is impossiible to sort a recordset post query?
Here is a big catch...I only want to this to run as one option. Sometimes I will be using an order by statement.
In case you are wondering, yes, this is part of my search engine and the list contains IDs from the search tag. I need the order of relevance preserved, which it isn't, but I caught before production. However, sometimes I will order by date, title, DESC or ASC and that works just peachy, performance is right on. I just never caught that my 'relevance' list was being reordered.
The thing about using loops is that could be a lot of queries, almost 500 for some searches.
>>So am I to take it that it is impossiible to sort a recordset post query?
No.
But without adding a sort field I don't know of a way to sort based on the IN order - esepcially since records are not returned in that order - without manually doing so.
You could do a loop over the query results and add into a new manual query in the order you desire, but that would be slow too..
No.
But without adding a sort field I don't know of a way to sort based on the IN order - esepcially since records are not returned in that order - without manually doing so.
You could do a loop over the query results and add into a new manual query in the order you desire, but that would be slow too..
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
sorry, perform a join on rowValue and sort by the row number - make sense?
ASKER
Tacobell - Well, the goal was to not go back to server because I already have a query cached in memory that basically needs a partial pull and an ordering of data.
I have a good strored proc from the SQL experts and I've even got a very structured XML bit, but I'd much rather resort the recordset if possible.
Sorry about the delay, I was fortunately without access this weekend.
Mrichmon - it seems that a second query is about as slow as repointing the recordset with a bubble sort. Doh! Too many loops.
Trail - That query option was pretty slow as well.
The big issue is that I have a lot happening with the cached query, so it runs once for 600ms every hour; I simply can't go back to the SQL server everytime someone does a search. I'm going to look at an output loop option to see if that will work...
I've got an unordered recordset with IDs. I have a list of the IDs in the correct order, something fast should be simple!
I have a good strored proc from the SQL experts and I've even got a very structured XML bit, but I'd much rather resort the recordset if possible.
Sorry about the delay, I was fortunately without access this weekend.
Mrichmon - it seems that a second query is about as slow as repointing the recordset with a bubble sort. Doh! Too many loops.
Trail - That query option was pretty slow as well.
The big issue is that I have a lot happening with the cached query, so it runs once for 600ms every hour; I simply can't go back to the SQL server everytime someone does a search. I'm going to look at an output loop option to see if that will work...
I've got an unordered recordset with IDs. I have a list of the IDs in the correct order, something fast should be simple!
ASKER
Just tried two for loops
one looped through list, the other the query to look for matching ids and build a temporary in memory table with querysetcell.
This took 1 second for max results - too long.
A storedproc might be my only way.
one looped through list, the other the query to look for matching ids and build a temporary in memory table with querysetcell.
This took 1 second for max results - too long.
A storedproc might be my only way.
Why can't you put the order on that query that runs before you cache it?
PS. 600ms is not even a second so why are you worried?
ASKER
Well, the 600ms could be hit multiple times per second, I'm worred about a queue building and crashing the server.
This list will change nearly every time the query is processed, so caching it would be pointless with an order by.
I think what I'm going to try is to build a temporary in memory table with with coldfusion or use a sql function and then do a join with a sort on the id field of the temp table.
This method most recommended by SQL people and you Tacobell.
I'll let you know how I fare!
This list will change nearly every time the query is processed, so caching it would be pointless with an order by.
I think what I'm going to try is to build a temporary in memory table with with coldfusion or use a sql function and then do a join with a sort on the id field of the temp table.
This method most recommended by SQL people and you Tacobell.
I'll let you know how I fare!
ASKER
Good call with the function.
I just created a recordset with the function, giving me access to the temp table as a join in my original query of the cached query.
Total performance cost: less than 10 millis on average!
I just created a recordset with the function, giving me access to the temp table as a join in my original query of the cached query.
Total performance cost: less than 10 millis on average!
you have this..where tableID in (342,34,532,24,463,14,12,4
so you're saying you want the output to sort in this order... "342,34,532,24,463,14,12,4
I'm just not sure how you want to order your output, is there other columns ur outputing? what's the original order?