Hello,

I use FileSystemObject to get files collection for some folder( Files
property )
How do I sort this collection by name by date and etc.
I can build sort logic of course but maybe there are easy standard ways ?

thanks
Vilius

Vilius Mockûnas schrieb:

(1) use .Run or .Exec to do a "dir /O ..." command and use its
     output: simple, fast, but not very flexible (what hides behind
     your "and etc"?)

(2) put the objects of the files collection in an array, write a
     sort sub/function that uses the relevant property/ies for
     comparison: a lot of work

(3) put the properties of the objects in the files collection in
     a disconnected ADO recordset, use its .Sort method to solve
     your problem: easy and flexible

  Here's a basic script that alphabetizes all file
names in C:\Windows. It's not terribly complex,
but it would get more tricky if you want to sort
by date. This particular QuickSort sorts non-case-
sensitive. (Otherwise you get all capitalized
names first.) For dates, as an offhand guess, I
think you'd want to convert the dates to numeric
and then use another version of QuickSort in
which you drop the Ucase operation. (The sorting
uses > comparison, so it can be adapted to both
words and numbers.)

   I was actually just exploring sort routines a few
days ago. Interesting stuff. On my old Win98SE
machine with a Sempron 1,800 Mhz CPU, a basic
QuickSort can sort close to 30,000 words per second
in a VBScript, and it doesn't seem to slow down with
increasing size. (The more well known bubble sort,
by comparison, is lucky to do 1,000 in a second and
quickly slows to the point of unusability as the array to
sort gets bigger.

'--------------------------------------------------
Sort all files in C:\Windows alphabetically
'------------------------------

Dim FSO, oFol, oFils, oFil, AFils(), i2
Set FSO = CreateObject("Scripting.FileSystemObject")
Set oFol = FSO.GetFolder("C:\Windows")
  Set oFils = oFol.Files
    ReDim AFils(oFils.count - 1)
      i2 = 0
       For Each oFil in oFils
          AFils(i2) = oFil.Name
          i2 = i2 + 1
       Next
   Set oFils = Nothing
  Set oFol = Nothing
 Set FSO = Nothing

 QuickSort AFils, 0, 0

Dim S2
  S2 = Join(AFils, vbCrLf)

  '-- watch out for this. An oversize message box
  '-- sometimes puts the close button offscreen. :)
  MsgBox S2

Sub QuickSort(AIn, LBeg, LEnd)
  Dim LBeg2, vMid, LEnd2, vSwap
      If (LEnd = 0) Then LEnd = UBound(AIn)
    LBeg2 = LBeg
    LEnd2 = LEnd
     vMid = UCase(AIn((LBeg + LEnd) \ 2))
      Do
          Do While UCase(AIn(LBeg2)) < vMid And LBeg2 < LEnd
             LBeg2 = LBeg2 + 1
          Loop
          Do While vMid < UCase(AIn(LEnd2)) And LEnd2 > LBeg
             LEnd2 = LEnd2 - 1
          Loop
            If LBeg2 <= LEnd2 Then
               vSwap = AIn(LBeg2)
               AIn(LBeg2) = AIn(LEnd2)
               AIn(LEnd2) = vSwap
               LBeg2 = LBeg2 + 1
               LEnd2 = LEnd2 - 1
            End If
     Loop Until LBeg2 > LEnd2
       If LBeg < LEnd2 Then QuickSort AIn, LBeg, LEnd2
       If LBeg2 < LEnd Then QuickSort AIn, LBeg2, LEnd
End Sub

If .NET is installed, use the .Sort method of a
CreateObject("System.Collections.ArrayList") object.
If not, do the ugly vbscript sort junk.
If you can get a directory listing to a file, use the directory options /OD
to sort.
If not, sorting by file date could get ugly.

news:uOpH2baIKHA.4436@TK2MSFTNGP04.phx.gbl...

  That's an interesting logic: Loading a 200MB+
dependency, that won't be on all machines, is
less "ugly" than 20-odd lines of VBScript "junk"? :)

Eric schrieb:

AFAIK, you can use the ArrayList only with one-dimensional arrays (with
items of the same type). So sorting a file collection (essentially a
table) will be difficult, perhaps even ugly in the sense that you
have to do silly things - like putting the sizes right aligned in the
front of the strings or formatting the m/d/y dates to make them
sortable.

Why do you say that? What is wrong with mayayana's quicksort?

Using dir will force you to shell out for each order you need.

The best method - in my opinion - would be the ADO approach.

[...]

news:4a8db4bd$0$31331$9b4e6d93@newsspool4.arcor-online.net...

I would use the ADO disconnected recordset and the Sort method provided with
that object. I use it often. However, I don't know if it is faster than
mayayana's sort.

--
Richard Mueller
MVP Directory Services
Hilltop Lab - http://www.rlmueller.net
--

  I'd be interested to know how that works,
and what it's strengths are. I've never used
ADO before. Would you have code or links
you could post?

   I never looked into sorting until recently.
When I went searching I came across this
page, with code by Ellis Dee, who seems
to be somewhat of a specialist in sorting:

http://www.vbforums.com/showthread.php?t=473677

   He presents a number of methods for VB6,
which were easily adaptable to VBS. I tested
4 of the most promising, along with the bubble
sort method that's best known. I finally settled
on QuickSort as being the fastest for sorting
words alphabetically. And conveniently, it's also
one of the most compact methods. (I wanted to
avoid using a method that spans more than 1
function.) My 5 test scripts, along with explanation
and my results, are here:

www.jsware.net/jsware/scripts.php5#sorting

   Frankly, I have a hard time grasping the logic
of why some of these methods work so well.
There is a remarkable amount of variation... and
there are a surprising number of methods available.

news:OTxXWFfIKHA.3632@TK2MSFTNGP05.phx.gbl...

That's an interesting link. When I've coded my own I've always used a bubble
sort. I assume the Knuth shuffle refers to Donald Knuth, so I may have that
in his book.

I don't know what algorithm ADO uses, but I always assumed they took the
effort to find an efficient one. Even though I hate to require .NET, I found
a .NET function that was faster than other methods I tried by a factor of at
least 2 (and sometimes 10). This pasted from a post by Tom Lavedas last Dec.
1:
================
thought I'd mention that there is a
sort object available to script if one is willing to make use of
the .Net framework, for example ...

' List object available to scripting from .Net framework
Set aDataList = CreateObject("System.Collections.ArrayList")

aDataList.Add "A"
aDataList.Add "E"
aDataList.Add "D"
aDataList.Add "F"
aDataList.Add "B"
aDataList.Add "C"

' Just for demonstration ...
s = ""
For Each sItem in aDataList
s = s & sItem & vbNewline
Next
wsh.echo "Before:" & vbnewline & s

' Sort
aDataList.Sort()
'aDataList.Reverse() ' the last shall be first

' Convert to an array
s = ""
For Each sItem in aDataList
s = s & sItem & vbNewline
Next
aList = Split(s, vbNewline)

' Just for demonstration ...
wsh.echo "After:" & vbnewline & s

I haven't tried, but I suspect it's faster than the fully scripted
version. Clearly it is simpler code
===========
An simple example using ADO and a disconnected recordset follows:
===============
Const adVarChar = 200
Const adSmallInt = 2
Const adDBTimeStamp = 135
Const MaxCharacters = 255

' Setup disconnected recordset.
Set objDataList = CreateObject("ADODB.Recordset")
objDataList.Fields.Append "Description", adVarChar, MaxCharacters
objDataList.Fields.Append "Number", adSmallInt
objDataList.Fields.Append "Index", adSmallInt
objDataList.Fields.Append "Date", adDBTimeStamp, MaxCharacters
objDataList.Open

' Create a few records.
objDataList.AddNew
objDataList("Description") = "Start"
objDataList("Number") = 4
objDataList("Index") = 3
objDataList("Date") = #8/15/2007#
objDataList.Update

objDataList.AddNew
objDataList("Description") = "Start"
objDataList("Number") = 4
objDataList("Index") = 2
objDataList("Date") = #8/18/2007#
objDataList.Update

objDataList.AddNew
objDataList("Description") = "Start"
objDataList("Number") = 4
objDataList("Index") = 1
objDataList("Date") = #8/17/2007#
objDataList.Update

