Re: [sqlite] Loading a database from memory

[Glenn McCord]

On Tue, Jul 19, 2011 at 4:39 PM, Glenn McCord  wrote:
> On Tue, Jul 19, 2011 at 4:31 PM, Simon Slavin  wrote:
>>
>> On 19 Jul 2011, at 5:17am, Glenn McCord wrote:
>>
>>> Hi. I've been trying to find out the best way of loading an sqlite
>>> database from memory. Basically I'd like to be able to save a small
>>> sqlite database as meta data to an audio file, i.e. an mpeg4
>>> container. I'd then be able read, modify then write it back to the
>>> file.
>>
>> You can use the SQLite backup API to transfer an entire database from or to 
>> memory:
>>
>> http://www.sqlite.org/backup.html
>>

I've just had a quick look at the backup API, and it seems to make use
of filenames and sqlite databases, which is not exactly what I'm
after.
What I need is the ability to read in an array of bytes, (read from
meta data of an mpeg4, say, via fstream or some other equivalent),
that represents the raw data of an sqlite database.

I'm trying to avoid having to parse in that data, save it to a
separate file first, then load it using a mysql open() call.

I'll admit that I only had a quick read, so may have missed something.

Thanks


>> However, I'm not sure of the wisdom of saving data in this way.  The SQLite 
>> file format is not simple or easy to inspect, and uses up a lot of space 
>> because it has to hold indexing information.  Would it not make more sense 
>> to encode your data as XML or JSON and save this (or a .zipped version of 
>> this) in your MPEG4 instead ?
>>
>
> I was thinking the same thing, but some of the guys around the office
> are pretty keen on the idea of using sqlite. I'm just doing the
> required research and weighing up the options. Using JSON seems like a
> good idea.
>
> Thanks for the link.
>
>> Simon.
>> ___
>> 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] Loading a database from memory

[Glenn McCord]

On Tue, Jul 19, 2011 at 4:31 PM, Simon Slavin  wrote:
>
> On 19 Jul 2011, at 5:17am, Glenn McCord wrote:
>
>> Hi. I've been trying to find out the best way of loading an sqlite
>> database from memory. Basically I'd like to be able to save a small
>> sqlite database as meta data to an audio file, i.e. an mpeg4
>> container. I'd then be able read, modify then write it back to the
>> file.
>
> You can use the SQLite backup API to transfer an entire database from or to 
> memory:
>
> http://www.sqlite.org/backup.html
>
> However, I'm not sure of the wisdom of saving data in this way.  The SQLite 
> file format is not simple or easy to inspect, and uses up a lot of space 
> because it has to hold indexing information.  Would it not make more sense to 
> encode your data as XML or JSON and save this (or a .zipped version of this) 
> in your MPEG4 instead ?
>

I was thinking the same thing, but some of the guys around the office
are pretty keen on the idea of using sqlite. I'm just doing the
required research and weighing up the options. Using JSON seems like a
good idea.

Thanks for the link.

> Simon.
> ___
> 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] Loading a database from memory

[Simon Slavin]

On 19 Jul 2011, at 5:17am, Glenn McCord wrote:

> Hi. I've been trying to find out the best way of loading an sqlite
> database from memory. Basically I'd like to be able to save a small
> sqlite database as meta data to an audio file, i.e. an mpeg4
> container. I'd then be able read, modify then write it back to the
> file.

You can use the SQLite backup API to transfer an entire database from or to 
memory:

http://www.sqlite.org/backup.html

However, I'm not sure of the wisdom of saving data in this way.  The SQLite 
file format is not simple or easy to inspect, and uses up a lot of space 
because it has to hold indexing information.  Would it not make more sense to 
encode your data as XML or JSON and save this (or a .zipped version of this) in 
your MPEG4 instead ?

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

[sqlite] Loading a database from memory

[Glenn McCord]

Hi. I've been trying to find out the best way of loading an sqlite
database from memory. Basically I'd like to be able to save a small
sqlite database as meta data to an audio file, i.e. an mpeg4
container. I'd then be able read, modify then write it back to the
file.

I'm starting to get the impression that the only way for me to do this
is to make my own vfs layer...
http://stackoverflow.com/questions/3839158/using-sqlite-with-stdiostream

Would sqlite's test_onefile.c example code be the most appropriate
starting point? http://www.sqlite.org/src/doc/trunk/src/test_onefile.c

