Re: [sqlite] SQLite - Dropping TEMP table doesn't free allocated memory

[Ryan Johnson]

On 28/12/2012 4:04 PM, Krzysztof wrote:

I don't understand :/ So what is solution in my case?
What is the problem you need to solve? If I understand correctly, your 
app repeatedly creates and deletes ~80MB of temp data. If so, it's 
actually a good thing that sqlite doesn't release the memory, because it 
would just have to re-allocate it soon anyway. I see nothing to worry 
about unless your platform is really memory-starved (to the point that 
freeing up 80MB actually matters), or you see the memory footprint 
growing with each create/delete cycle (indicating a memory leak).


Ryan

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

Re: [sqlite] Bug in Cygwin SQLite: temporary table creation fails

[Warren Young]

On 12/27/2012 21:17, Joe Mistachkin wrote:


I just looked at the patch briefly and I'm wondering if we could use the
existing GetTempPath[A/W] as another fallback directory?


The way I see it is, we are migrating from a hybrid Windows/Cygwin mode 
toward a purer POSIX style.  Eventually, I want Cygwin SQLite to behave 
just like SQLite on any Linux variant, except that it happens to still 
interoperate well with native Windows programs.



Also, is "cygwin_conv_path" superior to "cygwin_conv_to_win32_path" in
some way?


It reflects an API change between Cygwin 1.5 and Cygwin 1.7.  The old 
API still exists -- if it didn't, we'd be calling it Cygwin 2.x -- but 
you get a deprecation warning from the compiler if you call it.  The new 
API is 100% equivalent, it just offers a more flexible API.


I've posted this patch here twice before, but since upstream hasn't 
accepted it, I keep patching Cygwin SQLite.  I guess I should try 
posting it to sqlite-devel.  I believe when I first developed the patch, 
that list was closed to "outsiders" like me.  Now that I've got patching 
it in down as part of the normal build procedure, I haven't bothered 
reposting it until now.

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

Re: [sqlite] SQLite - Dropping TEMP table doesn't free allocated memory

[Kees Nuyt]

On Sat, 29 Dec 2012 00:04:12 +0100, Krzysztof  wrote:

> I don't understand :/ So what is solution in my case?

You could try :
PRAGMA temp_store=file;
immediately followed by:
PRAGMA temp_store=memory;

Please note:
http://sqlite.org/pragma.html#pragma_temp_store
"When the temp_store setting is changed, all existing temporary tables,
indices, triggers, and views are immediately deleted."

Warning: I didn't do any real research on this, and not all your
requirements so I'm not sure this is of any help.

-- 
Groet, Cordialement, Pozdrawiam, Regards,

Kees Nuyt

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

Re: [sqlite] SQLite - Dropping TEMP table doesn't free allocated memory

[Krzysztof]

I don't understand :/ So what is solution in my case?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite - Dropping TEMP table doesn't free allocated memory

[Simon Slavin]

On 28 Dec 2012, at 10:16pm, Krzysztof  wrote:

> I thought about reopen connection (this would solve my problem in simple
> way) but my dependencies are tricky. I'm using free pascal which have
> TSQLite3Connection. This class handle sqlite connection, so I can attach
> multiple tables in the same session, so I can share temp tables between all
> tables in my database.

Okay.  Well as you can guess, the memory is reused immediately you start 
inserting new rows.  It's always available to SQLite, it's just SQLite isn't 
releasing it to other parts of your application.

>  If I close connection, I lose data from all temp
> tables :/

True.  If you need to do operations on multiple tables at once, there's a good 
reason to keep all the tables accessible using the same connection.

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

Re: [sqlite] SQLite - Dropping TEMP table doesn't free allocated memory

[Krzysztof]

I thought about reopen connection (this would solve my problem in simple
way) but my dependencies are tricky. I'm using free pascal which have
TSQLite3Connection. This class handle sqlite connection, so I can attach
multiple tables in the same session, so I can share temp tables between all
tables in my database. If I close connection, I lose data from all temp
tables :/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Fwd: Write performance question for 3.7.15

[Michael Black]

Perhaps the rowid index cache gets too big?  I assume you don't have any
indexes of your own?

Does the knee change if you say, double your cache_size?

Default should be 2000;

pragma cache_size=4000;


