Re: [sqlite] Mac file locking

[Dave Dyer]

>
>Yes, I've noticed and reported this same problem. It appears that the  
>SQLite bundled in Mac OS X has a special flag set to allow opening  
>database files on networked volume, and that works great. 


So what's the name of this special flag?


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

Re: [sqlite] Date/Time Pains

[Igor Tandetnik]

"jason weaver" <[EMAIL PROTECTED]> wrote
in message
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 have the same date and time.  And that doesn't
> work very well. ;)

You will have to deal with this in any case. E.g. on Windows the system 
timer resolution is 15ms by default. You can insert quite a few records 
in 15 ms.

It is unrealistic to expect that every record could be inserted with a 
unique timestamp. Find some other way to ensure uniqueness (e.g. just 
assign a sequential number to each).

Igor Tandetnik



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

[sqlite] Date/Time Pains

[jason weaver]

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 this:
- create table my_table(date_stuff real);

And for "right" way to store the dates, when I do inserts I should do the
equiv of this:
- insert into my_table values(julianday('now'));

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 have
the same date and time.  And that doesn't work very well. ;)
I need a python equivalent of the Sqlite julianday.  It seemed so simple of
a question.  But I won't kid you that I'm just a little bit confused by the
python representations of date and time. Can someone point me to what I
need?

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

Re: [sqlite] Mac file locking

[BareFeet]

Hi Dave,

> I'm experimenting with using networked files as sqlite databases  
> (yes, I'm aware this is a questionable practice)

It seems quite valid for remote access to make ad hoc small changes to  
large hosted database files, or to provide shared access to small user  
databases files via desktop GUIs.

> If the client is running on a mac, and the database file is not a  
> local disk, "database locked" error is returned immediately.

Yes, I've noticed and reported this same problem. It appears that the  
SQLite bundled in Mac OS X has a special flag set to allow opening  
database files on networked volume, and that works great.  
Unfortunately, it is not enabled by default in the SQLite source code.  
I have enabled it myself in the past and compiled my own later version  
of SQLite for Mac OS X, though I haven't tried it with recent releases  
and seem to recall someone else saying that the old method no longer  
works.

I would really like to see the SQLite source code and downloadable Mac  
OS X binary have this flag enabled, so that it behaves predictably the  
same as the version distributed with Mac OS X.

This has far reaching indirect consequences such as the developer of  
the Zentus JDBC SQLite driver matching their code also with the  
default distribution, so currently the dozen or so SQLite applications  
for Mac OS X that use the JDBC driver also fail to open networked  
database files. You can see some of the programs this limitation  
affects in my comparison table as "Can open database file on  
AppleShare volume" here:
http://www.tandb.com.au/sqlite/compare/

Richard and co, please enable this flag by default.

Thanks,
Tom
BareFeet

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

Re: [sqlite] Daily Digest- Is this an Option?

[BareFeet]

> IS it possible to get on a daily digest list so that it is not  
> clogging my e-box with the individual e-mails?
> I know of several other group that have that option
>
> Chris
>
> Design simplicity eliminates engineering complexity.


Hi Chris,

The design simplicity of this mail list eliminates requirement for any  
engineering complexity to enable digest mode ;-)

There's a link on the bottom of each and every message that you're  
receiving from the SQLite mailing list messages. Just click it. On the  
web page that appears, use the option: "Would you like to receive list  
mail batched in a daily digest?"

Tom

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

Re: [sqlite] Speeding up the simplest queries

[BareFeet]

