Re: [sqlite] Setting boundaries in a search

[RSmith]

On 2014/07/17 03:48, RSmith wrote:


In a similar fashion I had made this system for basically loading CSV files into an SQLite DB, then running all kinds of rules on 
it//..


(btw: This app is freely shared if anyone needs something of the kind or fancy 
testing the above, just mail me)


I have received more requests than anticipated for this, it seems many people use CSV type data transfers. I've responded to some 
and then decided it is probably easier to just upload and link it, if I have not responded to anyone specifically, apologies, but 
herewith the link - thanks.


http://www.rifin.co.za/software/utils/RCSVServ.zip

A Readme is included, but as it is (was) mostly intended for internal use, there is not a lot of other Help included, though after a 
cursory glance at the readme, everything else should be intuitive, please feel free to mail any questions directly as I don't wish 
to occupy this forum/thread with it.


The app is Win-32-bit and you can drop in your own dll if needed.


Thank you and have a great day!
Ryan

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

Re: [sqlite] Setting boundaries in a search

[Kees Nuyt]

On Thu, 17 Jul 2014 13:06:36 +0530, Sky Meena 
wrote:

> how to set a password to open a sqlite  db in sqlite browser

Answered in a different thread 2 minutes ago.


-- 
Regards,

Kees Nuyt



>On Thu, Jul 17, 2014 at 7:18 AM, RSmith  wrote:
>
>>
>> On 2014/07/16 14:55, Rob Willett wrote:
>>
>>  I’ll second what Simon says, I use the very same technique for a table
>>> with 4M+ records in and its so fast I thought I had an error and looked for
>>> bugs in my code.  I >assumed (incorrectly) that it would be very slow, It
>>> isn’t.
>>>
>>
>> In a similar fashion I had made this system for basically loading CSV
>> files into an SQLite DB, then running all kinds of rules on it (which I
>> could make with a designer) such as search-replace, substitute column
>> values, do checks, delete rows with empty values in a certain column, etc.
>> etc. and then finally export it to a CSV again, all basically streamlining
>> a datafeed alteration process into a one-click thing. The viewer I used was
>> based on a method discussed in another thread where the virtual view would
>> get actual data only for items in the visible field by primary key. I
>> tested it with some CSV tables over 250MB big resulting in 10mil+ rows, and
>> here was my surprise, whether I looked at the top of the list, or the
>> bottom, or anywhere else, the data retrieval was instantaneous - retrieving
>> a page worth of records at whatever speed I can scroll the vertical
>> scrollbar - not a single slow-down as I got further down or indeed any
>> other slowness. I have come to expect great performance from SQLite as a
>> standard, but I am still often surprised at just how quick it can be.
>>
>> (btw: This app is freely shared if anyone needs something of the kind or
>> fancy testing the above, just mail me)
>>
>>  ...// I have come to realise that the people who answer here are real
>>> experts, I will not embarrass them by naming names//...
>>>
>>
>> Oh don't worry, we know exactly who you mean... ;)
>>
>> It's Igor right?
>>
>> We all want to be like Igor when we grow up... *sighs dreamily*
>>
>>
>>
>>
>>
>> ___
>> 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
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Setting boundaries in a search

[Sky Meena]

how to set a password to open a sqlite  db in sqlite browser


On Thu, Jul 17, 2014 at 7:18 AM, RSmith  wrote:

>
> On 2014/07/16 14:55, Rob Willett wrote:
>
>  I’ll second what Simon says, I use the very same technique for a table
>> with 4M+ records in and its so fast I thought I had an error and looked for
>> bugs in my code.  I >assumed (incorrectly) that it would be very slow, It
>> isn’t.
>>
>
> In a similar fashion I had made this system for basically loading CSV
> files into an SQLite DB, then running all kinds of rules on it (which I
> could make with a designer) such as search-replace, substitute column
> values, do checks, delete rows with empty values in a certain column, etc.
> etc. and then finally export it to a CSV again, all basically streamlining
> a datafeed alteration process into a one-click thing. The viewer I used was
> based on a method discussed in another thread where the virtual view would
> get actual data only for items in the visible field by primary key. I
> tested it with some CSV tables over 250MB big resulting in 10mil+ rows, and
> here was my surprise, whether I looked at the top of the list, or the
> bottom, or anywhere else, the data retrieval was instantaneous - retrieving
> a page worth of records at whatever speed I can scroll the vertical
> scrollbar - not a single slow-down as I got further down or indeed any
> other slowness. I have come to expect great performance from SQLite as a
> standard, but I am still often surprised at just how quick it can be.
>
> (btw: This app is freely shared if anyone needs something of the kind or
> fancy testing the above, just mail me)
>
>  ...// I have come to realise that the people who answer here are real
>> experts, I will not embarrass them by naming names//...
>>
>
> Oh don't worry, we know exactly who you mean... ;)
>
> It's Igor right?
>
> We all want to be like Igor when we grow up... *sighs dreamily*
>
>
>
>
>
> ___
> 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] Setting boundaries in a search

[RSmith]

On 2014/07/16 14:55, Rob Willett wrote:


I’ll second what Simon says, I use the very same technique for a table with 4M+ 
records in and its so fast I thought I had an error and looked for bugs in my 
code.  I >assumed (incorrectly) that it would be very slow, It isn’t.


In a similar fashion I had made this system for basically loading CSV files into an SQLite DB, then running all kinds of rules on it 
(which I could make with a designer) such as search-replace, substitute column values, do checks, delete rows with empty values in a 
certain column, etc. etc. and then finally export it to a CSV again, all basically streamlining a datafeed alteration process into a 
one-click thing. The viewer I used was based on a method discussed in another thread where the virtual view would get actual data 
only for items in the visible field by primary key. I tested it with some CSV tables over 250MB big resulting in 10mil+ rows, and 
here was my surprise, whether I looked at the top of the list, or the bottom, or anywhere else, the data retrieval was instantaneous 
- retrieving a page worth of records at whatever speed I can scroll the vertical scrollbar - not a single slow-down as I got further 
down or indeed any other slowness. I have come to expect great performance from SQLite as a standard, but I am still often surprised 
at just how quick it can be.


(btw: This app is freely shared if anyone needs something of the kind or fancy 
testing the above, just mail me)


...// I have come to realise that the people who answer here are real experts, 
I will not embarrass them by naming names//...


Oh don't worry, we know exactly who you mean... ;)

It's Igor right?

We all want to be like Igor when we grow up... *sighs dreamily*




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

Re: [sqlite] Setting boundaries in a search

[jose isaias cabrera]

"Simon Slavin" wrote...


On 16 Jul 2014, at 1:23pm, jose isaias cabrera  
wrote:



"Simon Slavin" wrote...


CREATE INDEX sci ON startcodes (code,id)

You will find that that SELECT will then be blisteringly fast even with 
millions of rows in your table.


I do have that INDEX for that id and table.  Thanks. :-)  I was just 
trying to be greedy and see if I could become even faster.


Executing ANALYZE (just once: the results are saved in the database) might 
help SQLite pick the best index.  However, if you have that index I'd be 
stunned if you have any real problem with the speed of SQLite, unless you 
are hampering it in some way, perhaps with badly chosen PRAGMAs.


The speed is fine.  I am just a greedy punk. :-)

To answer your original question, having that index and putting both terms 
in your WHERE clause is the recognised way of making SQL do the search you 
want.  From there it's up to SQL to do its job quickly and it shouldn't be 
your problem.


Yep.  Thanks. 


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

Re: [sqlite] Setting boundaries in a search

[jose isaias cabrera]

"RSmith" wrote...



On 2014/07/16 14:23, jose isaias cabrera wrote:

"Simon Slavin" wrote...


That way is not particularly slow.  You just need to have a good index. 
A good index for that search would be


CREATE INDEX sci ON startcodes (code,id)

You will find that that SELECT will then be blisteringly fast even with 
millions of rows in your table.


I do have that INDEX for that id and table.  Thanks. :-)  I was just 
trying to be greedy and see if I could become even faster. Thanks for 
your help.


Greed is good in this regard :)