If someone could offer ways of achieving this, or could at least
clarify that a vfs is the only way to go, then I'd appreciate the
feedback.

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

Re: [sqlite] Handle leak using IIS on windows 7?

[Joe Mistachkin]

After reading the code, I noticed the following:

1. From the static Dump method, an instance of the DbLogger class is
created via the static Get method and stored in the _instance static
variable.

2. The connection itself is opened in the constructor for the DbLogger
class via the InitDb method.

3. Prior to returning a result, the Dump method closes the connection
and sets the _connection instance variable to null.

4. The second time the Dump method is executed, the existing instance
of the DbLogger class will be used (i.e. the one stored in the static
_instance variable).

5. This existing instance of the DbLogger class no longer has a valid
connection because it was previously closed (and set to null).

6. Newly created commands will not have a valid connection.

7. Attempting to execute a command without a valid connection will
result in the exception you are seeing.

--
Joe Mistachkin

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

Re: [sqlite] Handle leak using IIS on windows 7?

[Simon Slavin]

On 19 Jul 2011, at 4:07am, Doug wrote:

> I'm pretty sure the issue is something to do with file handles. For the same
> reason after loading the page (from an IIS server) and then closing the
> page, waiting 1 minute or two and then attempting to remove the db.sqlite
> file, I get an "error, file is in use".

Remove as much as you can and see if the error disappears.  For instance, 
delete the stuff about creating and deleting the file.  Delete the stuff about 
creating the table.  In fact everything that depends on 'init'.  What about 
that INSERT ?  If you remove it does the error go away ?

Give us a ten-line toy example that still produces the error.  Then we'll be 
more likely to guess what's going on.

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

Re: [sqlite] Handle leak using IIS on windows 7?

[Doug]

Hm... I'm not doing anything fancy.

I've attached the tiny sqlite logging class below. It's called in an MVC app
simply by invoking:

@Html.Raw(Doug.Utils.Web.DbLogger.Dump())

Yes, it uses transactions; is that a bad thing?

I'm pretty sure the issue is something to do with file handles. For the same
reason after loading the page (from an IIS server) and then closing the
page, waiting 1 minute or two and then attempting to remove the db.sqlite
file, I get an "error, file is in use".

(Obviously, if you use File.Open() without a File.Close() this does not
happen; when the page view ends the file handle is automatically released).

If you want to see it in action, create a new MVC project and add:
DbLogger.Get();

To the home index page. Run it in debug mode and you'll see the issue.

Cheers,
Doug.

code (in case the attachment fails):

using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Web;
using System.Data.SQLite;
using System.IO;

