Re: [sqlite] Speeding up your SQLite DB (Windows mostly)

[Chris Schirlinger]

> I wouldn't sell the BDE-style components short, as they may be useful
> for some, if well-written and well-documented, but I like simple. 

Oh quite true, but in my case (and really what I was trying to get at 
in that monotribe) was to get the speediest database access, you'd be 
better server looking at accesing the DB natively.

The biggest speed increase I found was replacing some old "IF 
Locate() THEN Update() ELSE Insert()" with INSERT OR REPLACE INTO sql 
which sped things up a LOT

Componants out there now needed to roll their own Locates and Seeks 
which are generic, and not necessarly the fastest way of doing things

> I'm a big fan of small and lightweight, and have chosen that route
> myself. If you're talking to a bare-bones DLL interface, be careful
> of memory leaks. Much of the "Delphi wrapper plus" code I've tried
> has problems I've caught even as a rank beginner. 

Yeah, tell me about it :) I am going through ours as we speak (or 
rather was, got bored and saw I had new email) fixing it up, 
indenting, fixing the hints and warnings (hate those) and making sure 
it is bug free 

> Thanks for mentioning the bits and pieces. I'll keep them handy.

welcome :) Someone told me to make a Wiki of it so I did, its 
somewhere now in the Wiki area

Re: [sqlite] Speeding up your SQLite DB (Windows mostly)

[mswarm]

>>From what I experienced, the exported functions from the DLL were 
>simple to use and did everything you wanted, though perhaps slightly 
>differently from the Delphi DBE way some people may be used to

I wouldn't sell the BDE-style components short, as they may be useful
for some, if well-written and well-documented, but I like simple. 


>I found a simple wrapper class worked better than trying to use a 
>bunch of componants pretending to be the BDE, but of course, a lot 
>depends on what you want to do and how confident you are using 
>exported DLL functions

I'm a big fan of small and lightweight, and have chosen that route
myself. If you're talking to a bare-bones DLL interface, be careful
of memory leaks. Much of the "Delphi wrapper plus" code I've tried
has problems I've caught even as a rank beginner. I think some mid-
level code is a good idea, though, even if only to get it right one 
time and insulate programmers from pointers, leaks and details. 



>2) Indexes and DB structure are important. Fairly generic DB rule one 
>I suppose, but this is an SQL DB, its VITAL you add the indexs you 
>need, and even MORE important you *DON'T* add the indexs you don't 
>need.
>Plan your DB before hand with n eye that everything has a function, 
>if you don't use it, don't have it. There is more about this on the 
>SQLite docs
>
>3) The default cluster size for a Windows NTFS system seems to be 
>4096 bytes. Setting the SQLite database page size to the same size 
>will speed up your database on systems where the cluster size is the 
>same (Note, Linux cluster I believe to 1024 which is the default for 
>new SQLite databases)
>Easiest way to tell your cluster size is to defragment your drive and 
>analyze. It tells you in there
>
>To set the SQLite page size, create a new *EMPTY* database and do a
>PRAGMA page_size=4096;
>Now create your tables immedeatly (if you close down the SQLite 
>commandline program and reopen the DB, the page size is reset to 
>1024). The page size must be set before the first table is created. 
>Once that tables made, you can't change the size
>
>Typing
>PRAGMA page_size;
>will tell you what it is currently set at
>
>4) SQLite doesn't support clustered indexes (simply, indexes that 
>force the data in the database to be physically layed down in the 
>SAME order as the index needs it to be in.) 
>This means that if your index is sequential INTEGER, the records are 
>physically layed out in the database in that INTEGERs order, 1 then 2 
>then 3.
>
>You can't make a Clustered index, but you CAN sort your data in order 
>so that any historical data is ordered nicely. Of course, as the 
>database matures, you lose that, but it helps
>
>Someone else posted this, and it is a nice example to use, so I will. 
>If you have a table WIBBLE whose field KEY you want to access a lot, 
>it would be nice if everything was in order. Using the command line 
>tool, you can create a fake cluster by doing the following:
>
>   create table wibble2 as select * from wibble;  
>   delete from wibble;
>   insert into wibble select * from wibble2 order by key;
>   drop table wibble2;
>
>5) Ok, as a reward for reading this far, here is the dumb thing.
>
>Be *VERY, VERY* careful what you name your database, especially the 
>extension
>
>For example, if you give all your databases the extension .sdb 
>(SQLite Database, nice name hey?) you discover that the SDB extension 
>is already associated with APPFIX PACKAGES.
>
>Now, here is the cute part, APPFIX is an executable/package that 
>Windows XP recognizes, and it will, (emphasis mine) *ADD THE DATABASE 
>TO THE SYSTEM RESTORE FUNCTIONALITY*
>
>This means, stay with me here, every time you write ANYTHING to the 
>database, the Windows XP system thinks a bloody executable has 
>changed and copies your ENTIRE 800 meg database to the system restore 
>directory
>
>I recommend something like DB or DAT. 