Are all the searches unique or do you repeat a lot of searches for a very 
specific range? If the latter, then partitioning the table (well in SQLite 
that would really be a second derived table) might speed things up if 
space is not an issue, but I would only invest the design time for this 
once the standard query is proven to be slow - which might be the case.


Now that you mention this, I remember someone in this list that suggested 
for me to break this table down into closed projects (many), archived 
projects (the list keeps growing) and open projects.  So, thanks for this 
reminder.  I just have to write another email to this list later when I make 
those changes to see how I would search in all tables (Archived, Closed and 
Open) for all projects (archived, closed and opened) that belong to, say 
cust='Xerox', but, that will be later. :-)


Thanks. 


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

Re: [sqlite] Setting boundaries in a search

[jose isaias cabrera]

"Rob Willett" wrote...

Hi,

Can I add my very first piece of advice after listening and reading for 
the last 6-9 months :)


I’ll second what Simon says, I use the very same technique for a table 
with 4M+ records in and its so fast I thought I had an error and looked 
for bugs in my code.  I assumed (incorrectly) that it would be very slow, 
It isn’t.


I’ll also add one other piece of advice to the people clogging up the list 
on hot journals with childish comments. I’m a very old developer, C and 
UNIX, well into my 4th decade of programming. I have learnt over the years 
that some things I know very well and some things (SQL and SQLIte are 
excellent examples) I’m a novice and a noob and a newbie and all those 
other words we use. This list is an excellent source of knowledge and very 
valuable (both in terms of time and money). I have learnt an awful lot 
from reading the mails here, there are often very good questions and 
normally excellent answers from people who take a significant amount of 
time to understand the problem and to write comprehensive replies. I thank 
all the people who write such good replies and maintain the high quality 
of the mailing list. It is very rare indeed to have short and curt answers 
to people who make the effort to write a decent question.


Whilst I cannot add much to any SQL discussion (point one above excepted, 
of which I’m sinfully proud to have contributed something at long last, 
even if its only to confirm what somebody else has done), I have come to 
realise that the people who answer here are real experts, I will not 
embarrass them by naming names, and if they say something which disagrees 
with what I think or have done, my first thought now is to challenge what 
I think and how I do it, because there is a very, very, very good chance I 
am wrong and the people here on the list are right. I’m old enough not to 
be bothered by admitting I got it wrong, and that other people know SQLite 
better than I do.


There, I’ll now go back to the rock from which I came and lurk for another 
9 months :)


Thanks for all the input and very best wishes,

Rob


Thanks for this Rob.  I also want to thank the wonderful folks that provide 
support and answers to the newbies and oldies.  Thanks so much.


josé 


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

Re: [sqlite] Setting boundaries in a search

[Rob Willett]

All my searches are unique and go across the whole table. The range I select 
from us normally between 500 and 600 rows.  

I benchmarked the select over the growth of the database to circa 4m records 
and the slowdown was negligible. I'm not looking at optimising it as I have far 
better candidates for optimisation (sadly). I'm still building the application 
and still adding data, and may double the test database size over the next 
week. I'm confident (famous last words) it won't be a problem (stop sniggering 
at the back there).  

-- 

Rob


On Wednesday, 16 July 2014 at 15:49, RSmith wrote:

> 
> On 2014/07/16 14:23, jose isaias cabrera wrote:
> > "Simon Slavin" wrote...
> > > 
> > > That way is not particularly slow. You just need to have a good index. A 
> > > good index for that search would be
> > > 
> > > CREATE INDEX sci ON startcodes (code,id)
> > > 
> > > You will find that that SELECT will then be blisteringly fast even with 
> > > millions of rows in your table.
> > 
> > I do have that INDEX for that id and table. Thanks. :-) I was just trying 
> > to be greedy and see if I could become even faster. 
> > Thanks for your help.
> > 
> 
> 
> Greed is good in this regard :)
> 
> Are all the searches unique or do you repeat a lot of searches for a very 
> specific range? If the latter, then partitioning the table 
> (well in SQLite that would really be a second derived table) might speed 
> things up if space is not an issue, but I would only invest 
> the design time for this once the standard query is proven to be slow - which 
> might be the case.
> 
> ___
> 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] Setting boundaries in a search

