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 (on Linux) similarly drastic
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. On Linux,
> creating the ta
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" statem
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
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
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;
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;
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 fi
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 optional
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
__
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 index memoria.Dispositivos_Tipo
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
___
s
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 from
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 ex
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 descripto
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 MAP_POP
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
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. Linux
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:
>> > impro
--- 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. Wh
--- [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 a
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 af
--- [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 fragm
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 relea
--- 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 c
--- 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 t
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 has
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
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 simila
--- [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.
> > >
> > > T
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 cor
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 VARCH
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 VARCHAR(80) );
>> CREATE INDEX rawfen_idx_fen ON rawfe
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 m
--- 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 h
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
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 interf
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.
--
Pune
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]
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
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 f
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 s
On 3/22/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
"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 lik
"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 a
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
identif
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 ca
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, checki
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 SELE
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
> 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 the
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.
>
>
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 make
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 th
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 i
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 TAB
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 rawfen_id
56 matches
Mail list logo
