On Fri, 05 Jul 2013 02:19:08 +0200
Olaf Schmidt <n...@vbrichclient.com> wrote:
> Create Table T (ID Integer Primary Key, Item Text)
>
> Select Count(Lesser.ID), T.ID, T.Item From T As T
> Left Outer Join T As Lesser
> On T.ID > Lesser.ID
> Group By T.ID
> Order By T.ID
>
> I don't know, what I'm doing wrong - but my timing-trend
> comes out like this:
>
> RecordCount in T msec for the above query
>
> 100 3msec
> 1000 186msec
> 10000 17857msec
>
> And so, after waiting already for about 18 seconds for the query
> to complete with only 10000 records in T, I will of course not
> try it with the "1,000,000 rows" you were mentioning above. <g>
I'm glad I provoked you into doing that. It means I'm making testable
assertions. I agree that on sqlite-users it's good to keep in mind
the limits of the system we're discussing.
"Fancy algorithms are slow when n is small,
and n is usually small."
-- Rob Pike
SQLite uses a simple algorithm that is usually fast, because N
is usually small. When N is large, a fancier algorithm would be
much faster, at the price of complexity.
Your test doesn't show that the above *query* is inefficient, but that
SQLite executes it inefficiently. The query can be executed
efficiently; whether or not SQLite does so is a design choice.
All of which is orthogonal to the question of whether or not SQLite
should include an SQL RANK() function. The algorithm could be added
without changing the syntax, and changing the syntax would not make
SQLite compute the rank more efficiently. (Full-blown "windowing"
partition syntax, OTOH, would allow some queries to be expressed much
more succinctly.)
But I think what you want isn't even a true ranking function, but a
client-side row-counting function. From what I can tell, you'd like to
remedy what you see as a shortcoming in container libraries you use by
adding a counter to the SQLite SQL. There are ramifications, though,
that make that remedy much worse than the disease, ramifications that
obvious to those who know why SQL tables have no implicit order. It is
because of those ramifications that I recommend you treat the disease
itself by fixing the libraries.
--jkl
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
