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 ?
_
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
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
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.
>
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:
>
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
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
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
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',
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
>
> 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 ?
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
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
-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
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
> &
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
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.
>
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
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
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
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
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
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
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
>> 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
> 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
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.
>
> 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,
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
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
>> 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
> 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
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
>> 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
>> 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.
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
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
>> 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
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
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
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
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 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
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
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'
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 @
>> 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
: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
>> 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
; 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
>
> 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
>> 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
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
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
54 matches
Mail list logo