Thanks for mentioning the bits and pieces. I'll keep them handy.

Nathan

Re: [sqlite] Speeding up your SQLite DB (Windows mostly)

[Chris Schirlinger]

> Make a wiki page.

Not a bad idea :) Done

Re: [sqlite] What forms of comments does SQLite honor?

[mswarm]

>>Not in C. The closing */ is necessary.
>
>That's the case in  Delphi also, practically speaking. Code 
>ends with a end. and any (* before that would hide it from the 
>compiler. 
>
>But I just tested the /* in SQLite, and apparently it comments 
>eveything to the end of the text block without causing an error. 
>
>So the valid comment forms appear to be: 
>
>-- to end of line
>/* to the next */
>/* to the end of the text block 
>
>-- causes a /* before EOL to be ignored 
>
>In looking up SQL comments in general on SQL newsgroups, 
>I found at least one claim that SQL has no comment mechanism.
>I wonder if that's true for the standard. 
>
>In any event, I'm glad to see SQLite has both line and block
>comments. 
>
>Nathan 

Not suggesting the DLL ought to have a requirement to terminate 
a /* comment, but I can see the wisdom of having my editor catch 
that. I spent some time looking for the problem with code I had 
block-commented with /* words /* instead of /* words */.  

Maybe longtime C users would spot that right away, but my eyes 
gloss right over it. 

NH

Re: [sqlite] Could location of journal file be changed?

[Kurt Welgehausen]

> ... an alternate location for the journal file ...?

This has been discussed before; it's not likely to happen.
It wouldn't be safe without changing the naming scheme.
Two users with identically named db files, using the same
alternate directory, would overwrite each other's  journal
files.

> I'd rather not put the database in a world-writable directory.

Why not? Any user can destroy the db file now -- or are you
afraid that someone will delete the db file accidentally?

Regards

Re: [sqlite] Speeding up your SQLite DB (Windows mostly)

[Cory Nelson]

Make a wiki page.


On Thu, 10 Feb 2005 11:18:39 +1100, Chris Schirlinger
<[EMAIL PROTECTED]> wrote:
> I've been posting a lot on speed and SQLite, just thought I'd dump
> the results and findings into one message including a nice example on
> how dumb you can get in the hopes no one else falls this low :)
> 
> After several weeks of playing with SQlite3 we have finally gotton
> what we want out of a DB. For single user apps (I can't talk much
> about multi-user, that wasn't what we were doing) SQLite is
> fantastic, fast, easy to use and has great functionality. (hell, it's
> even fun, I wv INSERT OR REPLACE INTO)
> 
> Here's the various things we discovered to speed up the system the
> most. This relates *mostly* to Windows and Delphi enviroments but may
> help others:
> 
> 1) Talk to the SQLite functions directly if you can, ExecSQLing stuff
> down to the DB is better than trying to use some faked Locate or Seek
> function. Even if you have to change the way you code, I recommend
> it.
> From what I experienced, the exported functions from the DLL were
> simple to use and did everything you wanted, though perhaps slightly
> differently from the Delphi DBE way some people may be used to
> 
> I found a simple wrapper class worked better than trying to use a
> bunch of componants pretending to be the BDE, but of course, a lot
> depends on what you want to do and how confident you are using
> exported DLL functions
> 
> 2) Indexes and DB structure are important. Fairly generic DB rule one
> I suppose, but this is an SQL DB, its VITAL you add the indexs you
> need, and even MORE important you *DON'T* add the indexs you don't
> need.
> Plan your DB before hand with n eye that everything has a function,
> if you don't use it, don't have it. There is more about this on the
> SQLite docs
> 
> 3) The default cluster size for a Windows NTFS system seems to be
> 4096 bytes. Setting the SQLite database page size to the same size
> will speed up your database on systems where the cluster size is the
> same (Note, Linux cluster I believe to 1024 which is the default for
> new SQLite databases)
> Easiest way to tell your cluster size is to defragment your drive and
> analyze. It tells you in there
> 
> To set the SQLite page size, create a new *EMPTY* database and do a
> PRAGMA page_size=4096;
> Now create your tables immedeatly (if you close down the SQLite
> commandline program and reopen the DB, the page size is reset to
> 1024). The page size must be set before the first table is created.
> Once that tables made, you can't change the size
> 
> Typing
> PRAGMA page_size;
> will tell you what it is currently set at
> 
> 4) SQLite doesn't support clustered indexes (simply, indexes that
> force the data in the database to be physically layed down in the
> SAME order as the index needs it to be in.)
> This means that if your index is sequential INTEGER, the records are
> physically layed out in the database in that INTEGERs order, 1 then 2
> then 3.
> 
> You can't make a Clustered index, but you CAN sort your data in order
> so that any historical data is ordered nicely. Of course, as the
> database matures, you lose that, but it helps
> 
> Someone else posted this, and it is a nice example to use, so I will.
> If you have a table WIBBLE whose field KEY you want to access a lot,
> it would be nice if everything was in order. Using the command line
> tool, you can create a fake cluster by doing the following:
> 
>create table wibble2 as select * from wibble;
>delete from wibble;
>insert into wibble select * from wibble2 order by key;
>drop table wibble2;
> 
> 5) Ok, as a reward for reading this far, here is the dumb thing.
> 
> Be *VERY, VERY* careful what you name your database, especially the
> extension
> 
> For example, if you give all your databases the extension .sdb
> (SQLite Database, nice name hey?) you discover that the SDB extension
> is already associated with APPFIX PACKAGES.
> 
> Now, here is the cute part, APPFIX is an executable/package that
> Windows XP recognizes, and it will, (emphasis mine) *ADD THE DATABASE
> TO THE SYSTEM RESTORE FUNCTIONALITY*
> 
> This means, stay with me here, every time you write ANYTHING to the
> database, the Windows XP system thinks a bloody executable has
> changed and copies your ENTIRE 800 meg database to the system restore
> directory
> 
> I recommend something like DB or DAT.
> 
> Have fun people and thanks for all the help :)
> 
> 


