Sorting 2d arrays
I have an app currently that needs a list sorted alphabetically with alpha-numeric chars. I am using a query and then narrowing the results into an array and then creating two lists for an autosuggest. Imagine searching by an alphanumeric ID (not SQL ID) and also by the description: 1234ii -- apple 1234i -- banana 123x -- orange orange -- 123x banana -- 1234i apple -- 1234ii Then once the sorting is completed, I need to grab the unique ID (incremental SQL ID) from the original array again and then use that to update the database. Currently I am building the array, sorting with quickSort2D(), building the list and then to grab the SQL ID from the array again I use a function that loops through the array to match ID ' -- ' text or vice versa. This takes about 20 seconds to build. Also, this is something that will change per page refresh so storing anything in memory is pretty much nil. Any thoughts on simplifying the process? Anyone have experience with speeding up array processes? ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:327249 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Sorting 2d arrays
Tony, Probably a ton of ways to do this. Would this get you what you want?: (note: the below would be for oracle, you need + instead of || for Sql server - not sure about what db you're using) cfquery datasource=whatever name=qMySuggestions select sql_id, alphanumeric_id || description as auto_suggest_descr from mytable union select sql_id, description || alphanumeric_id as auto_suggest_descr from mytable order by augo_suggest_descr /cfquery Now you should have a query with the results in the order you want them with the original sql_id also there. Now instead of looping through an array to do a match, you simply do a query of query: cfquery name=qFindMatch dbtype=query SELECT sql_id FROM qMySuggestions WHERE (put your condition here to find a match) /cfquery qFindMatch.sql_id is the record you want. Dave Phillips ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:327252 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Sorting 2d arrays
Okay I figured it was going to be a query based solution. Turns out there is a table in SQL Server that lists all chars in ASCII so I can sort based on that table's numeric values and then return two columns to output. Pretty RAD. here's the t-sql to generate it. --- set nocount on declare @integers table ([Number] INT) declare @i int declare @char varchar(255) set @i = 1 while @i 128 begin insert into @integers values (@i) set @i = @i + 1 end declare char_test cursor for select [Number] from @integers open char_test fetch next from char_test into @char while @@fetch_status = 0 begin print 'char #' + @char + ' = ' + char(@char) fetch next from char_test into @char end close char_test deallocate char_test ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:327263 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Sorting 2d arrays
Sorry, forgot to mention that the query above just returns the ASCII chars. Then I use a different query to classify the first char: DECLARE @ErrorNum INT SET @ErrorNum = 1 DECLARE @ TABLE ( [Id]INT, [Name] VARCHAR(255), [Number]VARCHAR(255), [_] VARCHAR(255), [_] VARCHAR(255), [SortOrder] TINYINT) ERT INTO@ SELECT a.[Id], a.[Name], CASE WHEN ISNULL(a.[Number], '') = '' THEN '' ELSE a.[Number] END, CASE WHEN ISNULL(a.[Number], '') = '' THEN a.[Name] ELSE a.[Name] + ' -- ' + a.[Number] END, CASE WHEN ISNULL(a.[Number], '') = '' THEN '' ELSE a.[Number] + ' -- ' + a.[Name] END, CASE WHEN ASCII(LEFT(a.[Name], 1)) BETWEEN 0 AND 47 THEN 1 -- symbols ordered first WHEN ASCII(LEFT(a.[Name], 1)) BETWEEN 48 AND 57 THEN 2 -- numbers ordered second WHEN ASCII(LEFT(a.[Name], 1)) BETWEEN 58 AND 64 THEN 1 -- symbols ordered first WHEN ASCII(LEFT(a.[Name], 1)) BETWEEN 65 AND 90 THEN 3 -- letters ordered third WHEN ASCII(LEFT(a.[Name], 1)) BETWEEN 91 AND 96 THEN 1 -- symbols ordered first WHEN ASCII(LEFT(a.[Name], 1)) BETWEEN 97 AND 122 THEN 3 -- letters ordered third ELSE 1 END FROM[dbo].[] AS a WHERE a.[Name] 'All s' There is more but you get the idea. Talk about rabbit and tortoise! ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:327264 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Sorting 2D Arrays
Just to spawn a little discussion here. The issue of sorting 2D Array's has come up and been discussed quite a bit here so I will give my feedback on the issue. This weekend I experimented and tinkered a bit and came up with a few different ways of doing this. The 'brute force' way I came up with was to use Three custom tags already in existence. Since a 2D Array models a Query rather closely EX: Query has Rows, and Columns 2d Array has Rows and Cols [Row1][Col1] = Name [Row1][Col2] = Address [Row1][Col3] = DOB [Row1][Col4] = Phone [Row2][Col1] = Name [Row2][Col2] = Address [Row2][Col3] = DOB [Row2][Col4] = Phone Since there is no direct way to handle an array via the CFX API (That I am aware of...) You can transform your 2D array into a query, given that you are *sure* your array has the columns equal each time.. CF does not enforce your arrays to have the same 'columns' per row since they are dynamic arrays. This leaves it to the programmer to ensure the data is ont he up and up, otherwise there would be no sane method for sorting a 2d array. Now with the array as a Query there is already a tag CFX_QuerySort qhich implements Quicksort which is going to be extremely fast. I implemented a quicksort and on my machine PIII 500 with 512MB of ram it sorted 32,000 random 8 character strings (all lower case) In 170 milliseconds or.. Less than 1/5 of a second So the sorting is going to be the least of the overhead. So available was everything needed to sort a 2D Array. There are two Custom tags written by Nate Weiss (Which I more or less wrote and then discovered the tags *ugh*) which are caled ArrayToQuery and QueryToArray. So my idea was to write a wrapper tag that calls a C++ Sorting routine and returns the data. My concerns in doing this were the fact that the data is being transformed like this Array --- Query --- Sort Query --- Array So there is the assocaited over head of Three data transformations from Array to Query back to Array. Working with large amounts of data that can amount to a lot of data swapping. Again the overhead of the actual sorting is probably the least of the processing time/overhead in this process because quicksort does a very minimum of data moving. I think there is a better way so here is what I want some discussion on :)) The trouble in implementing Quicksort in ColdFusion is that in C/C++ you are really just moving the pointers around and you access the entire data set via pointers to that data (in most cases) as this makes sorting ultra fast. There are only crude ways to achieve the effect of pointers in CF so that pretty much locks out implementing Quicksort in CF (or any really effecient sorting in CF) Another problem is the fact that CF is a Typeless language meaning a variable *can* be anything at any given time. So when something such as a comparison occurs foo LT bar The interpreter has to know what kind of variable it is somewhere or it has to track its type or "guess" the type any time you do anything typeish specific with it such as a struct operation for example. Which all leads to more overhead which makes writing your own sorting routines in CF less and less attractive. So my question is.. does anyone have a better idea for sorting a 2D array? The only place I truly see this leading is to avoid using Structs and Arrays that need to be sorted when possible. Jeremy Allen [EMAIL PROTECTED] [Insert Quarter] -- Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
