Sort UDT vs Database

22 May 2009 2:12 AM

David

Looking for feedback.

I have a UDT array less than 1000 elements.

I need to sort it on various properties of the array.

Debate is whether to read the UDT array into a DB table and use it to sort
or attempt a UDT array sort.

Any suggestions appreciated.

22 May 2009 2:49 AM

Nobody

"David" <dw85745***@earthlink.net> wrote in message
news:OpOVGLo2JHA.1092@TK2MSFTNGP06.phx.gbl...
> Looking for feedback.
>
> I have a UDT array less than 1000 elements.
>
> I need to sort it on various properties of the array.
>
> Debate is whether to read the UDT array into a DB table and use it to sort
> or attempt a UDT array sort.
>
> Any suggestions appreciated.

1000 is not much, but since you are sorting by various fields, a DB is a
better choice, especially if the contents don't change much. A DB can have
indices on each field, so retrieval would be quick, and there is no need to
resort.

If the contents change every time, then use array sorting. Here are VB6
samples:

http://vbnet.mvps.org/code/sort/index.html

Avoid using sort methods that use recursion, such as QuickSortNumbers. These
use the stack, which is limited to 1 MB. These routines can be converted to
use an array instead of the stack, but you have to find one or modify the
routine yourself. Here is a link to sorting algorithms:

http://en.wikipedia.org/wiki/Sorting_algorithm

22 May 2009 10:58 AM

Bob Riemersma

"David" <dw85745***@earthlink.net> wrote in message
news:OpOVGLo2JHA.1092@TK2MSFTNGP06.phx.gbl...
> Looking for feedback.
>
> I have a UDT array less than 1000 elements.
>
> I need to sort it on various properties of the array.
>
> Debate is whether to read the UDT array into a DB table and use it to sort
> or attempt a UDT array sort.
>
> Any suggestions appreciated.

You don't need a database, and you often can do better than a UDT array in
the first place unless you have a real need for such a thing.

Consider a fabricated ADO Recordset. They can often be used as a
replacement for UDT arrays and can be treated as a "multivalued collection"
object. You get sorting, filtering, searching, etc. as well as deletion.
They are also easily persisted to a file for later reloading. See "Adding
Fields to a Recordset" at:

http://msdn.microsoft.com/en-us/library/ms676135(VS.85).aspx

These can even be hierarchical:

http://msdn.microsoft.com/en-us/library/ms677525(VS.85).aspx

Any column that requires heavy use can be indexed to improve performance by
setting its Optimize dynamic property to True:

http://msdn.microsoft.com/en-us/library/ms677521(VS.85).aspx

While a hand coded sort can always outperform this it should probably seldom
be your default option. That level of micro-optimization is costly. You
have to choose an appropriate sort algorithm, carefully code and test it,
and then support it over the life of the application. Attempts to write one
for generic use can sacrifice much of the performance gain.

ADO is one of the crown jewels of Windows, and quite a kit of power tools in
general. Through Providers and Data Source Objects it can be used for tons
of things including XML parsing and reading/writing files to Web servers
that have the necessary options enabled (WebDAV or FrontPage Web Extender
Client protocols). Without them it offers things like your sorting as well
as a useful Stream object that gives you "Byte array concatenation" and even
supports character encoding and decoding between VB's UTF-16 and
alternatives such as UTF-8 or various ANSI codepages. Database access is
only the tip of the iceberg.

22 May 2009 1:05 PM

David

Thanks for both your views and references.

David

Show quoteHide quote

"Bob Riemersma" <nospam@nil.net> wrote in message
news:OCPHQxs2JHA.4416@TK2MSFTNGP05.phx.gbl...
> "David" <dw85745***@earthlink.net> wrote in message
> news:OpOVGLo2JHA.1092@TK2MSFTNGP06.phx.gbl...
>> Looking for feedback.
>>
>> I have a UDT array less than 1000 elements.
>>
>> I need to sort it on various properties of the array.
>>
>> Debate is whether to read the UDT array into a DB table and use it to
>> sort or attempt a UDT array sort.
>>
>> Any suggestions appreciated.
>
> You don't need a database, and you often can do better than a UDT array in
> the first place unless you have a real need for such a thing.
>
> Consider a fabricated ADO Recordset. They can often be used as a
> replacement for UDT arrays and can be treated as a "multivalued
> collection" object. You get sorting, filtering, searching, etc. as well
> as deletion. They are also easily persisted to a file for later reloading.
> See "Adding Fields to a Recordset" at:
>
> http://msdn.microsoft.com/en-us/library/ms676135(VS.85).aspx
>
> These can even be hierarchical:
>
> http://msdn.microsoft.com/en-us/library/ms677525(VS.85).aspx
>
> Any column that requires heavy use can be indexed to improve performance
> by setting its Optimize dynamic property to True:
>
> http://msdn.microsoft.com/en-us/library/ms677521(VS.85).aspx
>
> While a hand coded sort can always outperform this it should probably
> seldom be your default option. That level of micro-optimization is
> costly. You have to choose an appropriate sort algorithm, carefully code
> and test it, and then support it over the life of the application.
> Attempts to write one for generic use can sacrifice much of the
> performance gain.
>
>
> ADO is one of the crown jewels of Windows, and quite a kit of power tools
> in general. Through Providers and Data Source Objects it can be used for
> tons of things including XML parsing and reading/writing files to Web
> servers that have the necessary options enabled (WebDAV or FrontPage Web
> Extender Client protocols). Without them it offers things like your
> sorting as well as a useful Stream object that gives you "Byte array
> concatenation" and even supports character encoding and decoding between
> VB's UTF-16 and alternatives such as UTF-8 or various ANSI codepages.
> Database access is only the tip of the iceberg.