[sqlite] Re: Question on SQL query optimization with joins

[Igor Tandetnik]

Bob Dankert <[EMAIL PROTECTED]> wrote:

I have been pondering an issue for a while regarding the separation of
query conditions from the join condition and the where condition of
the query.  All I have been able to find on this matter is general
text "use the ON clause for conditions that specify how to join
tables, and the WHERE clause to restrict which rows you want in the
result set", but I have not been able to get any reason why this is?


Purely stylistic, readability reasons. See 
http://www.sqlite.org/optoverview.html - internally SQLite converts ON 
conditions into WHERE conditions.



SELECT table2.name FROM table1 JOIN table2 ON table2.table1id =
table1.id WHERE table1.name like 'bob%';

Wouldn't it be better to put the filter on table1.name in the on
condition as well so it does not have to join as many rows?


SQL query planner is not as simple-minded as you imagine it to be. SQL 
query is not a set of instructions to be executed one by one, left to 
right. The optimizer does a fairly sofisticated processing to come up 
with a good query execution plan.


Igor Tandetnik 

[sqlite] Question on SQL query optimization with joins

[Bob Dankert]

I have been pondering an issue for a while regarding the separation of
query conditions from the join condition and the where condition of the
query.  All I have been able to find on this matter is general text "use
the ON clause for conditions that specify how to join tables, and the
WHERE clause to restrict which rows you want in the result set", but I
have not been able to get any reason why this is?  Take the following
table:

 

CREATE TABLE table1 (id INTEGER PRIMARY KEY, name TEXT);

CREATE TABLE table2 (id INTEGER PRIMARY KEY, table1id INTEGER, name
TEXT);

CREATE INDEX table2index ON table2(table1id);

 

And I do the following query:

 

SELECT table2.name FROM table1 JOIN table2 ON table2.table1id =
table1.id WHERE table1.name like 'bob%';

 

Wouldn't it be better to put the filter on table1.name in the on
condition as well so it does not have to join as many rows?  If this is
the case, it seems it would make sense to put almost all filtering
conditions in the join condition rather than the where condition.  I
know this is not correct and I suspect it is related to the use of
indexes while joining the tables.  If this is the case, would it be
quicker if I had an index on both table1id and the name columns from
table2?

 

I am more or less looking at the theory behind these optimizations in
SQL and not a specific case for SQLite - just trying to understand how
all this stuff works so I can write better queries.

 

Thanks!

 

Bob Dankert

Re: [sqlite] speed of ORDER BY clause?

[drh]

Eduardo <[EMAIL PROTECTED]> wrote:
> At 19:42 14/06/2006, you wrote:
> >On Jun 14, 2006, at 16:42 UTC, [EMAIL PROTECTED] wrote:
> >
> > > Might go a lot faster if you put a "+" in fron of
> > > recID.  Like this:
> > >
> > >SELECT * FROM table WHERE +recID IN (...) ORDER BY dateFld
> >
> >What magic is this?  I checked both lang_expr.html and 
> >lang_select.html but can't find any such syntax -- perhaps I missed 
> >it.  What does it do?
> 
> The '+' mads the optimizer, so it will not use any optimization. In 
> these case, the optimization engine (at where.c) takes a bad 
> decission. It's not a feature, is a side effect of the optimizer design. 
> 

Eduardo is essentially correct (even if his English is a tad off :-)).
Putting "+" in front of the name of a column in the WHERE clause
(or in the ORDER BY clause) disqualifies that column from use by 
the optimizer.  So instead of using the index on recID to satisfy
the WHERE clause as it normally would, the extra "+" causes SQLite
to chooses the index on dateFld to satisfy the ORDER BY clause.
Whether or not that is a win or a loss depends on the content of
your table and is best determined by experimentation.

--
D. Richard Hipp   <[EMAIL PROTECTED]>

Re: [sqlite] speed of ORDER BY clause?

[Eduardo]

At 19:42 14/06/2006, you wrote:

On Jun 14, 2006, at 16:42 UTC, [EMAIL PROTECTED] wrote:

> Might go a lot faster if you put a "+" in fron of
> recID.  Like this:
>
>SELECT * FROM table WHERE +recID IN (...) ORDER BY dateFld