objDataList.AddNew
objDataList("Description") = "Middle"
objDataList("Number") = 2
objDataList("Index") = 3
objDataList("Date") = #8/15/2007#
objDataList.Update

objDataList.AddNew
objDataList("Description") = "End"
objDataList("Number") = 3
objDataList("Index") = 3
objDataList("Date") = #8/15/2007#
objDataList.Update

objDataList.Sort = "Number,Index"

' Display sorted values.
objDataList.MoveFirst
Do Until objDataList.EOF
    Wscript.Echo objDataList.Fields.Item("Description") _
        & "," & objDataList.Fields.Item("Number") _
        & "," & objDataList.Fields.Item("Index") _
        & "," & objDataList.Fields.Item("Date")
    objDataList.MoveNext
Loop
objDataList.Close
==========
Generally you already have a recordset and you populate the diconnected
recordset in a loop, so you don't have so much code.

--
Richard Mueller
MVP Directory Services
Hilltop Lab - http://www.rlmueller.net
--

Searching and sorting are closely related.  A comprehensive book on the
subject was written back in the 1970's:
http://search.half.ebay.com/knuth-searching-sorting_W0QQmZbooks
There are a lot of other sources on sorting algorithms and their benefits
and shortcomings and formulas on how the time for sorting similar sets of
objects varies with the number of items being sorted.  Some algoriths have
widely varying execution times for a given number if items, depending on how
they are initially ordered.  Other algorithms, such as Shell sort, have
similar execution times no matter what the initial order is.

Quicksort is so quick because it splits the dataset into two subsets between
which is a value correctly positioned; it then recursively works on each of
the subsets, doing the smaller subset first to conserve stack space.  Often
a different algorithm is used for a subset containing only a few items.

QBasic (I think that is what it was called) that came with later versions of
MSDOS included some fun graphic with sound examples that demonstrated how
some common sorting algorithms work as well as their relative speeds.  They
were designed with 50-100 MHZ computers; you would need a way to slow down
modern computers by a factor of 20 to 100 to be able to appreciate what you
see and hear.

-Paul Randall

news:OTxXWFfIKHA.3632@TK2MSFTNGP05.phx.gbl...

    I'd be interested in .Net if it were all scriptable
COM, and if the runtime were not so gigantic. But as
it is, like Java, .Net seems to have very little purpose
outside of corporate intranet applet writing. I don't
have either the JVM or the .Net runtime installed
myself. I've never had any reason to do so, so it
would just be adding extra bloat and security risks
for no reason. And I wouldn't assume that others have
it installed. ...Which is not even getting into the
gargantuan resource load and initial lagtime that
System.Collections.ArrayList must be involve. (I
assume the whole 200+ MB of the .Net runtime
gets loaded for that call; and .Net software is
notoriously slow to get started because of that.)

   But I suppose if one is writing only to a known
target audience that's known to be running .Net
software, then there's no reason not to
use what's available.

----------

  With the code below: I don't do much with databases
and have never used ADO. So I find it hard to make
sense of what you wrote. The idea here is that you
have to create a record with several fields for each
item? And create a database for those records? In my
own sort tests I've been dropping a text file onto a script,
which uses Split(filetext, " ") to create an array of words
to sort. How does such an array get put into records?
Would it be something like:

For i = 0 to UBound(a)
  objDataList.AddNew
    objDataList("Word") = a(i)
 objDataList.Update
Next

OR

For each fil in oFils
  objDataList.AddNew
    objDataList("Word") = Fil.Name
 objDataList.Update
Next

  Does that make sense? I'm not clear
about which fields are required or what
"Number" and "Index" are for.

   And what do you mean by, "generally
you already have a recordset"? I was assuming
that the point was that ADO sorting could be
used to sort something like an array efficiently,
without necessarily having a database. Can it
really be more efficient to create a database
to be sorted, rather than just sorting an array?
The QuickSort I posted is virtually instant up
to several thousand items. It only took about
2 1/2 seconds to sort 70,000+ words.

   Yes. I didn't do very extensive testing myself but
