Re: [sqlite] index creation 30x slower on Mac than on Linux

On Tue, Apr 23, 2013 at 5:58 PM, Alan Frankel wrote: > ...Why is the Mac 30 times slower than Linux? They're both on the same > network, accessing the same network drive location, so neither has the > advantage of local disk access. > i'm speculating, but i have seen

Re: [sqlite] index creation 30x slower on Mac than on Linux

On 23 Apr 2013, at 4:58pm, Alan Frankel wrote: > We're using SQLite to manage a roughly 250Mb database on both Linux and Mac. > In order to make our lookups fast, we're creating an index table in the > database. On Linux, creating the table takes about 200 seconds.

[sqlite] index creation 30x slower on Mac than on Linux

We're using SQLite to manage a roughly 250Mb database on both Linux and Mac. In order to make our lookups fast, we're creating an index table in the database. On Linux, creating the table takes about 200 seconds. On the Mac the same operation takes 6,400 seconds. Here's the "CREATE INDEX"

Re: [sqlite] index creation for single-use DB

On Sun, May 13, 2012 at 11:02:54AM +0300, Baruch Burstein wrote: > I am using an in-memory DB to load data into it, do a few sort / find > duplicates / SELECTs, and then dispose of the DB. It can vary in size from > a few thousand rows to over a million. > Would the time used for creating an index

[sqlite] index creation for single-use DB

I am using an in-memory DB to load data into it, do a few sort / find duplicates / SELECTs, and then dispose of the DB. It can vary in size from a few thousand rows to over a million. Would the time used for creating an index be worth it for just a single sort and a few SELECTs? If so, would it be

[sqlite] Index creation using 'strftime'.

Hi to all, I'm trying to create an index as follow: CREATE TABLE FattureCli ( fat_id INTEGER PRIMARY KEY, fat_rif_sogid INTEGER DEFAULT NULL, fat_rif_mpaid INTEGER DEFAULT NULL, fat_numfat INTEGER NOT NULL, -- It would be unique by year; fat_datfat CHAR(10) NOT NULL, -- -MM-DD;

[sqlite] Index creation using 'strftime'.

Hi to all, I'm trying to create an index as follow: CREATE TABLE FattureCli ( fat_id INTEGER PRIMARY KEY, fat_rif_sogid INTEGER DEFAULT NULL, fat_rif_mpaid INTEGER DEFAULT NULL, fat_numfat INTEGER NOT NULL, -- It would be unique by year; fat_datfat CHAR(10) NOT NULL, -- -MM-DD;

Re: [sqlite] Index creation in a memory database

Hi! Uppsss, sorry about it, I wrote it incomplete, because we were talking about another part of the sentence. The next one is the original sentence I asked here what was wrong:create index memoria.Dispositivos_TipoDispositivo on memoria.Dispositivos (Tipo_Dispositivo) So you see that the

Re: [sqlite] Index creation in a memory database

Regarding: Maybe the question for this issue is why it's not ok this syntax: create index memoria.Dispositivos_TipoDispositivo on memoria.Dispositivos >From my reading of the CREATE INDEX syntax at: http://www.sqlite.org/lang_createindex.html the parentheses are required, not

Re: [sqlite] Index creation in a memory database

Hi Simon! That was the solution. I thought I have tested all the combinations but I was wrong. Maybe the question for this issue is why it's not ok this syntax: create index memoria.Dispositivos_TipoDispositivo on memoria.Dispositivos Thank you, David

Re: [sqlite] Index creation in a memory database

2010/1/21 David Alcelay : > Hi Igor! > Thanks for your reply. > I tested my self that solution before sending the question, but didn't work. > I have tested again now (may be I did not ok before), but the same result:     > near ".": syntax error Did you try create

[sqlite] Index creation in a memory database

Hi Igor! Thanks for your reply. I tested my self that solution before sending the question, but didn't work. I have tested again now (may be I did not ok before), but the same result: near ".": syntax error Any idea? Thanks in advance, David ___

Re: [sqlite] Index creation in a memory database

David Alcelay wrote: > 3) create an index over this table: > create index memoria.Dispositivos_TipoDispositivo on memoria.Dispositivos > (Tipo_Dispositivo) > > I get this error: near ".": syntax error You don't specify database name in trigger name; the appropriate database is inferred