-- 
Cory Nelson
http://www.int64.org

Re: [sqlite] Speeding up your SQLite DB (Windows mostly)

[rbundy]

No, thank you for taking the time and effort for this very informative
post-mortem. There's some stuff here I didn't know, so my knowledge and
skills have also been expanded.

Regards.

rayB




*** Confidentiality and Privilege Notice ***

This e-mail is intended only to be read or used by the addressee. It is
confidential and may contain legally privileged information. If you are not
the addressee indicated in this message (or responsible for delivery of the
message to such person), you may not copy or deliver this message to anyone,
and you should destroy this message and kindly notify the sender by reply
e-mail. Confidentiality and legal privilege are not waived or lost by reason
of mistaken delivery to you.

Qantas Airways Limited
ABN 16 009 661 901

Visit Qantas online at http://qantas.com

[sqlite] Speeding up your SQLite DB (Windows mostly)

[Chris Schirlinger]

I've been posting a lot on speed and SQLite, just thought I'd dump 
the results and findings into one message including a nice example on 
how dumb you can get in the hopes no one else falls this low :)

After several weeks of playing with SQlite3 we have finally gotton 
what we want out of a DB. For single user apps (I can't talk much 
about multi-user, that wasn't what we were doing) SQLite is 
fantastic, fast, easy to use and has great functionality. (hell, it's 
even fun, I wv INSERT OR REPLACE INTO)

Here's the various things we discovered to speed up the system the 
most. This relates *mostly* to Windows and Delphi enviroments but may 
help others:

1) Talk to the SQLite functions directly if you can, ExecSQLing stuff 
down to the DB is better than trying to use some faked Locate or Seek 
function. Even if you have to change the way you code, I recommend 
it. 
>From what I experienced, the exported functions from the DLL were 
simple to use and did everything you wanted, though perhaps slightly 
differently from the Delphi DBE way some people may be used to