What magic is this?  I checked both lang_expr.html and 
lang_select.html but can't find any such syntax -- perhaps I missed 
it.  What does it do?


The '+' mads the optimizer, so it will not use any optimization. In 
these case, the optimization engine (at where.c) takes a bad 
decission. It's not a feature, is a side effect of the optimizer design. 

Re: [sqlite] Avoiding Fragmentation of SQLite database file ???

[RohitPatel9999]

> SQLite seeks to keep its database file size minimized. 
> I think that SQLite should by default continue to follow 
> its current strategy of minimizing file size.  But I 
> am not adverse to adding a PRAGMA that will put the 
> database into a different "preallocation" mode where 
> the database size increases by larger chunks.  At the 
> time of each size inceased, the additional unused space 
> can simply be added to the freelist.  There are a few 
> unused bytes in the database file header that can be 
> used to record the fact that the "preallocation" pragma 
> is in force, so no file format change of any kind is 
> required - older versions of SQLite would continue to 
> be able to read and write newer databases. 

> This is something we may look into over the next 
> few months 
> -- 
> D. Richard Hipp   <[EMAIL PROTECTED]> 


That looks promising. Because SQLite is very good to manage freelists, so
such type of preallocation will require to add some more chunk to freelist
in SQLite DB file.

And app developers who can estimate how SQLite DB file size will be
increasing will be able to reduce fragmentation to get improved SQLite
performance. (In many cases, end-users of app may not have even idea which
database their app is using, where and how app is storing data. So app
developers may use such feature for performance improvement)

Rohit

--
View this message in context: 
http://www.nabble.com/Avoiding-Fragmentation-of-SQLite-database-file-t1780629.html#a4871149
Sent from the SQLite forum at Nabble.com.

Re: [sqlite] speed of ORDER BY clause?

[joe]

On Jun 14, 2006, at 16:42 UTC, [EMAIL PROTECTED] wrote:

> Might go a lot faster if you put a "+" in fron of
> recID.  Like this:
> 
>SELECT * FROM table WHERE +recID IN (...) ORDER BY dateFld

What magic is this?  I checked both lang_expr.html and lang_select.html but 
can't find any such syntax -- perhaps I missed it.  What does it do?

Thanks,
- Joe

--
Joe Strout -- [EMAIL PROTECTED]
Verified Express, LLC "Making the Internet a Better Place"
http://www.verex.com/

Re: [sqlite] speed of ORDER BY clause?

[joe]

On Jun 14, 2006, at 16:34 UTC, Eduardo wrote:

> >I need to select a large set of records out of a table, sort them by 
> >one column, and then get just a subset of the sorted list.  (For 
> >example, I might want records 40-60 ordered by date, which is a 
> >completely different set than records 40-60 ordered by user ID.)  I 
> >start with the full list of record IDs I want, and a query something like 
> >this:
> 
> So you want the records from position 40 to position 60 ?

Yes, if by "position" you are referring to the sorted list.

> In DB2 was faster select the 0-59 (1 to 60) records from the date 
> ordered table and from that selection in inverse order, select the 
> 0-19 (1-20). It was faster because selecting records from 0 (1) to 
> N-1 (N) was a lot faster than using the select records M-1(M) to 
> N-1(N), even when used two times.

In my tests, selecting the 20 records I want is no problem (iterating 
througheven 13000 records once the SELECT returns is lickety-split).  It's 
mainly the sorting that was the problem.

(Though the SELECT itself takes several seconds, but I'm assuming that that's 
already as fast as it can get.)

Best,
- Joe

--
Joe Strout -- [EMAIL PROTECTED]
Verified Express, LLC "Making the Internet a Better Place"
http://www.verex.com/

Re: [sqlite] Avoiding Fragmentation of SQLite database file ???

[A. Pagaltzis]

* Florian Weimer <[EMAIL PROTECTED]> [2006-06-14 18:50]:
> * Jay Sprenkle:
> > On 6/14/06, RohitPatel <[EMAIL PROTECTED]> wrote:
> >> Any solution to that (which does not force end-user of app
> >> to manage sqlite file fragments or to defragment disk) ?
> >
> > A scheduled task or cron job is trivial to implement
> > and does not add any extra work for the end user.
> 
> On Windows, perhaps. On most UNIX systems, this is very hard
> to do and often not supported at all by the file system.

