Re: [sqlite] sqlite3_close() is failing

[Ashok Pitambar]

Yes you are right there were some unfinalized statements left.which were
causing this problem.

Thanks
On Jul 5, 2013 4:42 PM, "Simon Slavin"  wrote:
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] table format for most efficient query

[RSmith]

Sorry - thats what I mean - the table (as redefined by simon)

create table lookup (rowindex int, rangestart int, rangeend int)

values in rowindex are not unique so I cannot make this column a primary
key so i am not sure whether RTrees would be any help in this scenario


Hi Paul,
Firstly, the RTRee will work spectacularly well for your case, but you are in no way bound by the rowid or primary keys to conform 
to your row id, but if your row ids are not unique, then you need to simply use a different key for that function.

For instance, 2 tables, one RTree table witht he standard Primary key + start + 
end range columns,
Second your data table with Key, rowindex, field3, field4, etc. starting with a Key which should be a primary key or (best be 
INTEGER PRIMARY KEY) or a foreign key on the Primary Key of the RTree table (I wont go into foreign key relationships here, you can 
read it on the SQLite web pages, it's only an option, you can very well simply refer the id's in Select queries).
The second column being your rowindex which may very well be non-unique and void of all other restrictions even, followed by the 
rest of however many data columns are needed.


Think of the RTree table as a sort of ranged-Index to your data table. So you 
can do queries like:
SELECT * from myData AS D JOIN rtreeTable AS R ON (R.PrimKey=D.Key) WHERE 
(Range-select-values);

This will return all the data in your data table (which may very well again re-include the actual ranged values if space is not a 
concern) according to the lookup index IDs supplied from the RTree efficient search algorithm.


This select above can take many other forms but as long as you are looking for a range specifier, the Rtree table will be much 
faster on the lookup, and it returns Key alues from an Index, which is no effort to simply look up for the data table. Also, never 
be afraid of using an additional index, the more ways SQLite can find your data, the better it should be at picking the fastest.



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] table format for most efficient query

[Igor Tandetnik]

On 7/5/2013 10:59 AM, Paul Sanderson wrote:

Sorry - thats what I mean - the table (as redefined by simon)

create table lookup (rowindex int, rangestart int, rangeend int)

values in rowindex are not unique so I cannot make this column a primary
key so i am not sure whether RTrees would be any help in this scenario


create virtual table lookup_tree using rtree_i32(rangeid, rangestart, 
rangeend);

create table lookup(rangeid integer primary key, rowindex int);

select * from
  master m join lookup l on (m.rowindex = l.rowindex)
  join lookup_tree lt on (l.rangeid=lt.rangeid)
where :x >= rangestart and and :x < rangeend;

--
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is there a way to return the row number? (NOT the rowid)

[Olaf Schmidt]

Am 04.07.2013 20:36, schrieb James K. Lowden:


Yes, and there's your O(N log N): N for the SCAN and log(N) for the
SEARCH. To process 1,000,000 rows takes 1,000,000 accesses.  To produce
the rank requires roughly 20 searches per row (given an appropriate
index), or 20,000,000 total accesses. Plus some work, depending on
memoization, to count the nodes beneath the found one.

Inefficient? It's the theoretical *minimum* for the general case.


>... snip ...
>

Did I miss something, or have I answered your efficiency concerns?


What you missed, is apparently *testing* your own suggestions...
You know, those "practical things" some of us do from time to time...
;-)

Well, I just did so (using a small Table with an index on ID
for your self-join-suggestion):

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

 1003msec
 1000 186msec
 1  17857msec

And so, after waiting already for about 18 seconds for the query
to complete with only 1 records in T, I will of course not
try it with the "1,000,000 rows" you were mentioning above. 


Olaf

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is there a way to return the row number? (NOT the rowid)

[Olaf Schmidt]

Am 03.07.2013 14:50, schrieb Keith Medcalf:



Given all that, I will NEVER use the pure sql (if I can use any
other solution).


given that the "ordinal in the result set" is a fallacious
concept being created for the convenience of an application

> program which cannot deal with sets properly ...

Oh, so convenience is now bad suddenly?
Why do you think, libraries like SQLite exist at all?
They're there to offer *convenience* - to save other
developers some time (or headaches)... thanks to the
SQLite-team BTW at this occasion - you've developed a
really convenient "time-saver" here over the years for
a lot "of us"...

But given your logic, should the usage of e.g. literals in SQL-
Field-expressions be forbidden now too? ... because:
  "this is stuff, which should be handled at the application-
  level, otherwise the developer is to blame, for abusing
  the concept of relational databases..."

What kind of theorizing is that here... there's a whole lot
of stuff in DBEngines, libraries (and in SQL as well), which
is just there for convenience.

Having a Counter-Number conveniently available (without creating
a clumsy performance-hog as James K. Lowden was suggesting) is
a reasonable request from a *practical* point of view and pretty
cheap to implement.

So, I'm voting +1 for that...

Because there's a lot of things one can use that for - especially
when you consider the concept of disconnected Recordsets, which
can be passed around in an application, or across thread- or
machine-boundaries - generic container-classes, which can be
bound to grids - or used as the datasource for parts in a Report
... in any of those cases such a "directly contained info" can
be useful, when it's already "there in the returned set-object
as a calculated column-value".
It would allow to write more "generic code" at the app-level -
when for e.g. the visualizing of Line-Numbers (or to remember
or visualize an initial-Sort-Order) in a Grid for example, one
could just "hang the Recordset-Datasource in 'as is'" - without
having to resort to "manually managing the filling of a spare-
column" within that Grid in question, because the information
was not contained as a Field in the Recordset-Container-Class.