I found a simple wrapper class worked better than trying to use a 
bunch of componants pretending to be the BDE, but of course, a lot 
depends on what you want to do and how confident you are using 
exported DLL functions

2) Indexes and DB structure are important. Fairly generic DB rule one 
I suppose, but this is an SQL DB, its VITAL you add the indexs you 
need, and even MORE important you *DON'T* add the indexs you don't 
need.
Plan your DB before hand with n eye that everything has a function, 
if you don't use it, don't have it. There is more about this on the 
SQLite docs

3) The default cluster size for a Windows NTFS system seems to be 
4096 bytes. Setting the SQLite database page size to the same size 
will speed up your database on systems where the cluster size is the 
same (Note, Linux cluster I believe to 1024 which is the default for 
new SQLite databases)
Easiest way to tell your cluster size is to defragment your drive and 
analyze. It tells you in there

To set the SQLite page size, create a new *EMPTY* database and do a
PRAGMA page_size=4096;
Now create your tables immedeatly (if you close down the SQLite 
commandline program and reopen the DB, the page size is reset to 
1024). The page size must be set before the first table is created. 
Once that tables made, you can't change the size

Typing
PRAGMA page_size;
will tell you what it is currently set at

4) SQLite doesn't support clustered indexes (simply, indexes that 
force the data in the database to be physically layed down in the 
SAME order as the index needs it to be in.) 
This means that if your index is sequential INTEGER, the records are 
physically layed out in the database in that INTEGERs order, 1 then 2 
then 3.

You can't make a Clustered index, but you CAN sort your data in order 
so that any historical data is ordered nicely. Of course, as the 
database matures, you lose that, but it helps

Someone else posted this, and it is a nice example to use, so I will. 
If you have a table WIBBLE whose field KEY you want to access a lot, 
it would be nice if everything was in order. Using the command line 
tool, you can create a fake cluster by doing the following:

   create table wibble2 as select * from wibble;  
   delete from wibble;
   insert into wibble select * from wibble2 order by key;
   drop table wibble2;

5) Ok, as a reward for reading this far, here is the dumb thing.

Be *VERY, VERY* careful what you name your database, especially the 
extension

For example, if you give all your databases the extension .sdb 
(SQLite Database, nice name hey?) you discover that the SDB extension 
is already associated with APPFIX PACKAGES.

Now, here is the cute part, APPFIX is an executable/package that 
Windows XP recognizes, and it will, (emphasis mine) *ADD THE DATABASE 
TO THE SYSTEM RESTORE FUNCTIONALITY*

This means, stay with me here, every time you write ANYTHING to the 
database, the Windows XP system thinks a bloody executable has 
changed and copies your ENTIRE 800 meg database to the system restore 
directory

I recommend something like DB or DAT. 

Have fun people and thanks for all the help :)

[sqlite] SQLite and Apache runtime

[Rick Keiner]

Has there been any development with the Apache runtime? What type of 
effort would be involved to modify SQLite for the APR?

[sqlite] SQLite and the Apache runtime

[Rick Keiner]

Has there been any development with the Apache runtime? What type of effort 
would be involved to modify SQLite for the APR?

Re: [sqlite] VACUUM question

[rbundy]

Just curious. What is it that is motivating you to vacuum so frequently?

Regards.

rayB




*** Confidentiality and Privilege Notice ***

This e-mail is intended only to be read or used by the addressee. It is
confidential and may contain legally privileged information. If you are not
the addressee indicated in this message (or responsible for delivery of the
message to such person), you may not copy or deliver this message to anyone,
and you should destroy this message and kindly notify the sender by reply
e-mail. Confidentiality and legal privilege are not waived or lost by reason
of mistaken delivery to you.