I did notice that. Bubble sort, especially, seems to get
slower and slower as the number of items increases.
Though with the exception of bubble sort, the methods I
was testing don't seem to matter all that much until one
gets into the hundreds of thousands or millions
of items. If a sort routine can sort, say, 10,000 items
in 150 ms, it may take 300 ms on the second run and
220 ms on the third. VBScript is just too crude to get
high accuracy in tests on that scale. But even though
there's a 200% difference in the range of results, they're
all essentially instant for most purposes. It's unlikely
that I'll ever need to sort more than a few hundred items. :)

I use ADO to sort recordsets resulting from queries of Active Directory or
of SQL Server databases. You are correct that if the data is in an array,
you have the extra step of looping through the array and adding the values
to a disconnected recordset. Yes, this recordset is like a database, but it
is in memory and is much faster to work with than any database residing in a
file system. If you are sorting an array, I'm thinking the data came from
somewhere, and it might be possible to read the data into a disconnected
recordset in the first place instead of an array. I think it would be almost
as fast to populate a recordset as the array.

Following are two examples using disconnected ADO recordsets to sort. The
first converts an array of string values into a disconnected recordset to
sort. The second example retrieves all user names from Active Directory in
an ADO recordset, disconnects the recordset, and then sorts the values
before displaying. The example I posted earlier might have been confusing.
The "Number" and "Index" fields were just examples. My example below has
just one field.

This first example is one I used when I was comparing the performance of
several sort methods, including bubble sort (slowest), something called a
Benny sort (Benny Pedersen was active in the newsgroups), and using .NET to
sort (I have to admit the fastest method I found). This script shows that
the sort takes almost no time, but a fraction of a second is required to
setup the recordset and read the array values into it. It requires MDAC on
the client, but I believe any version will do:
==============
' ArraySort.vbs
' VBScript program to test several methods to sort.
Option Explicit

Dim arrAscending, adoDataList, strValue, intCount
Dim dtmT1, dtmT2, dtmT3

Const adVarChar = 200
Const MaxCharacters = 255

arrRandom = Array("Potato", "Lettuce", "Onion", "Bread", _
    "Apple", "Orange", "Cherry", "Pear", "Milk", "Eggs", _
    "Hamburger", "Ham", "Corn", "Beans", "Soup", "Pepper", _
    "Salt", "Cornmeal", "Swiss Cheese", "Cheddar", "Basil", _
    "Paprika", "Oregano", "Chili Powder", "Cottage Cheese", _
    "French Fries", "Onion Rings", "Cola", "Waffles", "Peas", _
    "Baked Beans", "Chili", "Pinto Beans", "Black Eyed Peas", _
    "15 Bean Soup", "Cream", "Cream Cheese", "Yogurt", _
    "Cake", "Ice Cream", "Cookies", "Butter", "Rye Bread", _
    "Rice Cakes", "Whole Wheat Bread", "Butter Cookies", _
    "Green Onion", "Celery", "Garlic Bread", "Pizza", _
    "Tomato", "Grapes", "Black Beans", "Pancakes", "Red Beans", _
    "Frozen Yogurt", "Tomato Paste", "Yeast", "Stewed Tomatoes", _
    "French Toast", "Ginger Snaps", "Candy", "Chocolate", _
    "Raisins", "Bay Leaves", "Rosemary", "Garlic Powder", _
    "Thyme", "Macaroni", "Clam Chowder", "Split Pea")

dtmT1 = Timer()

' Setup disconnected recordset.
Set adoDataList = CreateObject("ADODB.Recordset")
adoDataList.Fields.Append "Value", adVarChar, MaxCharacters
adoDataList.Open

For Each strValue In arrRandom
    adoDataList.AddNew
    adoDataList("Value") = strValue
    adoDataList.Update
Next

dtmT2 = Timer()

adoDataList.Sort = "Value"

dtmT3 = Timer()

' Display sorted values.
intCount = 0
adoDataList.MoveFirst
Do Until adoDataList.EOF
    Wscript.Echo adoDataList.Fields.Item("Value")
    intCount = intCount + 1
    adoDataList.MoveNext
Loop
adoDataList.Close