[sqlite] Index creation in a memory database

Hi! I started having problems when I started using sqlite in 'memory' mode, this is, workin only in memory. It was not a problem to configure this working mode, it's really easy, but somethings goes wrong. So, working in no memory mode works ok, but if I go to memory mode, doesn't work with a

[sqlite] Index Creation Questions

I'm creating some indices, and want to know if it's going to help or hurt me (or be totally irrelevant because of optimization) to include the primary key at the end of the index. All of my tables (in question) have an INTEGER PRIMARY KEY AUTOINCREMENT field, and I have several textual

Re: [sqlite] Index creation

There is an important element to bear in mind when using mmaping to take advanrage of physical memory. The POSIX advisory file locks are not necessarily effective in that mode. Joe Wilson wrote: Sure, pre-caching is a hack - but a very effective one. It has also been suggested to use

Re: [sqlite] Index creation

Sure, pre-caching is a hack - but a very effective one. It has also been suggested to use MAP_POPULATE for mmap() and posix_fadvise() in Linux. http://www.mail-archive.com/monotone-devel@nongnu.org/msg03222.html The general reaction against pre-caching (especially for tiny 100M databases) is

Re: [sqlite] Index creation

An interesting approach would be to use some sort of async I/O facility to implement read-ahead. Short of that, I have found that in some cases, on some operating systems, implementing explicit read-ahead buffering for fts2 segment merges improves performance when the disk caches are cold.

Re: [sqlite] Index creation

I did two things: 1. Catted the file to /dev/null 2. Reduced cache_size from 1 down to 2000 I don't know what actions enabled the creation of the index. Before I did these two things, the index would not create. Joe Wilson wrote: > > --- Joe Wilson <[EMAIL PROTECTED]> wrote: >> >

Re: [sqlite] Index creation

--- Joe Wilson <[EMAIL PROTECTED]> wrote: > > improved dramatically. So I attempted the creation of the index off hours on > > the production system, and after 4 hours no index. I can't detect any > > activity at all. The journal file and the .db file just sit at the same size > > for 4 hours.

Re: [sqlite] Index creation on huge table will never finish.

--- [EMAIL PROTECTED] wrote: > Joe Wilson <[EMAIL PROTECTED]> wrote: > > > > As for the stats from sqlite3_analyzer, they seem to be in the right > > ballpark. > > But I'm not sure its heuristic accounts for rows that are significantly > > larger > > than the page size, though. In such cases I

Re: [sqlite] Index creation on huge table will never finish.

Joe Wilson <[EMAIL PROTECTED]> wrote: > > As for the stats from sqlite3_analyzer, they seem to be in the right ballpark. > But I'm not sure its heuristic accounts for rows that are significantly larger > than the page size, though. In such cases I am seeing higher than expected > fragmentation

Re: [sqlite] Index creation on huge table will never finish.

--- [EMAIL PROTECTED] wrote: > Joe Wilson <[EMAIL PROTECTED]> wrote: > > > > See also: Changes to support fragmentation analysis in sqlite3_analyzer. > > http://www.sqlite.org/cvstrac/chngview?cn=3634 > > > > I'm not real sure those patches are working right. > I need to revisit that whole

Re: [sqlite] Index creation on huge table will never finish.

Joe Wilson <[EMAIL PROTECTED]> wrote: > > See also: Changes to support fragmentation analysis in sqlite3_analyzer. > http://www.sqlite.org/cvstrac/chngview?cn=3634 > I'm not real sure those patches are working right. I need to revisit that whole fragmentation analysis thing before the next

Re: [sqlite] Index creation

--- turnleftjohn <[EMAIL PROTECTED]> wrote: > the production system, and after 4 hours no index. I can't detect any > activity at all. The journal file and the .db file just sit at the same size > for 4 hours. Why is this failing? It seems like it is just sitting there > doing nothing. When I

Re: [sqlite] Index creation

--- turnleftjohn <[EMAIL PROTECTED]> wrote: > improved dramatically. So I attempted the creation of the index off hours on > the production system, and after 4 hours no index. I can't detect any > activity at all. The journal file and the .db file just sit at the same size > for 4 hours. Why is

[sqlite] Index creation

I am new to sqlite. I have done some reading up. I have a table that I am having difficulty creating an index on. The table holds ~180 million rows, simple four column, integer schema. It is taking up about 1.5 gigs of space. I inherited this application and it is slowing down. The table

