Re: [sqlite] Disk caching impacts performance.

[Michael Scharf]

[EMAIL PROTECTED] wrote:

"Trevor Talbot" <[EMAIL PROTECTED]> wrote:

Beyond that, I'm not aware of anything that would help.



All good advice.  But you left off the obvious:  Get a
faster disk drive.  ;-)


...which does not really help unless you buy a very expensive
flash disk drive. How much faster is a *really* fast spinning disk?

Trevo, have you tried to put your database on a (fast!) USB stick.
It should be much faster in 'seeking' but is slower in the
data transfer. This would give some indication if the access
is limited by seek or the disk reading speed.


Michael

--
http://MichaelScharf.blogspot.com/


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re[2]: [sqlite] SQLite and Large Databases

[Teg]

Hello drh,

Thursday, November 8, 2007, 3:03:07 PM, you wrote:

dhc> PokerAce <[EMAIL PROTECTED]> wrote:
>> I am using Windows Task Manager to determine memory usage.  I minimize my
>> application (which causes the task manager to show a more accurate
>> measurement of memory used) and still the memory usage is enormous.
>> 
>> 

dhc> Recompile version 3.5.2 with -DSQLITE_MEMORY_SIZE=1000
dhc> or however much memory you want SQLite to use.  This will
dhc> create a static array of char[] of size 1000 (or whatever
dhc> other size you give it) and use that instead of malloc()
dhc> to obtain all the memory it needs.  With this approach it
dhc> is impossible for SQLite to use more than the specified
dhc> amount of memory since it never calls malloc().

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


dhc> 
-
dhc> To unsubscribe, send email to [EMAIL PROTECTED]
dhc> 
-

Win32 here. I set this option and get insta-crashes in both debug and
release builds. Using the latest 3.5.2 from the website.

-DSQLITE_MEMORY_SIZE=1000

It crashes right here when it was trying to push pager pages out.

int sqlite3OsWrite(sqlite3_file *id, const void *pBuf, int amt, i64 offset){
  DO_OS_MALLOC_TEST;
  return id->pMethods->xWrite(id, pBuf, amt, offset);
}


If you need more information let me know. It's not causing my any real
problems, I just disable the setting and it's back to normal.


-- 
Best regards,
 Tegmailto:[EMAIL PROTECTED]


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re[2]: [sqlite] SQLite and Large Databases

[Teg]

Hello Trevor,

Thursday, November 8, 2007, 5:48:59 PM, you wrote:

TT> On 11/8/07, PokerAce <[EMAIL PROTECTED]> wrote:

>> I am using Windows Task Manager to determine memory usage.  I minimize my
>> application (which causes the task manager to show a more accurate
>> measurement of memory used) and still the memory usage is enormous.

TT> Task Manager is lousy at getting the information you really need.  Try
TT> Process Explorer instead, and pay attention to the "Private Bytes"
TT> number for your process:
TT> http://www.microsoft.com/technet/sysinternals/utilities/processexplorer.mspx

TT> That said, it does sound like something is leaking memory, rather than
TT> SQLite just behaving as normal.  I would investigate detailed memory
TT> tracking options for your app, to find out where it's going.

TT> 
-
TT> To unsubscribe, send email to [EMAIL PROTECTED]
TT> 
-

You can turn on lots of additional columns for things like virtual
memory sizes and the like. The default details selection is
pretty lacking.

-- 
Best regards,
 Tegmailto:[EMAIL PROTECTED]


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] SQLite and Large Databases

[Nuno Lucas]

On Nov 8, 2007 5:04 PM, PokerAce <[EMAIL PROTECTED]> wrote:
> I am using Windows Task Manager to determine memory usage.  I minimize my
> application (which causes the task manager to show a more accurate
> measurement of memory used) and still the memory usage is enormous.

Windows Task Manager doesn't count DLL's memory usage twice, it just
add that memory to the first linked program, while a second one will
appear to consume a lot less memory (but inverting the situation when
the order of the programs execution changes).

For example, the first .NET application will seem to use a LOT of
memory (because all the DLLs it will link to), while the second one
may use just a few mega (which is probably the actual memory it's
allocating more the program executable size, adding any DLL loaded
specific to the program).

As other said, use ProcessExplorer (www.sysinternals.com) if you
really want to have a meaningful clue on the memory usage of your
program.

Regards,
~Nuno Lucas

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] SQLite and Large Databases

[Trevor Talbot]

On 11/8/07, PokerAce <[EMAIL PROTECTED]> wrote:

> I am using Windows Task Manager to determine memory usage.  I minimize my
> application (which causes the task manager to show a more accurate
> measurement of memory used) and still the memory usage is enormous.

Task Manager is lousy at getting the information you really need.  Try
Process Explorer instead, and pay attention to the "Private Bytes"
number for your process:
http://www.microsoft.com/technet/sysinternals/utilities/processexplorer.mspx

That said, it does sound like something is leaking memory, rather than
SQLite just behaving as normal.  I would investigate detailed memory
tracking options for your app, to find out where it's going.

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Suggests for improving the SQLite website

[Trevor Talbot]

The documentation page is too scattered, and needs to be categorized:
short bits, like the intro and features lists; reference material,
like the SQL syntax and API pages; and detailed discussion, like the
locking methods and the like.

I echo P Kishor's comment that the date/time functions need to be
moved from the wiki to the docs, and that goes for anything else on
the wiki that's stable.

For the most part, I hate wikis.  I find some random wiki on the
internet, and it has no organization, the quality is dubious, the
search pages suck, etc.  When I want tinformation, I go straight to
the official documentation.  Even if there's a "wiki" link in the
site's menu, I ignore it as irrelevant.

SQLite's wiki, on the other hand, is quite useful.  That means it
needs to be promoted: it should be mentioned on the main documentation
page, along with examples of what information you can find there
(users, tools, enhanced versions, examples, etc).  It should also be
mentioned why it's on this separate "wiki" thing: it is open to
real-time user contributions to help stay current (or something).

If you can integrate user-contributed comments into the reference
material, similar to PostgreSQL's release documentation, I believe
that would be useful too.  It's a way to get data that's on the wiki
now closer to where it's needed.

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Suggests for improving the SQLite website

[Liam Healy]

I think the documentation section needs to be organized.  I count over
two dozen links in a simple list with no apparent organization.  Some
(like "copyright") might be best under some other heading, like
downloading.  Some are minor topics, or of transient interest, such as
moving from 3.4 to 3.5.  Some, like v2 docs, are obsolete but still
needed somewhere.  The "5 minutes or less" probably deserves to be a
big button on the front page --- that's the programmer's equivalent of
eye candy.  Overall, I find that the most important docs (the v3 API
and API reference) are buried.

I suppose the best way to approach organizing this is to look at what
other free software sites do.  Just to pick the first on your list,
http://www.postgresql.org/docs/ looks like a reasonable way to go.
Each version has its own heading.  Manuals are separated from What's
New, FAQs,  etc., user documentation (API) separated from internals,
etc.  Maybe that's a good template for organization.

Liam



Liam

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Suggests for improving the SQLite website

[Richard Klein]

[EMAIL PROTECTED] wrote:

We are looking at renovating the design of the SQLite
website and would love to have suggestions from the
community.  If you have any ideas on how to improve
the SQLite website, please constribute either to the
mailing list or directly to me.


o The 'contrib' section could be better organized, say
into categories.  Also it would be nice if someone could
at least desk-check these contributions for accuracy,
relevancy, etc.

o The 'documentation' section could be organized into
subsections, with a table of contents.  Someone should
write documentation on how to test SQLite using the
test fixture.  Also need documentation on how to use
Mktclapp to build the test scripts, Tcl interpreter,
and SQLite code into an executable that can run on
any platform.

o The 'faq' should be updated to include some additional
frequently asked questions, such as "how do I implement
a scrolling cursor using SQLite?"  (Yes, there's already
an article in the Wiki, but the faq should at least link
to that article.)

o The 'wiki' Index page needs updating.

o It's not immediately obvious that when you select
certain links -- namely, 'bugs', 'timeline', and 'wiki',
that you are entering a browser-based tool (Cvstrac?)
where some links have different meanings than what you'd
expect.

For example, the 'Home' link (in the blue rectangle)
takes you to the Cvstrac home page, not the SQLite
home page.

The blue rectangle does give the user some indication
that he is in the Cvstrac context; however, I'd recommend
making this more explicit -- maybe put a Cvstrac logo
inside the blue rectangle.

o Overall, I love the way the website emphasizes content
over form, and recommend you keep it that way.  Give us
meat, meat, and more meat!  Keep the sales bulls**t to
a minimum.

- Richard Klein


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] SQLite and Large Databases

[drh]

PokerAce <[EMAIL PROTECTED]> wrote:
> I am using Windows Task Manager to determine memory usage.  I minimize my
> application (which causes the task manager to show a more accurate
> measurement of memory used) and still the memory usage is enormous.
> 
> 

Recompile version 3.5.2 with -DSQLITE_MEMORY_SIZE=1000
or however much memory you want SQLite to use.  This will
create a static array of char[] of size 1000 (or whatever
other size you give it) and use that instead of malloc()
to obtain all the memory it needs.  With this approach it
is impossible for SQLite to use more than the specified
amount of memory since it never calls malloc().

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


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] SQLITE_OMIT_MEMORY_ALLOCATION

[Mark Spiegel]