Qantas Airways Limited
ABN 16 009 661 901

Visit Qantas online at http://qantas.com

Re: [sqlite] VACUUM question

[Guy Hachlili]

At 14:56 09/02/2005 -0500, Luc wrote:
My application does a VACUUM everytime it is launched. This slows down the
application a little (it's a plugin for Outlook so it freezes Outlook for a
second or two at most, but users could find that annoying). So I was
wondering if my app has a sqlite3* pointer to the database and another app
does a VACUUM on the same database, will the pointer still be valid? I know
that VACUUM actually deletes the database file and replaces it with another
one so that's why I need to know if it's safe to do so. My point is that I
could get the manager app to do the VACUUM a couple of times during the day
so that loading time of the other app (the plugin) would be faster.
Why don't you VACUUM on exit? Outlook takes its time closing as it is... 
and another second or two closing is usually much less noticeable then 
making the user wait for the application to launch.

Guy

Re: [sqlite] VACUUM question

[D. Richard Hipp]

On Wed, 2005-02-09 at 14:56 -0500, Luc Vandal wrote:
> if my app has a sqlite3* pointer to the database and another app does
> a VACUUM on the same database, will the pointer still be valid? 

Yes.


> 
> Also, what if, while doing a VACUUM, a query executes on the database?
> Will it just fail?

You will get SQLITE_BUSY.
-- 
D. Richard Hipp <[EMAIL PROTECTED]>

[sqlite] would someone check my SQL..

[Asko Kauppi]

I'm crafting a storage module based on SQLite internally.  Think about 
it as 'ini files, but with a database'.  Since this is my first touch 
to SQL, and the number of statements I need is rediculously small, I'd 
like some kind soul to 'proof-read' these and give comments.

Each SQL block is a single statement, and I precompile them with a 
certain 'section' name baked into the code. Here goes..

Table creation:
Loc_PrepareSql( db, ,
"CREATE TABLE '%q' ( key TEXT UNIQUE ON CONFLICT 
REPLACE,"
   " val VARIANT );", section );

	Q #1: What happens if the table already exists?  Should I check for 
that?  How?

Value setting & getting:
Loc_PrepareSql( db, >sql_set,
"UPDATE '%q' SET val=?2 WHERE key='?1';", section );
Loc_PrepareSql( db, >sql_remove_key,
"DELETE FROM '%q' WHERE key='?1';", section );
Loc_PrepareSql( db, >sql_get,
"SELECT val FROM '%q' WHERE key='?1';", section );
Key enumeration (returns 1..N keys):
Loc_PrepareSql( db, >sql_all_keys,
"SELECT key FROM '%q';", section );
Key enumeration with a prefix (ie. "subtable.b."):
Loc_PrepareSql( db, >sql_like_keys,
"SELECT key FROM '%q' WHERE key= LIKE '?1.%';", 
section );

Similar, returns 1..N key + value pairs:
Loc_PrepareSql( db, >sql_like_dump,
"SELECT key,val FROM '%q' WHERE key= LIKE '?1.%';", 
section );

Loc_PrepareSql( db, >sql_transaction_begin, "BEGIN;", NULL );
Loc_PrepareSql( db, >sql_transaction_commit, "COMMIT;", NULL );
Thanks for help, feedback, and encouragement. :)
-ak

Re: [sqlite] What forms of comments does SQLite honor?

[mswarm]

>>  see whether
>> a /* comments to the end of text without a */ being syntactically
>> necessary.
>
>Not in C. The closing */ is necessary.