[Simon Slavin]

On 16 Jul 2014, at 1:23pm, jose isaias cabrera  wrote:

> "Simon Slavin" wrote...
> 
>> CREATE INDEX sci ON startcodes (code,id)
>> 
>> You will find that that SELECT will then be blisteringly fast even with 
>> millions of rows in your table.
> 
> I do have that INDEX for that id and table.  Thanks. :-)  I was just trying 
> to be greedy and see if I could become even faster.

Executing ANALYZE (just once: the results are saved in the database) might help 
SQLite pick the best index.  However, if you have that index I'd be stunned if 
you have any real problem with the speed of SQLite, unless you are hampering it 
in some way, perhaps with badly chosen PRAGMAs.

To answer your original question, having that index and putting both terms in 
your WHERE clause is the recognised way of making SQL do the search you want.  
From there it's up to SQL to do its job quickly and it shouldn't be your 
problem.

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

Re: [sqlite] Setting boundaries in a search

[RSmith]

On 2014/07/16 14:23, jose isaias cabrera wrote:

"Simon Slavin" wrote...


That way is not particularly slow.  You just need to have a good index.  A good 
index for that search would be

CREATE INDEX sci ON startcodes (code,id)

You will find that that SELECT will then be blisteringly fast even with 
millions of rows in your table.


I do have that INDEX for that id and table.  Thanks. :-)  I was just trying to be greedy and see if I could become even faster. 
Thanks for your help.


Greed is good in this regard :)

Are all the searches unique or do you repeat a lot of searches for a very specific range? If the latter, then partitioning the table 
(well in SQLite that would really be a second derived table) might speed things up if space is not an issue, but I would only invest 
the design time for this once the standard query is proven to be slow - which might be the case.


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

Re: [sqlite] Setting boundaries in a search

[Rob Willett]

Hi,

Can I add my very first piece of advice after listening and reading for the 
last 6-9 months :)

I’ll second what Simon says, I use the very same technique for a table with 4M+ 
records in and its so fast I thought I had an error and looked for bugs in my 
code.  I assumed (incorrectly) that it would be very slow, It isn’t. 

I’ll also add one other piece of advice to the people clogging up the list on 
hot journals with childish comments. I’m a very old developer, C and UNIX, well 
into my 4th decade of programming. I have learnt over the years that some 
things I know very well and some things (SQL and SQLIte are excellent examples) 
I’m a novice and a noob and a newbie and all those other words we use. This 
list is an excellent source of knowledge and very valuable (both in terms of 
time and money). I have learnt an awful lot from reading the mails here, there 
are often very good questions and normally excellent answers from people who 
take a significant amount of time to understand the problem and to write 
comprehensive replies. I thank all the people who write such good replies and 
maintain the high quality of the mailing list. It is very rare indeed to have 
short and curt answers to people who make the effort to write a decent question.

Whilst I cannot add much to any SQL discussion (point one above excepted, of 
which I’m sinfully proud to have contributed something at long last, even if 
its only to confirm what somebody else has done), I have come to realise that 
the people who answer here are real experts, I will not embarrass them by 
naming names, and if they say something which disagrees with what I think or 
have done, my first thought now is to challenge what I think and how I do it, 
because there is a very, very, very good chance I am wrong and the people here 
on the list are right. I’m old enough not to be bothered by admitting I got it 
wrong, and that other people know SQLite better than I do. 

There, I’ll now go back to the rock from which I came and lurk for another 9 
months :)

Thanks for all the input and very best wishes,

Rob



On 16 Jul 2014, at 06:48, Simon Slavin  wrote:

> 
>> On 16 Jul 2014, at 3:21am, jose isaias cabrera  wrote:
>> 
>> SELECT * from startcodes where code = 'e';
>> 
>> but I want to search only from id >= 8 and <= 14.  Is there a way to set the 
>> boundary for that SELECT that will only search ids 8-14?  I know I can do a 
>> WHERE id BETWEEN 8 AND 14, but is there another faster way?
> 
> That way is not particularly slow.  You just need to have a good index.  A 
> good index for that search would be
> 
> CREATE INDEX sci ON startcodes (code,id)
> 
> You will find that that SELECT will then be blisteringly fast even with 
> millions of rows in your table.
> 
> Simon.
> ___
> 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] Setting boundaries in a search