I'm sure I can use some #defines to point to my own malloc, realloc, and 
free routines.  Unfortunately, it may be some time before I get to this 
so I'll have to stick with 3.5.1 for now.


Before I do that, I'm going to have to study the later source to try to 
get a better understanding of what you are trying to accomplish with the 
memory management changes. 


[EMAIL PROTECTED] wrote:

Mark Spiegel <[EMAIL PROTECTED]> wrote:
  
I just started looking at 3.5.2 and notice that support for 
SQLITE_OMIT_MEMORY_ALLOCATION was removed.  This is a problem for me.  
Is there some reasoning behind this?



Yes.  We are doing a lot of experimental work on the
memory allocation.  See

   http://www.sqlite.org/mpool/timeline

We quickly found that the SQLITE_OMIT_MEMORY_ALLOCATION
feature severely limited our options in designing new
memory allocation architectures.  We might to back with
some other means of providing user-definable memory allocation
in the future, but for the time being you have to either
use malloc, or else use SQLITE_MEMORY_SIZE to enable the
zero-malloc memory allocator.

Can you compile with macros like this:  -Dmalloc=my_app_malloc
-Drealloc=my_app_realloc -Dfree=my_app_free to get the effect
you want?

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


-
To unsubscribe, send email to [EMAIL PROTECTED]
-


  



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] library versioning

[Filipe Fernandes]

I've noticed the 'Suggests for improving the SQLite website' e-mail and
point number (4) that drh made was the frequent releases of the sqlite
library make the library look bad.

I'm quite happy at the release rate and believe as one of the respondents
to the thread mentioned that 'release early, release often' is the way to go.

But every time a release comes out, I notice that the release contains
both features and bug fixes which concerns me since new features could
potentially bring about new bugs.

A suggestion is to branch sqlite where the third version number (ie.
3.5.x) could be used to identify strictly bug fixes to the library and
incrementing the major.minor numbers for major and minor
features/improvements.

ie. major.minor.patch (3.5.3 could be a bug fix release and 3.6.0 a
feature release).

This makes for more work since fixes would have to be back-ported to maybe
one or two previous releases (as you see fit).  This method could be used
to release often for those that would like to see bug fixes and improve
sqlite's image as a continually improving robust library.  At the same
time, improvements/features could be introduced at a slower pace without
the push to release because of bug-fixes in the same branch of development.

I don't have anything in particular about sqlite to complain about.  I've
been watching the list for about 1/2 a year and like the little library
myself. But point #4 prompted me to scratch an itch and suggest an
improvement to workflow.

filipe


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] SQLITE_OMIT_MEMORY_ALLOCATION

[drh]

Mark Spiegel <[EMAIL PROTECTED]> wrote:
> I just started looking at 3.5.2 and notice that support for 
> SQLITE_OMIT_MEMORY_ALLOCATION was removed.  This is a problem for me.  
> Is there some reasoning behind this?

Yes.  We are doing a lot of experimental work on the
memory allocation.  See

   http://www.sqlite.org/mpool/timeline

We quickly found that the SQLITE_OMIT_MEMORY_ALLOCATION
feature severely limited our options in designing new
memory allocation architectures.  We might to back with
some other means of providing user-definable memory allocation
in the future, but for the time being you have to either
use malloc, or else use SQLITE_MEMORY_SIZE to enable the
zero-malloc memory allocator.

Can you compile with macros like this:  -Dmalloc=my_app_malloc
-Drealloc=my_app_realloc -Dfree=my_app_free to get the effect
you want?

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


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] Suggests for improving the SQLite website

[Wilson, Ron]

IMO, the http://www.postgresql.org/ site is the most appealing site of
the competing products.  I would be happy if SQLite followed a similar
look.

I would try to keep the top-right menu links from SQLite.org, but I like
someone's earlier suggestion that they be grouped in categories.  I'm
also fond of tag clouds, but I'm not sure how a tag cloud would improve
the top-right menu.  http://en.wikipedia.org/wiki/Tag_cloud

RW

Ron Wilson, Senior Engineer, MPR Associates, 518.831.7546

-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
Sent: Thursday, November 08, 2007 11:29 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] Suggests for improving the SQLite website

We are looking at renovating the design of the SQLite
website and would love to have suggestions from the
community.  If you have any ideas on how to improve
the SQLite website, please constribute either to the
mailing list or directly to me.

Here are some links to competing database products
that might inspire comments:

   http://www.postgresql.org/
   http://www.firebirdsql.org/
   http://www.hsqldb.org/
   http://opensource.ingres.com/
   http://db.apache.org/derby/
   http://exist.sourceforge.net/

Among the comments received already are these:

  (1) It is not clear from the homepage that the software
  is free.
  (2) Half the page is devoted to talking about bugs in
  the software.  This suggests low quality.
  (3) The "News" contains scary words: "radical changes".
  (4) Three releases in as many months suggests the
  code is not stable.
  (5) Move the BigNameUsers to the front page
  (see http://www.sqlite.org/wiki?p=BigNameUsers)
  (6) Need more eye-candy.

I do not necessary agree with the above comments, but I
am open to any and all ideas.  You will not hurt my feels,
so speak freely.

Thanks in advance for your input.

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




-
To unsubscribe, send email to [EMAIL PROTECTED]

-


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Suggests for improving the SQLite website

[P Kishor]

Hi Richard,

I might be in a minority on this list for suggesting that the SQLite
website could indeed become more helpful and informative through a
redesign.

I am not suggesting that the redesign necessarily be focused on
attractiveness, although, there can be some correlation in ease of use
and general attractiveness. I do think that it needs to be embellished
with more information, and with more ease to find that information.
Here are some concrete examples --

1. Perhaps the greatest improvement would occur if we enhance the
syntax section with more examples, particularly in the EXPRESSION
sub-section. Some functions are just not clear (for example, what does
glob(X,Y) do... granted, I can probably search for it and find
examples, but examples right there would be very helpful). Bringing
the date and time functions from the wiki to the syntax page would be
more direct and helpful. Having examples for each and every command
would be very useful and also educational. On this email list what I
learn about SQL is way more than what I learn about SQLite, and
expanding the syntax section would go a long way toward achieving both
these aims.

2. An extension web project for SQLite and its various language
wrappers would be very useful. Perhaps that could be accomplished by
sub-wikis. Some might contend that language-specific documentation
belongs with that language website, and that would be not an invalid
argument, but if the focus of the SQLite website is to promote the use
of SQLite no matter what language is being used, then the SQLite
website should try to accomplish, or enable accomplishing of, this
goal even if it means possibly creating redundant websites.

3. A user-editable list of frequently used tasks --

- how to rename a table
- how to import export data
- how to deal with blobs
- creating use-defined functions
- implementing full-text search

In all of the above cases, a wiki style that allows users to add
comments right on the documentation pages would be great. Once set up,
it will be up to the users to populate it with useful content. This
has worked well in other projects and would work well here.

Of course, a version *without* user-generated content would still be
included in the source code so users can build one themselves for
their local, offline use.


On 11/8/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
> We are looking at renovating the design of the SQLite
> website and would love to have suggestions from the
> community.  If you have any ideas on how to improve
> the SQLite website, please constribute either to the
> mailing list or directly to me.
>
> Here are some links to competing database products
> that might inspire comments:
>
>http://www.postgresql.org/
>http://www.firebirdsql.org/
>http://www.hsqldb.org/
>http://opensource.ingres.com/
>http://db.apache.org/derby/
>http://exist.sourceforge.net/
>
> Among the comments received already are these:
>
>   (1) It is not clear from the homepage that the software
>   is free.
>   (2) Half the page is devoted to talking about bugs in
>   the software.  This suggests low quality.
>   (3) The "News" contains scary words: "radical changes".
>   (4) Three releases in as many months suggests the
>   code is not stable.
>   (5) Move the BigNameUsers to the front page
>   (see http://www.sqlite.org/wiki?p=BigNameUsers)
>   (6) Need more eye-candy.
>
> I do not necessary agree with the above comments, but I
> am open to any and all ideas.  You will not hurt my feels,
> so speak freely.
>
> Thanks in advance for your input.
>
>


-- 
Puneet Kishor

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] SQLITE_OMIT_MEMORY_ALLOCATION

[Mark Spiegel]

I just started looking at 3.5.2 and notice that support for 
SQLITE_OMIT_MEMORY_ALLOCATION was removed.  This is a problem for me.  
Is there some reasoning behind this?

RE: [sqlite] Suggests for improving the SQLite website

[Igor Sereda]

I'm new to this list and to the SQLite website, so my feedback is more of
the "first impression" kind. And that impression is: the site is ok. It is
clear, simple, with almost anything I need reachable through one or two
clicks. The things I would probably do is place a google search field
somewhere in a corner and list what programming languages are supported.

The comments you mention, in my opinion, may be valid for promoting a
product sold to consumers or enterprise, which are not target audience I
would associate with this mailing list. A developer who looks for an
embedded database doesn't need eye candy, and big name users don't mean
anything (everyone uses Oracle). But that's subjective, of course. Here's
what I was looking at when making try/not try decision for sqlite: a)
license; b) features; c) could be used with Java; d) source code
availability; e) project age and release cycle (is it too young or already
dead); f) is active community present; g) options for commercial support
available.

> (1) It is not clear from the homepage that the software is free.

I guess mentioning this won't hurt, but it's no problem. Certainly someone
who can write SQL can also find "license" in the menu :)