Re: [sqlite] Index creation on huge table will never finish.

P Kishor wrote: Mac/Unix person meself, but the Windows XP sort is pretty darn good as well. I'll take a look. Last time I used it it was useless. Win9x days? these days (especially for a one off) I'd probably go straight to doing it in Python to avoid x-platform syntax issues. Martin

Re: [sqlite] Index creation on huge table will never finish.

Ah yes, I should read more carefully :) Thanks, right, I was actually guaranteeing uniqueness originally by just fetching and then inserting only if there wasn't a match (I needed a rowid if the row existed anyway). Now I'm guaranteeing uniqueness by letting sort do the work for me, but

Re: [sqlite] Index creation on huge table will never finish.

--- [EMAIL PROTECTED] wrote: > Gerry Snyder <[EMAIL PROTECTED]> wrote: > > Chris Jones wrote: > > > Hi all, > > > > > > I have a very simple schema. I need to assign a unique identifier to a > > > large collection of strings, each at most 80-bytes, although typically > > > shorter. > > > > > >

Re: [sqlite] Index creation on huge table will never finish.

Chris Jones wrote: I probably should have made this more explicit, but in sqlite, every row has a unique identifier named rowid, which exists even if it isn't explicity declared in the schema, and I was depending on that. If you declare a PRIMARY KEY, then this replaces rowid. A tiny

Re: [sqlite] Index creation on huge table will never finish.

Chris Jones <[EMAIL PROTECTED]> writes: > Derrell.Lipman wrote: >> >> Chris Jones <[EMAIL PROTECTED]> writes: >> >> I don't think that your original solution solves that problem either. You >> first posted this schema: >> >>> My schema looks as follows: >>> >>> CREATE TABLE rawfen ( fen

Re: [sqlite] Index creation on huge table will never finish.

Chris Jones wrote: So, I did a "sort -u -S 1800M fenout.txt > fenoutsort.txt" The sort took about 45 minutes, which is acceptable for me (it was much longer without the -S option to tell it to make use of more memory), and then loading the table was very efficient. Inserting all the rows into

Re: [sqlite] Index creation on huge table will never finish.

--- Dennis Cote <[EMAIL PROTECTED]> wrote: > You could also improve the locality in the database file further by > running a vacuum command after it has been created. this will move the > pages around so that the page of the table are contiguous and so are the > pages of the index, rather than

Re: [sqlite] Index creation on huge table will never finish.

A fast technique to achieve your objective is to perform what I believe is called a "monkey puzzle" sort. The data is not moved, instead an array of descriptors to each element is sorted. The output is realized by scanning the list of descriptors and picking up the associated record from the

Re: [sqlite] Index creation on huge table will never finish.

An issue with cache is cache shadowing, the churning as data is copied from one cache to another to another. An example is the speed-up achieved on network accesses by using sendfile or TransmitFile and bypassing up to four levels of buffering for a message being despatched to a network

Re: [sqlite] Index creation on huge table will never finish.

On 3/22/07, Martin Jenkins <[EMAIL PROTECTED]> wrote: Chris Jones wrote: > realized that the unix "sort" If I'd known you were on 'nix I'd have suggested using 'sort' and/or 'md5sum' about 12 hours ago. ;) Mac/Unix person meself, but the Windows XP sort is pretty darn good as well. --

Re: [sqlite] Index creation on huge table will never finish.

Chris Jones wrote: realized that the unix "sort" If I'd known you were on 'nix I'd have suggested using 'sort' and/or 'md5sum' about 12 hours ago. ;) Martin - To unsubscribe, send email to [EMAIL PROTECTED]

Re: [sqlite] Index creation on huge table will never finish.

You could sort the table then perform a merge which removes duplicates. Chris Jones wrote: I don't think that solves my problem. Sure, it guarantees that the IDs are unique, but not the strings. My whole goal is to be able to create a unique identifier for each string, in such a way that I

Re: [sqlite] Index creation on huge table will never finish.

Thanks everyone for your feedback. I ended up doing a presort on the data, and then adding the data in order. At first I was a little concerned about how I was going to implement an external sort on a data set that huge, and realized that the unix "sort" command can handle large files, and in

Re: [sqlite] Index creation on huge table will never finish.