If SQL allows to create (from a Double-typed-Field as the
Input-source) a convenient, directly displayable visual output,
handing out "calculated Percent-Strings, nicely formatted to
two decimal-places after the dot" over an SQL-expression... -
well, then I don't understand what all the fuss is about here,
when there's only the simple request for a *little* bit
"more of the same" in this regard...

Olaf



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] table format for most efficient query

[Paul Sanderson]

Sorry - thats what I mean - the table (as redefined by simon)

create table lookup (rowindex int, rangestart int, rangeend int)

values in rowindex are not unique so I cannot make this column a primary
key so i am not sure whether RTrees would be any help in this scenario


On 5 July 2013 14:39, Igor Tandetnik  wrote:

> On 7/5/2013 9:13 AM, Paul Sanderson wrote:
>
>> my primary key would not be unique :(
>>
>
> That's an oxymoron - primary key is unique, by definition.
>
> --
> Igor Tandetnik
>
> __**_
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] table format for most efficient query

[Igor Tandetnik]

On 7/5/2013 9:13 AM, Paul Sanderson wrote:

my primary key would not be unique :(


That's an oxymoron - primary key is unique, by definition.
--
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] table format for most efficient query

[Paul Sanderson]

Having read up on RTrees I may have a problem - my primary key would not be
unique :(


On 5 July 2013 12:05, Paul Sanderson  wrote:

> Thanks for that Igor - I had RTree in the back of my mine but couldn't
> remember what or where I had read about it.
>
> before I compile a dll (or someone provides a link to a 32 bit dl with
> RTree enabled) is RTree likely to be useful in the following scenario?
>
> Each look up will usually, but not always, be successful - i.e. about 90%
> of the time a search will result in about 4 rows being returned the other
> 10% of the time nothing will be found
>
> Just sitting down to look at Simons suggestions.
>
>
>
>
> On 5 July 2013 01:47, Igor Tandetnik  wrote:
>
>> On 7/4/2013 5:29 PM, Paul Sanderson wrote:
>>
>>> select * from master as m, lookup as l where x >= start and and x < end
>>> and
>>> m.index = l.index
>>>
>>
>> You might want to look at the RTree module:
>>
>> http://www.sqlite.org/rtree.**html 
>>
>> It's specifically designed to implement such range queries efficiently.
>> --
>> Igor Tandetnik
>>
>>
>> __**_
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**users
>>
>
>
>
>
>


-- 
Paul
www.sandersonforensics.com
skype: r3scue193
twitter: @sandersonforens
Tel +44 (0)1326 572786
http://sandersonforensics.com/forum/content.php?168-Reconnoitre - VSC
processing made easy
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3_close() is failing

[Simon Slavin]

On 5 Jul 2013, at 12:10pm, Ashok Pitambar  wrote:

> sqlite3_close() is failing with return value SQLITE_BUSY. what could
> bethe reason for this?

One possibility is that you have an open statement.  In other words you have 
created a statement with sqlite3_prepare() but never got around to using 
sqlite3_finalize() on it.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] sqlite3_close() is failing

[Ashok Pitambar]

Hi All,

sqlite3_close() is failing with return value SQLITE_BUSY. what could
bethe reason for this?

Thanks,

Ashok
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] table format for most efficient query

[Paul Sanderson]

Thanks for that Igor - I had RTree in the back of my mine but couldn't
remember what or where I had read about it.

before I compile a dll (or someone provides a link to a 32 bit dl with
RTree enabled) is RTree likely to be useful in the following scenario?

Each look up will usually, but not always, be successful - i.e. about 90%
of the time a search will result in about 4 rows being returned the other
10% of the time nothing will be found

Just sitting down to look at Simons suggestions.



On 5 July 2013 01:47, Igor Tandetnik  wrote:

> On 7/4/2013 5:29 PM, Paul Sanderson wrote:
>
>> select * from master as m, lookup as l where x >= start and and x < end
>> and
>> m.index = l.index
>>
>
> You might want to look at the RTree module:
>
> http://www.sqlite.org/rtree.**html 
>
> It's specifically designed to implement such range queries efficiently.
> --
> Igor Tandetnik
>
>
> __**_
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is there a way to return the row number? (NOT the rowid)

[Gabriel Corneanu]

Hi James,

About complexity: I'm not sure it's NlogN; for each N you need to count 
N-1 columns, that's N^2 IMO.

And you have an EXTRA temporary B-TREE? Doesn't it matter??
Although I don't really understand why, it has an index on it.

My original concern is indeed simplicity and efficiency.
But I'm NOT advocating any complex RANK function, but rather to expose 
(as pseudo-data) the row index IN THE CURRENT RESULT SET; while usually 
the result set is ordered, it doesn't really matter. This information 
HAS NO other technical meaning (like rowid).


Like having about a list (on paper) containing (pseudo) RANDOM data and 
telling someone "look at 13th row"; by 13th I mean exactly as the list 
is printed.

The other one can much easily spot 13 if available than to quickly count.
If instead of row number I use "the row having xxx in it", the other one 
has to do some visual search (binary if the data would be sorted).


Now all I say is, if I print the list from my code I can simply generate 
such info.
But it would be greatly simplified if I can have it from SQL without 
complex/inefficient constructs, especially because it already does 
something similar for LIMIT.


Of course I have to accept "no" as an answer if most people are against 
this idea.


Regards,
Gabriel

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users