-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Dan Frankowski
Sent: Friday, December 28, 2012 3:34 PM
To: sqlite-users@sqlite.org
Subject: [sqlite] Fwd: Write performance question for 3.7.15

I am running a benchmark of inserting 100 million (100M) items into a
table. I am seeing performance I don't understand. Graph:
http://imgur.com/hH1Jr. Can anyone explain:

1. Why does write speed (writes/second) slow down dramatically around 28M
items?
2. Are there parameters (perhaps related to table size) that would change
this write performance?

===

Create and insert statements:

create table if not exists t_foo (
  key binary(16) primary key,
  value binary(16));

insert or replace into t_foo (key, value) values (?, ?)

key and value are each 16-byte arrays.

I turn auto-commit off and commit every 1000 inserts.
I set synchronous mode to OFF and journaling mode to WAL (write-ahead log).

I am using sqlite 3.7.15 through the Xerial JDBC driver (see
https://bitbucket.org/xerial/sqlite-jdbc). I built it myself, due to a
glibc incompatibility (see
https://groups.google.com/d/msg/Xerial/F9roGuUjH6c/6RuxqmG6UK4J).

I am running on Gentoo. Output of uname -a:

Linux mymachine 3.2.1-c42.31 #1 SMP Mon Apr 30 10:55:12 CDT 2012 x86_64
Quad-Core AMD Opteron(tm) Processor 1381 AuthenticAMD GNU/Linux

It has 8G of memory.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

Re: [sqlite] Write performance question for 3.7.15

[Dan Frankowski]

On Fri, Dec 28, 2012 at 3:34 PM, Dan Frankowski  wrote:

> I am running a benchmark of inserting 100 million (100M) items into a
> table. I am seeing performance I don't understand. Graph:
> http://imgur.com/hH1Jr. Can anyone explain:
>
> 1. Why does write speed (writes/second) slow down dramatically around 28M
> items?
> 2. Are there parameters (perhaps related to table size) that would change
> this write performance?
>

3. Would horizontal partitioning (i.e. creating multiple tables, each for a
different key range) help?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Fwd: Write performance question for 3.7.15

[Dan Frankowski]

I am running a benchmark of inserting 100 million (100M) items into a
table. I am seeing performance I don't understand. Graph:
http://imgur.com/hH1Jr. Can anyone explain:

1. Why does write speed (writes/second) slow down dramatically around 28M
items?
2. Are there parameters (perhaps related to table size) that would change
this write performance?

===

Create and insert statements:

create table if not exists t_foo (
  key binary(16) primary key,
  value binary(16));

insert or replace into t_foo (key, value) values (?, ?)

key and value are each 16-byte arrays.

I turn auto-commit off and commit every 1000 inserts.
I set synchronous mode to OFF and journaling mode to WAL (write-ahead log).

I am using sqlite 3.7.15 through the Xerial JDBC driver (see
https://bitbucket.org/xerial/sqlite-jdbc). I built it myself, due to a
glibc incompatibility (see
https://groups.google.com/d/msg/Xerial/F9roGuUjH6c/6RuxqmG6UK4J).

I am running on Gentoo. Output of uname -a:

Linux mymachine 3.2.1-c42.31 #1 SMP Mon Apr 30 10:55:12 CDT 2012 x86_64
Quad-Core AMD Opteron(tm) Processor 1381 AuthenticAMD GNU/Linux

It has 8G of memory.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite - Dropping TEMP table doesn't free allocated memory

[Richard Hipp]

On Fri, Dec 28, 2012 at 10:04 AM, Krzysztof  wrote:

> Hi,
>
> I'm using PRAGMA temp_store=2 so all temp tables are created in memory. I
> have temp table with 1 000 000 000 records. Memory used by my test
> application grow up to ~80 MB. If I delete all records from this table or
> drop table, then my application still use 80 MB of memory. I have tried
> also PRAGMA shrink_memory. Why sqlite don't free memory?'
>

The memory is available for reuse and will be reused for subsequent TEMP
tables.  The memory is not freed until the database connection closes,
however.  After 12 years and over 1,000,000 applications, you are the first
person to request that memory from deleted TEMP tables actually be freed.


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



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

Re: [sqlite] SQLite - Dropping TEMP table doesn't free allocated memory

[Pavel Ivanov]

You may be hitting the memory fragmentation issue. Try to run your
application with different memory managers (Hoard is my favorite -
http://www.hoard.org/) and see if the memory consumption is the same.

Also if you close all connections to your database (and other SQLite
databases too) does amount of used memory get lower?


Pavel

On Fri, Dec 28, 2012 at 7:04 AM, Krzysztof  wrote:
> Hi,
>
> I'm using PRAGMA temp_store=2 so all temp tables are created in memory. I
> have temp table with 1 000 000 000 records. Memory used by my test
> application grow up to ~80 MB. If I delete all records from this table or
> drop table, then my application still use 80 MB of memory. I have tried
> also PRAGMA shrink_memory. Why sqlite don't free memory?
>
> Regards
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Select statements using date function such as date('-1 day')

[Keith Christian]

On Fri, Dec 28, 2012 at 12:26 PM, Igor Tandetnik  wrote:
> You forgot to tell date() function which date it's supposed to subtract one
> day from. Make it date('now', '-1 day')


Thanks, Igor, that fixes the problem, both of these work!

select * from general where foodate=date('now', '-1 day') limit 10;

select * from general where foodate=date('now', '-2 day') limit 10;


I appreciate the quick response.


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

Re: [sqlite] useful Perl modules for working with SQLite databases?

[Larry Brasfield]

James Hartley wrote:

SQLite has recently been added to the base installation to the Unix-variant
I frequently use.  This is a good thing.

I'm looking at the various CPAN modules which are available in the
ancillary ports sponsored by that Unix-like project, & I see that
DBD::SQLite package could be updated.  This raises the question as to
whether other CPAN modules should be made available too.

So, I ask the fine readership here.  Are there particular Perl CPAN modules
which are especially useful to the applications you develop?


That's a very broad, and mostly off-topic question here.

That said, and without pretending it answers the question, I can say 
that I have found these modules handy for DB work in Perl, much of it 
using SQLite:

  use DateTime;
  use DBI;
  use DateTime::Format::DBI;
  use DateTime::Format::ISO8601;

Cheers,
--
Larry Brasfield

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

[sqlite] useful Perl modules for working with SQLite databases?

[James Hartley]

SQLite has recently been added to the base installation to the Unix-variant
I frequently use.  This is a good thing.

I'm looking at the various CPAN modules which are available in the
ancillary ports sponsored by that Unix-like project, & I see that
DBD::SQLite package could be updated.  This raises the question as to
whether other CPAN modules should be made available too.

So, I ask the fine readership here.  Are there particular Perl CPAN modules
which are especially useful to the applications you develop?

Thanks for any insight shared.

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

Re: [sqlite] Select statements using date function such as date('-1 day')

[Igor Tandetnik]

On 12/28/2012 2:14 PM, Keith Christian wrote:

Is it possible to use the 'date' function to select a date using the
date function as below?


 select * from general where foobar_date=date('-1 day');


You forgot to tell date() function which date it's supposed to subtract 
one day from. Make it date('now', '-1 day')

--
Igor Tandetnik

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

[sqlite] Select statements using date function such as date('-1 day')

[Keith Christian]

Running SQLite version 3.7.13 2012-06-11 02:05:22.

A table has a date field with entries in this range: 2012-10-25
through 2012-12-27.

2735080|2012-12-27
2735081|2012-12-27
2735083|2012-12-27
2735084|2012-12-27
2735085|2012-12-27
2735086|2012-12-27
2735087|2012-12-27
2735088|2012-12-27
2735089|2012-12-27

Is it possible to use the 'date' function to select a date using the
date function as below?


select * from general where foobar_date=date('-1 day');
E.g. return foobar_date of 2012-12-27 if today's date is 2012-12-28.
select * from general where foobar_date=date('-2 days');
 E.g. return foobar_date of 2012-12-26 if today's date is 2012-12-28.

Neither of these return any output.  Date ranges in the table start at
2012-10-25 and go until 2012-12-27, so I was hoping one of the two
queries above would return something.

Or is it necessary to explicitly state the value desired, e.g.

select * from general where foobar_date='2012-12-27';


Thanks for any suggestions.

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

Re: [sqlite] SQLite - Dropping TEMP table doesn't free allocated memory

[Simon Slavin]

On 28 Dec 2012, at 6:01pm, Krzysztof  wrote:

> Can I call vacuum on custom tables (for example only on temp tables) ?
> Because vacuum takes some time (my "normal" tables are big and often
> modified, trim of those tables are not important for me) and I would like
> to free up memory taken only by temp tables

VACUUM is something you do to the database as a whole, not individual tables.

It may be that instead of using temp=2 you should specifically be using the 
'memory' database space:



That way, to retrieve all the memory you're missing you can just close the 
connection you have open, open a new one, and CREATE new TABLEs.

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

Re: [sqlite] SQLite - Dropping TEMP table doesn't free allocated memory

[Krzysztof]

Can I call vacuum on custom tables (for example only on temp tables) ?
Because vacuum takes some time (my "normal" tables are big and often
modified, trim of those tables are not important for me) and I would like
to free up memory taken only by temp tables
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite - Dropping TEMP table doesn't free allocated memory

[Simon Slavin]

On 28 Dec 2012, at 3:04pm, Krzysztof  wrote:

> If I delete all records from this table or
> drop table, then my application still use 80 MB of memory. I have tried
> also PRAGMA shrink_memory. Why sqlite don't free memory?

SQLite does not free up space from a database until it does a VACUUM.  Either 
use the command for it:



or use the auto-vacuum pragma:



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

[sqlite] SQLite - Dropping TEMP table doesn't free allocated memory

[Krzysztof]

Hi,

I'm using PRAGMA temp_store=2 so all temp tables are created in memory. I
have temp table with 1 000 000 000 records. Memory used by my test
application grow up to ~80 MB. If I delete all records from this table or
drop table, then my application still use 80 MB of memory. I have tried
also PRAGMA shrink_memory. Why sqlite don't free memory?

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

Re: [sqlite] Update with two tables

[Hick Gunter]

You need two UPDATEs to change rows in two tables.

BEGIN;
UPDATE
TabAgeFattProdutt
SET
Stato ='I'
WHERE
NumFattAG =
-- the INNER JOIN evaluates to a aingle value
-- (SELECT NumFatt FROM TabTestaFattAG WHERE NumFatt =
  '23/12'
-- )
;

UPDATE
TabTestaFattAG
SET
StatoFatt = 'Chiusa'
WHERE
NumFatt = '23/12'
;
COMMIT;

-Ursprüngliche Nachricht-
Von: Leonardodavinci [mailto:alessan...@system-ini.it]
Gesendet: Donnerstag, 27. Dezember 2012 16:57
An: sqlite-users@sqlite.org
Betreff: Re: [sqlite] Update with two tables

hi i.m new user with sqlite and now i have this problem with update function:
with mysql run this script:

UPDATE
TabAgeFattProdutt
INNER JOIN
TabTestaFattAG
ON
TabAgeFattProdutt.NumFattAG = TabTestaFattAG.NumFatt
SET
TabAgeFattProdutt.Stato ='I',
TabTestaFattAG.StatoFatt = 'Chiusa'
WHERE
TabTestaFattAG.NumFatt = '23/12'


but under sqlite not work!!!
i think about the problem is the syntax but where???
thanks Alex



--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/Update-with-two-tables-tp9506p66287.html
Sent from the SQLite mailing list archive at Nabble.com.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


--
 Gunter Hick
Software Engineer
Scientific Games International GmbH
Klitschgasse 2 – 4, A - 1130 Vienna, Austria
FN 157284 a, HG Wien
Tel: +43 1 80100 0
E-Mail: h...@scigames.at

This e-mail is confidential and may well also be legally privileged. If you 
have received it in error, you are on notice as to its status and accordingly 
please notify us immediately by reply e-mail and then delete this message from 
your system. Please do not copy it or use it for any purposes, or disclose its 
contents to any person as to do so could be a breach of confidence. Thank you 
for your cooperation.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Update with two tables

[Leonardodavinci]

hi i.m new user with sqlite and now i have this problem with update function:
with mysql run this script:

UPDATE 
TabAgeFattProdutt 
INNER JOIN 
TabTestaFattAG 
ON 
TabAgeFattProdutt.NumFattAG = TabTestaFattAG.NumFatt 
SET 
TabAgeFattProdutt.Stato ='I', 
TabTestaFattAG.StatoFatt = 'Chiusa' 
WHERE 
TabTestaFattAG.NumFatt = '23/12' 


but under sqlite not work!!!
i think about the problem is the syntax but where???
thanks Alex



--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/Update-with-two-tables-tp9506p66287.html
Sent from the SQLite mailing list archive at Nabble.com.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users