>> I was using count(*) just to detect the presence of any record  
>> meeting given condition. Is it possible to make
>> it fast _not_ using count(*)? I need just a "boolean result" of 1/0  
>> (yes, there is at least one / there aren't any).
>>
> Add  limit 1  to the query, so that it stops after finding the
> first match.

Actually, IIRC, "limit" only operates on the rows after a full table  
scan, so doesn't help.

What you need instead is:

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.

Tom
BareFeet

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

Re: [sqlite] Daily Digest- Is this an Option?

[P Kishor]

On 9/21/08, chris newgent <[EMAIL PROTECTED]> wrote:
> IS it possible to get on a daily digest list so that it is not clogging my 
> e-box with the individual e-mails?
>  I know of several other group that have that option
>
>  Chris
>
>  Design simplicity eliminates engineering complexity.
>
>
>
>  ___
>  sqlite-users mailing list
>  sqlite-users@sqlite.org

Every email (including this one that you just sent)... has the
following URI at the bottom. Following that link and entering your
password will reveal untold riches and power over the world.

>  http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>


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

[sqlite] Daily Digest- Is this an Option?

[chris newgent]

IS it possible to get on a daily digest list so that it is not clogging my 
e-box with the individual e-mails?
I know of several other group that have that option

Chris

Design simplicity eliminates engineering complexity.


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

Re: [sqlite] Speeding up the simplest queries

[Gerry Snyder]

Zbigniew Baniewski wrote:
> On Sun, Sep 21, 2008 at 05:22:46PM -0500, Jay A. Kreibich wrote:
>
>   
>>   count(*) is an odd one...  In most database systems it is extremely
>>   fast, but in SQLite it tends to be rather slow.
>> 
>
> I forgot the important thing: usually I was using count(*) just to detect
> the presence of any record meeting given condition. Is it possible to make
> it fast _not_ using count(*)? I need just a "boolean result" of 1/0 (yes,
> there is at least one / there aren't any).
>   
Add  limit 1  to the query, so that it stops after finding the 
first match.


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

Re: [sqlite] Speeding up the simplest queries

[Jay A. Kreibich]

On Mon, Sep 22, 2008 at 01:13:30AM +0200, Zbigniew Baniewski scratched on the 
wall:
> On Sun, Sep 21, 2008 at 05:22:46PM -0500, Jay A. Kreibich wrote:
> 
> >   count(*) is an odd one...  In most database systems it is extremely
> >   fast, but in SQLite it tends to be rather slow.
> >   [..]
> >   If you search the archives, you'll find many discussions on the best
> >   way to create a system table that keeps track of the number of rows
> >   in each table via triggers.
> 
> The problem is, that it's not only about global number of records

  Oh, sure... I wasn't very clear about that.

  What I said about count(*) being slower in SQLite only applies to
  global table counts, such as "SELECT count(*) FROM ;" where there
  is no WHERE clause.  Most database systems can optimize this type of
  query just use the internal table structures to return an answer almost
  instantly.

  As soon as you add a WHERE clause, then database systems are forced to do
  some kind of lookup via full table scan or index searches, however.
  There is no significant performance different in these types of
  situations.

  That said, assuming the WHERE clause can use an available index,
  indexes can be used to speed up a conditional search, including
  testing for the presence of a row (as described in your other email).

> - I would
> to have a possibility to quickly count number of records found by
> conditional queries like: "select count(*) from table where ".

  From a performance standpoint, the "count(*)" part of this statement
  is mostly irrelevant.  If you can speed up the WHERE condition, you
  should speed up any type of count().

> Partially it can be solved by moving the task to the application (fetch,
> then count list size), although not always I want to fetch all that data.

  That's not going to be any faster, although the difference in SQLite
  vs a traditional network-based client/server system is going to be
  much less.  Regardless, returning values is still going to take
  more resources and more time than just counting them.

> It wasn't a problem, when I was counting several thousands of records - but,
> as I can see, it will be inconvenient in the case of the larger table.

  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.

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Our opponent is an alien starship packed with atomic bombs.  We have
 a protractor."   "I'll go home and see if I can scrounge up a ruler
 and a piece of string."  --from Anathem by Neal Stephenson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Duplicated primary key error

[Mariano Martinez Peck]

Hi everybody!

This is my first post in this list! I am very newbie with Sqlite. This is
the first time I am trying to use it. I am using Sqlite3 trough C interface.
The problem I have is this: I have a table created, just like this:

CREATE TABLE materia(
codigo INTEGER PRIMARY KEY,
nombre CHARACTER VARYING(50),
observaciones CHARACTER VARYING(255),
)

Then I do 2 inserts one after the other, with the same data. For example:

INSERT INTO materia(codigo, nombre, observaciones) VALUES (55, ''TADP'',
''Nothing")

After doing this, I thought the second query ( i am using sqlite3_step()
function ) will returns me a
SQLITE_ERROR.
However, it SQLITE_IOERR_BLOCKED.

Is this correct? what should sqlite3_step returns me in this case?

Many thanks for the help,

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

Re: [sqlite] Performance/bug in multikey 'group by' in 3.6.2

[Igor Tandetnik]

"D. Richard Hipp" <[EMAIL PROTECTED]> wrote
in message news:[EMAIL PROTECTED]
> On Sep 21, 2008, at 8:51 AM, Russ Leighton wrote:
>
>> I am interested in ... a way
>> to constraint/control index selection on queries.
>>
>
> What other SQL database engines have this capability and what syntax
> do they use?

MS SQL Server:

http://msdn.microsoft.com/en-us/library/ms187713.aspx

Igor Tandetnik



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

Re: [sqlite] Speeding up the simplest queries

[Igor Tandetnik]

"Zbigniew Baniewski" <[EMAIL PROTECTED]> wrote
in message news:[EMAIL PROTECTED]
> The problem is, that it's not only about global number of records - I
> would to have a possibility to quickly count number of records found
> by conditional queries like: "select count(*) from table where
> ". Partially it can be solved by moving the task to the
> application (fetch, then count list size)

It's unlikely to be any faster. count(*) does precisely the same thing 
internally, anyway. Depending on the nature of , you might be 
able use indexes to speed up the WHERE part.

> 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 to be 
able to do any better than a statement using count().
-- 
With best wishes,
Igor Tandetnik

With sufficient thrust, pigs fly just fine. However, this is not 
necessarily a good idea. It is hard to be sure where they are going to 
land, and it could be dangerous sitting under them as they fly 
overhead. -- RFC 1925 



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

Re: [sqlite] Speeding up the simplest queries

[Zbigniew Baniewski]

On Sun, Sep 21, 2008 at 05:22:46PM -0500, Jay A. Kreibich wrote:

>   count(*) is an odd one...  In most database systems it is extremely
>   fast, but in SQLite it tends to be rather slow.

I forgot the important thing: usually I was using count(*) just to detect
the presence of any record meeting given condition. Is it possible to make
it fast _not_ using count(*)? I need just a "boolean result" of 1/0 (yes,
there is at least one / there aren't any).
-- 
pozdrawiam / regards

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

Re: [sqlite] Speeding up the simplest queries

[Zbigniew Baniewski]

On Sun, Sep 21, 2008 at 05:22:46PM -0500, Jay A. Kreibich wrote:

>   count(*) is an odd one...  In most database systems it is extremely
>   fast, but in SQLite it tends to be rather slow.
>   [..]
>   If you search the archives, you'll find many discussions on the best
>   way to create a system table that keeps track of the number of rows
>   in each table via triggers.

The problem is, that it's not only about global number of records - I would
to have a possibility to quickly count number of records found by
conditional queries like: "select count(*) from table where ".
Partially it can be solved by moving the task to the application (fetch,
then count list size), although not always I want to fetch all that data.
Sometimes I would just to count it.

It wasn't a problem, when I was counting several thousands of records - but,
as I can see, it will be inconvenient in the case of the larger table.
-- 
pozdrawiam / regards

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

[sqlite] Speeding up the simplest queries

[Zbigniew Baniewski]

I've created a test database with almost one million records (about 20 fields
each). I noticed, that the simple query like "select count(*) from table"
takes about 10 seconds (the database file is of about 300 MB size).

I'm wondering: is it the limit - or is it still possible to reduce the
response time? I mean the simplest queries here, mostly: "select * from",
"...where something=''", "...where something like '%that%'. Yes, I know:
indexing. Unfortunately, indexing won't have any effect on "count(*)". But
of course, indexing tips are welcome too (for SELECT ...).

Found a page http://web.utk.edu/~jplyon/sqlite/SQLite_optimization_FAQ.html
- but perhaps someone gathered some more tips? Maybe latest versions of
SQLite (the page is from 2003) are giving some more options for tuning?
-- 
pozdrawiam / regards

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

Re: [sqlite] Performance/bug in multikey 'group by' in 3.6.2

[Russ Leighton]

Personally, I like the Mysql syntax best of the below links. Seems  
natural and unobtrusive (unlike 'hints').

On Sep 21, 2008, at 12:12 PM, Alex Scotti wrote:

> not surprisingly, the db2 approach is the only one that seems to
> follow the "ideal" of the relational dbms.  looks like you get to
> provide to the engine information about your data, rather than
> describing to the engine what steps it's supposed to take.  hints
> that directly influence query plans seem like a huge step backwards.
> if you want tight control over what the engine does you use a pure
> rules based (rather than cost based) optimizer.  but nobody
> (including sqlite) does that anymore for good reasons.
>
>
> On Sep 21, 2008, at 12:59 PM, Stephen Woodbridge wrote:
>
>> D. Richard Hipp wrote:
>>> On Sep 21, 2008, at 8:51 AM, Russ Leighton wrote:
>>>
 I am interested in ... a way
 to constraint/control index selection on queries.

>>>
>>> What other SQL database engines have this capability and what syntax
>>> do they use?
>>
>> Richard,
>>
>> Hope this are useful:
>>
>> Oracle:
>> http://www.adp-gmbh.ch/ora/sql/hints/index.html
>> http://download.oracle.com/docs/cd/E12096_01/books/admintool/
>> admintool_PhysicalSetup32.html
>>
>> Postgresql:
>> Does not support it unless it is a very recent addition, but its
>> planner
>> occasionally/frequently? makes a poor choice and adding support for
>> hints ala Oracle and/or discussions like "Why didn't Postgresql use  
>> my
>> index?" regularly occur on the user list.
>>
>> DB2:
>> http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp?topic=/
>> com.ibm.db2.udb.doc/admin/t0005308.htm
>>
>> MySQL:
>> http://dev.mysql.com/doc/refman/5.0/en/index-hints.html
>>
>> -Steve
>> ___
>> 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] Performance/bug in multikey 'group by' in 3.6.2

[Alex Scotti]

not surprisingly, the db2 approach is the only one that seems to  
follow the "ideal" of the relational dbms.  looks like you get to  
provide to the engine information about your data, rather than  
describing to the engine what steps it's supposed to take.  hints  
that directly influence query plans seem like a huge step backwards.   
if you want tight control over what the engine does you use a pure  
rules based (rather than cost based) optimizer.  but nobody  
(including sqlite) does that anymore for good reasons.


On Sep 21, 2008, at 12:59 PM, Stephen Woodbridge wrote:

> D. Richard Hipp wrote:
>> On Sep 21, 2008, at 8:51 AM, Russ Leighton wrote:
>>
>>> I am interested in ... a way
>>> to constraint/control index selection on queries.
>>>
>>
>> What other SQL database engines have this capability and what syntax
>> do they use?
>
> Richard,
>
> Hope this are useful:
>
> Oracle:
> http://www.adp-gmbh.ch/ora/sql/hints/index.html
> http://download.oracle.com/docs/cd/E12096_01/books/admintool/ 
> admintool_PhysicalSetup32.html
>
> Postgresql:
> Does not support it unless it is a very recent addition, but its  
> planner
> occasionally/frequently? makes a poor choice and adding support for
> hints ala Oracle and/or discussions like "Why didn't Postgresql use my
> index?" regularly occur on the user list.
>
> DB2:
> http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp?topic=/ 
> com.ibm.db2.udb.doc/admin/t0005308.htm
>
> MySQL:
> http://dev.mysql.com/doc/refman/5.0/en/index-hints.html
>
> -Steve
> ___
> 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] Performance/bug in multikey 'group by' in 3.6.2

[Russell Leighton]

A reason I think such functionality would be ideal for sqlite is that  
it avoids the need for a fancy query plan optimizer. The user would  
have a way to direct the query plan if the simple and obvious plan is  
suboptimal.



On Sep 21, 2008, at 11:36 AM, "D. Richard Hipp" <[EMAIL PROTECTED]> wrote:

>
> On Sep 21, 2008, at 8:51 AM, Russ Leighton wrote:
>
>> I am interested in ... a way
>> to constraint/control index selection on queries.
>>
>
> What other SQL database engines have this capability and what syntax
> do they use?
>
>
> D. Richard Hipp
> [EMAIL PROTECTED]
>
>
>
> ___
> 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] Performance/bug in multikey 'group by' in 3.6.2