> (2) Half the page is devoted to talking about bugs in
> the software.  This suggests low quality.

This suggests openness. If it went "we have no bugs in our software", I
would probably leave immediately. But there's a point that news column
usually takes less than 50% of page's width, something I'd agree with. 

> (3) The "News" contains scary words: "radical changes".

s/changes/improvements/ :) Seriously, there's too much care for single
words, IMHO.

> (4) Three releases in as many months suggests the code is not stable.

And 98% test coverage mentioned in the other column suggests otherwise. For
me, frequent releases are good. What will you do anyway, hide release
history? 


Hope this helps,
Igor

 
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
Sent: Thursday, November 08, 2007 7:29 PM
To: sqlite-users@sqlite.org
Subject: [sqlite] Suggests for improving the SQLite website

We are looking at renovating the design of the SQLite website and would love
to have suggestions from the community.  If you have any ideas on how to
improve the SQLite website, please constribute either to the mailing list or
directly to me.

Here are some links to competing database products that might inspire
comments:

   http://www.postgresql.org/
   http://www.firebirdsql.org/
   http://www.hsqldb.org/
   http://opensource.ingres.com/
   http://db.apache.org/derby/
   http://exist.sourceforge.net/

Among the comments received already are these:

  (1) It is not clear from the homepage that the software
  is free.
  (2) Half the page is devoted to talking about bugs in
  the software.  This suggests low quality.
  (3) The "News" contains scary words: "radical changes".
  (4) Three releases in as many months suggests the
  code is not stable.
  (5) Move the BigNameUsers to the front page
  (see http://www.sqlite.org/wiki?p=BigNameUsers)
  (6) Need more eye-candy.

I do not necessary agree with the above comments, but I am open to any and
all ideas.  You will not hurt my feels, so speak freely.

Thanks in advance for your input.

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




-
To unsubscribe, send email to [EMAIL PROTECTED]

-



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Suggests for improving the SQLite website

[bartsmissaert]

What could be interesting is a listing of all the ways SQLite is used and
for users to upload their
details with a number of fixed fields like:
short description, size of database, number of users,
commercial/non-commercial, platform/OS, programming language, wrapper,
satisfaction with the application, plans for future.
Not sure if this is feasible, but it would be interesting to read + to see
the stats of it.
Obviously it could become a very big list and I am not sure
web-design-wise how that should be handled.

RBS

> We are looking at renovating the design of the SQLite
> website and would love to have suggestions from the
> community.  If you have any ideas on how to improve
> the SQLite website, please constribute either to the
> mailing list or directly to me.
>
> Here are some links to competing database products
> that might inspire comments:
>
>http://www.postgresql.org/
>http://www.firebirdsql.org/
>http://www.hsqldb.org/
>http://opensource.ingres.com/
>http://db.apache.org/derby/
>http://exist.sourceforge.net/
>
> Among the comments received already are these:
>
>   (1) It is not clear from the homepage that the software
>   is free.
>   (2) Half the page is devoted to talking about bugs in
>   the software.  This suggests low quality.
>   (3) The "News" contains scary words: "radical changes".
>   (4) Three releases in as many months suggests the
>   code is not stable.
>   (5) Move the BigNameUsers to the front page
>   (see http://www.sqlite.org/wiki?p=BigNameUsers)
>   (6) Need more eye-candy.
>
> I do not necessary agree with the above comments, but I
> am open to any and all ideas.  You will not hurt my feels,
> so speak freely.
>
> Thanks in advance for your input.
>
> --
> D. Richard Hipp <[EMAIL PROTECTED]>
>
>
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
>
>
>




-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] SQLite and Large Databases

[PokerAce]

Actually, I am.  I abstract the database communication in my application,
but I access each database using their native API.


On Nov 8, 2007 12:44 PM, Joe Wilson <[EMAIL PROTECTED]> wrote:

> --- PokerAce <[EMAIL PROTECTED]> wrote:
> > "Are you certain it's sqlite RAM, and not your application?"
> >
> > Yes, we are also testing PostgreSQL and MySQL and the application memory
> > with those stays < 20 mb.
>
> You're not using the sqlite3 API directly, are you?
> If that's the case, I think your database driver is leaking memory.
>
>
> __
> Do You Yahoo!?
> Tired of spam?  Yahoo! Mail has the best spam protection around
> http://mail.yahoo.com
>
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
>
> -
>
>

Re: [sqlite] Suggests for improving the SQLite website

[John Stanton]

Rich Shepard wrote:

On Thu, 8 Nov 2007, Stephan Beal wrote:


google code award implies that it's free (doesn't it?)




Only to people not used to open source. "Release early, release often."




Definitely not. Its simplicity is its main beauty.



Stephan,

  Good comments from your point of view. However, what the development team