[jose isaias cabrera]

"Simon Slavin" wrote...


On 16 Jul 2014, at 3:21am, jose isaias cabrera  
wrote:


SELECT * from startcodes where code = 'e';

but I want to search only from id >= 8 and <= 14.  Is there a way to set 
the boundary for that SELECT that will only search ids 8-14?  I know I 
can do a WHERE id BETWEEN 8 AND 14, but is there another faster way?


That way is not particularly slow.  You just need to have a good index.  A 
good index for that search would be


CREATE INDEX sci ON startcodes (code,id)

You will find that that SELECT will then be blisteringly fast even with 
millions of rows in your table.


I do have that INDEX for that id and table.  Thanks. :-)  I was just trying 
to be greedy and see if I could become even faster.  Thanks for your help.


josé 


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

Re: [sqlite] Setting boundaries in a search

[jose isaias cabrera]

"Igor Tandetnik" wrote...


On 7/15/2014 10:21 PM, jose isaias cabrera wrote:

SELECT * from startcodes where code = 'e';

but I want to search only from id >= 8 and <= 14.


Just say so:

SELECT * from startcodes where code = 'e' and id between 8 and 14;

I know I can do a WHERE id BETWEEN 8 AND 14, but is there another faster 
way?


So you already know the answer. How exactly does it fail to satisfy your 
requirements?


Well, it was not that is not satisfying, but I have a huge DB and I thought 
that I can set the boundaries before the search and allow the SELECT to have 
less rows to work with.  But, your answer has satisfied me.  Thanks again, 
o'great one.


josé 


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

Re: [sqlite] Setting boundaries in a search

[Simon Slavin]

> On 16 Jul 2014, at 3:21am, jose isaias cabrera  wrote:
> 
> SELECT * from startcodes where code = 'e';
> 
> but I want to search only from id >= 8 and <= 14.  Is there a way to set the 
> boundary for that SELECT that will only search ids 8-14?  I know I can do a 
> WHERE id BETWEEN 8 AND 14, but is there another faster way?

That way is not particularly slow.  You just need to have a good index.  A good 
index for that search would be

CREATE INDEX sci ON startcodes (code,id)

You will find that that SELECT will then be blisteringly fast even with 
millions of rows in your table.

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

Re: [sqlite] Setting boundaries in a search

[Igor Tandetnik]

On 7/15/2014 10:21 PM, jose isaias cabrera wrote:

SELECT * from startcodes where code = 'e';

but I want to search only from id >= 8 and <= 14.


Just say so:

SELECT * from startcodes where code = 'e' and id between 8 and 14;


I know I
can do a WHERE id BETWEEN 8 AND 14, but is there another faster way?


So you already know the answer. How exactly does it fail to satisfy your 
requirements?

--
Igor Tandetnik

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

[sqlite] Setting boundaries in a search

[jose isaias cabrera]

Greetings.

Pardon the newbie question, but is there a way to set boundaries on a 
search?  Imagine this scenario:

startcodes
id,code,date
1,a,2014-08-06
2,b,2014-08-06
3,z,2014-08-06
4,g,2014-08-06
5,g,2014-08-06
6,j,2014-08-06
7,p,2014-08-06
8,t,2014-08-06
9,e,2014-08-06
10,w,2014-08-06
11,w,2014-08-06
12,y,2014-08-06
13,m,2014-08-06
14,o,2014-08-06
15,o,2014-08-06
16,p,2014-08-06
17,u,2014-08-06
18,u,2014-08-06
19,a,2014-08-06
20,a,2014-08-06

SELECT * from startcodes where code = 'e';

but I want to search only from id >= 8 and <= 14.  Is there a way to set the 
boundary for that SELECT that will only search ids 8-14?  I know I can do a 
WHERE id BETWEEN 8 AND 14, but is there another faster way?


thanks.

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