Wscript.Echo "Number of values: " & CStr(intCount)
Wscript.Echo "ADO Sort setup: " & FormatNumber(dtmT2 - dtmT1, 4)
Wscript.Echo "ADO Sort      : " & FormatNumber(dtmT3 - dtmT2, 4)
Wscript.Echo "ADO Sort total: " & FormatNumber(dtmT3 - dtmT1, 4)
==========
This second example shows how to sort all user names retrieved from Active
Directory. The trick is to specify the CursorLocation, CursorType, and
LockType to allow the recordset to be disconnected and sorted.
==========
Option Explicit

Dim objRootDSE, strDNSDomain, adoConnection
Dim strBase, strFilter, strAttributes, strQuery, adoRecordset
Dim strDN, intCount, strName

Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adUseClient = 3

' Determine DNS domain name.
Set objRootDSE = GetObject("LDAP://RootDSE")
strDNSDomain = objRootDSE.Get("defaultNamingContext")

' Use ADO to search Active Directory.
Set adoConnection = CreateObject("ADODB.Connection")
adoConnection.Provider = "ADsDSOObject"
adoConnection.Open "Active Directory Provider"

Set adoRecordset = CreateObject("ADODB.Recordset")
adoRecordset.ActiveConnection = adoConnection
adoRecordset.CursorLocation = adUseClient
adoRecordset.CursorType = adOpenStatic
adoRecordset.LockType = adLockOptimistic

' Search entire domain.
strBase = "<LDAP://" & strDNSDomain & ">"

' Filter on all user objects.
strFilter = "(&(objectCategory=person)(objectClass=user))"

' Comma delimited list of attribute values to retrieve.
strAttributes = "distinguishedName,sAMAccountName"

' Construct the LDAP query.
strQuery = strBase & ";" & strFilter & ";" & strAttributes & ";subtree"

' Run the query.
adoRecordset.Source = strQuery
adoRecordset.Open

' Disconnect the recordset.
Set adoRecordset.ActiveConnection = Nothing
adoConnection.Close

' Sort the recordset.
adoRecordset.Sort = "sAMAccountName"
adoRecordset.MoveFirst

' Enumerate the resulting recordset.
intCount = 0
Do Until adoRecordset.EOF
    ' Retrieve values.
    strDN = adoRecordset.Fields("distinguishedName").Value
    strName = adoRecordset.Fields("sAMAccountName").Value
    Wscript.Echo strName & ": " & strDN
    intCount = intCount + 1
    adoRecordset.MoveNext
Loop

' Clean up.
adoRecordset.Close

Wscript.Echo "Number of objects: " & CStr(intCount)
--
Richard Mueller
MVP Directory Services
Hilltop Lab - http://www.rlmueller.net
--

  Thanks for that detailed explanation. Ill have to
explore this. ADO is all new to me.

news:ef4uv20IKHA.4708@TK2MSFTNGP03.phx.gbl...

I'll just add that there are GetRows and GetString methods of the
disconnected recordset object that can quickly convert into an array or
string. For example, adding to the first example I posted earlier:
========
' Convert recordset into an array.
adoDataList.MoveFirst
arrResults = adoDataList.GetRows

' Convert recordset into a semicolon delimited string.
Const adClipString = 2
adoDataList.MoveFirst
strResults = adoDataList.GetString(adClipString, , , ";", "<NULL>")
adoDataList.Close

' Enumerate the array.
intCount = 0
For Each strValue In arrResults
    Wscript.Echo strValue
    intCount = intCount + 1
Next
Wscript.Echo CStr(intCount)
' Display the string.
Wscript.Echo strResults
=====
I haven't found a quick way to convert an array into a recordset. But, you
can also apply filters to the disconnected recordset. For example:

adoDataList.MoveFirst
adoDataList.Filter = "Value > 'Fruit'"

And finally, you can sort descending with:

adoDataList.Sort = "Value DESC"

--
Richard Mueller
MVP Directory Services
Hilltop Lab - http://www.rlmueller.net
--

Richard Mueller [MVP] schrieb:
[...]

.GetRows() will return a two dimensional array with as many 'rows'
(first dimension) as there are columns and as many 'colums' (second
dimension) as there are elements in the one dimensional array you
put into the table.