[Stephen Woodbridge]

D. Richard Hipp wrote:
> On Sep 21, 2008, at 8:51 AM, Russ Leighton wrote:
> 
>> I am interested in ... a way
>> to constraint/control index selection on queries.
>>
> 
> What other SQL database engines have this capability and what syntax  
> do they use?

Richard,

Hope this are useful:

Oracle:
http://www.adp-gmbh.ch/ora/sql/hints/index.html
http://download.oracle.com/docs/cd/E12096_01/books/admintool/admintool_PhysicalSetup32.html

Postgresql:
Does not support it unless it is a very recent addition, but its planner 
occasionally/frequently? makes a poor choice and adding support for 
hints ala Oracle and/or discussions like "Why didn't Postgresql use my 
index?" regularly occur on the user list.

DB2:
http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp?topic=/com.ibm.db2.udb.doc/admin/t0005308.htm

MySQL:
http://dev.mysql.com/doc/refman/5.0/en/index-hints.html

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

Re: [sqlite] Performance/bug in multikey 'group by' in 3.6.2

[Russell Leighton]

Oracle has 'hints' which live in the comments emdedded in the select.  
Google 'oracle hint use index'. The 3rd hit down my result list has a  
nice overview.(I'd send the link but this stupid iPhone has no cut-n- 
paste). I think that hints are really ugly. Not sure about the other  
big dmbs.




