Reverse a list

[Peter Haworth]

On Wed, Feb 11, 2015 at 6:18 PM, Kay C Lan <lan.kc.macmail at gmail.com> wrote:

> I did a similar test to you using a Valentina DB with and without unicode.
> Specifically I used a UTF8 db so the unicode test data had to be passed
> through LCs textDecode(dboutput,"utf8") to get the correct results; which
> obviously takes time. My results suggest that if Ali's fix is anything to
> go by, when working with utf8 data there could be a noticeable difference
> for big data sets and that Mark's qrevers() shines a very favourable light
> on LCs capabilities.
>
> I wish to again point out,
>
> NOT because I don't understand how LC counts lines
> NOT because I want to change the way LC works
> NOT because I want to open a discussion about whehter it's right or wrong,
>
> but simply for awareness, that the DB approach MAY produce a different
> result than the LC functions thus far offered. Lets say for example you had
> a list of 10,000 customers and their email addresses. Most customers do
> have an email, a few don't and it just so happens that your first customer
> Aardvark, and last customer, Zoe, don't have email. If you read just the
> emails into an LC variable the first line will be blank and you'll have
> 9999 lines. If you use the LC provided functions thus far presented to
> reverse the data, you'll end up with the first line not being blank, and
> 9998 lines.
>

I haven't run any of the LC scripts to do this but if that's true, then
they don't achieve the original objective of reversing the list.


>
> On the other hand, if you use a DB, your record count will always be 10000
> and your first and last record will always be blank no matter which
> direction you sort it.
>

The record count will always be 10,000 as you say but why would the first
and last records always be the blank?  I added a return character at the
beginning and end of the list I'm using and reran my test.  Sorting
ascending, the first two lines were the empty lines and sorting descending
the last two lines were the empty lines, as you'd expect.

Also tried it using INSERT INTO .... DEFAULT VALUES with the same results.


> What did surprise me was that there was NO penalty to adding an ORDER BY
> clause to the SELECT statement. My tests included:
> natural = SELECT data FROM db -- NO order by clause
> forward = SELECT data FROM db ORDER BY recid
> reverse = SELECT data FROM db ORDER BY recid DESC
>
>
Didn't see that with SQLite.  A Select with no ORDER BY took half as long
as a SELECT with a sort in either direction.

This is a guess but it looks from your examples that you were sorting by
the primary key column of the table.  That's a special case where the
normal rules don't apply since the primary keys are stored in a form that
is already sorted.  Try sorting on a data column and I think you will see a
difference.  In my tests an unordered SELECT took about half as long as an
ordered SELECT in either direction.

Kinda getting off topic I guess.


>
>
Pete
lcSQL Software <http://www.lcsql.com>
Home of lcStackBrowser <http://www.lcsql.com/lcstackbrowser.html> and
SQLiteAdmin <http://www.lcsql.com/sqliteadmin.html>