I will make the changes as per my knowledge and send it to all on this
list to see if it helps in anyway.
On Thu, Mar 6, 2014 at 5:25 AM, Roger Binns wrote:
> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
>
> On 05/03/14 10:59, Raheel Gupta wrote:
> > If you point out
On Wed, Mar 5, 2014 at 1:24 PM, Raheel Gupta wrote:
>
> > SQLITE_MAX_PAGE_COUNT is 2147483646.
> > After looking at the code, Pgno is a u32.
> > So isnt SQLITE_MAX_PAGE_COUNT capable of actually being 4294967292 ?
> >
>
> We have your request. But as it will r
SQLITE_MAX_PAGE_COUNT is 2147483646.
After looking at the code, Pgno is a u32.
So isnt SQLITE_MAX_PAGE_COUNT capable of actually being 4294967292 ?
Please correct me if I am wrong
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:80
guess
SQlite has some free flags in its superblock. Maybe we can use a single
byte to mark that this is a 64 bit page number ?
Ext File System does that as well :)
On Mon, Feb 10, 2014 at 4:42 PM, Clemens Ladisch wrote:
> Raheel Gupta wrote:
> >If only the number of pages could be increase
his to 6.25% but requires 29 adjacent
> deletes for 1 guaranteed free page.
>
> You can choose the source of fragmentation: loosing close to 1 row per
> page (better in bigger pages) or having ununsed space due to nonadjacent
> deletes (better in smaller pages)
>
> -U
Integer.
I do understand that VACUUM is not a good option for me.
On Sun, Feb 9, 2014 at 4:48 PM, Simon Slavin wrote:
>
> On 9 Feb 2014, at 10:45am, RSmith wrote:
>
> > On 2014/02/09 12:06, Raheel Gupta wrote:
> >> Sir, I have only one auto increment primary key.
>
he NEW row which will be assigned ROWID 10001 ?
On Sat, Feb 8, 2014 at 11:38 PM, Richard Hipp wrote:
> On Fri, Feb 7, 2014 at 7:39 AM, Raheel Gupta wrote:
>
> > Hi,
> >
> > My Page size is 64KB and I store around 4KB of row data in one row.
> > I store around 1
optimize my storage space
utilization.
On Sat, Feb 8, 2014 at 10:21 PM, Simon Slavin wrote:
>
> On 8 Feb 2014, at 11:24am, Raheel Gupta wrote:
>
> > I dont want to repack the DB sir.
> > When a page becomes free I want to make sure that page is used up first
> and
> &
-pack the db, though you might get better results at re-using the
> space - but this is a compromise and one that does not sit well with you
> (if I read you right).
>
> Best of luck!
> Ryan
>
>
>
> On 2014/02/08 07:57, Raheel Gupta wrote:
>
>> Hi,
>> Sir, th
ou will have to re-plan
> or reconsider either the max allowable DB, or the physical layer's space
> availability - sorry.
>
>
>
> On 2014/02/07 20:35, Raheel Gupta wrote:
>
>> Hi,
>>
>> I use a page size of 64 KB. But my row consists of 2 columns that is :
&g
Griggs wrote:
> Can you write more about how this is causing you a problem? Most users
> don't experience this as a problem
> On Feb 7, 2014 10:30 AM, "Raheel Gupta" wrote:
>
> > >
> > > SQLite's tables are B-trees, sorted by the rowid. Your new dat
>
> SQLite's tables are B-trees, sorted by the rowid. Your new data will
> probably get an autoincremented rowid, which will be appended at the end
> of the table.
>
> A page gets reorganized only when about 2/3 is free space.
>
Anyway to make this ratio to lets say 1/3 ?
Hi,
My Page size is 64KB and I store around 4KB of row data in one row.
I store around 1 rows in one table and the database size reaches 42MB.
Now, I am facing a peculiar problem. When I delete just 2-3 rows, that page
is not reused for the new data which will be inserted in the future.
The
SCHEMA :
CREATE TABLE checksums
(i INTEGER,c VARCHAR(20) PRIMARY KEY) ;
CODE :
// Generates a Random string
QByteArray Randstr_B(int len) {
char chars[36] = {'a', 'b', 'c', 'd', 'e', 'f', 'g', 'h', 'i', 'j',
'k', 'l', 'm', 'n', 'o', 'p', 'q', 'r', 's', 't', 'u', 'v', 'w', 'x', 'y',
'z', '0',
Sir, I tested the method of the following :
sqlite3_exec("INSERT OR IGNORE INTO ;");
if( sqlite3_changes()==0 ){
sqlite3_exec("UPDATE ...");
}
I have seen my performance degrade from 1 records / second to 1
records / 10 seconds after 3 million UNIQUE Checksums inserted. Am I doing
anyth
Hi,
I have the following tables :
CREATE TABLE checksums
(i INTEGER,c VARCHAR(16) PRIMARY KEY) ;
CREATE TABLE data
(i INTEGER, data BLOB, isUnique INTEGER)
My application needs to insert 1000s of rows/second into the data table
hence I use transactions and prepare statements.
Now is it possible
Hi,
I am trying to add some additional data with the table structure stored in
SQLite. Everything seems to be working fine but when I do an alter query it
seems to drop the table.
I have made changes in the following functions to handle the addition of
the data:
sqlite3AlterFinishAddColumn() ,
sq
Yes, I did test SQLCipher and it slows down a bit.
Now, I would like to go with SEE if its available for the latest version.
SQLCipher is available for 3.8.0.2 while 3.8.1 is out.
On Thu, Dec 5, 2013 at 9:34 PM, Simon Slavin wrote:
>
> On 5 Dec 2013, at 3:02pm, Raheel Gupta wrote:
>
9:15am, Raheel Gupta wrote:
>
> > Yes, I agree. But it should not make the inserts and read too slow as
> well.
>
> The key word here is 'too'. If there's only 5% difference in speed
> between the two systems then it doesn't matter which one you use.
>
a.
>
> There is more in the world than speed and size.
>
> On Wed, Dec 4, 2013 at 6:18 PM, Simon Slavin wrote:
>
>
> On 4 Dec 2013, at 10:45am, Raheel Gupta wrote:
>
>
> I wanted to know which is the best in perf
ed, Dec 4, 2013 at 6:18 PM, Simon Slavin wrote:
>
> On 4 Dec 2013, at 10:45am, Raheel Gupta wrote:
>
> > I wanted to know which is the best in performance ?
>
> There would probably not be a simple definite answer that applied to all
> setups. The answer may depend on wha
Hi,
I wanted to implement Encryption on my sqlite DB with least load on
performance.
I have come across Sqlcipher.net and SQLite Encryption Extension (By Dr.
Hipp)
I wanted to know which is the best in performance ? Has anyone tested both
of these and can provide some feedback ?
_
Ok. Thank you for your explanation everyone and for being patient with me.
I look forward to Sqlite4 in whatever features you implement :)
On Mon, Nov 11, 2013 at 8:17 PM, Richard Hipp wrote:
> On Mon, Nov 11, 2013 at 1:40 AM, Raheel Gupta wrote:
>
> >
> > I guess a Row leve
te must be "lite"
On Sun, Nov 10, 2013 at 8:39 PM, Simon Slavin wrote:
>
> On 10 Nov 2013, at 12:05pm, Raheel Gupta wrote:
>
> >>> I can't think of any other single feature that would remove the "lite"
> >
> > I am not a database expe
>> Look at the performance difference between BDB and SQLite3 here
http://symas.com/mdb/microbench/#sec1
I did, and I really cant comment on that. The results are of 2012 and its
almost 2013. You should update the page with a newer result set.
>> I can't think of any other single feature that wou
>
> This is the BDB SQL doc I found.
>
>
> http://docs.oracle.com/cd/E17076_02/html/bdb-sql/dbfeatures.html#bulkloading
>
> If you insert record in bulk, you can use PRAGMA TXN_BULK for optimization.
>
I tested TXN_BULK, still pretty slow. Nearly 2.4 times.
Also the space wastage is pretty high. I
; in 1 second, BDB was taking 2.5 seconds and more CPU as well. I did
> this
> >>> in
> >>> QT C++. Overall BDB SQL interface is slower than Sqlite for inserts.
> >>> That
> >>> is what I found.
> >>
> >> Have you consult this to t
>> No. It's not even feature-frozen yet, as far as we know. And whenever it
is, it's incredibly unlikely to have row level locking.
Please add row-level locking if possible.
On Fri, Nov 8, 2013 at 12:03 AM, Simon Slavin wrote:
>
> On 7 Nov 2013, at 6:31pm, Raheel Gupta
:03 AM, Simon Slavin wrote:
>
> On 7 Nov 2013, at 6:31pm, Raheel Gupta wrote:
>
> > Any idea when will SQLite4 be released as stable ?
>
> No. It's not even feature-frozen yet, as far as we know. And whenever it
> is, it's incredibly unlik
>> SQLite4 still in development phase. It is not production ready.
Any idea when will SQLite4 be released as stable ?
On Tue, Nov 5, 2013 at 8:20 AM, Howard Chu wrote:
> Aris Setyawan wrote:
>
>> Hi Howard,
>>
>> I just looked, sophia is nothing special. See these microbench results.
>>> http
can choose DBMS, other than SQLite, try to use DB that have
> storage engine optimized for write, for example LSM (hypertable),
> Fractal Tree (tokudb engine for mysql).
>
> On 11/4/13, Raheel Gupta wrote:
> > Will SQLite4 be a better solution for me then ?
> >
> > Also @
Will SQLite4 be a better solution for me then ?
Also @aris do you recommend BDB over Sqlite for 1-10 Billion records ?
On Mon, Nov 4, 2013 at 8:03 AM, Aris Setyawan wrote:
> > I just looked, sophia is nothing special. See these microbench results.
> > http://pastebin.com/cFK1JsCN
> >
> > LMDB'
e-comparison-wp-176431.pdf
>>>>
>>>> * You must aware that BDB now have AGPL license.
>>>>
>>>
>>> SQLightning replaces the SQLite backend with Symas' LMDB, which also uses
>>> MVCC
>>> and thus supports high concurrency. It is also
Hi,
I have been using SQLite for one project of mine and I will be storing TBs
of Data.
Now there will be a lot of selections in this database and I have come
across one problem with SQLite.
In journal_mode=delete the selection is database locked.
When one thread does a "TRANSACTION" on the databa
20 Bytes or more than that ?
On Wed, Oct 30, 2013 at 3:15 PM, Simon Slavin wrote:
>
> On 30 Oct 2013, at 8:37am, Raheel Gupta wrote:
>
> > email VARCHAR(255) UNIQUE NOT NULL
> > }
> >
> > I wanted to know if I create an INDEX for the column "email" wha
Hi,
I have the following Table :
CREATE TABLE users (
uid INTEGER PRIMARY KEY AUTOINCREMENT,
username VARCHAR(100) UNIQUE NOT NULL DEFAULT '',
email VARCHAR(255) UNIQUE NOT NULL
}
I wanted to know if I create an INDEX for the column "email" what isg going
to be the extra space the index will occu
Sir, is there any way to not allow malloc to hold memory ? I mean shouldnt
free(), be freeing the memory ?
On Tue, Oct 29, 2013 at 1:19 AM, Richard Hipp wrote:
> On Mon, Oct 28, 2013 at 3:47 PM, Raheel Gupta wrote:
>
> >
> > Then I ran "Pragma shrink_memo
side Usage: 1408 bytes
Fullscan Steps: 0
Sort Operations: 0
Autoindex Inserts: 0
On Mon, Oct 28, 2013 at 6:51 PM, Richard Hipp wrote:
> On Mon, Oct 28, 2013 at 9:16 AM, Raheel Gupta wrote:
>
> > >> Whether or not
>> Whether or not free() returns that space to the operating system or keeps
it around to satisfy future malloc() calls is a detail of the
implementation of free().
Sir, anyway to be sure of that ?
On Mon, Oct 28, 2013 at 5:45 PM, Richard Hipp wrote:
> On Mon, Oct 28, 2013 at 8:13 A
usage of around 2-3 MB ?
No matter what you do the 65MB usage is always there.
On Mon, Oct 28, 2013 at 5:20 PM, Simon Slavin wrote:
>
> On 28 Oct 2013, at 10:45am, Raheel Gupta wrote:
>
> >>> PRAGMA cache_size
> >
> > I have set that 2000 in both cases.
>
&g
ct 28, 2013 at 4:15 PM, Raheel Gupta wrote:
> >> PRAGMA cache_size
>
> I have set that 2000 in both cases.
>
>
> On Sun, Oct 27, 2013 at 7:49 PM, Simon Slavin wrote:
>
>>
>> On 27 Oct 2013, at 5:10am, Raheel Gupta wrote:
>>
>> > But why wou
>> PRAGMA cache_size
I have set that 2000 in both cases.
On Sun, Oct 27, 2013 at 7:49 PM, Simon Slavin wrote:
>
> On 27 Oct 2013, at 5:10am, Raheel Gupta wrote:
>
> > But why would this happen with 64K pages ? In 1024 Sqlite is able to
> > release all the memory.
>> Whether and how you can do so will depend on what operating system you
are
using.
I am using a CentOS 6.4
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
On Sun, Oct 27, 2013 at 9:46 AM, Richard Hipp wrote:
> On Sun, Oct 27, 2013 at 12:02 AM, Raheel Gupta
> wrote:
>
> > Hi,
> >
> > Sir, if you see my first email, I have already tried that. When the 15
> > Million records are being outputted, the ram usage shoots t
> On Sat, Oct 26, 2013 at 3:03 PM, Raheel Gupta wrote:
>
> >
> > This leads me to conclude that there is some kind of Memory Leakage when
> > the page size is 64K.
> >
> > How can I bring down the memory usage atleast when I shrink_memory after
> > t
>> Try letting SQLite use its default for your platform.
The default when the database is created is 1024.
It works well in that page size as I have mentioned in my first email.
The issue is with 65536.
Why should there be a memory leak when the page size is 65536 ?
I have to use 65536 to enable
Hi,
I am using a Page Size of 65536 and I have found the performance good
enough for me until now.
I have the database having the following table:
CREATE TABLE map (
n BIGINT NOT NULL DEFAULT 0,
s INT(5) NOT NULL DEFAULT 0,
d INT(5) NOT NULL DEFAULT 0,
c INT(1) NOT NULL DEFAULT 0,
b UNSIGNED BIGI
Hi,
The index of (d,n,s) has improved the performance and is WAY better than
(n,s,d)
Thanks to everyone for helping me out.
>> So which is better ? An Index or a Primary Key ?
My index is not unique and hence I guess going to Primary Keys would slow
down inserts quite a lot. Please correct me i
>
> Yes, but they allow the searches to be faster. You are making it longer
> to do INSERT but shorter to do SELECT. Which is best for you depends on
> your purposes.
>
I need the inserts to be faster.
So which is better ? An Index or a Primary Key ?
The new INDEX that I created on your suggest
Hi,
>> First, consider if some combination of those columns constitute a
primary key. That would be stronger than a simple index.
Does SQLite support multi column primary keys ?
Also wouldnt primary keys actually slow down further inserts. I have
queries to insert nearly 1 rows in one second.
> I cannot definitely solve your problem but I can think of some things to
> try. First, do these:
>
> ANALYZE;
> CREATE INDEX map_dsn ON map (d, s, n);
> CREATE INDEX map_dns ON map (d, n, s);
>
> then execute the same SELECT. Does it have the same problem ? Does the
> EXPLAIN QUERY PLAN tell y
>> See if the situation changes if you drop all those single quotes around
your constants. Why are you comparing integer values to string literals?
Tried that and it doesnt change.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8
Hi,
Here is the output :
0|0|0|SEARCH TABLE map USING COVERING INDEX map_index (n>?) (~4166 rows)
0|0|0|EXECUTE LIST SUBQUERY 1
I am not sure how can I optimize this ?
(Also I checked again and there are 4166 rows in this last result and not
1568 as per my last email.)
It seems to be using the t
Hi,
I am facing a peculiar issue with SQLITE.
The following is my table structure :
CREATE TABLE map (
n BIGINT NOT NULL DEFAULT 0,
s INT(5) NOT NULL DEFAULT 0,
d INT(5) NOT NULL DEFAULT 0,
c INT(1) NOT NULL DEFAULT 0,
b UNSIGNED BIGINT N
54 matches
Mail list logo