On Sep 21, 2008, at 11:36 AM, "D. Richard Hipp" <[EMAIL PROTECTED]> wrote:

>
> On Sep 21, 2008, at 8:51 AM, Russ Leighton wrote:
>
>> I am interested in ... a way
>> to constraint/control index selection on queries.
>>
>
> What other SQL database engines have this capability and what syntax
> do they use?
>
>
> D. Richard Hipp
> [EMAIL PROTECTED]
>
>
>
> ___
> 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] Performance/bug in multikey 'group by' in 3.6.2

[D. Richard Hipp]

On Sep 21, 2008, at 8:51 AM, Russ Leighton wrote:

> I am interested in ... a way
> to constraint/control index selection on queries.
>

What other SQL database engines have this capability and what syntax  
do they use?


D. Richard Hipp
[EMAIL PROTECTED]



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

Re: [sqlite] prices

[Regnirps]

In a message dated 9/21/08 4:21:44 AM, [EMAIL PROTECTED] writes:


> (I'm sorry. I saw this and I just couldn't resist. I deserve everything you
> throw at me, but this is even better than the Windows Cache Manager bashing
> a few days ago.)
> 

Doesn't the Windows Cash Manager list the prices?

-- Charlie Springer


**
Looking for simple solutions to your 
real-life financial challenges?  Check out WalletPop for the latest news and 
information, tips and calculators.
  