needs to define is the audience -- or audiences -- they want to effectively
reach. Not everyone looking at the web site is a linux/*BSD user, highly
involved with database management systems, code development, or other
technical aspects. Quite often the key decision-maker knows little about 
the

technology, but needs to be assured that there is a solid business reason
for adopting it. These folks need to see something more appealing to them.

Rich

To my mind the current Sqlite page is revealing and honest.  A cursory 
glance and you can see what is being presented, where it has been and 
where it is going.  It is not doing people a favour to entice them into 
using something which they may not be able to handle or which is 
inappropriate for their needs.


My suggestion for improvment is is access to the wiki and the archives 
of support information.  That could be refined.


A horde of raving, enthusiastic fans is a better way to promote a 
product than have disgruntled people who failed to achieve a good result 
with it.


There is no need to apologize for the absense of snake oil, smoke and 
mirrors.


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] SQLite and Large Databases

[Joe Wilson]

--- PokerAce <[EMAIL PROTECTED]> wrote:
> "Are you certain it's sqlite RAM, and not your application?"
> 
> Yes, we are also testing PostgreSQL and MySQL and the application memory
> with those stays < 20 mb.

You're not using the sqlite3 API directly, are you?
If that's the case, I think your database driver is leaking memory.


__
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] Suggests for improving the SQLite website

[Wilson, Ron]

QFT

I agree with both Stephan *and* Rich.  It would be great if the site
stayed clean, white, and straightforward.  I don't personally have
difficulty selling SQLite to my boss because we've used it in many
successful projects, but I can see how the current site may not measure
up to so-called 'web 2.0' expectations.

Just please please please avoid cheezy stock photos of happy users in
breathtaking vistas of nature.

Maybe a logo design contest is in order?

RW

Ron Wilson, Senior Engineer, MPR Associates, 518.831.7546

-Original Message-
From: Rich Shepard [mailto:[EMAIL PROTECTED] 
Sent: Thursday, November 08, 2007 11:47 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Suggests for improving the SQLite website

On Thu, 8 Nov 2007, Stephan Beal wrote:

> google code award implies that it's free (doesn't it?)

> Only to people not used to open source. "Release early, release
often."

> Definitely not. Its simplicity is its main beauty.

Stephan,

   Good comments from your point of view. However, what the development
team
needs to define is the audience -- or audiences -- they want to
effectively
reach. Not everyone looking at the web site is a linux/*BSD user, highly
involved with database management systems, code development, or other
technical aspects. Quite often the key decision-maker knows little about
the
technology, but needs to be assured that there is a solid business
reason
for adopting it. These folks need to see something more appealing to
them.

Rich

-- 
Richard B. Shepard, Ph.D.   |  Integrity
Credibility
Applied Ecosystem Services, Inc.|Innovation
 Voice: 503-667-4517  Fax:
503-667-8863


-
To unsubscribe, send email to [EMAIL PROTECTED]

-


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] Suggests for improving the SQLite website

[Evans, Mark (Tandem)]

I love the website.  Suggested improvements:
- link to the users site from home:
http://www.mail-archive.com/sqlite-users@sqlite.org/info.html
- a publicity page that contains links to interviews, reviews, etc.
- expand the list of things that cannot be done with virtual tables (it
lists triggers but not check constraints and maybe other things)
- developers page with pictures and brief bios

Cheers,
Mark

> -Original Message-
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
> Sent: Thursday, November 08, 2007 10:29 AM
> To: sqlite-users@sqlite.org
> Subject: [sqlite] Suggests for improving the SQLite website
> 
> We are looking at renovating the design of the SQLite website 
> and would love to have suggestions from the community.  If 
> you have any ideas on how to improve the SQLite website, 
> please constribute either to the mailing list or directly to me.
> 
> Here are some links to competing database products that might 
> inspire comments:
> 
>http://www.postgresql.org/
>http://www.firebirdsql.org/
>http://www.hsqldb.org/
>http://opensource.ingres.com/
>http://db.apache.org/derby/
>http://exist.sourceforge.net/
> 
> Among the comments received already are these:
> 
>   (1) It is not clear from the homepage that the software
>   is free.
>   (2) Half the page is devoted to talking about bugs in
>   the software.  This suggests low quality.
>   (3) The "News" contains scary words: "radical changes".
>   (4) Three releases in as many months suggests the
>   code is not stable.
>   (5) Move the BigNameUsers to the front page
>   (see http://www.sqlite.org/wiki?p=BigNameUsers)
>   (6) Need more eye-candy.
> 
> I do not necessary agree with the above comments, but I am 
> open to any and all ideas.  You will not hurt my feels, so 
> speak freely.
> 
> Thanks in advance for your input.
> 
> --
> D. Richard Hipp <[EMAIL PROTECTED]>
> 
> 
> 
> --
> ---
> To unsubscribe, send email to [EMAIL PROTECTED]
> --
> ---
> 
> 

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Suggests for improving the SQLite website

[Rich Shepard]

On Thu, 8 Nov 2007, Stephan Beal wrote:


google code award implies that it's free (doesn't it?)



Only to people not used to open source. "Release early, release often."



Definitely not. Its simplicity is its main beauty.


Stephan,

  Good comments from your point of view. However, what the development team
needs to define is the audience -- or audiences -- they want to effectively
reach. Not everyone looking at the web site is a linux/*BSD user, highly
involved with database management systems, code development, or other
technical aspects. Quite often the key decision-maker knows little about the
technology, but needs to be assured that there is a solid business reason
for adopting it. These folks need to see something more appealing to them.

Rich

--
Richard B. Shepard, Ph.D.   |  IntegrityCredibility
Applied Ecosystem Services, Inc.|Innovation
 Voice: 503-667-4517  Fax: 503-667-8863

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] SQLite and Large Databases

[PokerAce]

"Are you certain it's sqlite RAM, and not your application?"

Yes, we are also testing PostgreSQL and MySQL and the application memory
with those stays < 20 mb.

"Is your temp_store set to memory or file?"

I have not changed this, so it's whatever the default is.

"How are you performing your inserts (prepared statements)?"

Not using prepared statements, just sending a batch of "INSERT INTO"
statements.

"How many rows are you inserting per batch?"

Do you mean per transaction?  If so, approximately 30 or so.  The 500 mb
memory usage was after 150k transactions.

"What's your database page_size?"

Whatever the default is.

"Can you build your table indexes after you populate the data?"

Preferably not.

"Is this for a poker showdown database by any chance?"

Nope. :-)

It's for: http://preview.pokertracker3.com


On Nov 8, 2007 11:59 AM, Joe Wilson <[EMAIL PROTECTED]> wrote:

> --- PokerAce <[EMAIL PROTECTED]> wrote:
> > I'm trying to see if SQLite is suitable for large databases ( > 1 gb,
> > millions of rows in each of several tables).  Initially, the memory
> usage
> > was outrageous (~ 500 mb for a 1.3 gb db), but I got that down to < 30
> mb by
> > setting the cache size to 0 and setting a low soft heap limit.  That
> works
> > when I'm reading from the database, but when I am inserting these rows,
> the
> > memory usage grows back into the ~500 mb range.  My goal is to never
> have
> > the application use more than 100 mb of memory, preferably much less
> than
> > that.  Does anyone have any suggestions?
>
> If your cache_size is 0 I'm not sure what's eating up 500M of RAM for
> inserts.
>
> Some questions that might give you some ideas:
>
> Are you certain it's sqlite RAM, and not your application?
> Is your temp_store set to memory or file?
> How are you performing your inserts (prepared statements)?
> How many rows are you inserting per batch?
> What's your database page_size?
> Can you build your table indexes after you populate the data?
>
> Is this for a poker showdown database by any chance?
> http://games.cs.ualberta.ca/poker/IRC/
>
>
>
> __
> Do You Yahoo!?
> Tired of spam?  Yahoo! Mail has the best spam protection around
> http://mail.yahoo.com
>
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
>
> -
>
>

Re: [sqlite] Suggests for improving the SQLite website

[Michael Schlenker]

[EMAIL PROTECTED] schrieb:

We are looking at renovating the design of the SQLite
website and would love to have suggestions from the
community.  If you have any ideas on how to improve
the SQLite website, please constribute either to the
mailing list or directly to me.

Among the comments received already are these:

  (1) It is not clear from the homepage that the software
  is free.

Its stated on the homepage, but maybe it should be in one of the
first paragraphs, not at the last bullet in the list. I would
reorder the features list quite a bit, the more technical
details down (like the byte order thing and some others).


  (2) Half the page is devoted to talking about bugs in
  the software.  This suggests low quality.
  (3) The "News" contains scary words: "radical changes".
  (4) Three releases in as many months suggests the
  code is not stable.

All those are under 'NEWS'. Maybe make the news section smaller
and provide 'details' and 'developer details' links. Then the
news could be like an 'executive summary' and the real facts
would be listed under details and developer details.


  (5) Move the BigNameUsers to the front page
  (see http://www.sqlite.org/wiki?p=BigNameUsers)

Propaganda isn't bad. Maybe a rotating banner like it is found on many
websites that lists one of those with 'used by' for every page visit.


  (6) Need more eye-candy.

Not really. Its simple, clean and nice.

One thing that might be worth it, is a bit of cleanup of the menu in
the upper right corner, maybe group a bit.

I see these main categories there:
Code/Binaries: cvs, download, contrib
About: support, license, news?
Documentation: quick start, faq, syntax, wiki (changes?/news?)
Development: bugs, changes, timeline

Currently the trackers and the main website are not really integrated,
more side by side..., don't know if it would be good to change it.

Michael
Michael


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Suggests for improving the SQLite website

[Joe Wilson]

--- [EMAIL PROTECTED] wrote:
> We are looking at renovating the design of the SQLite
> website and would love to have suggestions from the
> community.  If you have any ideas on how to improve
> the SQLite website, please constribute either to the
> mailing list or directly to me.

Prize giveaway to millionth page visitor.


__
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] SQLite and Large Databases

[PokerAce]

I am using Windows Task Manager to determine memory usage.  I minimize my
application (which causes the task manager to show a more accurate
measurement of memory used) and still the memory usage is enormous.


On Nov 8, 2007 11:55 AM, Roger Binns <[EMAIL PROTECTED]> wrote:

> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
>
> PokerAce wrote:
> > Initially, the memory usage
> > was outrageous (~ 500 mb for a 1.3 gb db),
>
> How are you measuring memory usage?  You have to be very careful as most
> operating system tools will report a number greater than the size of all
> current mallocs() and depending on the pattern of malloc/free can report
> something substantially larger.  It doesn't mean the memory usage is
> that large.
>
> Roger
> -BEGIN PGP SIGNATURE-
> Version: GnuPG v1.4.6 (GNU/Linux)
>
> iD4DBQFHMz9wmOOfHg372QQRAtPXAJiW52di3U2JP6ywpE6MvB3e0Jr4AJ9B1bvj
> kpBlioHvm8lesFP3S1LEfQ==
> =zwuw
> -END PGP SIGNATURE-
>
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
>
> -
>
>

Re: [sqlite] SQLite and Large Databases

[PokerAce]

If you have to set that manually, it was not set.  I am using version 3.5.2.

I am doing tens of thousands of transactions, each without ~30 separate
INSERTs.


On Nov 8, 2007 11:40 AM, <[EMAIL PROTECTED]> wrote:

> PokerAce <[EMAIL PROTECTED]> wrote:
> > I'm trying to see if SQLite is suitable for large databases ( > 1 gb,
> > millions of rows in each of several tables).  Initially, the memory
> usage
> > was outrageous (~ 500 mb for a 1.3 gb db), but I got that down to < 30
> mb by
> > setting the cache size to 0 and setting a low soft heap limit.  That
> works
> > when I'm reading from the database, but when I am inserting these rows,
> the
> > memory usage grows back into the ~500 mb range.  My goal is to never
> have
> > the application use more than 100 mb of memory, preferably much less
> than
> > that.  Does anyone have any suggestions?
> >
>
> Did you compile with SQLITE_ENABLE_MEMORY_MANAGEMENT=1?
> The soft_heap_limit is a no-op if you did not.
>
> What version are you running.  What SQL are you executing that
> causes the memory usage to shoot up?
>
>
> --
> D. Richard Hipp <[EMAIL PROTECTED]>
>
>
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
>
> -
>
>

Re: [sqlite] SQLite and Large Databases

[Joe Wilson]

--- PokerAce <[EMAIL PROTECTED]> wrote:
> I'm trying to see if SQLite is suitable for large databases ( > 1 gb,
> millions of rows in each of several tables).  Initially, the memory usage
> was outrageous (~ 500 mb for a 1.3 gb db), but I got that down to < 30 mb by
> setting the cache size to 0 and setting a low soft heap limit.  That works
> when I'm reading from the database, but when I am inserting these rows, the
> memory usage grows back into the ~500 mb range.  My goal is to never have
> the application use more than 100 mb of memory, preferably much less than
> that.  Does anyone have any suggestions?

If your cache_size is 0 I'm not sure what's eating up 500M of RAM for inserts.

Some questions that might give you some ideas:

Are you certain it's sqlite RAM, and not your application?
Is your temp_store set to memory or file?
How are you performing your inserts (prepared statements)?
How many rows are you inserting per batch?
What's your database page_size?
Can you build your table indexes after you populate the data?

Is this for a poker showdown database by any chance?
http://games.cs.ualberta.ca/poker/IRC/



__
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] SQLite and Large Databases

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

PokerAce wrote:
> Initially, the memory usage
> was outrageous (~ 500 mb for a 1.3 gb db),

How are you measuring memory usage?  You have to be very careful as most
operating system tools will report a number greater than the size of all
current mallocs() and depending on the pattern of malloc/free can report
something substantially larger.  It doesn't mean the memory usage is
that large.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.6 (GNU/Linux)

iD4DBQFHMz9wmOOfHg372QQRAtPXAJiW52di3U2JP6ywpE6MvB3e0Jr4AJ9B1bvj
kpBlioHvm8lesFP3S1LEfQ==
=zwuw
-END PGP SIGNATURE-

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] SQLite and Large Databases

[drh]

PokerAce <[EMAIL PROTECTED]> wrote:
> I'm trying to see if SQLite is suitable for large databases ( > 1 gb,
> millions of rows in each of several tables).  Initially, the memory usage
> was outrageous (~ 500 mb for a 1.3 gb db), but I got that down to < 30 mb by
> setting the cache size to 0 and setting a low soft heap limit.  That works
> when I'm reading from the database, but when I am inserting these rows, the
> memory usage grows back into the ~500 mb range.  My goal is to never have
> the application use more than 100 mb of memory, preferably much less than
> that.  Does anyone have any suggestions?
> 

Did you compile with SQLITE_ENABLE_MEMORY_MANAGEMENT=1?  
The soft_heap_limit is a no-op if you did not.

What version are you running.  What SQL are you executing that
causes the memory usage to shoot up?


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


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Suggests for improving the SQLite website

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

[EMAIL PROTECTED] wrote:
> If you have any ideas on how to improve
> the SQLite website, please constribute either to the
> mailing list or directly to me.

"Search" is missing on the main page.  I always have to click around a
bit until a page with search appears.  As for all the other content you
think people miss, a small sidebar labelled "Popular Content" with links
to the relevant pages will do the trick.  If you want to go the whole
way then an animated gif with big name user logos, Google open source
awards, quotes from happy customers etc as a page banner would help.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFHMz50mOOfHg372QQRAhYYAJ9SqQEh9dyzahsANuzs1y+WZTn5NgCfb1S9
xl41GG5YpsJ3AQ4UeMdG4N8=
=5dgv
-END PGP SIGNATURE-

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Suggests for improving the SQLite website

[Stephan Beal]

On Nov 8, 2007 5:29 PM,  <[EMAIL PROTECTED]> wrote:
> We are looking at renovating the design of the SQLite
> website and would love to have suggestions from the
> community.  If you have any ideas on how to improve
> the SQLite website, please constribute either to the
> mailing list or directly to me.

One of the things i love about the sqlite site is how lightweight and
simple it is. The only thing which i find marginally confusing is how
to figure out which bugs report to select when i'm in the mood to
check out the latest open tickets. That said, i don't have any
concrete suggestions except, "leave it all the same, except for making
the bug reports selection a bit more intuitive."

>   (1) It is not clear from the homepage that the software
>   is free.

google code award implies that it's free (doesn't it?)

>   (4) Three releases in as many months suggests the
>   code is not stable.

Only to people not used to open source. "Release early, release often."

>   (6) Need more eye-candy.

Definitely not. Its simplicity is its main beauty.


-- 
- stephan beal
http://wanderinghorse.net/home/stephan/

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] SQLite and Large Databases

[PokerAce]

I'm trying to see if SQLite is suitable for large databases ( > 1 gb,
millions of rows in each of several tables).  Initially, the memory usage
was outrageous (~ 500 mb for a 1.3 gb db), but I got that down to < 30 mb by
setting the cache size to 0 and setting a low soft heap limit.  That works
when I'm reading from the database, but when I am inserting these rows, the
memory usage grows back into the ~500 mb range.  My goal is to never have
the application use more than 100 mb of memory, preferably much less than
that.  Does anyone have any suggestions?

Josh

[sqlite] Suggests for improving the SQLite website

[drh]

We are looking at renovating the design of the SQLite
website and would love to have suggestions from the
community.  If you have any ideas on how to improve
the SQLite website, please constribute either to the
mailing list or directly to me.

Here are some links to competing database products
that might inspire comments:

   http://www.postgresql.org/
   http://www.firebirdsql.org/
   http://www.hsqldb.org/
   http://opensource.ingres.com/
   http://db.apache.org/derby/
   http://exist.sourceforge.net/

Among the comments received already are these:

  (1) It is not clear from the homepage that the software
  is free.
  (2) Half the page is devoted to talking about bugs in
  the software.  This suggests low quality.
  (3) The "News" contains scary words: "radical changes".
  (4) Three releases in as many months suggests the
  code is not stable.
  (5) Move the BigNameUsers to the front page
  (see http://www.sqlite.org/wiki?p=BigNameUsers)
  (6) Need more eye-candy.

I do not necessary agree with the above comments, but I
am open to any and all ideas.  You will not hurt my feels,
so speak freely.

Thanks in advance for your input.

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



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Disk caching impacts performance.

[Joe Wilson]

This disk cache effect is pretty well known on this list.

Mozilla/Firefox also uses a similar technique for its sqlite database:

 http://developer.mozilla.org/en/docs/Storage:Performance#Priming_the_cache

They go to the extra step of populating the sqlite page cache with
some custom code:

 http://mxr.mozilla.org/mozilla/source/db/sqlite3/preload-cache.patch

This patch probably won't work with the sqlite 3.5.x series without 
modification. But I don't think it's worth the hassle when a plain 
'file read and discard' without modifying sqlite code delivers 
comparable results for most (all?) desktop OSes with file caches,
as you've discovered.

--- Julien Renggli <[EMAIL PROTECTED]> wrote:
> I then had the following idea: SQLite is probably reading the file
> randomly, depending on where the data lies. If I can force the DB to be
> cached, everything should be fine. So before connecting the database, I
> first read it sequentially (using a C++ ifstream) until the end of file.
> 
> It perfectly solves the database problem, even though I still notice a
> difference (3s to read the file on 1st run, 0.2s later). But 3s is OK
> where 30s was worrying me.


__
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Disk caching impacts performance.

[Mark Spiegel]

As Trevor cautioned, you run the risk of really impacting the other 
applications running on the system and the system itself.  As you fill 
up the Windows cache manager with your file, you will push out other 
relevant data including data that the OS may be using.  This will cause 
applications to run slower until the data is reloaded and new 
applications to start slower.  The cache manager will drop cached data 
as it loads your file.  Assuming that the cache manager is not already 
using memory at the limit allowed, it will ask the memory manager for 
more pages which will cause the memory manager to (on an LRU basis) drop 
pages that are backed by a file via a mapping and write pages to the 
paging file in cases where the pages are not backed by a map.  These 
writes (if they happen) are expensive in a relative sense and will now 
be mixed with your reads.  If the OS is not under stress and has 
sufficient resources, you probably won't see any of this.


Given that you can't write your own VFS, there is not much to suggest, 
but one question to ask.  Is the sum of the time for the pre-read you 
perform and the subsequent database operation(s) smaller than doing the 
database operation(s) without the pre-read?  I see that in the 3.5.x 
source Dr. Hipp gives the file system the proper random access hint to 
the file system when opening databases.  This is just a hint to the 
cache manager and it is not obligated to honor it, but it will 
effectively shut down most read ahead and large block reads which is 
what you are getting when you sequentially pre-read.


One more thing, did raising the limit on the number of pages SQLITE can 
cache internally have any effect?



Trevor Talbot wrote:

On 11/8/07, Julien Renggli <[EMAIL PROTECTED]> wrote:

  

As I said, the "nasty" trick works and we'll live with it (writing our
own FileSystem is not an option). We would just like to understand
better what's happening, to know why and when the "first run" is so much
slower. And should we read the whole file beforehand, only parts of it?
Does it depend on how much RAM is installed, ...? If you have any hints
they are welcome. I guess I should ask NTFS experts as well.



What you've discovered is pretty accurate: when the delay is due to
disk I/O, pre-reading the file will load it into the OS's disk cache,
so subsequent accesses are faster.  It will depend on available RAM,
not only what is physically installed but also memory pressure from
other running applications, other disk I/O in progress, various OS
settings, etc.  There isn't any way to accurately predict it.

Reading the entire file when the OS is unwilling to cache all of it
will simply result in only part of the file being cached.  It may also
cause other data to be pushed into the pagefile, slowing down
applications when they later access their own stale data.  In the
worst case, it could slow everything down for a short time.

Reading the entire file from start to finish is pretty much the only
effective way to pull it into cache.  An ifstream is not the most
efficient way to do that, since it does its own buffering, but that's
not important as far as the disk caching effects are concerned.

VACUUM removes internal fragmentation and writes rows in order, which
helps make disk I/O more sequential.  You may be able to one-up it by
inserting data in the order you intend to access it (but create any
indexes afterward).  Beyond that, I'm not aware of anything that would
help.

-
To unsubscribe, send email to [EMAIL PROTECTED]
-


  

Re: [sqlite] sqlite for 16 bits microcontroler x186

[Joe Wilson]

--- Felix <[EMAIL PROTECTED]> wrote:
> I would use sqlite in an embedded DOS system ( intel 16 bits processor ) .
> 
> Do you think that is possible or sqlite work only on 32/64 bits systems?

I could be wrong, but I think sqlite expects that int and pointers are
at least 32 bit.

See if you can find a C compiler that emulates 32 bit ints and pointers 
on 80186 - although it would be very slow. But even if such a compiler
exists your chip may not have enough RAM to effectively run sqlite anyway.

__
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] is safe to use the same database connection at the same time in more than one thread?

[Salles, Joaquim Campos]

-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
Sent: quinta-feira, 8 de novembro de 2007 12:04
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] is safe to use the same database connection at the
same time in more than one thread?

>Yes.  The wiki page was correct when written but that was
>prior to the release of version 3.5.0.

OK. Maybe some upgrade the wiki release 3.5.*...


>Note that SQLite uses mutexes internally so what will really
>happen is one thread will block while the other is using the
>database connection.  Access to database connections is serialized.
>But the application code does not need to concern itself with
>the serialization any more.  That is now handled automatically
>by SQLite.

Here is an important point: "Access to database connections is
serialized".

Thanks for the help.

Joaquim

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re[2]: [sqlite] Disk caching impacts performance.

[Teg]

Hello Renaud,

Thursday, November 8, 2007, 9:11:41 AM, you wrote:


RH> Is the mmap option part of sqlite or do we need to change the sqlite code ?


RH> If so, has somebody here already tried it ? Any source available ?


RH> What 's the order of improvement ?





RH> Renaud

>> Date: Thu, 8 Nov 2007 15:54:35 +0500
>> From: [EMAIL PROTECTED]
>> To: sqlite-users@sqlite.org
>> Subject: Re: [sqlite] Disk caching impacts performance.
>>
>> Try this, use mmap
>> (i assume u'r db is readonly)this is much faster and
>> better than ifstream read. Also ifstream read can keep the data in cache as 
>> long as no other serious
>> i/o occurs.
>>
>> U need to accept it as we work with Virtual Mem or
>> write your own FileSystem which is mem based and short circuits os calls. 
>> Sqlite 3.5x has good support for such ext.
>>
>> regrads
>> ragha
>>
>> **
>> This email and its attachments contain confidential information from HUAWEI, 
>> which is intended only for the person or entity whose address is listed 
>> above. Any use of the information contained herein in any way (including, 
>> but not limited to, total or partial disclosure, reproduction, or 
>> dissemination) by persons other than the intended recipient(s) is 
>> prohibited. If you receive this e-mail in error, please notify the sender by 
>> phone or email immediately and delete it!
>> *
>>
>> - Original Message -
>> From: Julien Renggli 
>> Date: Thursday, November 8, 2007 4:15 pm
>> Subject: [sqlite] Disk caching impacts performance.
>>
>>> Hello,
>>>
>>>
>>>
>>> I'm currently working on a project which is very likely to use
>>> SQLite as
>>> data storage. Since performance is important for us, I already
>>> found on
>>> the SQLite website many ways to optimise the code (always working in
>>> transactions where possible, using a page size of 4096 since it's
>>> running on the Windows platform, using integers primary keys, ...).
>>> ButI have one problem that I "solved" in an unorthodox way; it
>>> works, but
>>> maybe you have a better solution than mine?
>>>
>>>
>>>
>>> I've been doing some test with a ~100 MB database, in which I have
>>> three
>>> tables: one for structured data (Objects, 2000 entries), one for the
>>> blobs we have to store (ObjectBlobs ID INTEGER PRIMARY KEY, Data BLOB)
>>> (8000 entries), and one which binds the structured data and the blobs
>>> (8000 entries). As you can imagine, each Object has 4 blobs linked to
>>> it; the blobs can be quite large (let's say up to 1 MB).
>>>
>>> My (C++) application just has to read the table "Objects", and one of
>>> the Blobs for each Object.
>>>
>>>
>>>
>>> Now the first time I run my application, it takes quite a long time
>>>
>>> (30s) to load the blobs. But if I re-run the app, it only takes 1s to
>>> load them. It's clearly a disk caching issue: if I copy huge files to
>>> the disk between two runs, it takes again 30s to load the blobs (i.e.
>>>
>>> the DB is no more in the disk cache). Profiling the application
>>> indicates sqlite::winRead() is the bottleneck.
>>>
>>>
>>>
>>> I then had the following idea: SQLite is probably reading the file
>>> randomly, depending on where the data lies. If I can force the DB
>>> to be
>>> cached, everything should be fine. So before connecting the
>>> database, I
>>> first read it sequentially (using a C++ ifstream) until the end of
>>> file.
>>> It perfectly solves the database problem, even though I still
>>> notice a
>>> difference (3s to read the file on 1st run, 0.2s later). But 3s is OK
>>> where 30s was worrying me.
>>>
>>>
>>>
>>> I hope I explain the situation clear enough, and ask you now: is it
>>> theonly way to do it? I find the trick a bit nasty and don't like
>>> it; maybe
>>> I missed something? Before you ask: I tried to VACUUM the DB, it only
>>> reduced the timings to 16s, which was still bad for our requirements.
>>>
>>> Tests with a larger DB (it can get much bigger than my example) and on
>>> different machines tend to confirm my theory.
>>>
>>>
>>>
>>> Thanks in advance (and a big thank for SQLite which is really nice and
>>> easy to use !),
>>>
>>>
>>>
>>> Julien Renggli
>>>
>>>
>>>
>>> P.S.: Some technical informations:
>>>
>>> - sqlite v.3.3.16 (we will upgrade to the latest version later), C API
>>>
>>> - Windows XP SP2
>>>
>>> - Timings on Pentium 4 3.4GHz, 2GB RAM
>>>
>>>
>>>
>>>
>>
>> -
>> To unsubscribe, send email to [EMAIL PROTECTED]
>> -
>>

RH> _
RH> Explore the seven wonders of the world
RH> http://search.msn.com/results.aspx?q=7+wonders+world&mkt=en-US&form=QBRE
RH> 
-

Re: [sqlite] why select distinct does not use index?

[Joe Wilson]

--- "Maxim V. Shiyanovsky" <[EMAIL PROTECTED]> wrote:
> Suppose I have simple table:
> 
> CREATE TABLE [profile_data] (
>   [profile_id] INTEGER,
>   [version] INTEGER);
> 
> CREATE INDEX [by_id] ON [profile_data] ([id]);
> 
> Why 
> 
> sqlite> explain query plan
>...> select distinct(profile_id) from profile_data;
> 
> 0|0|TABLE profile_data
> 
> Does not use index?

It should, but sqlite doesn't appear to make that optimization.

Try this instead:

CREATE TABLE profile_data(profile_id INTEGER, version INTEGER);
CREATE INDEX by_id ON profile_data(profile_id);
explain query plan select profile_id from profile_data group by 1;
0|0|TABLE profile_data WITH INDEX by_id ORDER BY


__
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] is safe to use the same database connection at the same time in more than one thread?

[Salles, Joaquim Campos]

-Original Message-
>From: Salles, Joaquim Campos [mailto:[EMAIL PROTECTED] 
>Sent: quinta-feira, 8 de novembro de 2007 11:45
>To: sqlite-users@sqlite.org
>Subject: [sqlite] is safe to use the same database connection at the
same >time in more than one thread?

>Is safe to use the same database connection  at the same time in more
>than one thread? 

Hello,

I found two e-mails (bellow) that I think give the answer: yes is safe
to use the same database connection, but is not work in the way that I
imagined. "Transaction control is per connection, not per thread." (Igor
Tandetnik) or "That connection can then be used across threads, but it
is for all intents and purposes a single line of communication with a
database (using it twice at the same time doesn't somehow multiply that
relationship)". (Stephan Beal)


My interpretation is correct? Probably is a good idea to review Multi
Threading page (http://www.sqlite.org/cvstrac/wiki?p=MultiThreading) and
alert about the characteristic :treath safe and Transaction control is
per connection

Best regards,

Joaquim 

Reference:

http://www.mail-archive.com/sqlite-users%40sqlite.org/msg28858.html

You're confusing threads with the context of the connection. When you
call sqlite3_open() you get a single connection to a db. That
connection can then be used across threads, but it is for all intents
and purposes a single line of communication with a database (using it
twice at the same time doesn't somehow multiply that relationship).

stephan beal

http://www.mail-archive.com/sqlite-users%40sqlite.org/msg28868.html

That would happen if thread B had its own, separate connection, but not
when the two threads are working with the same connection. As far as
SQLite is concerned, there's no difference between a single thread
making two changes to the database within a single transaction, or two
threads each making one change. Transaction control is per connection,
not per thread. 

Igor Tandetnik



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] Disk caching impacts performance.

[Renaud HUILLET]

Is the mmap option part of sqlite or do we need to change the sqlite code ?


If so, has somebody here already tried it ? Any source available ?


What 's the order of improvement ?





Renaud

> Date: Thu, 8 Nov 2007 15:54:35 +0500
> From: [EMAIL PROTECTED]
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Disk caching impacts performance.
>
> Try this, use mmap
> (i assume u'r db is readonly)this is much faster and
> better than ifstream read. Also ifstream read can keep the data in cache as 
> long as no other serious
> i/o occurs.
>
> U need to accept it as we work with Virtual Mem or
> write your own FileSystem which is mem based and short circuits os calls. 
> Sqlite 3.5x has good support for such ext.
>
> regrads
> ragha
>
> **
> This email and its attachments contain confidential information from HUAWEI, 
> which is intended only for the person or entity whose address is listed 
> above. Any use of the information contained herein in any way (including, but 
> not limited to, total or partial disclosure, reproduction, or dissemination) 
> by persons other than the intended recipient(s) is prohibited. If you receive 
> this e-mail in error, please notify the sender by phone or email immediately 
> and delete it!
> *
>
> - Original Message -
> From: Julien Renggli 
> Date: Thursday, November 8, 2007 4:15 pm
> Subject: [sqlite] Disk caching impacts performance.
>
>> Hello,
>>
>>
>>
>> I'm currently working on a project which is very likely to use
>> SQLite as
>> data storage. Since performance is important for us, I already
>> found on
>> the SQLite website many ways to optimise the code (always working in
>> transactions where possible, using a page size of 4096 since it's
>> running on the Windows platform, using integers primary keys, ...).
>> ButI have one problem that I "solved" in an unorthodox way; it
>> works, but
>> maybe you have a better solution than mine?
>>
>>
>>
>> I've been doing some test with a ~100 MB database, in which I have
>> three
>> tables: one for structured data (Objects, 2000 entries), one for the
>> blobs we have to store (ObjectBlobs ID INTEGER PRIMARY KEY, Data BLOB)
>> (8000 entries), and one which binds the structured data and the blobs
>> (8000 entries). As you can imagine, each Object has 4 blobs linked to
>> it; the blobs can be quite large (let's say up to 1 MB).
>>
>> My (C++) application just has to read the table "Objects", and one of
>> the Blobs for each Object.
>>
>>
>>
>> Now the first time I run my application, it takes quite a long time
>>
>> (30s) to load the blobs. But if I re-run the app, it only takes 1s to
>> load them. It's clearly a disk caching issue: if I copy huge files to
>> the disk between two runs, it takes again 30s to load the blobs (i.e.
>>
>> the DB is no more in the disk cache). Profiling the application
>> indicates sqlite::winRead() is the bottleneck.
>>
>>
>>
>> I then had the following idea: SQLite is probably reading the file
>> randomly, depending on where the data lies. If I can force the DB
>> to be
>> cached, everything should be fine. So before connecting the
>> database, I
>> first read it sequentially (using a C++ ifstream) until the end of
>> file.
>> It perfectly solves the database problem, even though I still
>> notice a
>> difference (3s to read the file on 1st run, 0.2s later). But 3s is OK
>> where 30s was worrying me.
>>
>>
>>
>> I hope I explain the situation clear enough, and ask you now: is it
>> theonly way to do it? I find the trick a bit nasty and don't like
>> it; maybe
>> I missed something? Before you ask: I tried to VACUUM the DB, it only
>> reduced the timings to 16s, which was still bad for our requirements.
>>
>> Tests with a larger DB (it can get much bigger than my example) and on
>> different machines tend to confirm my theory.
>>
>>
>>
>> Thanks in advance (and a big thank for SQLite which is really nice and
>> easy to use !),
>>
>>
>>
>> Julien Renggli
>>
>>
>>
>> P.S.: Some technical informations:
>>
>> - sqlite v.3.3.16 (we will upgrade to the latest version later), C API
>>
>> - Windows XP SP2
>>
>> - Timings on Pentium 4 3.4GHz, 2GB RAM
>>
>>
>>
>>
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
>

_
Explore the seven wonders of the world
http://search.msn.com/results.aspx?q=7+wonders+world&mkt=en-US&form=QBRE
-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] Is possible put a password in database ?

[Samuel R. Neff]

Paulito,

I believe from a previous post you're using the System.Data.SQLite wrapper,
right?  That provides Windows-only encryption built in to the wrapper.

HTH,

Sam



---
We're Hiring! Seeking a passionate developer to join our team building Flex
based products. Position is in the Washington D.C. metro area. If interested
contact [EMAIL PROTECTED]
 
-Original Message-
From: paulito santana [mailto:[EMAIL PROTECTED] 
Sent: Thursday, November 08, 2007 3:08 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] Is possible put a password in database ?

Hei all,
is possible i put a password in the file that represents a SQLite database
??  There is any instruction in SQLite command line that allows this ?



Regards,
Paulito


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] is safe to use the same database connection at the same time in more than one thread?

[drh]

"Salles, Joaquim Campos" <[EMAIL PROTECTED]> wrote:
> Hello,
> 
> Is safe to use the same database connection  at the same time in more
> than one thread? The bellow like that is safe: 
> 
> http://www.sqlite.org/34to35.html
> 
> "Restrictions on the use of the same database connection by multiple
> threads have been dropped. It is now safe for multiple threads to use
> the same database connection at the same time."
> 
> But in following URL say:
> 
> http://www.sqlite.org/cvstrac/wiki?p=MultiThreading
> 
> "Do not use the same database connection at the same time in more than
> one thread."
> 
> 
> So, for SQlite 3.5.* is safe to use the same database connection at the
> same time in more than one thread?
> 

Yes.  The wiki page was correct when written but that was
prior to the release of version 3.5.0.

Note that SQLite uses mutexes internally so what will really
happen is one thread will block while the other is using the
database connection.  Access to database connections is serialized.
But the application code does not need to concern itself with
the serialization any more.  That is now handled automatically
by SQLite.

It is also my duty to earnestly warn programmers of all ilks
that multi-threaded programming is madness.  Multi-threading
causes programs to run slower, have more bugs, and become harder
to maintain.  Threads are deadly.  Avoid them.

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



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Disk caching impacts performance.

[drh]

"Trevor Talbot" <[EMAIL PROTECTED]> wrote:
> Beyond that, I'm not aware of anything that would help.
> 

All good advice.  But you left off the obvious:  Get a
faster disk drive.  ;-)

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


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] is safe to use the same database connection at the same time in more than one thread?

[Salles, Joaquim Campos]

Hello,

Is safe to use the same database connection  at the same time in more
than one thread? The bellow like that is safe: 

http://www.sqlite.org/34to35.html

"Restrictions on the use of the same database connection by multiple
threads have been dropped. It is now safe for multiple threads to use
the same database connection at the same time."

But in following URL say:

http://www.sqlite.org/cvstrac/wiki?p=MultiThreading

"Do not use the same database connection at the same time in more than
one thread."


So, for SQlite 3.5.* is safe to use the same database connection at the
same time in more than one thread?

Thanks for the help,

Best regards,

Joaquim

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Disk caching impacts performance.

[Trevor Talbot]

On 11/8/07, Julien Renggli <[EMAIL PROTECTED]> wrote:

> As I said, the "nasty" trick works and we'll live with it (writing our
> own FileSystem is not an option). We would just like to understand
> better what's happening, to know why and when the "first run" is so much
> slower. And should we read the whole file beforehand, only parts of it?
> Does it depend on how much RAM is installed, ...? If you have any hints
> they are welcome. I guess I should ask NTFS experts as well.

What you've discovered is pretty accurate: when the delay is due to
disk I/O, pre-reading the file will load it into the OS's disk cache,
so subsequent accesses are faster.  It will depend on available RAM,
not only what is physically installed but also memory pressure from
other running applications, other disk I/O in progress, various OS
settings, etc.  There isn't any way to accurately predict it.

Reading the entire file when the OS is unwilling to cache all of it
will simply result in only part of the file being cached.  It may also
cause other data to be pushed into the pagefile, slowing down
applications when they later access their own stale data.  In the
worst case, it could slow everything down for a short time.

Reading the entire file from start to finish is pretty much the only
effective way to pull it into cache.  An ifstream is not the most
efficient way to do that, since it does its own buffering, but that's
not important as far as the disk caching effects are concerned.

VACUUM removes internal fragmentation and writes rows in order, which
helps make disk I/O more sequential.  You may be able to one-up it by
inserting data in the order you intend to access it (but create any
indexes afterward).  Beyond that, I'm not aware of anything that would
help.

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Is possible put a password in database ?

[Trevor Talbot]

On 11/8/07, Jonas Sandman <[EMAIL PROTECTED]> wrote:

> How much is it for the encrypted version?

The info on that is here: http://www.hwaci.com/sw/sqlite/prosupport.html

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] Disk caching impacts performance.

[Julien Renggli]

Thanks for your quick answer.

RaghavendraK 70574 wrote:
> Try this, use mmap 
> (i assume u'r db is readonly)this is much faster and
> better than ifstream read. Also ifstream read can keep the data in
cache as long as no other serious
> i/o occurs.

Our DB is readwrite, though in my example readonly is a valid option.
I just saw the sqlite3_open_v2 function takes a READONLY flag as
argument, so I'll try to use it for my example, and see if it makes any
difference to READWRITE mode...

> 
> U need to accept it as we work with Virtual Mem or
> write your own FileSystem which is mem based and short circuits os
calls. Sqlite 3.5x has good support for such ext.

As I said, the "nasty" trick works and we'll live with it (writing our
own FileSystem is not an option). We would just like to understand
better what's happening, to know why and when the "first run" is so much
slower. And should we read the whole file beforehand, only parts of it?
Does it depend on how much RAM is installed, ...? If you have any hints
they are welcome. I guess I should ask NTFS experts as well.

> regrads
> ragha
> 
>

**
>  This email and its attachments contain confidential information from
HUAWEI, which is intended only for the person or entity whose address is
listed above. Any use of the information contained herein in any way
(including, but not limited to, total or partial disclosure,
reproduction, or dissemination) by persons other than the intended
recipient(s) is prohibited. If you receive this e-mail in error, please
notify the sender by phone or email immediately and delete it!
>

*

Regards,

Julien

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Is possible put a password in database ?

[Jonas Sandman]

How much is it for the encrypted version?

Regards,
Jonas

On Nov 8, 2007 1:12 PM, Trevor Talbot <[EMAIL PROTECTED]> wrote:
> On 11/8/07, paulito santana <[EMAIL PROTECTED]> wrote:
>
> > is possible i put a password in the file that represents a SQLite database
> > ??  There is any instruction in SQLite command line that allows this ?
>
> No.  drh sells a version of SQLite with encryption support, and some
> others maintain their own versions.  The free standard version has no
> such support though.
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
>
>

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Is possible put a password in database ?

[Trevor Talbot]

On 11/8/07, paulito santana <[EMAIL PROTECTED]> wrote:

> is possible i put a password in the file that represents a SQLite database
> ??  There is any instruction in SQLite command line that allows this ?

No.  drh sells a version of SQLite with encryption support, and some
others maintain their own versions.  The free standard version has no
such support though.

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Disk caching impacts performance.

[RaghavendraK 70574]

Try this, use mmap 
(i assume u'r db is readonly)this is much faster and
better than ifstream read. Also ifstream read can keep the data in cache as 
long as no other serious
i/o occurs.

U need to accept it as we work with Virtual Mem or
write your own FileSystem which is mem based and short circuits os calls. 
Sqlite 3.5x has good support for such ext.

regrads
ragha

**
 This email and its attachments contain confidential information from HUAWEI, 
which is intended only for the person or entity whose address is listed above. 
Any use of the information contained herein in any way (including, but not 
limited to, total or partial disclosure, reproduction, or dissemination) by 
persons other than the intended recipient(s) is prohibited. If you receive this 
e-mail in error, please notify the sender by phone or email immediately and 
delete it!
 
*

- Original Message -
From: Julien Renggli <[EMAIL PROTECTED]>
Date: Thursday, November 8, 2007 4:15 pm
Subject: [sqlite] Disk caching impacts performance.

> Hello,
> 
> 
> 
> I'm currently working on a project which is very likely to use 
> SQLite as
> data storage. Since performance is important for us, I already 
> found on
> the SQLite website many ways to optimise the code (always working in
> transactions where possible, using a page size of 4096 since it's
> running on the Windows platform, using integers primary keys, ...). 
> ButI have one problem that I "solved" in an unorthodox way; it 
> works, but
> maybe you have a better solution than mine?
> 
> 
> 
> I've been doing some test with a ~100 MB database, in which I have 
> three
> tables: one for structured data (Objects, 2000 entries), one for the
> blobs we have to store (ObjectBlobs ID INTEGER PRIMARY KEY, Data BLOB)
> (8000 entries), and one which binds the structured data and the blobs
> (8000 entries). As you can imagine, each Object has 4 blobs linked to
> it; the blobs can be quite large (let's say up to 1 MB).
> 
> My (C++) application just has to read the table "Objects", and one of
> the Blobs for each Object.
> 
> 
> 
> Now the first time I run my application, it takes quite a long time
> 
> (30s) to load the blobs. But if I re-run the app, it only takes 1s to
> load them. It's clearly a disk caching issue: if I copy huge files to
> the disk between two runs, it takes again 30s to load the blobs (i.e.
> 
> the DB is no more in the disk cache). Profiling the application
> indicates sqlite::winRead() is the bottleneck.
> 
> 
> 
> I then had the following idea: SQLite is probably reading the file
> randomly, depending on where the data lies. If I can force the DB 
> to be
> cached, everything should be fine. So before connecting the 
> database, I
> first read it sequentially (using a C++ ifstream) until the end of 
> file.
> It perfectly solves the database problem, even though I still 
> notice a
> difference (3s to read the file on 1st run, 0.2s later). But 3s is OK
> where 30s was worrying me.
> 
> 
> 
> I hope I explain the situation clear enough, and ask you now: is it 
> theonly way to do it? I find the trick a bit nasty and don't like 
> it; maybe
> I missed something? Before you ask: I tried to VACUUM the DB, it only
> reduced the timings to 16s, which was still bad for our requirements.
> 
> Tests with a larger DB (it can get much bigger than my example) and on
> different machines tend to confirm my theory.
> 
> 
> 
> Thanks in advance (and a big thank for SQLite which is really nice and
> easy to use !),
> 
> 
> 
> Julien Renggli
> 
> 
> 
> P.S.: Some technical informations:
> 
> - sqlite v.3.3.16 (we will upgrade to the latest version later), C API
> 
> - Windows XP SP2
> 
> - Timings on Pentium 4 3.4GHz, 2GB RAM
> 
> 
> 
> 

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Disk caching impacts performance.

[Julien Renggli]

Hello,

 

I'm currently working on a project which is very likely to use SQLite as
data storage. Since performance is important for us, I already found on
the SQLite website many ways to optimise the code (always working in
transactions where possible, using a page size of 4096 since it's
running on the Windows platform, using integers primary keys, ...). But
I have one problem that I "solved" in an unorthodox way; it works, but
maybe you have a better solution than mine?

 

I've been doing some test with a ~100 MB database, in which I have three

tables: one for structured data (Objects, 2000 entries), one for the
blobs we have to store (ObjectBlobs ID INTEGER PRIMARY KEY, Data BLOB)
(8000 entries), and one which binds the structured data and the blobs
(8000 entries). As you can imagine, each Object has 4 blobs linked to
it; the blobs can be quite large (let's say up to 1 MB).

My (C++) application just has to read the table "Objects", and one of
the Blobs for each Object.

 

Now the first time I run my application, it takes quite a long time

(30s) to load the blobs. But if I re-run the app, it only takes 1s to
load them. It's clearly a disk caching issue: if I copy huge files to
the disk between two runs, it takes again 30s to load the blobs (i.e.

the DB is no more in the disk cache). Profiling the application
indicates sqlite::winRead() is the bottleneck.

 

I then had the following idea: SQLite is probably reading the file
randomly, depending on where the data lies. If I can force the DB to be
cached, everything should be fine. So before connecting the database, I
first read it sequentially (using a C++ ifstream) until the end of file.

It perfectly solves the database problem, even though I still notice a
difference (3s to read the file on 1st run, 0.2s later). But 3s is OK
where 30s was worrying me.

 

I hope I explain the situation clear enough, and ask you now: is it the
only way to do it? I find the trick a bit nasty and don't like it; maybe
I missed something? Before you ask: I tried to VACUUM the DB, it only
reduced the timings to 16s, which was still bad for our requirements.

Tests with a larger DB (it can get much bigger than my example) and on
different machines tend to confirm my theory.

 

Thanks in advance (and a big thank for SQLite which is really nice and
easy to use !),

 

Julien Renggli

 

P.S.: Some technical informations:

- sqlite v.3.3.16 (we will upgrade to the latest version later), C API

- Windows XP SP2

- Timings on Pentium 4 3.4GHz, 2GB RAM

 

Re: [sqlite] why select distinct does not use index?

[Ian Frosst]

In the call to create index, it looks like you're creating a duplicate
index on [id], which I believe references the default btree id (also
called rowid.)  If you change that line to:

CREATE INDEX [by_id] ON [profile_data] ([profile_id]);

your index should  be used (though whether indexes get used with
select distinct clauses, I'm not sure.)

Ian

On Nov 8, 2007 4:30 AM, Maxim V. Shiyanovsky <[EMAIL PROTECTED]> wrote:
> Suppose I have simple table:
>
> CREATE TABLE [profile_data] (
>
>  [profile_id] INTEGER,
>
>  [version] INTEGER);
>
> CREATE INDEX [by_id] ON [profile_data] ([id]);
>
>
>
> Why
>
> sqlite> explain query plan
>
>  ...> select distinct(profile_id) from profile_data;
>
> 0|0|TABLE profile_data
>
>
>
> Does not use index?
>
>

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Can sqlite3_reset() ever fail?

[Ralf Junker]

Hello,

I wonder if sqlite3_reset() can ever fail. In particular, does sqlite3_reset() 
always reset the statement even if it returns an error code?

>From the documentation and mailing list, my understanding is that 
>sqlite3_reset() errors always relate to the latest (or possibly ongoing) VM 
>execution triggered by sqlite3_step(). Whatever VM error returned, the 
>statement itself will nevertheless be reset after the call.

I mostly conclude this reasoning from the sqlite3_finalize() documentation and 
source code. However, since this is not explicitly spelled out for 
sqlite3_reset(), I would like to ask if sqlite3_reset()

* can also be called at any point during the execution of the virtual machine?

* will also result in an error or interrupt if the virtual machine has not 
completed execution, roll back or cancel transactions, and return SQLITE_ABORT?

For the new sqlit3_prepare_v2 API, can sqlite3_reset() and sqlite3_finalize() 
return any error codes except for SQLITE_ABORT that indicate incomplete DB 
operations not already indicated by sqlite3_step()?

Ralf


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] sqlite for 16 bits microcontroler x186

[Felix]

 

Hello,

 

I would use sqlite in an embedded DOS system ( intel 16 bits processor ) .

Do you think that is possible or sqlite work only on 32/64 bits systems?

Do you know an alternative to sqlite?

 

Thank you for your help.

 

Felix

 

 

[sqlite] why select distinct does not use index?

[Maxim V. Shiyanovsky]

Suppose I have simple table:

CREATE TABLE [profile_data] (

  [profile_id] INTEGER,

  [version] INTEGER);

CREATE INDEX [by_id] ON [profile_data] ([id]);

 

Why 

sqlite> explain query plan

   ...> select distinct(profile_id) from profile_data;

0|0|TABLE profile_data

 

Does not use index?

[sqlite] Is possible put a password in database ?

[paulito santana]

Hei all,
is possible i put a password in the file that represents a SQLite database
??  There is any instruction in SQLite command line that allows this ?



Regards,
Paulito