That's the case in  Delphi also, practically speaking. Code 
ends with a end. and any (* before that would hide it from the 
compiler. 

But I just tested the /* in SQLite, and apparently it comments 
eveything to the end of the text block without causing an error. 

So the valid comment forms appear to be: 

-- to end of line
/* to the next */
/* to the end of the text block 

-- causes a /* before EOL to be ignored 

In looking up SQL comments in general on SQL newsgroups, 
I found at least one claim that SQL has no comment mechanism.
I wonder if that's true for the standard. 

In any event, I'm glad to see SQLite has both line and block
comments. 

Nathan 

[sqlite] VACUUM question

[Luc Vandal]

Hi,

 

My application does a VACUUM everytime it is launched. This slows down the
application a little (it's a plugin for Outlook so it freezes Outlook for a
second or two at most, but users could find that annoying). So I was
wondering if my app has a sqlite3* pointer to the database and another app
does a VACUUM on the same database, will the pointer still be valid? I know
that VACUUM actually deletes the database file and replaces it with another
one so that's why I need to know if it's safe to do so. My point is that I
could get the manager app to do the VACUUM a couple of times during the day
so that loading time of the other app (the plugin) would be faster.

 

Also, what if, while doing a VACUUM, a query executes on the database? Will
it just fail?

 

 

Thanks!

 

Luc Vandal
Edovia Technologies Inc.
[EMAIL PROTECTED]
www.edovia.com

 

 

 

AW: [sqlite] How to unite query results from two databases

[Michael Ruck]

I didn't even think of the simplest solution...

Thanks,
Michael 

-Ursprüngliche Nachricht-
Von: Bob Dankert [mailto:[EMAIL PROTECTED] 
Gesendet: Mittwoch, 9. Februar 2005 19:53
An: sqlite-users@sqlite.org
Betreff: RE: [sqlite] How to unite query results from two databases

I havent worked with multiple databases before, but I would think you could
just union two queries together if nothing else.

Eg: select ... union select ... order by col

Bob

Envision Information Technologies
Associate
[EMAIL PROTECTED]
v. 608.256.5680
f. 608.279.3780
 
-Original Message-
From: Michael Ruck [mailto:[EMAIL PROTECTED]
Sent: Wednesday, February 09, 2005 12:49 PM
To: sqlite-users@sqlite.org
Subject: [sqlite] How to unite query results from two databases

I need to perform a select, which queries two databases (same tables and
columns in both databases; both open at the same time in the same SQLite
session via ATTACH DATABASE.) I need the query result to appear as a single
result with sorting etc. performed on the entire result from both databases.
Does someone have an idea of how to perform this in SQLite?

Thanks,
Michael

RE: [sqlite] How to unite query results from two databases

[Bob Dankert]

I havent worked with multiple databases before, but I would think you
could just union two queries together if nothing else.

Eg: select ... union select ... order by col

Bob

Envision Information Technologies
Associate
[EMAIL PROTECTED]
v. 608.256.5680
f. 608.279.3780
 
-Original Message-
From: Michael Ruck [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, February 09, 2005 12:49 PM
To: sqlite-users@sqlite.org
Subject: [sqlite] How to unite query results from two databases

I need to perform a select, which queries two databases (same tables and
columns in both databases; both open at the same time in the same SQLite
session via ATTACH DATABASE.) I need the query result to appear as a
single
result with sorting etc. performed on the entire result from both
databases.
Does someone have an idea of how to perform this in SQLite?

Thanks,
Michael

[sqlite] How to unite query results from two databases

[Michael Ruck]

I need to perform a select, which queries two databases (same tables and
columns in both databases; both open at the same time in the same SQLite
session via ATTACH DATABASE.) I need the query result to appear as a single
result with sorting etc. performed on the entire result from both databases.
Does someone have an idea of how to perform this in SQLite?

Thanks,
Michael

Re: [sqlite] What forms of comments does SQLite honor?

[Will Leshner]

On Wed, 9 Feb 2005 02:00:07 -0800, [EMAIL PROTECTED]
<[EMAIL PROTECTED]> wrote:

>  see whether
> a /* comments to the end of text without a */ being syntactically
> necessary.

Not in C. The closing */ is necessary.

Re: [sqlite] How to return only certain number of rows from a table in a select statement and not all the rows

[Dennis Cote]

Steve Frierdich wrote:
Does anyone know what I need to add to a select statement so only
certain number of rows will return?
What would I add to a select statement query so only 10 rows from a
table would return and not all the records in the table.
Thanks
Steve
SELECT * FROM table LIMIT 10

Re: [sqlite] How to return only certain number of rows from a table in a select statement and not all the rows

[Kurt Welgehausen]

Re: [sqlite] How to return only certain number of rows from a table in a select statement and not all the rows

[Steve Frierdich]

Thanks  Bert
Bert Verhees wrote:
Steve Frierdich wrote:

Does anyone know what I need to add to a select statement so only 
certain number of rows will return?
What would I add to a select statement query so only 10 rows from a 
table would return and not all the records in the table.
Thanks
Steve



select * from table LIMIT 10

Re: [sqlite] How to return only certain number of rows from a table in a select statement and not all the rows

[Bert Verhees]

Steve Frierdich wrote:

Does anyone know what I need to add to a select statement so only 
certain number of rows will return?
What would I add to a select statement query so only 10 rows from a 
table would return and not all the records in the table.
Thanks
Steve



select * from table LIMIT 10

[sqlite] How to return only certain number of rows from a table in a select statement and not all the rows

[Steve Frierdich]

Does anyone know what I need to add to a select statement so only 
certain number of rows will return?
What would I add to a select statement query so only 10 rows from a 
table would return and not all the records in the table.
Thanks
Steve

Re: [sqlite] Using SQLite3 under Visual C++

[Asko Kauppi]

I need to do that for OS X, since 'fink' does not currently have 
SQLite3 ported to it, yet.

The solution is multi-OS (Win32, Linux, OS X, *BSD, .. WinCE) so I 
don't want to carry things statically.
For some situation, it might suit.

-ak
9.2.2005 kello 13:40, Dennis Volodomanov kirjoitti:
 I use it under Visual C++ by simply including all the files in the 
project, so that they're compiled in and linked statically.
  Dennis
// MCP, MCSD
// ASP Developer Member
// Software for animal shelters!
// www.smartpethealth.com
// www.amazingfiles.com
- Original Message - From: "Asko Kauppi" <[EMAIL PROTECTED]>
To: 
Sent: Wednesday, February 09, 2005 6:58 PM
Subject: [sqlite] Using SQLite3 under Visual C++

The binary package of SQLite 3.0.8 for Win32 only has the .def and 
.dll files.  While this is totally okay for MinGW (gcc) the lack of a 
.lib import library creates problems for Visual C++ users.

Am I missing something obvious, or what should I do?  Shouldn't this 
be a FAQ item?

-ak


--
No virus found in this incoming message.
Checked by AVG Anti-Virus.
Version: 7.0.300 / Virus Database: 265.8.6 - Release Date: 07-Feb-05

--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.300 / Virus Database: 265.8.6 - Release Date: 07-Feb-05

Re: [sqlite] Using SQLite3 under Visual C++

[Dennis Volodomanov]

I use it under Visual C++ by simply including all the files in the project, 
so that they're compiled in and linked statically.

  Dennis
// MCP, MCSD
// ASP Developer Member
// Software for animal shelters!
// www.smartpethealth.com
// www.amazingfiles.com
- Original Message - 
From: "Asko Kauppi" <[EMAIL PROTECTED]>
To: 
Sent: Wednesday, February 09, 2005 6:58 PM
Subject: [sqlite] Using SQLite3 under Visual C++


The binary package of SQLite 3.0.8 for Win32 only has the .def and .dll 
files.  While this is totally okay for MinGW (gcc) the lack of a .lib 
import library creates problems for Visual C++ users.

Am I missing something obvious, or what should I do?  Shouldn't this be a 
FAQ item?

-ak


--
No virus found in this incoming message.
Checked by AVG Anti-Virus.
Version: 7.0.300 / Virus Database: 265.8.6 - Release Date: 07-Feb-05


--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.300 / Virus Database: 265.8.6 - Release Date: 07-Feb-05

Re: [sqlite] speedtest result is obsolete

[Chris Schirlinger]

> I think you people are missing the point here, the performance increase 
> you're seeing is all down to OS caching and will vary across different 
> ports.  It's nothing to do with sqlite, and will affect every package.
> 
> Therefore the only way to fairly compare mysql/postgress/sqlite is to make 
> sure the machine is cleanly booted, before running any tests.
> 
> ( well and then maybe run the tests twice in succession, so the caching 
> effect can be taken into account )

This was what we found, and we tested dozens of databases and home 
grown systems

Some tools did better with initial cacheing and some better once 
cached

All our speed tests started on a clean boot, then we tested several 
iterations. 

Even delphi wrappers for the same back end (in one case SQLite) 
differed in the speed they worked at. Has taken months to get to this 
point where we are finally happy with the results we are getting from 
a DB

Re: [sqlite] What forms of comments does SQLite honor?

[mswarm]

>The source code in tokenize.c mentions: 
>
>/* SQL-style comments from "--" to end of line */
>
>and 
>
>/* C-style comments */
>

I got my code reconnected and managed to verify that these 
two are apparently valid comment forms. /* */ appears to span 
lines. 

Now to confirm that a /* */ spans -- comments, and see whether 
a /* comments to the end of text without a */ being syntactically
necessary.

NH

Re: [sqlite] speedtest result is obsolete

[Dan Keeley]

I think you people are missing the point here, the performance increase 
you're seeing is all down to OS caching and will vary across different 
ports.  It's nothing to do with sqlite, and will affect every package.

Therefore the only way to fairly compare mysql/postgress/sqlite is to make 
sure the machine is cleanly booted, before running any tests.

( well and then maybe run the tests twice in succession, so the caching 
effect can be taken into account )

From: Christian Smith <[EMAIL PROTECTED]>
Reply-To: sqlite-users@sqlite.org
To: sqlite-users@sqlite.org, [EMAIL PROTECTED]
Subject: Re: [sqlite] speedtest result is obsolete
Date: Tue, 8 Feb 2005 18:27:21 + (GMT)
On Tue, 8 Feb 2005, Chris Schirlinger wrote:
>> I did a small test to see if performance was linear with time.
>> I wanted to make sure it was suitable for my application.
>> It seems with both indexed and unindexed tables it doesn't take
>> significantly longer to do the 1,000,000th insert than it did the
>> first.
>
>I've discovered there are oodles of factors with this sort of thing,
>all with depend on what you actually want to do. With 2 million rows,
>a simple select statement (select * from wibble where key = 1)
>returning 2000 records out of a dataset of 2 million+, takes between
>3 and 10 seconds
Doing a keyed search is no guarantee that you won't touch *every* single
page in the table, if the rows are inserted in random order. Try this:
sqlite> create table wibble2 as select * from wibble;
sqlite> delete from wibble;
sqlite> insert into wibble select * from wibble2 order by key;
sqlite> drop table wibble2;
Assuming key is the key field you want, the records will be inserted into
wibble in key order. Selecting by key will then touch the least number of
pages, speeding up the select.
>
>The *SECOND* time you call this, it's instant due mostly to SQLites
>caching and HDD caching, however in our case, the 10 second wait at
>the start was a major issue
What do you expect? SQLite can't second guess what might be needed and
load pages in the background ready for use.
>
>The only way we could get correct test results for our purposes was
>to clean boot between every test, and then the results are
>depressing. Still trying to get past this
>
Why? Does you program require the machine to be rebooted before use?
I'm not trying to be facetious, but your test seem very invalid without
further explanation.
Christian

--
/"\
\ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL
 X   - AGAINST MS ATTACHMENTS
/ \

RE: [sqlite] Using SQLite3 under Visual C++

[steve]

Look in the Wiki.
http://www.sqlite.org/cvstrac/wiki?p=HowToCompile 
MSVC and SQLite DLL

LIB /DEF:sqlite.def  is all you really need if you know what you're doing.

-Original Message-
From: Asko Kauppi [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, February 08, 2005 11:59 PM
To: sqlite-users@sqlite.org
Subject: [sqlite] Using SQLite3 under Visual C++


The binary package of SQLite 3.0.8 for Win32 only has the .def and .dll
files.  While this is totally okay for MinGW (gcc) the lack of a .lib import
library creates problems for Visual C++ users.

Am I missing something obvious, or what should I do?  Shouldn't this be a
FAQ item?

-ak