(http://www.walletpop.com/?NCID=emlcntuswall0001)
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Performance/bug in multikey 'group by' in 3.6.2

[Russ Leighton]

On Sep 19, 2008, at 6:30 PM, D. Richard Hipp wrote:

>
> On Sep 19, 2008, at 5:47 PM, Russ Leighton wrote:
>
>>
>> Observation 1:  Group by (in my case) is faster w/out using the index
>> than with using the index by 10X
>>
>> In my app I have a table-
>>
>> create table foo(k1integer ,k2 integer,k3 integer,...);
>> create index foo_idx on foo(k1,k2,k3);
>>
>> when I do-
>>
>> select k1,k2,sum() as s,count(1) as c from foo group by k1,k2;
>>
>> The query is 10X slower with the above index created as when I drop
>> the index and don't use it. Bug or
>> just happens to be the case for some data distribution this is just
>> the way it is? Yes, I know I  have
>> a 3 column index and only using 2 for this query.
>
> I do not see how it is possible for what you say to be true - unless
> you have omitted important details of your query, such as a WHERE
> clause.
>


Okay, this is a bit embarrassing but I can't seem to reproduce the  
performance
difference. I spent hours tracking this issue down previously and  
now , going back to reproduce,
I don't see it. Index'd group by's are always faster. Must have been  
some strange state of the equipment.

Sorry for the false alarm.

That said, the _real_ thing I am interested in as a requested  
enhancement is a way
to constraint/control index selection on queries. This would give me  
an out should
the situation above repeat itself in a more consistent manner.

Thanks for sqlite. It is an amazing tool.

BTW, in my mind lite != small, rather  lite == simple+powerful+fast .  
We are using it over very large
quantities of data on very powerful servers.

-Russ

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

Re: [sqlite] prices

[Sherief N. Farouk]

> -Original Message-
> From: [EMAIL PROTECTED] [mailto:sqlite-users-
> [EMAIL PROTECTED] On Behalf Of Arvind M
> Sent: Sunday, September 21, 2008 3:30 AM
> To: sqlite-users@sqlite.org
> Subject: [sqlite] prices
> 
> dear sir
> 
> i am interested in  purchase of licence of sqlite server
> 
> kindly send me the detail
> 
> arvind
> systime

[Sherief N. Farouk] 

Dear Pointy Haired Boss,

Please let Dilbert handle this one instead.

Best Regards,

- Sherief

(I'm sorry. I saw this and I just couldn't resist. I deserve everything you
throw at me, but this is even better than the Windows Cache Manager bashing
a few days ago.)

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

Re: [sqlite] prices

[Gerry Snyder]

Arvind M wrote:
> dear sir 
>  
> i am interested in  purchase of licence of sqlite server 
>   

SQLite does not have a server-client architecture now, any more than it 
did five months ago.

It is all-in-one. You can get the source code and write a server-client 
pair yourself that uses the same file format as SQLite, if you desire.

SQLite is free.



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

[sqlite] prices

[Arvind M]

dear sir 
 
i am interested in  purchase of licence of sqlite server 
 
kindly send me the detail 
 
arvind 
systime


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

[sqlite] [3.6.2] make test errors

[Alexander Batyrshin]

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
memsubsys1-2.4 memsubsys1-3.2.4 memsubsys1-4.3 memsubsys1-4.4
memsubsys1-5.3 memsubsys1-6.3 memsubsys1-6.4 memsubsys1-7.4
memsubsys1-7.5
Unfreed memory: 1080 bytes
Writing unfreed memory log to "./memleak.txt"
Memory used:  now   1080  max 112168  max-size4128768
Page-cache used:  now  0  max  0  max-size   4096
Page-cache overflow:  now  0  max3258200
Scratch memory used:  now  0  max  0
Scratch overflow: now  0  max  33320  max-size  33320
Maximum memory usage: 112168 bytes
Current memory usage: 1080 bytes
Number of malloc()  : -1 calls
make: *** [test] Error 1



--
Alexander Batyrshin aka bash
bash = Biomechanica Artificial Sabotage Humanoid
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] DBD::SQLite::Amalgamation-3.6.1.2 FTS3 seg faulting... solution... upgrade code

[Alexander Batyrshin]

I mean what is benefits of using DBD::SQLite::Amalgamation? Where it
can be needed?
--
Alexander Batyrshin aka bash
bash = Biomechanica Artificial Sabotage Humanoid



On Sun, Sep 21, 2008 at 9:51 AM, P Kishor <[EMAIL PROTECTED]> wrote:
> On 9/21/08, Alexander Batyrshin <[EMAIL PROTECTED]> wrote:
>> Sorry for off-topic.
>>  What is main difference between DBD::SQLite and DBD::SQLite::Amalgamation?
>
> Exactly what it says on the box. The latter uses the SQLite
> amalgamation. The former doesn't. They both work exactly the same for
> the end user.
>
>
>>  --
>>  Alexander Batyrshin aka bash
>>  bash = Biomechanica Artificial Sabotage Humanoid
>>
>>
>>
>>
>>  On Sat, Sep 20, 2008 at 8:04 PM, P Kishor <[EMAIL PROTECTED]> wrote:
>>  > I encountered this problem and solved it, so hopefully this will help
>>  > some other poor sod.
>>  >
>>  > Audrey Tang's otherwise most excellent DBD::SQLite::Amalgamation
>>  > (bless her for this incredible package) was causing segmentation
>>  > faults for me while doing FTS3 searches on a RH ES3 Linux box. The
>>  > package version 3.6.1.2 has code for SQLite 3.6.1.
>>  >
>>  > I upgraded the code in the package to SQLite 3.6.2 (just copied the
>>  > corresponding files from the SQLite tarball... one file had to be
>>  > renamed from sqlite.c to sqlite-amalgamation.c) and rebuilt the DBD.
>>  > Everything is now fine in happy town.
>>  >
>>  > --
>>  > Puneet Kishor
>>
>> > ___
>>  > sqlite-users mailing list
>>  > sqlite-users@sqlite.org
>>  > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>  >
>>
>
>
> --
> Puneet Kishor http://punkish.eidesis.org/
> Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/
> Open Source Geospatial Foundation (OSGeo) http://www.osgeo.org/
> ___
> 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