Looks to me like you've forgotten the biggest performance factor of all ...
starting a transaction before you begin the loop and committing it
afterwards.
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Gavin Kistner
Sent: Monday, September 22, 2008 9:45
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Gavin Kistner wrote:
> Mostly I'm sharing this as a curiosity, though I'm quite interested if
> anyone has a suggestion on why
> this might be so much slower on a roughly equivalent machine differing
> only in OS.
See points zero and five at
http:
warning: I know nothing about Ruby.
On 9/22/08, Gavin Kistner <[EMAIL PROTECTED]> wrote:
> (Right off the bat, let me say that I'm not sure if the problem here
> is Sequel, sqlite3-ruby, or
> sqlite. Just in case...)
>
> I have a Ruby script to migrate data from an old sqlite DB to a new
> sch
(Right off the bat, let me say that I'm not sure if the problem here
is Sequel, sqlite3-ruby, or
sqlite. Just in case...)
I have a Ruby script to migrate data from an old sqlite DB to a new
schema. It's quite simple, selecting rows from db 1 and creating
records in db 2. (A rough representat
On Sep 22, 2008, at 11:18 AM, Jay A. Kreibich wrote:
> On Mon, Sep 22, 2008 at 10:07:54AM -0400, D. Richard Hipp scratched
> on the wall:
>> I am reluctant to add to SQLite the ability to explicitly specify the
>> index for a query. I agree with Alex Scotti that the whole idea
>> seems
>> ve
> My sense is that most users don't want network share support, hence it
> is not default. For the relatively few that do want, it exists as an
> option.
I find that statement a bit ambiguous, so allow me to clarify my own
interpretation:
1. Few or probably no users want to NOT have network sha
In modern working environments, network shares are not an abnormality.
Sqlite Network shares work by default for pcs.
If sqlite network shares don't work by default for macs, it looks like
sqlite is broken or macs are broken. I suppose that's why apple made
it unbroken by default in the softw
On 9/22/08, BareFeet <[EMAIL PROTECTED]> wrote:
> > Right, does sound easy. But what if I am on a Mac (which I am), but
> > don't want network shares support (which I don't, well, not because I
> > have anything against it, but I just don't need it) in case it comes
> > with some side-effects?
>
> Right, does sound easy. But what if I am on a Mac (which I am), but
> don't want network shares support (which I don't, well, not because I
> have anything against it, but I just don't need it) in case it comes
> with some side-effects?
Well, when you're using the SQLite that shipped on your Mac
"P Kishor" <[EMAIL PROTECTED]> wrote in
message
news:[EMAIL PROTECTED]
> This is very cool. But, this raises another question (for my
> edification, natch). What if the transaction fails and the db rolls
> back? Do the triggered updates of the counts table also roll back?
Of course. These changes
On 9/22/08, BareFeet <[EMAIL PROTECTED]> wrote:
> > h... I understood DRH's statement as "no, it can't be enabled by
> > default in the source because that flag works only for Macs and it
> > causes breakage on other platforms." Unless, he started providing
> > platform specific code tarball
> h... I understood DRH's statement as "no, it can't be enabled by
> default in the source because that flag works only for Macs and it
> causes breakage on other platforms." Unless, he started providing
> platform specific code tarballs
I wouldn't expect it to require multiple code versions,
On 9/22/08, Jay A. Kreibich <[EMAIL PROTECTED]> wrote:
> On Mon, Sep 22, 2008 at 04:02:37PM -0600, Dennis Cote scratched on the wall:
> > P Kishor wrote:
>
> > > How do I get the number of rows that were deleted by the above DELETE?
> > > Do I first do a SELECT to find out the number of matches
On 9/22/08, Dennis Cote <[EMAIL PROTECTED]> wrote:
> P Kishor wrote:
>
> > So, I am creating a new database, and want to set up triggers to track
> > row counts in each table as rows are inserted or deleted.
> >
> > A couple of questions --
> >
> > 1. Do I have to create a separate pair of AFTER IN
On 9/22/08, BareFeet <[EMAIL PROTECTED]> wrote:
> >> It only works on a Mac. The build fails on other posix platforms.
> >
> > Then of course, it should only be enabled on Macs.
>
>
> Yes, I humbly agree. Can it please be enabled by default in the source
> so that when compiled on a Mac,
h
>> It only works on a Mac. The build fails on other posix platforms.
>
> Then of course, it should only be enabled on Macs.
Yes, I humbly agree. Can it please be enabled by default in the source
so that when compiled on a Mac, it will enable opening of files on a
network volume, in the same w
On Mon, Sep 22, 2008 at 04:02:37PM -0600, Dennis Cote scratched on the wall:
> P Kishor wrote:
> > How do I get the number of rows that were deleted by the above DELETE?
> > Do I first do a SELECT to find out the number of matches before doing
> > the DELETE?
>
> You don't need to the trigger fir
"Andrew Drummond" <[EMAIL PROTECTED]>
wrote in message
news:[EMAIL PROTECTED]
> I am trying to write an sqlite select statement that performs the
> below pseudo code, but am getting nowhere. Any help we be very
> appreciated.
>
> PSEUDO CODE
>
> for each element1 in (select address.* from numbers,
P Kishor wrote:
> So, I am creating a new database, and want to set up triggers to track
> row counts in each table as rows are inserted or deleted.
>
> A couple of questions --
>
> 1. Do I have to create a separate pair of AFTER INSERT and AFTER
> DELETE triggers for each table that I want to tr
So, I am creating a new database, and want to set up triggers to track
row counts in each table as rows are inserted or deleted.
A couple of questions --
1. Do I have to create a separate pair of AFTER INSERT and AFTER
DELETE triggers for each table that I want to track, or is there a way
to crea
Andrew Drummond wrote:
>
> for each element1 in (select address.* from numbers,address where
> numbers.number = "12345678" and numbers.address_id = address.address_id)
> {
> for each element2 in (select numbers.* from numbers where address_id =
> element1.address_id LIMIT 20)
> return ele
Hi,
I've checked the "Limits in sqlite" page and don't see any limits to
tablenames or columnnames. Does that mean there isn't any? (or at
least they're only limited by the maximum length of a sql statement).
Cheers,
Andy
___
sqlite-users mailing list
"Zbigniew Baniewski" <[EMAIL PROTECTED]> wrote
in message news:[EMAIL PROTECTED]
> On Mon, Sep 22, 2008 at 09:42:56PM +0100, Seun Osewa wrote:
>
>> You need FTS3:
>> http://www.sqlite.org/cvstrac/wiki?p=FtsUsage
>> It comes with the command line version of SQLite 3.6
>
> You mean: it won't work u
select a.address_id, a.name, n.number from address a, numbers n where
a.address_id=b.address_id
On Mon, Sep 22, 2008 at 8:28 PM, Andrew Drummond <[EMAIL PROTECTED]> wrote:
> That is given
>
> table "address" contains
>
> address_id , name
> --
> 1
On 9/22/08, Zbigniew Baniewski <[EMAIL PROTECTED]> wrote:
> On Mon, Sep 22, 2008 at 12:17:44PM +1000, BareFeet wrote:
>
> > select exists (select 1 from MyTable where condition)
> >
> > which will return a boolean result, and stop scanning the table after
> > the first match is found.
>
>
> Yes
On Mon, Sep 22, 2008 at 09:42:56PM +0100, Seun Osewa wrote:
> You need FTS3:
> http://www.sqlite.org/cvstrac/wiki?p=FtsUsage
> It comes with the command line version of SQLite 3.6
You mean: it won't work using SQLite's module for TCL?
--
pozdrawiam / regards
Hello everyone,
Hoping that I could get some help with a performance problem. Using version
3.5.2
Here are the tables:
CREATE TABLE Objects (ObjectId INTEGER PRIMARY KEY, Name TEXT)
CREATE TABLE Keywords4Objects (ObjectId INTEGER, KeywordId INTEGER)
CREATE TABLE Keywords (KeywordId INTEGER PRIMA
This was my fault. http://www.sqlite.org/cvstrac/chngview?cn=5654
strcasecmp() isn't available on all platforms, and I naively assumed
sqlite3StrICmp() would be (it's not in this case do to the way you
are compiling/linking). I'll review the issue and see what I can do.
On Mon, Sep 22, 2008 at
You need FTS3:
http://www.sqlite.org/cvstrac/wiki?p=FtsUsage
It comes with the command line version of SQLite 3.6
On Mon, Sep 22, 2008 at 8:43 PM, Zbigniew Baniewski <[EMAIL PROTECTED]> wrote:
> On Sun, Sep 21, 2008 at 07:50:23PM -0400, Igor Tandetnik wrote:
>
> > > although not always I want
>
On Mon, Sep 22, 2008 at 08:03:49AM -0400, D. Richard Hipp wrote:
> Version 3.6.3 fixes several bugs in version 3.6.2, most notably the
> problem with DISTINCT.
Just tried to compile and link against TCL 8.5.4 - unfortunately, after the
compilation:
#v+
% package require sqlite3
couldn't load f
Seems to me,
That maybe "index by" might be better served as an "access by" key phrase
instead. That way the access to the table can be described, join order etc. Not
just pick an index. That way you can programatically describe the "access"
path, index, full scan, rowid and potentially the
On Sun, Sep 21, 2008 at 07:50:23PM -0400, Igor Tandetnik wrote:
> > although not always I want
> > to fetch all that data. Sometimes I would just to count it.
>
> Don't call sqlite3_column_*. Just call sqlite3_step in a loop. But
> again, if you want to get a count of records, you are unlikely t
If you are using the verson of Sqlite in one big file you are expecting
a lot from the optimizer. I would never compile Sqlite that way,
instead do it the normal way and link the optimized. code. Also make
sure you remove the -g option which interferes with some AIX linking.
My experience wit
On Sun, Sep 21, 2008 at 07:07:33PM -0500, Jay A. Kreibich wrote:
> It sounds like you need to take a more general approach to speeding
> up your queries. If you've not yet looked at building appropriate
> indexes, that seems like a good place to start.
Right, proper indexing gives signific
I am trying to write an sqlite select statement that performs the below
pseudo code, but am getting nowhere. Any help we be very appreciated.
PSEUDO CODE
for each element1 in (select address.* from numbers,address where
numbers.number = "12345678" and numbers.address_id = address.address_id)
{
>
>It only works on a Mac. The build fails on other posix platforms.
Then of course, it should only be enabled on Macs.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
On Mon, Sep 22, 2008 at 12:17:44PM +1000, BareFeet wrote:
> select exists (select 1 from MyTable where condition)
>
> which will return a boolean result, and stop scanning the table after
> the first match is found.
Yes, thanks - that's right: it is partial solution indeed. "Partial" -
because
On 9/22/08, Dave Dyer <[EMAIL PROTECTED]> wrote:
>
> >
> >Probably this:
> >
> > http://www.sqlite.org/compile.html#enable_locking_style
> >
> >Compilation option "SQLITE_ENABLE_LOCKING_STYLE".
>
>
> Compiling the mac version with this flad defined seems to have
> fixed my problem. Is th
On Sep 22, 2008, at 3:02 PM, Dave Dyer wrote:
>
>>
>> Probably this:
>>
>> http://www.sqlite.org/compile.html#enable_locking_style
>>
>> Compilation option "SQLITE_ENABLE_LOCKING_STYLE".
>
> Compiling the mac version with this flad defined seems to have
> fixed my problem. Is there any reason
>
>Probably this:
>
> http://www.sqlite.org/compile.html#enable_locking_style
>
>Compilation option "SQLITE_ENABLE_LOCKING_STYLE".
Compiling the mac version with this flad defined seems to have
fixed my problem. Is there any reason this shouldn't be on
by default?
___
Since someone mentioned only the support in passing, not the details, I
thought I'd throw out this:
Microsoft SQL Server using the following syntax for hints:
SELECT * FROM FOO WITH (hints)
'hints' can included locking hints, index-usage hints, and whatnot.
I will quote some of the proposed ex
jason weaver wrote:
> I've searched and searched but haven't found anything that really answers
> this question. If I've missed something, please point me in the right
> direction.
>
> I want to put the "right" type of timestamp in my dbase. According to my
> research, the "right" type is like th
INTEGER PRIMARY KEY AUTOINCREMENT
2008/9/22 Victor Hugo Oliveira <[EMAIL PROTECTED]>
> hi everybody,
>
> SQLite 3 doesn't have a 'serial' datatype? If not is there a simple way
> that i can implement myself (i'm not a very experient programmer)?
>
> thanks
>
>
>
> Novos endereços, o Yahoo! q
On 9/22/08, Victor Hugo Oliveira <[EMAIL PROTECTED]> wrote:
> hi everybody,
>
> SQLite 3 doesn't have a 'serial' datatype? If not is there a simple way that
> i can implement myself (i'm not a very experient programmer)?
What do you mean by a serial datatype. If you want a sequence, you can
get
hi everybody,
SQLite 3 doesn't have a 'serial' datatype? If not is there a simple way that i
can implement myself (i'm not a very experient programmer)?
thanks
Novos endereços, o Yahoo! que você conhece. Crie um email novo com a sua
cara @ymail.com ou @rocketmail.com.
http://br.new.mai
Unfortunately gcc is not an option right now.
I wouldn't be very surprised if it turned out to be that the very large
source file is overwhelming IBM's optimizer. So far the non-optimized
compiles of sqlite3 are working fine.
-Original Message-
From: John Stanton [mailto:[EMAIL PROTECTED]
On 9/22/08, Steve Friedman <[EMAIL PROTECTED]> wrote:
>
> >>
> >> There seems to be no standard SQL way of providing hints to the query
> >> optimizer for which index to use. Every SQL database engine does it
> >> differently. The MySQL approach is the simplest by far. But even it
> >> is
Why something like "USE INDEX a,b,c"?
On Mon, Sep 22, 2008 at 5:42 PM, Steve Friedman <[EMAIL PROTECTED]> wrote:
> As a pedant, I have two comments:
>
> - INDEX BY is a verb form. I would think that INDEXED BY (a past
> participle) would be more accurate syntax since no new indices are being
> c
On 2008 September 16 (Tue) 07:12:02am PDT, "Jay A. Kreibich" <[EMAIL
PROTECTED]> wrote:
> On Mon, Sep 15, 2008 at 10:57:37PM -0700, Tomas Lee scratched on the wall:
>
>> I want to delete the 1000 members with the lowest scores. Assume that
>> it is extremely unlikely for two members to have iden
>>
>> There seems to be no standard SQL way of providing hints to the query
>> optimizer for which index to use. Every SQL database engine does it
>> differently. The MySQL approach is the simplest by far. But even it
>> is more complex than is really needed. I propose syntax for SQLite
We use gcc.
Ribeiro, Glauber wrote:
> This issue continues with version 3.6.2
>
> g
>
> -Original Message-
> From: Ribeiro, Glauber
> Sent: Friday, September 19, 2008 11:55 AM
> To: General Discussion of SQLite Database
> Subject: [sqlite] Core dumps on AIX with optimization
>
> Just
To me this is a very rational approach. It is simple and unambiguous to
understand and use and simple to implement compared to the alternative
schemes. That fits nicely with the "lite" approach.
Ad the directive to the SQL and measure the result and the effect is
immediately obvious. Hard to
On 2008 September 22 (Mon) 06:25:34am PDT, jason weaver <[EMAIL PROTECTED]>
wrote:
> "jason weaver" <[EMAIL PROTECTED]> wrote:
> news:[EMAIL PROTECTED]
>>> However, due to database locking issues, I need to do a bunch of
>>> inserts in one transaction or batch. Thus, I store them in a simple
>>>
This issue continues with version 3.6.2
g
-Original Message-
From: Ribeiro, Glauber
Sent: Friday, September 19, 2008 11:55 AM
To: General Discussion of SQLite Database
Subject: [sqlite] Core dumps on AIX with optimization
Just wondering, are there other AIX Sqlite users out there, and
jason weaver wrote:
> "jason weaver" <[EMAIL PROTECTED]> wrote:
> news:[EMAIL PROTECTED]
>>> However, due to database locking issues, I need to do a bunch of
>>> inserts in one transaction or batch. Thus, I store them in a simple
>>> queue. Therefore, the julianday('now') won't work because all o
On Mon, Sep 22, 2008 at 8:23 AM, D. Richard Hipp <[EMAIL PROTECTED]> wrote:
> In the two high-profile use cases, the programmers already have the
> statement using the "correct" index without an INDEX BY clause. They
> just want to be alerted if some future schema change alters the index
> choice
On Mon, Sep 22, 2008 at 08:25:34AM -0500, jason weaver scratched on the wall:
> What is the julianday("now") equivalent in python? I can't find a simple,
> straight-forward answer to this question.
Just ask SQLite for the time:
SELECT julianday('now');
(It is single quotes, BTW).
Th
On Sat, Sep 20, 2008 at 2:07 PM, ivo welch <[EMAIL PROTECTED]> wrote:
> * sqlitebrowser is very buggy under linux when it comes to importing
> csv files. often, nothing happens. sometimes, trying a second import
> works. just buggy.
sqlitebrowser is a third party application made by someone ext
On Sep 22, 2008, at 10:53 AM, Jeffrey Becker wrote:
> I think the policy other dbms
> systems have of making these things hints rather than requirements is
> a good one because it still allows the query optimizer to make the
> best choice when the hints its given become incorrect.
If you want
On Mon, Sep 22, 2008 at 10:07:54AM -0400, D. Richard Hipp scratched on the wall:
> I am reluctant to add to SQLite the ability to explicitly specify the
> index for a query. I agree with Alex Scotti that the whole idea seems
> very un-RDBMS like.
Well it is outside of the Relational Model,
Hi all,
I'm using a C++ wrapper to SQLite (CppSQLite3) and want to be able to
set the temp_store, page_size, cache_size etc. I am currently doing this
directly after opening the database (but before creating any tables)
with the sqlite3_exec statement, however when I come to do anything to
the
First, I have to agree that this is very 'un-rdbmsish'. I understand
that sometimes the programmer really does know better than the DB
engine which indexes it should use. However, the RDBMS is
fundamentally an abstraction layer. I think the policy other dbms
systems have of making these things h
I am reluctant to add to SQLite the ability to explicitly specify the
index for a query. I agree with Alex Scotti that the whole idea seems
very un-RDBMS like.
On the other hand, just because a feature is there does not mean
people have to use it. The documentation can make it clear that t
Instead of doing julianday('now') why not put a text-based timestamp
into whatever object you're queueing up and pass that into the
julianday() function to convert it to a real.
On Mon, Sep 22, 2008 at 9:51 AM, P Kishor <[EMAIL PROTECTED]> wrote:
> On 9/22/08, jason weaver <[EMAIL PROTECTED]> wrot
On 9/22/08, jason weaver <[EMAIL PROTECTED]> wrote:
> "jason weaver" <[EMAIL PROTECTED]> wrote:
> news:[EMAIL PROTECTED]
> >> However, due to database locking issues, I need to do a bunch of
> >> inserts in one transaction or batch. Thus, I store them in a simple
> >> queue. Therefore, the ju
Hello, Ivo,
Regarding:
"I hope my logarithm and standard deviation suggestion make it into
the list of possible future features."
Since sqlite is used for everything from tiny mp3 player firmware to
multi-gigabyte databases, the "lite" requirement precludes inclusion of
too many functions
"jason weaver" <[EMAIL PROTECTED]> wrote:
news:[EMAIL PROTECTED]
>> However, due to database locking issues, I need to do a bunch of
>> inserts in one transaction or batch. Thus, I store them in a simple
>> queue. Therefore, the julianday('now') won't work because all of my
>> batch inserts will
On Sun, 21 Sep 2008 10:03:07 +0400, Alexander Batyrshin wrote:
> I don't know, if it's really needed by community. But I got this erros
> by "make test" on x86_64-pc-linux-gnu platform:
>
> 12 errors out of 23377 tests
> Failures on these tests: lookaside-1.4 lookaside-1.5 memsubsys1-2.3
> memsub
I know I could get columne names. nevertheless, I find that
SHOW columns FROM table
was a pretty intuitive way to get this information. information
schema is less intuitive, but I would be happy to have something that
works across the board. why not have all of them?
I hope my logarithm and s
SQLite version 3.6.3 is now available for download from the website
http://www.sqlite.org/download.html
Version 3.6.3 fixes several bugs in version 3.6.2, most notably the
problem with DISTINCT.
D. Richard Hipp
[EMAIL PROTECTED]
___
sqlite-u
70 matches
Mail list logo