namespace Doug.Utils.Web
{
public class DbLoggerRecord
{
public String Context { get; set; }
public String Message { get; set; }
public DateTime Created { get; set; }
}

public class DbLogger
{
private static DbLogger _instance = null;

/// 
/// Where to store the logging database.
/// 
private const string relativeDbPath = "~/App_Data/DbLogger.sqlite";

private SQLiteConnection _connection = null;

private SQLiteTransaction _transaction = null;

public DbLogger()
{
var dbPath = Path.GetTempFileName();
if (HttpContext.Current != null)
dbPath = HttpContext.Current.Server.MapPath(relativeDbPath);
_connection = InitDb(dbPath);
}

private SQLiteConnection InitDb(String dbPath)
{
bool init = false;
if (!File.Exists(dbPath))
{
SQLiteConnection.CreateFile(dbPath);
init = true;
}
var rtn = new SQLiteConnection("Data Source="+dbPath);
rtn.Open();

// Pragma or this doesn't work in app_data folder.
SQLiteCommand cmd = new SQLiteCommand();
cmd.Connection = rtn;
string pragma = "PRAGMA journal_mode = OFF";
cmd.CommandText = pragma;
cmd.ExecuteNonQuery();

// Check if we realy neeed to init?
if (!init) {
try
{
GetRecords(1);
}
catch(Exception)
{
try
{
File.Delete(dbPath);
init = true;
}
catch(Exception)
{
}
}
}

if (init)
CreateTable(rtn);

return rtn;
}

private void CreateTable(SQLiteConnection c)
{
var createTable = new StringBuilder();
createTable.Append("CREATE TABLE Log (");
createTable.Append("  Id PRIMARY KEY,");
createTable.Append("  Context TEXT,");
createTable.Append("  Message TEXT,");
createTable.Append("  Created TEXT)");
var cmd = createTable.ToString();
var sqlCmd = new SQLiteCommand(c);
sqlCmd.CommandText = cmd;
try
{
sqlCmd.ExecuteNonQuery();
}
catch(Exception)
{
}
}

public void Trace(String context, String message)
{
if (_transaction == null)
_transaction = _connection.BeginTransaction();

var insertMsg = new StringBuilder();
insertMsg.Append("INSERT INTO Log (Context, Message, Created)
VALUES (@Context, @Message, @Created)");
var cmd = insertMsg.ToString();
var sqlCmd = new SQLiteCommand(_connection);
sqlCmd.CommandText = cmd;

// Params
sqlCmd.Parameters.AddWithValue("@Context", context);
sqlCmd.Parameters.AddWithValue("@Message", message);
sqlCmd.Parameters.AddWithValue("@Created",
DateTime.Now.ToString());

sqlCmd.ExecuteNonQuery();
}

public void Close() {
if (_connection != null)
{
_connection.Close();
_connection = null;
}
}

public IEnumerable GetRecords(int limit)
{
Commit();
var rtn = new List();

string cmd = "SELECT * FROM Log ORDER BY Id ASC LIMIT " + limit;
var sqlCmd = new SQLiteCommand(_connection);
sqlCmd.CommandText = cmd;

SQLiteDataReader reader = sqlCmd.ExecuteReader();

while(reader.Read())

Re: [sqlite] Performance Improvement

[J Decker]

On Mon, Jul 18, 2011 at 9:30 AM, Igor Tandetnik  wrote:
> On 7/18/2011 11:10 AM,
> abhisek...@gmail.com wrote:
>> Sorry the mail was sent prematurely. Continuing ...
>> 2. In a loop, read each address,
>>                     for that address find the next time this address was used
>> ( to implement the replacement policy)
>
> Sort the list by address then sequence number, probably using some form
> of the merge sort algorithm (which allows one to sort a data set larger
> than the available RAM). Then do a single pass over the sorted list,
> looking for sequences of repeated addresses.

ya - make the databae do your address comparison don't pull back each
record - just the records you need.  maybe add a timestamp or other
number you can increment rather than delete the old records, just use
the old record updated as appropriate

> --
> Igor Tandetnik
>
> ___
> 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] Script-friendly execution of sqlite3.exe for initializing a DB

[Jan Hudec]

On Thu, Jul 14, 2011 at 16:20:56 -0500, Marvin Bellamy wrote:
> I want to run sqlite3 from a script (actually from a ToolTask in the MSBuild
> environment) and get it to create a database without dropping into
> interactive mode.  It looks like ToolTasks don't like the redirection
> operator (they're interpreted as string literals), so this won't work:
> 
>Sqlite3  new.db < new.sql

That's the correct way to give input to sqlite3. Your problem is how to teach
ToolTasks to understand the redirection.

Well, quick look at the documentation suggests setting 'UseCommandProcessor'
to 'true' should do the trick (it will use cmd.exe than and that does
understand redirection).

Oh, you should probably add -batch option like this:

sqlite3 -batch new.db < new.sql

to suppress the prompts and command echo.

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

Re: [sqlite] Performance Improvement

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 07/18/2011 07:58 AM, abhisek...@gmail.com wrote:
> So I am coding up a cache simulator. 

I suggest having a look at cachegrind which does something similar.  You
should be able to learn from their data structures.  It is also open source
so maybe you could adapt it to meet your needs:

  http://valgrind.org/docs/manual/cg-manual.html

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

iEYEARECAAYFAk4kZEoACgkQmOOfHg372QTSkwCgsP2hB2v4wSVXCNDai7Y/fYri
m4oAnjVPHf4qFH86yauD0pktobrvGFmR
=iGFB
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Performance Improvement

[Igor Tandetnik]

On 7/18/2011 11:10 AM, 
abhisek...@gmail.com wrote:
> Sorry the mail was sent prematurely. Continuing ...
> 2. In a loop, read each address,
> for that address find the next time this address was used
> ( to implement the replacement policy)

Sort the list by address then sequence number, probably using some form 
of the merge sort algorithm (which allows one to sort a data set larger 
than the available RAM). Then do a single pass over the sorted list, 
looking for sequences of repeated addresses.
-- 
Igor Tandetnik

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

Re: [sqlite] Performance Improvement

[Black, Michael (IS)]

Unless your caching at byte-level you don't need all the addresses.



For 4K page size mask off the lower 11 bits.



You should actually be able to reduce your memory usage by a LOT if you track 
by pages and not bytes.

That will also speed you up along with it.





Michael D. Black

Senior Scientist

NG Information Systems

Advanced Analytics Directorate




From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of abhisek...@gmail.com [abhisek...@gmail.com]
Sent: Monday, July 18, 2011 10:50 AM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] Performance Improvement

Thanks Jay.

I guess you are right. I have very little experience in dealing with such
huge data-sets. So what I meant was,
initially I was doing this with c++ data structures like vectors and maps,
and I could not even store all the
entries. The program would crash out with bad_alloc exception. With sqlite,
I can at least store all the data,
for the cases I have tested till now (600 billion entries for now), but it
is awfully slow.

Also continuing with the mail, this is what I am doing:

2. In a loop, read each address,
   for that address find the next time this address was used
( to implement the replacement policy)

To do this:

1. I am creating a table: CREATE TABLE AddressSet (SeqNo UNSIGNED BIG INT
PRIMARY KEY, Address UNSIGNED BIG INT)
2. Inserting all the addresses INSERT INTO AddressSet VALUES(Seqno, Address)
...

3. In the loop I am doing this (for each of the seqeunce numbers):
  SELECT SeqNo, Address FROM AddressSet WHERE SeqNo=n ,
n = 1,2, ..   (Let the address selected be m)
  DELETE FROM AddressSet WHERE SeqNo=n
// delete it so that for the next select statement we get the next time this
address was seen
  SELECT SeqNo, Address FROM AddressSet WHERE Address=m
ORDER BY SeqNo ASC LIMIT 1 // here SeqNo gives us the next time this address
was seen

I was wondering if this flow can be modified so as to see some improvements
in performance.

Just to give some context,all these issues come in because we need the
entire history of the trace to do the processing, since when an address is
seen, we need to find out
when in future this was used again, In the worst case the very first address
was never used again, or maybe was used at the end of the address trace.
This is because I am trying to simulate an "optimal" replacement policy in
the cache, unlike the real ones such as LRU, in which I could have
maintained a limited history,
and be done with it.

Thanks again,
Abhisek

On Mon, Jul 18, 2011 at 11:23 AM, Jay A. Kreibich  wrote:

> On Mon, Jul 18, 2011 at 05:01:55PM +0200, Stephan Beal scratched on the
> wall:
> > On Mon, Jul 18, 2011 at 4:58 PM, abhisek...@gmail.com
> > wrote:
> >
> > > These are addresses accessed by a program. There will be 100 billion
> > > entries
> > >
> >
> > You won't be able to fit that many in your database - sqlite3 cannot
> scale
> > to the file size you will need for that. Assuming 10-byte addresses (as
> you
> > demonstrated), 10 bytes x 100B records = 1 terrabyte JUST for the
> addresses
> > (not including any sqlite3-related overhead per record, which is probably
> > much larger than the 10 bytes you're saving).
>
>   In theory, the maximum size of an SQLite database is 128 TB.
>
>  2^31 pages (2 giga-pages) @ 2^16 bytes (64K) = 128 TB, or ~140e12.
>
>  (I know http://sqlite.org/limits.html says 14TB, but I think they
>   dropped a digit)
>
>  If your file system can handle this or not is a different story.
>
>  Using SQLite for this type of data seems very questionable, however.
>  As Stephen points out, the database with just the addresses is likely
>  to be in the 3 to 4 TB range. You said "There will be 100 billion
>   entries or so like this, which makes it necessary to use the
>   database," but I think just the opposite is true.  If you have a
>  *very* large number of data points with with a very specific access
>  pattern, using a general purpose tool seems like exactly the wrong
>  choice.  You need some custom system that is highly optimized for
>  both storage space and your specific access patterns.
>
>   -j
>
> --
> Jay A. Kreibich < J A Y  @  K R E I B I.C H >
>
> "Intelligence is like underwear: it is important that you have it,
>  but showing it to the wrong people has the tendency to make them
>  feel uncomfortable." -- Angela Johnson
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



--
Abhisek
Live Long and Prosper
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list

Re: [sqlite] Performance Improvement

[abhisek...@gmail.com]

Thanks Jay.

I guess you are right. I have very little experience in dealing with such
huge data-sets. So what I meant was,
initially I was doing this with c++ data structures like vectors and maps,
and I could not even store all the
entries. The program would crash out with bad_alloc exception. With sqlite,
I can at least store all the data,
for the cases I have tested till now (600 billion entries for now), but it
is awfully slow.

Also continuing with the mail, this is what I am doing:

2. In a loop, read each address,
   for that address find the next time this address was used
( to implement the replacement policy)

To do this:

1. I am creating a table: CREATE TABLE AddressSet (SeqNo UNSIGNED BIG INT
PRIMARY KEY, Address UNSIGNED BIG INT)
2. Inserting all the addresses INSERT INTO AddressSet VALUES(Seqno, Address)
...

3. In the loop I am doing this (for each of the seqeunce numbers):
  SELECT SeqNo, Address FROM AddressSet WHERE SeqNo=n ,
n = 1,2, ..   (Let the address selected be m)
  DELETE FROM AddressSet WHERE SeqNo=n
// delete it so that for the next select statement we get the next time this
address was seen
  SELECT SeqNo, Address FROM AddressSet WHERE Address=m
ORDER BY SeqNo ASC LIMIT 1 // here SeqNo gives us the next time this address
was seen

I was wondering if this flow can be modified so as to see some improvements
in performance.

Just to give some context,all these issues come in because we need the
entire history of the trace to do the processing, since when an address is
seen, we need to find out
when in future this was used again, In the worst case the very first address
was never used again, or maybe was used at the end of the address trace.
This is because I am trying to simulate an "optimal" replacement policy in
the cache, unlike the real ones such as LRU, in which I could have
maintained a limited history,
and be done with it.

Thanks again,
Abhisek

On Mon, Jul 18, 2011 at 11:23 AM, Jay A. Kreibich  wrote:

> On Mon, Jul 18, 2011 at 05:01:55PM +0200, Stephan Beal scratched on the
> wall:
> > On Mon, Jul 18, 2011 at 4:58 PM, abhisek...@gmail.com
> > wrote:
> >
> > > These are addresses accessed by a program. There will be 100 billion
> > > entries
> > >
> >
> > You won't be able to fit that many in your database - sqlite3 cannot
> scale
> > to the file size you will need for that. Assuming 10-byte addresses (as
> you
> > demonstrated), 10 bytes x 100B records = 1 terrabyte JUST for the
> addresses
> > (not including any sqlite3-related overhead per record, which is probably
> > much larger than the 10 bytes you're saving).
>
>   In theory, the maximum size of an SQLite database is 128 TB.
>
>  2^31 pages (2 giga-pages) @ 2^16 bytes (64K) = 128 TB, or ~140e12.
>
>  (I know http://sqlite.org/limits.html says 14TB, but I think they
>   dropped a digit)
>
>  If your file system can handle this or not is a different story.
>
>  Using SQLite for this type of data seems very questionable, however.
>  As Stephen points out, the database with just the addresses is likely
>  to be in the 3 to 4 TB range. You said "There will be 100 billion
>   entries or so like this, which makes it necessary to use the
>   database," but I think just the opposite is true.  If you have a
>  *very* large number of data points with with a very specific access
>  pattern, using a general purpose tool seems like exactly the wrong
>  choice.  You need some custom system that is highly optimized for
>  both storage space and your specific access patterns.
>
>   -j
>
> --
> Jay A. Kreibich < J A Y  @  K R E I B I.C H >
>
> "Intelligence is like underwear: it is important that you have it,
>  but showing it to the wrong people has the tendency to make them
>  feel uncomfortable." -- Angela Johnson
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Abhisek
Live Long and Prosper
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Performance Improvement

[Stephan Beal]

On Mon, Jul 18, 2011 at 5:14 PM, abhisek...@gmail.com
wrote:

> file I am trying to implement, but that is fine. What would be the limit of
> the file size that sqlite can create, assuming disc space is not
> an issue.
>

http://www.sqlite.org/limits.html

Apparently i was wrong, in any case: that page claims that the limit of the
file size is theoretically 2^64 but that 14TB is the practical limit. i
thought that the limit was somewhere under 4GB.

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Performance Improvement

[Jay A. Kreibich]

On Mon, Jul 18, 2011 at 05:01:55PM +0200, Stephan Beal scratched on the wall:
> On Mon, Jul 18, 2011 at 4:58 PM, abhisek...@gmail.com
> wrote:
> 
> > These are addresses accessed by a program. There will be 100 billion
> > entries
> >
> 
> You won't be able to fit that many in your database - sqlite3 cannot scale
> to the file size you will need for that. Assuming 10-byte addresses (as you
> demonstrated), 10 bytes x 100B records = 1 terrabyte JUST for the addresses
> (not including any sqlite3-related overhead per record, which is probably
> much larger than the 10 bytes you're saving).

  In theory, the maximum size of an SQLite database is 128 TB.

  2^31 pages (2 giga-pages) @ 2^16 bytes (64K) = 128 TB, or ~140e12.

  (I know http://sqlite.org/limits.html says 14TB, but I think they
   dropped a digit)

  If your file system can handle this or not is a different story.

  Using SQLite for this type of data seems very questionable, however.
  As Stephen points out, the database with just the addresses is likely
  to be in the 3 to 4 TB range. You said "There will be 100 billion
  entries or so like this, which makes it necessary to use the
  database," but I think just the opposite is true.  If you have a
  *very* large number of data points with with a very specific access
  pattern, using a general purpose tool seems like exactly the wrong
  choice.  You need some custom system that is highly optimized for
  both storage space and your specific access patterns.

   -j

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

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Performance Improvement

[abhisek...@gmail.com]

Thanks a lot Stephen.

I guess I have to do this on parts of the input file at a time then, which I
will try, though it will be an approximation of the
file I am trying to implement, but that is fine. What would be the limit of
the file size that sqlite can create, assuming disc space is not
an issue.

-Abhisek

On Mon, Jul 18, 2011 at 11:01 AM, Stephan Beal wrote:

> On Mon, Jul 18, 2011 at 4:58 PM, abhisek...@gmail.com
> wrote:
>
> > These are addresses accessed by a program. There will be 100 billion
> > entries
> >
>
> You won't be able to fit that many in your database - sqlite3 cannot scale
> to the file size you will need for that. Assuming 10-byte addresses (as you
> demonstrated), 10 bytes x 100B records = 1 terrabyte JUST for the addresses
> (not including any sqlite3-related overhead per record, which is probably
> much larger than the 10 bytes you're saving).
>
> --
> - stephan beal
> http://wanderinghorse.net/home/stephan/
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Performance Improvement

[abhisek...@gmail.com]

Sorry the mail was sent prematurely. Continuing ...
2. In a loop, read each address,
   for that address find the next time this address was used
( to implement the replacement policy)

To do this:

1. I am creating a table: CREATE TABLE AddressSet (SeqNo UNSIGNED BIG INT
PRIMARY KEY, Address UNSIGNED BIG INT)
2. Inserting all the addresses INSERT INTO AddressSet VALUES(Seqno, Address)
...

3. In the loop I am doing this (for each of the seqeunce numbers):
  SELECT SeqNo, Address FROM AddressSet WHERE SeqNo=n ,
n = 1,2, ..   (Let the address selected be m)
  DELETE FROM AddressSet WHERE SeqNo=n
// delete it so that for the next select statement we get the next time this
address was seen
  SELECT SeqNo, Address FROM AddressSet WHERE Address=m
ORDER BY SeqNo ASC LIMIT 1 // here SeqNo gives us the next time this address
was seen

That is all.
Please let me know if these can be optimized somehow.

Thanks a lot for your help!
Abhisek









On Mon, Jul 18, 2011 at 10:58 AM, abhisek...@gmail.com  wrote:

> Hi,
>
> I am a new user of SQLite and I have a series of steps to do on a single
> table, and it takes way too long. I will outline the steps below and please
> let me know if I can do anything different to speed things up a bit.
>
> So I am coding up a cache simulator. The input basically consists of a
> series of addresses:
> Seq No Address
> 1 0x12459
> 2. 0x03300
> ...
>
> These are addresses accessed by a program. There will be 100 billion
> entries or so like this, which makes it necessary to use the database. The
> processing is as follows:
> 1. Read the addresses into the table.
>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Performance Improvement

[abhisek...@gmail.com]

Hi,

I am a new user of SQLite and I have a series of steps to do on a single
table, and it takes way too long. I will outline the steps below and please
let me know if I can do anything different to speed things up a bit.

So I am coding up a cache simulator. The input basically consists of a
series of addresses:
Seq No Address
1 0x12459
2. 0x03300
...

These are addresses accessed by a program. There will be 100 billion entries
or so like this, which makes it necessary to use the database. The
processing is as follows:
1. Read the addresses into the table.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Performance Improvement

[Stephan Beal]

On Mon, Jul 18, 2011 at 4:58 PM, abhisek...@gmail.com
wrote:

> These are addresses accessed by a program. There will be 100 billion
> entries
>

You won't be able to fit that many in your database - sqlite3 cannot scale
to the file size you will need for that. Assuming 10-byte addresses (as you
demonstrated), 10 bytes x 100B records = 1 terrabyte JUST for the addresses
(not including any sqlite3-related overhead per record, which is probably
much larger than the 10 bytes you're saving).

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Handle leak using IIS on windows 7?

[Black, Michael (IS)]

If you showed a small sample code that caused your problem it would sure help a 
lot.



I peeked at the library code.  That message is generated when _cnn is null (the 
DB connection).

private void InitializeForReader()
{
  if (_activeReader != null && _activeReader.IsAlive)
throw new InvalidOperationException("DataReader already active on this 
command");

  if (_cnn == null)
throw new InvalidOperationException("No connection associated with this 
command");



It is set on a "new Connection".



It is only turned to null in one place (in Commit).  But why in the world you 
would null _cnn on a commit is beyond me.

Are you using a commit?  It could be autocommit I supposed causing it.

I'd comment out that "_cnn = null" line below and see if it solves your 
problem.  Sure would explain why it only works once.



/// 
/// Commits the current transaction.
/// 
public override void Commit()
{
  IsValid(true);

  if (_cnn._transactionLevel - 1 == 0)
  {
using (SQLiteCommand cmd = _cnn.CreateCommand())
{
  cmd.CommandText = "COMMIT";
  cmd.ExecuteNonQuery();
}
  }
  _cnn._transactionLevel--;
  _cnn = null;
}



Michael D. Black

Senior Scientist

NG Information Systems

Advanced Analytics Directorate




From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Doug [douglas.lin...@gmail.com]
Sent: Monday, July 18, 2011 1:34 AM
To: sqlite-users@sqlite.org
Subject: EXT :[sqlite] Handle leak using IIS on windows 7?

Hi there,

I've searched around and found a few threads like this:
http://sqlite.phxsoftware.com/forums/t/2480.aspx

Basically, I have the same issue.

When access the sqlite database via a  website (MVC3 running on IIS) the
first time, sqlite works fine.

I properly call connections.Close() when I'm done...

And the next time I try to access it I get:
System.InvalidOperationException: No connection associated with this command

Manually stopping the dev web server, or restarting the iis application pool
fixes this for one more page view.

It seems like the IIS config is leaving the process hanging around, and
after calling close there (I guess) must be some handle which is being kept
and keeping a reference to the database, preventing anything else from
accessing it.

Seeing as how this has happened to a few people, I was hoping someone here
had seen this before and had a solution?

I'm using the Precompiled Binaries for 32-bit Windows (.NET Framework 4.0)
from http://system.data.sqlite.org/index.html/doc/trunk/www/downloads.wiki(32
bit mode enabled on iis), but I've tried the 64-bit version with the
same result.

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

[sqlite] Handle leak using IIS on windows 7?

[Doug]

Hi there,

I've searched around and found a few threads like this:
http://sqlite.phxsoftware.com/forums/t/2480.aspx

Basically, I have the same issue.

When access the sqlite database via a  website (MVC3 running on IIS) the
first time, sqlite works fine.

I properly call connections.Close() when I'm done...

And the next time I try to access it I get:
System.InvalidOperationException: No connection associated with this command

Manually stopping the dev web server, or restarting the iis application pool
fixes this for one more page view.

It seems like the IIS config is leaving the process hanging around, and
after calling close there (I guess) must be some handle which is being kept
and keeping a reference to the database, preventing anything else from
accessing it.

Seeing as how this has happened to a few people, I was hoping someone here
had seen this before and had a solution?

I'm using the Precompiled Binaries for 32-bit Windows (.NET Framework 4.0)
from http://system.data.sqlite.org/index.html/doc/trunk/www/downloads.wiki(32
bit mode enabled on iis), but I've tried the 64-bit version with the
same result.

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