Then again, Unix filesystems tend to be designed such
that as long as there is sufficient free space on the
device, fragmentation will remain insignificant.

Regards,
-- 
Aristotle Pagaltzis // 

Re: [sqlite] Avoiding Fragmentation of SQLite database file ???

[Florian Weimer]

* Jay Sprenkle:

> On 6/14/06, RohitPatel <[EMAIL PROTECTED]> wrote:
>>
>> Any solution to that (which does not force end-user of app to manage sqlite
>> file fragments or to defragment disk) ?
>
> A scheduled task or cron job is trivial to implement and does not
> add any extra work for the end user.

On Windows, perhaps.  On most UNIX systems, this is very hard to do
and often not supported at all by the file system.

Re: [sqlite] speed of ORDER BY clause?

[drh]

[EMAIL PROTECTED] wrote:
> 
>  SELECT  FROM  WHERE recID IN () ORDER BY dateFld
> 

Might go a lot faster if you put a "+" in fron of
recID.  Like this:

   SELECT * FROM table WHERE +recID IN (...) ORDER BY dateFld

Whether or not this is faster depends on what fraction of
redIDs actually match the (...). 
--
D. Richard Hipp   <[EMAIL PROTECTED]>

Re: [sqlite] speed of ORDER BY clause?

[Eduardo]

At 17:56 14/06/2006, you wrote:
I'm finding that ORDER BY is surprisingly slow, and it makes me 
wonder if I'm doing something wrong.  Here's the situation:


I need to select a large set of records out of a table, sort them by 
one column, and then get just a subset of the sorted list.  (For 
example, I might want records 40-60 ordered by date, which is a 
completely different set than records 40-60 ordered by user ID.)  I 
start with the full list of record IDs I want, and a query something like this:


So you want the records from position 40 to position 60 ?


 SELECT  FROM  WHERE recID IN ( ORDER BY dateFld

I have a unique index on recID, and an index on dateFld.

When my record IDs list is about 13000 items, the ORDER BY takes 
about 10 seconds (i.e., the query takes 10 seconds longer than the 
same query without the ORDER BY clause).  Yet if I remove the ORDER 
BY, grab all the dateFld values into my own array, and sort it 
myself, the sort takes about 2 seconds.


In DB2 was faster select the 0-59 (1 to 60) records from the date 
ordered table and from that selection in inverse order, select the 
0-19 (1-20). It was faster because selecting records from 0 (1) to 
N-1 (N) was a lot faster than using the select records M-1(M) to 
N-1(N), even when used two times.


HTH



Alien.org contacted...waiting for Ripley 

Re: [sqlite] speed of ORDER BY clause?

[joe]

On Jun 14, 2006, at 16:16 UTC, Paul Smith wrote:

> >  SELECT  FROM  WHERE recID IN ( ORDER BY dateFld
> >
> >I have a unique index on recID, and an index on dateFld.
> 
> Try making another index on both fields at once. SQLite can only use one 
> index at a time for each query.

Aha, that's probably it.  If I were the query engine, faced with this problem, 
I would certainly use the recID index to satisfy the WHERE clause.  I'll see if 
a combination index makes any difference.

Thanks for the pointer to the EXPLAIN command, too.

Best,
- Joe

--
Joe Strout -- [EMAIL PROTECTED]
Verified Express, LLC "Making the Internet a Better Place"
http://www.verex.com/

Re: [sqlite] Re: speed of ORDER BY clause?

[joe]

On Jun 14, 2006, at 16:15 UTC, Igor Tandetnik wrote:

> > SELECT  FROM  WHERE recID IN () ORDER BY
> > dateFld
> 
> Why not just
> 
> select  from  order by dateFld;
> 
> ?

Because that gets me all records.  I only want those in my list of record IDs 
(which comes from another source, outside the database).

> Can you show the exact query you use?

Well, no, because there are about 20 fields in  and 13000 values in 
.  Note that  does include both recID and dateFld, if that 
matters.  But apart from showing the actual field and value list, and the table 
name, the above query IS the exact query I use.

> There must be more to your query - something that prevents an index on
> dateFld from being used.

That's what it seems; I just can't imagine what that could be.

Thanks,
- Joe

--
Joe Strout -- [EMAIL PROTECTED]
Verified Express, LLC "Making the Internet a Better Place"
http://www.verex.com/

Re: [sqlite] speed of ORDER BY clause?

[joe]

On Jun 14, 2006, at 16:06 UTC, Marco Bambini wrote:

> Have you tried to create an indexed?

Yes, I mentioned that both recID and dateFld are indexed.

> Have you tried to analyze your query with SQLiteManager in order to  
> see which indexes are used?

No, I didn't know about that.  Thanks for the suggestion.

Best,
- Joe


--
Joe Strout -- [EMAIL PROTECTED]
Verified Express, LLC "Making the Internet a Better Place"
http://www.verex.com/

Re: [sqlite] speed of ORDER BY clause?

[Paul Smith]

At 16:56 14/06/2006, [EMAIL PROTECTED] wrote:
I'm finding that ORDER BY is surprisingly slow, and it makes me wonder if 
I'm doing something wrong.  Here's the situation:


I need to select a large set of records out of a table, sort them by one 
column, and then get just a subset of the sorted list.  (For example, I 
might want records 40-60 ordered by date, which is a completely different 
set than records 40-60 ordered by user ID.)  I start with the full list of 
record IDs I want, and a query something like this:


 SELECT  FROM  WHERE recID IN ( ORDER BY dateFld

I have a unique index on recID, and an index on dateFld.


Try making another index on both fields at once. SQLite can only use one 
index at a time for each query.


So,
CREATE INDEX table_recdate ON table (recID, dateFld);

See if that makes any difference.

When my record IDs list is about 13000 items, the ORDER BY takes about 10 
seconds (i.e., the query takes 10 seconds longer than the same query 
without the ORDER BY clause).  Yet if I remove the ORDER BY, grab all the 
dateFld values into my own array, and sort it myself, the sort takes about 
2 seconds.


This has left me with the weird result that it's actually *faster* for me 
to query the database twice: first to get the unordered list of all 
records and their dates, which I then sort myself, and then query again to 
get just the subset of records I really want.


(That's what we do in some cases, eg if we have to do some sorts of joins 
it's quicker to get all the data and merge it in memory, rather than use 
the DB)


Am I missing something here?  If my own code can sort these dates in 2 
seconds, why does sqlite take 10?  And why did indexing the dateFld not 
make any difference (i.e., it took about 10 seconds before I added the 
index too)?


SQLite hasn't got as powerful an optimiser as some DBs such as MySQL etc 
(but then it is a tiny fraction of the size, so what do you expect). Some 
DBs also automatically create indices as they decide they're necessary - 
SQLite doesn't, you need to do it yourself.


This means you need to think about things a bit more yourself. The 
'EXPLAIN' command is your friend - learn how to use it at least a bit if 
performance is an issue - you can usually see where there are plain loops 
(which go around all records) or indexed loops (which are much quicker). 
Usually careful creation of the suitable indices helps a lot.


Read http://www.sqlite.org/optoverview.html - this gives some details of 
the limited optimisations that SQLite can do, so you can try to take 
advantage of them




PaulVPOP3 - Internet Email Server/Gateway
[EMAIL PROTECTED]  http://www.pscs.co.uk/

[sqlite] Re: speed of ORDER BY clause?

[Igor Tandetnik]

joe-QzMH92Wc/[EMAIL PROTECTED] wrote:

I'm finding that ORDER BY is surprisingly slow, and it makes me
wonder if I'm doing something wrong.  Here's the situation:

I need to select a large set of records out of a table, sort them by
one column, and then get just a subset of the sorted list.  (For
example, I might want records 40-60 ordered by date, which is a
completely different set than records 40-60 ordered by user ID.)  I
start with the full list of record IDs I want, and a query something
like this:

SELECT  FROM  WHERE recID IN ( ORDER BY
dateFld


Why not just

select  from  order by dateFld;

? Can you show the exact query you use? There must be more to your 
query - something that prevents an index on dateFld from being used.


Igor Tandetnik 

Re: [sqlite] speed of ORDER BY clause?

[Marco Bambini]

Have you tried to create an indexed?
Have you tried to analyze your query with SQLiteManager in order to  
see which indexes are used?


---
Marco Bambini
http://www.sqlabs.net
http://www.sqlabs.net/blog/



On Jun 14, 2006, at 5:56 PM, [EMAIL PROTECTED] wrote:

I'm finding that ORDER BY is surprisingly slow, and it makes me  
wonder if I'm doing something wrong.  Here's the situation:


I need to select a large set of records out of a table, sort them  
by one column, and then get just a subset of the sorted list.  (For  
example, I might want records 40-60 ordered by date, which is a  
completely different set than records 40-60 ordered by user ID.)  I  
start with the full list of record IDs I want, and a query  
something like this:


 SELECT  FROM  WHERE recID IN ( ORDER BY  
dateFld


I have a unique index on recID, and an index on dateFld.

When my record IDs list is about 13000 items, the ORDER BY takes  
about 10 seconds (i.e., the query takes 10 seconds longer than the  
same query without the ORDER BY clause).  Yet if I remove the ORDER  
BY, grab all the dateFld values into my own array, and sort it  
myself, the sort takes about 2 seconds.


This has left me with the weird result that it's actually *faster*  
for me to query the database twice: first to get the unordered list  
of all records and their dates, which I then sort myself, and then  
query again to get just the subset of records I really want.


Am I missing something here?  If my own code can sort these dates  
in 2 seconds, why does sqlite take 10?  And why did indexing the  
dateFld not make any difference (i.e., it took about 10 seconds  
before I added the index too)?


Finally, can anyone see a more efficient solution to this problem?

Many thanks,
- Joe


--
Joe Strout -- [EMAIL PROTECTED]
Verified Express, LLC "Making the Internet a Better Place"
http://www.verex.com/

Re: [sqlite] Avoiding Fragmentation of SQLite database file ???

[Christian Smith]

John Stanton uttered:


Jay Sprenkle wrote:

On 6/14/06, RohitPatel <[EMAIL PROTECTED]> wrote:



Any solution to that (which does not force end-user of app to manage 
sqlite

file fragments or to defragment disk) ?



A scheduled task or cron job is trivial to implement and does not
add any extra work for the end user. Making Sqlite smart enough
to manipulate the operating systems storage management is
NOT trivial and would need to be different for every operating system.

We have a saying that applies: "There is no free lunch"


If the disk is kept is a tidy state with regularly scheduled defragmentation 
then the files which grow do not get gross fragmentation.


If there were a way to stop file fragmentation without requiring any 
management effort there would be no need for defrag programs.  With current 
file systems we need defraggers.  A fact of life like death and taxes.





I've never, ever defragged a UNIX box in my life. Keeping the amount of 
free space reasonable goes a long way to helping an OS avoid 
fragmentation. I only have the issue on Windows when I'm running out of 
disk space on a device. The other pain on Windows is the lack of swap 
partition support, which would be a massive performance benefit when 
memory is overcommitted. A fragmented swap file is a definite performance 
killer.


I don't know if cylinder groups help particularly for UNIX. I guess it 
must do as even a fragmented file will have it's fragments close 
together. There is a ext2 defragger, but it doesn't work with ext3 and 
hasn't been updated since 2002.


I can't wait to play with ZFS on Solaris. Doesn't look fragmentation 
prone. And with point in time writeable snapshots, it should even be safe 
to take a snapshot copy of a directory containing a SQLite database and 
SQLite will just do the right thing upon using the snapshot (the journal 
will be snapshot atomically with the DB file.)


Christian


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

[sqlite] speed of ORDER BY clause?

[joe]

I'm finding that ORDER BY is surprisingly slow, and it makes me wonder if I'm 
doing something wrong.  Here's the situation:

I need to select a large set of records out of a table, sort them by one 
column, and then get just a subset of the sorted list.  (For example, I might 
want records 40-60 ordered by date, which is a completely different set than 
records 40-60 ordered by user ID.)  I start with the full list of record IDs I 
want, and a query something like this:

 SELECT  FROM  WHERE recID IN ( ORDER BY dateFld

I have a unique index on recID, and an index on dateFld.

When my record IDs list is about 13000 items, the ORDER BY takes about 10 
seconds (i.e., the query takes 10 seconds longer than the same query without 
the ORDER BY clause).  Yet if I remove the ORDER BY, grab all the dateFld 
values into my own array, and sort it myself, the sort takes about 2 seconds.

This has left me with the weird result that it's actually *faster* for me to 
query the database twice: first to get the unordered list of all records and 
their dates, which I then sort myself, and then query again to get just the 
subset of records I really want.

Am I missing something here?  If my own code can sort these dates in 2 seconds, 
why does sqlite take 10?  And why did indexing the dateFld not make any 
difference (i.e., it took about 10 seconds before I added the index too)?

Finally, can anyone see a more efficient solution to this problem?

Many thanks,
- Joe


--
Joe Strout -- [EMAIL PROTECTED]
Verified Express, LLC "Making the Internet a Better Place"
http://www.verex.com/

Re: [sqlite] How to add a table ?

[Thierry Nauze]

Le 14 juin 06 à 18:33, Eduardo a écrit :


At 04:56 14/06/2006, you wrote:

Hello,

I have a database.

I want to add a table with columns
id integer
name varchar
addresse varchar
...

Which instructions (exactly) I have to give.


Check this page http://www.sqlite.org/sqlite.html , there are the  
basic SQL commands for manage a database, including create table.


Thank you, I have already had solution.

It was a question of adding a table to an existing base.
I did not think that it was the same command that for a creation.

Simple and efficient !


--
Thierry NAUZESaint-Denis de la Réunion

Re: [sqlite] sqlite system table names

[drh]

Ralf Junker <[EMAIL PROTECTED]> wrote:
> >sqlite_master is it.  there are no others.
> 
> How about sqlite_temp_master? Or database.sqlite_master for connected 
> databases? 
> 

OK.  Them too.
--
D. Richard Hipp   <[EMAIL PROTECTED]>

Re: [sqlite] How to add a table ?

[Eduardo]

At 04:56 14/06/2006, you wrote:

Hello,

I have a database.

I want to add a table with columns
id integer
name varchar
addresse varchar
...

Which instructions (exactly) I have to give.


Check this page http://www.sqlite.org/sqlite.html , there are the 
basic SQL commands for manage a database, including create table.



I work with REALBASIC 2006 on Mac

Thank you


I have seen Joe Strout posting here, he was (or is) a developer of 
realbasic (from version 2.x up to 4.5), perhaps he can help you more.


HTH

-
Antivirus. Warning: Usuario detectado. Por favor retirese del 
ordenador o sera ud. eliminado. Gracias 

Re: [sqlite] Avoiding Fragmentation of SQLite database file ???

[Eduardo]

At 16:43 13/06/2006, you wrote:


Hi SQLiteUsers

After using such application, all used SQLite DB files gets fragmented.


How to avoid such fragmentation ?


I'm not sure, but vacuum will create a new file, so perhaps this file 
will be less fragmented then previous one. Or create a new database 
and copy (via SQL commands) from old to new.


HTH


A: Because it messes up the order in which people normally read text.
Q: Why is top-posting such a bad thing?
A: Top-posting.
Q: What is the most annoying thing on usenet and in e-mail? 

Re: [sqlite] Avoiding Fragmentation of SQLite database file ???

[John Stanton]

Jay Sprenkle wrote:

On 6/14/06, RohitPatel <[EMAIL PROTECTED]> wrote:



Any solution to that (which does not force end-user of app to manage 
sqlite

file fragments or to defragment disk) ?



A scheduled task or cron job is trivial to implement and does not
add any extra work for the end user. Making Sqlite smart enough
to manipulate the operating systems storage management is
NOT trivial and would need to be different for every operating system.

We have a saying that applies: "There is no free lunch"


If the disk is kept is a tidy state with regularly scheduled 
defragmentation then the files which grow do not get gross fragmentation.


If there were a way to stop file fragmentation without requiring any 
management effort there would be no need for defrag programs.  With 
current file systems we need defraggers.  A fact of life like death and 
taxes.

Re: [sqlite] sqlite system table names

[Ralf Junker]

>sqlite_master is it.  there are no others.

How about sqlite_temp_master? Or database.sqlite_master for connected 
databases? 

Re: [sqlite] Avoiding Fragmentation of SQLite database file ???

[Jay Sprenkle]

On 6/14/06, RohitPatel <[EMAIL PROTECTED]> wrote:


Any solution to that (which does not force end-user of app to manage sqlite
file fragments or to defragment disk) ?


A scheduled task or cron job is trivial to implement and does not
add any extra work for the end user. Making Sqlite smart enough
to manipulate the operating systems storage management is
NOT trivial and would need to be different for every operating system.

We have a saying that applies: "There is no free lunch"

Re: [sqlite] Avoiding Fragmentation of SQLite database file ???

[drh]

RohitPatel <[EMAIL PROTECTED]> wrote:
> Thanks for prompt reply. 
> 
> Agreed. SQLite needs zero-configuration. 
> 
> But applications using multiple SQLite database files for read and write,
> makes those files with many-many fragments in disk. Which definitely
> degrades database file read/write performance tremendously. 
> 
> Any solution to that (which does not force end-user of app to manage sqlite
> file fragments or to defragment disk) ? 
> 

SQLite seeks to keep its database file size minimized.
I think that SQLite should by default continue to follow 
its current strategy of minimizing file size.  But I
am not adverse to adding a PRAGMA that will put the
database into a different "preallocation" mode where
the database size increases by larger chunks.  At the
time of each size inceased, the additional unused space 
can simply be added to the freelist.  There are a few
unused bytes in the database file header that can be 
used to record the fact that the "preallocation" pragma 
is in force, so no file format change of any kind is 
required - older versions of SQLite would continue to 
be able to read and write newer databases.

This is something we may look into over the next
few months
--
D. Richard Hipp   <[EMAIL PROTECTED]>

Re: [sqlite] Avoiding Fragmentation of SQLite database file ???

[RohitPatel9999]

Thanks for prompt reply. 

Agreed. SQLite needs zero-configuration. 

But applications using multiple SQLite database files for read and write,
makes those files with many-many fragments in disk. Which definitely
degrades database file read/write performance tremendously. 

Any solution to that (which does not force end-user of app to manage sqlite
file fragments or to defragment disk) ? 

Rohit
--
View this message in context: 
http://www.nabble.com/Avoiding-Fragmentation-of-SQLite-database-file-t1780629.html#a4863094
Sent from the SQLite forum at Nabble.com.

Re: [sqlite] Avoiding Fragmentation of SQLite database file ???

[C.Peachment]

On Wed, 14 Jun 2006 03:34:04 -0700 (PDT), RohitPatel wrote:


>But like some other Databases, if SQLite does provide any way to set
>auto-increment for database file by some fixed percentage (say 20%, 40%
>etc.) such that everytime database file reaches some threashould size it
>automatically add free space, that will reduce file defragmentation.

>What are your thoughts on that ?


One of Sqlite's strengths is its simplicity. Creeping featuritis plagues all
software development and what you are suggesting fits nicely with that
definition. The zero configuration principle is violated so that a very
small percentage of applications can do something as part of the database
module instead of leaving it to the operating system, running a cron task
at 3.00 am.

Regards,

Chris

Re: [sqlite] Avoiding Fragmentation of SQLite database file ???

[RohitPatel9999]

I know and have used deframentation apps. Thats good. But why to force
end-users to defragment their disk. 

SQLite manages free-space in file very-well (after deleting records). So I
also created one template database file with optimum number of records, then
deleted records and copied that file while creating new company db file. 

But like some other Databases, if SQLite does provide any way to set
auto-increment for database file by some fixed percentage (say 20%, 40%
etc.) such that everytime database file reaches some threashould size it
automatically add free space, that will reduce file defragmentation.

What are your thoughts on that ?

Rohit

--
View this message in context: 
http://www.nabble.com/Avoiding-Fragmentation-of-SQLite-database-file-t1780629.html#a4862560
Sent from the SQLite forum at Nabble.com.