Three problems:
   (1) you want an array (back) - so strResults has to be converted

   (2) if you use Split on strResults, the choice if the delimiter
       is critical: I would consider ";" as risky

   (3) After aData = Split( strResults, ";" ), aData will contain
       one more/last empty element than the array you started with,
       because Split treats its second parameter as a separator.

[...]

  I did some testing on this. It appears that
ADO has obvious advantages if one is dealing
with existing recordsets. It's fairly quick and
if I understand the Sort method correctly, it
seems to be sortable in more than 1 column,
so that names could be sorted within the same
date, for instance. (?)

  For basic speed ADO turned out mediocre. It's
plenty fast for most uses, as are all sort methods
I tested except bubble sort. But it's not actually
very fast.

   For my original sorting tests I use the following
code with each sort algorithm test script, to open
a dropped file and create an array:

Set FSO = CreateObject("Scripting.FileSystemObject")
  Set TS = FSO.OpenTextFile(WScript.arguments(0), 1)
     s1 = TS.ReadAll
     TS.Close
   Set TS = Nothing
A1 = Split(s1, " ")

 I then call:

i = timer
[SortRoutine Call here]
i2 = timer

i2 - i1 is the measurement.

   To test ADO I added the same arrray code
to the beginning of your sample. It turns out
that takes about 1/2 second to get an array.
It doesn't seem to matter much how big the
file is. I guess the FSO ops are probably most
of it and the Split is probably almost instant.

 Starting with the array, I came up with the following:

_______________________
txt file of 12,277 words
------------------

QuickSort:        441 ms

ADO setup        773 ms
ADO Sort          609 ms
ADO Sort total  1,380 ms

________________
txt file of 73,705 words (ECMA 3 Reference 477 KB)
-------------------
QuickSort:        2,860 ms

ADO setup        4,179 ms
ADOSort           5,269 ms
ADO Sort total   9,449 ms

 This was just two files tested, running the test