Chris Jones wrote: I've read elsewhere that this is a data locality issue, which certainly makes sense. And in those threads, a suggestion has been made to insert in sorted order. But it's unclear to me exactly what the sorting function would need to be - it's likely my sorting function (say

Re: [sqlite] Index creation on huge table will never finish.

"P Kishor" <[EMAIL PROTECTED]> wrote: > Richard, > > On 3/22/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: > ... > > The problem is that your working set is bigger than your cache > > which is causing thrashing. I suggest a solution like this: > > > > Add entries to table ONE until the table

Re: [sqlite] Index creation on huge table will never finish.

At 04:47 22/03/2007, you wrote: I don't think that solves my problem. Sure, it guarantees that the IDs are unique, but not the strings. My whole goal is to be able to create a unique identifier for each string, in such a way that I dont have the same string listed twice, with different

Re: [sqlite] Index creation on huge table will never finish.

Richard, On 3/22/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: .. The problem is that your working set is bigger than your cache which is causing thrashing. I suggest a solution like this: Add entries to table ONE until the table and its unique index get so big that they no longer fit in

Re: [sqlite] Index creation on huge table will never finish.

Gerry Snyder <[EMAIL PROTECTED]> wrote: > Chris Jones wrote: > Hi all, > I have a very simple schema. I need to assign a unique identifier > to a large collection of strings, each at most 80-bytes, although > typically shorter. Would it help to hash the strings, then save them in the DB,

Re: [sqlite] Index creation on huge table will never finish.

Ion Silvestru <[EMAIL PROTECTED]> wrote: > > drh wrote: > > INSERT INTO two SELECT * FROM one ORDER BY unique_column; > > >The ORDER BY is important here. > > This is an excerpt from SQLite documentation: > > The second form of the INSERT statement takes it data from a

Re: [sqlite] Index creation on huge table will never finish.

Chris Jones <[EMAIL PROTECTED]> writes: > I don't think that solves my problem. Sure, it guarantees that the IDs are > unique, but not the strings. > > My whole goal is to be able to create a unique identifier for each string, > in such a way that I dont have the same string listed twice, with

Re[2]: [sqlite] Index creation on huge table will never finish.

> drh wrote: > INSERT INTO two SELECT * FROM one ORDER BY unique_column; >The ORDER BY is important here. This is an excerpt from SQLite documentation: The second form of the INSERT statement takes it data from a SELECT statement. The number of columns in the result of

Re: [sqlite] Index creation on huge table will never finish.

Gerry Snyder <[EMAIL PROTECTED]> wrote: > Chris Jones wrote: > > Hi all, > > > > I have a very simple schema. I need to assign a unique identifier to a > > large collection of strings, each at most 80-bytes, although typically > > shorter. > > > > The problem is I have 112 million of them. > >

Re: [sqlite] Index creation on huge table will never finish.

Chris Jones wrote: Hi all, I have a very simple schema. I need to assign a unique identifier to a large collection of strings, each at most 80-bytes, although typically shorter. The problem is I have 112 million of them. Maybe you could start by breaking the data into 8 equal groups and

Re: [sqlite] Index creation on huge table will never finish.

You stated in your OP I need to assign a unique identifier to a large collection of strings Obviously I misunderstood that to mean you wanted the strings tagged uniquely, not that the strings were unique. In your case, it seems then, you will have to put up with checking each string, and as

Re: [sqlite] Index creation on huge table will never finish.

I don't think that solves my problem. Sure, it guarantees that the IDs are unique, but not the strings. My whole goal is to be able to create a unique identifier for each string, in such a way that I dont have the same string listed twice, with different identifiers. In your solution, there

Re: [sqlite] Index creation on huge table will never finish.

On 3/21/07, Chris Jones <[EMAIL PROTECTED]> wrote: Hi all, I have a very simple schema. I need to assign a unique identifier to a large collection of strings, each at most 80-bytes, although typically shorter. The problem is I have 112 million of them. My schema looks as follows: CREATE

[sqlite] Index creation on huge table will never finish.

Hi all, I have a very simple schema. I need to assign a unique identifier to a large collection of strings, each at most 80-bytes, although typically shorter. The problem is I have 112 million of them. My schema looks as follows: CREATE TABLE rawfen ( fen VARCHAR(80) ); CREATE INDEX