a handful of times, but it shows a trend similar
to other sort methods. If only the actual sorting
time is counted, ADO gets slower as the number
of items gets bigger. (That seems to be true with
most or all methods, except QuickSort. Or at
least it's less marked with QuickSort.)

   My earlier tests on the 477 KB file yielded the
following results:

quick   2859 ms
shell    5382 ms
snake  17,406 ms
merge  31,468 ms

  So ADO was comparable to ShellSort, if only
the sorting is counted. And it's faster to use
ADO, including setup, than it is to use SnakeSort
and MergeSort. (At least with large numbers of
items.) But even just the actual sorting itself is
notably slower than QuickSort.

  Another variable I wasn't able to check: I've been
using non-case-sensitive sorting. (Even though my
speed tests have been for basic case-sensitive sorting.)
I want an alphabetical list regardless of case. I don't
see where that option comes in with ADO. I looked up
the Sort method and found there seems to be only an
option for ASC or DESC to pick the sort direction. There
doesn't seem to be an option to choose the sort definition.

My test script, using your basic code, is below:

Dim FSO, TS, s1, A1, Arg, i, i2, s2, iAsc

Set FSO = CreateObject("Scripting.FileSystemObject")
  Set TS = FSO.OpenTextFile(WScript.arguments(0), 1)
     s1 = TS.ReadAll
     TS.Close
   Set TS = Nothing

A1 = Split(s1, " ")

Dim arrAscending, adoDataList, strValue, intCount
Dim dtmT1, dtmT2, dtmT3

Const adVarChar = 200
Const MaxCharacters = 255

dtmT1 = Timer()

' Setup disconnected recordset.
Set adoDataList = CreateObject("ADODB.Recordset")
adoDataList.Fields.Append "Value", adVarChar, MaxCharacters
adoDataList.Open

For Each strValue In A1  'arrRandom
    adoDataList.AddNew
    adoDataList("Value") = strValue
    adoDataList.Update
Next

dtmT2 = Timer()

adoDataList.Sort = "Value"

dtmT3 = Timer()

' Display sorted values.
intCount = 0
adoDataList.MoveFirst
Do Until adoDataList.EOF
   ' WScript.Echo adoDataList.Fields.Item("Value")
    intCount = intCount + 1
    adoDataList.MoveNext
Loop

adoDataList.Close
WScript.Echo "Number of values: " & CStr(intCount)
WScript.Echo "ADO Sort setup: " & FormatNumber(dtmT2 - dtmT1, 4)
WScript.Echo "ADO Sort      : " & FormatNumber(dtmT3 - dtmT2, 4)
WScript.Echo "ADO Sort total: " & FormatNumber(dtmT3 - dtmT1, 4)

mayayana schrieb:

ADO sorts case insensitive (by default?). See:

===== Data: 10Strings
  eins,Zwei,drei,vier,fünf,sechs,sieben,acht,neun,zehn
----- Func: ADOSimple
  eins,Zwei,drei,vier,fünf,sechs,sieben,acht,neun,zehn
  acht,drei,eins,fünf,neun,sechs,sieben,vier,zehn,Zwei
  Falsch  <== because my test function IsSorted is case sensitive, sigh

The challenge would be to get case sensitive sorting (maybe there
is a property of the recordset?)

[...]

 From my tests I assume that

  aName  = Array( "Value" )
  aValue = Array( Empty )
  For Each strValue In A1  'arrRandom
      aValue = strValue
      adoDataList.AddNew aName, aValue
  Next

is faster.

In microsoft.public.scripting.vbscript message <OEis0fhIKHA.1020@TK2MSFT
NGP02.phx.gbl>, Fri, 21 Aug 2009 00:33:11, mayayana
<mayaXXy...@rcXXn.com> posted:

Read <http://en.wikipedia.org/wiki/Sorting_algorithm> and its links.

--
 (c) John Stockton, Surrey, UK.  ?...@merlyn.demon.co.uk   Turnpike v6.05   MIME.
 Web  <URL:http://www.merlyn.demon.co.uk/> - FAQish topics, acronyms, & links.
 Proper <= 4-line sig. separator as above, a line exactly "-- " (SonOfRFC1036)
 Do not Mail News to me. Before a reply, quote with ">" or "> " (SonOfRFC1036)

   In that case the differences between methods
are not so marked. I get about 4.9 seconds on
the 477 KB file for non-case-sens. QuickSort.
So the ADO method is still a bit slower, even
if only the sorting itself is counted and the setup
cost is not included. But it's not slower by much.
It seems like the practical result is that QuickSort,
ShellSort and ADO are all quite adequate for almost
all needs, while BubbleSort should be discarded.

   Though
I should note that some of my results have also
varied a great deal. In general I keep getting
consistent results on different tests, using varied
sorting methods, but there have been occasional
instances when I've got an unexpected result. For
instance, sometimes I've dropped the 477 KB file
onto my QS or ADO test scripts and got a result
about 5 seconds longer than the other runs! I have
no idea why.

OK... this may be more than anyone wants
to know at this point, but I've done some more tests. :)

The results are as follows:
----------------------------------------
sort test on 23 KB text file:

quick          156 ms
quick(NCS)  222 ms
shell           226 ms
snake         171 ms
merge         226 ms
ADO(NCS)   58/379 ms   (actual sorting time / time to create ADO
                                  Recorset, transfer array, sort, and return
                                  sorted array.)
bubble        15,984  ms

---------------------------------
sort test on 477 KB plain text:

quick          2,859 ms
quick(NCS)  4,986 ms
shell           5,382 ms
ADO(NCS)   5,269/10,882  (actual sorting time / time to create ADO
                                    Recorset, transfer array, sort, and
return
                                    sorted array.)
snake         17,406 ms
merge         31,468 ms

---------------------------------

  I don't have the .Net runtime installed, so I
can't test that. NCS stands for non-case-sensitive.
I rewrote an ADO script to do the whole operation
of returning the sorted array. The ADO times
show both numbers: actual sorting time, applicable
for people working within ADO, and total time as
it would be for someone using the ADO method on
an array.
The ADO script follows. (I didn't change the AddNew
method as per ekkehard's spec. because I didn't
understand that code, but I'm guessing the difference
is probably negligible.)

'---- drop a text file onto this script ---------
Dim FSO, TS, s1, A1, Arg, i, s2, A2()
Dim ADO, sWord
Dim T1, T2, T3, T4

Set FSO = CreateObject("Scripting.FileSystemObject")
  Set TS = FSO.OpenTextFile(WScript.arguments(0), 1)
     s1 = TS.ReadAll
     TS.Close
   Set TS = Nothing

A1 = Split(s1, " ")

T1 = Timer
' Setup disconnected recordset.
Set ADO = CreateObject("ADODB.Recordset")
ADO.Fields.Append "Value", 200, 255
ADO.Open

For i = 0 to UBound(A1)
    ADO.AddNew
    ADO("Value") = A1(i)
    ADO.Update
Next

T2 = Timer

ADO.Sort = "Value"

T3 = Timer

'-- return the sorted array:
ADO.MoveFirst
A1 = ADO.GetRows
ReDim A2(UBound(A1, 2))
   For i = 0 to UBound(A1, 2)
      A2(i) = A1(0, i)
  Next
    A1 = A2
T4 = Timer
ADO.Close

MsgBox "ADO time required in milliseconds for " & CStr(UBound(A1)) & "
values:" & vbCrLf & "ADO setup: " & CStr((T2 - T1) * 1000) & vbCrLf &
"Actual Sorting: " & CStr((T3 - T2) * 1000) & vbCrLf & "Combined setup plus
sort: " & CStr((T3 - T1) * 1000) & vbCrLf & "Total time to return original
array sorted: " & CStr((T4 - T1) * 1000)

Set ADO = Nothing

I'm guessing that you are measuing 'wall clock' time, not time that the
processor is devoting to your sorting task.  Perhaps you could disconnect
from the internet and use MSConfig to disable all unnecessary startup and
service stuff, and see if that gives you more consistent run times.  If your
dataset is unchanging, then the sort times should be fairly consistent.  If
the dataset is different each time, then your sort times can vary wildly.
Shell sort has a relatively small range of run times because it does
essentially the same number of comparisons for best- and worst- case
scenerios.  The run time for any sorting algorithm is dependent on the total
number of comparisons that are made and the number of data moves that are
made.  Quicksort slows down significantly for the worst case scenerio, where
the initial pivot point chosen for for each itteration needs to be moved a
lot.  For this reason, some quicksort algorithms use a randomization
mechanism to choose the initial pivot point.

You could easily build a large dataset that is correctly sorted, and
backward sorted and a same-sized random data set, and instrument your
VBScript algorithms to count and report the number of comparisons and data
exchanges that are made for the various data sets, as a quick way to
understand sorting times a little better.  You may find that in some cases,
bubble sort can be faster than QuickSort or any of the other 'fast' sorts.

-Paul Randall

news:%23blFbyBJKHA.4376@TK2MSFTNGP03.phx.gbl...

"Wall clock time"? I'm just calling:

i = timer
dosort
i2 = timer

  But I don't know how accurate that is in script.
We're talking ms, after all, and the message has
to go through wscript, then probably to some
API call that may not be so perfect. Actually, in one
case where I got a 5 second diff. it turned out there
was another instance of wscript running. So maybe
that was the issue there. In general I've found a
fair amount of variation on smaller tests, with less
variation on larger tests, so I guessed that
VBS just wasn't accurate enough for the small
measurements.

 I beg your pardon. I'm running Win98SE, with
about 8 total processes and none of that
services crap, thank you very much. :)

news:%23%23EuvrDJKHA.3632@TK2MSFTNGP05.phx.gbl...

I'd say that your shortest times for each sort method/dataset combination
are the most accurate measure for comparing the efficiency of the various
sort methods.  The longer times are almost always due to delays caused by
multitasking of your script with other tasks and the routine housekeeping
the computer is doing.

I keep forgetting :-)

-Paul Randall

Several years ago, Mike Harris wrote a Shell Sort and a
quick sort in (vb)script.  He posted the result here,
(vbs ng) and on Clarence Washington's website:

   http://cwashington.netreach.net/

A while back, Clarence stopped maintaining his website,
thanks to the introduction of "a-little-bundle-of-joy"
into his life (which now consumes all his spare time).
And, when last seen, he was closing his site.  However,
nothing ever disappears from the web, and MikHar's sort
routines are probably still out there in some archival
site or other.

cheers, jw