Hi,
When the DB is opened in "in Memory mode",performance of query does not
improve. For table which has 10 columns of type Text and each column having
128bytes data and having a total of 1 records. Performance is around 2400
records/sec. Any ways to improve it.
Thanks in advance.
Hi,
We are using Sqlite in "in Memory Mode" and we have around 200 tables.
Each table has 10 columns of type text.
Each table has around 1 records each column has around 128bytes data.
Select performance is around 2000records/sec. Pls suggest if there is a way to
improve further.
Table
Hi,
We are using Sqlite in "in Memory Mode" and we have around 200 tables.
Each table has 10 columns of type text.
Each table has around 1 records each column has around 128bytes data.
Select performance is around 2000records/sec. Pls suggest if there is a way to
improve further.
Table
---
Don't use sqlite_get_table. Use sqlite3_prepare(_v2), sqlite3_step and
sqlite3_finalize/sqlite3_reset.
Mike
-Ursprüngliche Nachricht-
Von: RaghavendraK 70574 [mailto:[EMAIL PROTECTED]
Gesendet: Montag, 25. Juni 2007 13:48
An: sqlite-users@sqlite.org
Betreff: [sqlite] In Mem Query
t;
Date: Tuesday, June 26, 2007 10:58 pm
Subject: Re: [sqlite] In Mem Query Performance
> --- RaghavendraK 70574 <[EMAIL PROTECTED]> wrote:
> > Thanks for the suggestion. But with that performance went down
> by 25% further.
> > Pls suggest an alternative. Mr DRH says it is
[sqlite] In Mem Query Performance
> --- RaghavendraK 70574 <[EMAIL PROTECTED]> wrote:
> > Your input is valuable. I will increase the pg size to 4KB and
> check.
> :memory: databases only use 1024 byte pages if I remember
> correctly,
> so it would have to be file
databases only have a page size of 1024.
>
> Try various page_size settings for a file based database file and
> see what happens.
>
> I have no other suggestions.
>
> --- RaghavendraK 70574 <[EMAIL PROTECTED]> wrote:
> > It is a server platform,Linux SuSE9 ente
GetTable will retrive entire snapshot of our table.I guess u app
is a 32bit application hence u can maximum access 2GB of user address space.
Upgrade to 64bit to access beyond this limit.
regards
ragha
PS:Sqlite is designed for small data sets amied at Embedded apps
ase notify the sender by phone or email immediately and
delete it!
*
- Original Message -----
From: RaghavendraK 70574 <[EMAIL PROTECTED]>
Date: Tuesday, June 26, 2007 10:30 pm
Subject: Re: [sqli
l immediately and
delete it!
*
- Original Message -
From: "A. Pagaltzis" <[EMAIL PROTECTED]>
Date: Saturday, June 30, 2007 9:48 pm
Subject: [sqlite] Re: In Mem Query Performance
> Hi RaghavendraK
op.
> - query the test table directly - not the temporary ttest table.
> - don't SELECT * if you only need a column or two
>
> --- RaghavendraK 70574 <[EMAIL PROTECTED]> wrote:
> > int main()
> > {
> > sqlite3* db;
> > int ret = sqlite3_open("abc",);
>
, please notify the sender by phone or email immediately and
delete it!
*
- Original Message -
From: RaghavendraK 70574 <[EMAIL PROTECTED]>
Date: Sunday, July 1, 2007 11:58 pm
Subject: Re: [sqli
Congratulations to DRH and all.
I Just love this beast,incredibly small but with monster features.
regards
ragha
**
This email and its attachments contain confidential information from HUAWEI,
which is
and
delete it!
*
- Original Message -
From: P Kishor <[EMAIL PROTECTED]>
Date: Tuesday, July 3, 2007 10:32 am
Subject: Re: [sqlite] iPhone
> On 7/2/07, RaghavendraK 70574 <[EMAIL PROTE
Hi Ken,
Thanks a lot.
But this would require the key to be short. Will Check if this is acceptable
to all as we may not be able to port old db data if the key format is changed.
regards
ragha
**
This email
Hi,
As said, i used a seperate ID (hash) but it made the performance very bad.
Now it takes minutes.[Version: 3.4.0]
regards
ragha
unsigned long idGen(const string & id)
{
const char* __s = id.c_str();
unsigned long __h = 0;
for ( ; *__s; ++__s)
{
Pls check if can create a normal file more than 2GB from your program
if not then some options must be missing.
regards
ragha
**
This email and its attachments contain confidential information from HUAWEI,
Hi,
I want to use Sqlite as in-mem db and want this Mem to be alloacted on SHM.
Has anyone tried it?
regards
ragha
**
This email and its attachments contain confidential information from HUAWEI,
which is
Turn off sync using pragma.
Build sqlite with appropriate cache and page size suitable to u;r system.
use sqliteanalyze and find out the page size.
Always use begin and commit/rollback for insert and updates.
Joe& others had provided us with good direction.
regards
ragha
HI,
1)How can we prepare a SQliteStatement for the following type of select,
select * from tablename where column in (?);
?: we don;t know the length of this string.
2) Can we mix SqliteStatement and sqlite3_exec functions?
regads
ragha
May be my Q is not clear,
Problem is as shown below,
SQL = "select * from table where xyz in (:abc);"
bind_text("'ab','xy','zx','123'")
bind_text will append '' at the begining and at end making it as one string
rather
than ab,xy,zx.
regards
ragha
Hi,
How can check if a row exists in the db or not without querying for it?
regards
ragha
**
This email and its attachments contain confidential information from HUAWEI,
which is intended only for the
, please notify the sender by phone or email immediately and
delete it!
*
- Original Message -
From: RaghavendraK 70574 <[EMAIL PROTECTED]>
Date: Thursday, July 19, 2007 5:17 pm
Subject: [
Hi,
Is there any api which tell us if a transaction is already started or not?
regards
ragha
**
This email and its attachments contain confidential information from HUAWEI,
which is intended only for the
it!
*
- Original Message -
From: [EMAIL PROTECTED]
Date: Saturday, July 28, 2007 6:20 pm
Subject: Re: [sqlite] Transaction Check
> RaghavendraK 70574 <[EMAIL PROTECTED]> wrote:
> > Hi,
> >
>
ransaction Check
> Cool. Is there a way to ask the engine this?
> Some SELECT call?
>
> At 12:50 PM + 28/7/07, [EMAIL PROTECTED] wrote:
> >RaghavendraK 70574 <[EMAIL PROTECTED]> wrote:
> >> Hi,
> >>
> >> Is
, July 28, 2007 6:20 pm
Subject: Re: [sqlite] Transaction Check
> RaghavendraK 70574 <[EMAIL PROTECTED]> wrote:
> > Hi,
> >
> > Is there any api which tell us if a transaction is already
> started or not?
> >
>
> sqlite3_get_autocommit()
&
AIL PROTECTED]>
Date: Sunday, July 29, 2007 8:06 pm
Subject: [sqlite] Re: Transaction Check
> RaghavendraK 70574
> <[EMAIL PROTECTED]> wrote:
> > If the sqlite file is deleted at runtime and if there are any open
> > connections,can these connections detect and return error?
>
Hi,
is there a way to notify the application, such a thing happened,so that
application don;t add further data to the db.
regards
ragha
**
This email and its attachments contain confidential information
Hi,
How to form the SQL query(in SQLite) for the following problem below
table:
44
442
4454
Input String: 4429845
Expected output from SQL query: 442
regards
ragha
**
This email and its attachments
values( 3, '4454' );
> sqlite>
> sqlite> select * from tst where '4429845' > cast( c2 as text ) order
> by c2 desc limit 1;
> 2|442
>
> Maybe not the most efficient way, but seems to give requested
> result...
> Rgds
> Simon
>
>
> On 06/08/07, Raghave
hi,
Am not clear.
Suppose i have 2 databases and a process spwans 2 threads and each thread
opne the db will it result in any problem? They are independent files.
thx
ragha
**
This email and its
Hi,
we have given a web interface which receive delete request.
Now in the req we get "%" and in the delete impl we do this
delete from table where itemName like xxx.%;
since the key is % the above statement becomes,
"delete from table where itemName like %.%";And result in fatal problem of
sqlite] Re: like operator
> RaghavendraK 70574
> <[EMAIL PROTECTED]> wrote:
> > we have given a web interface which receive delete request.
> > Now in the req we get "%" and in the delete impl we do this
> > delete from table where itemName like xxx.%
use sqlite statements.
regards
ragha
**
This email and its attachments contain confidential information from HUAWEI,
which is intended only for the person or entity whose address is listed above.
Any use
Hi,
But the performance is very bad in comparision with the file based DB,
any suggestion to improve the same.
regards
ragha
**
This email and its attachments contain confidential information from HUAWEI,
Hi,
I have tried with a ram disk and ramfs. Read Performance is same as that of
disk based system.
How did i evaluate?
wrote simple program
create file on disk,ramfs
do pread(...)
In fact sometimes the performance is low when using ramdisk or ramfs or dev/shm.
FileSize: 80MB
read at once:
Hi,
Sqlite uses pread and the performance with ramdisk and disk is the same
on Linux SuSE9.
regards
ragha
**
This email and its attachments contain confidential information from HUAWEI,
which is intended
;
Date: Friday, August 17, 2007 7:25 am
Subject: [sqlite] Re: like operator
> RaghavendraK 70574
> <[EMAIL PROTECTED]> wrote:
> > we have given a web interface which receive delete request.
> > Now in the req we get "%" and in the delete impl we do this
>
it!
*
- Original Message -
From: Igor Tandetnik <[EMAIL PROTECTED]>
Date: Sunday, August 26, 2007 7:00 pm
Subject: [sqlite] Re: Re: like operator
> RaghavendraK 70574
> <[EMAIL PR
Hi,
I did tried something crazy like this (it worked, for read only DB only).
I changed pread to preadCustom api.Then in preadCustom maintained a static
fdArray. if fd is not listed then i mmap the whole file and the use the memcpy
to return the data. There was significant performance again as
Hi,
create table test (t text);
insert into test values ('9');
insert into test values ('98');
insert into test values ('986');
insert into test values ('9867');
select * from test where '98555' like t || '%' order by t desc limit 1;
When we try to compile the above sql as a statement,we get
it!
*
- Original Message -
From: Dan Kennedy <[EMAIL PROTECTED]>
Date: Wednesday, August 29, 2007 7:07 pm
Subject: Re: [sqlite] BestMatch and SqliteStatment Clash
> On Wed, 2007-08-29 at 18:37 +0800, RaghavendraK 70574 wrote:
> > Hi,
> >
> > Am using
From: John Machin <[EMAIL PROTECTED]>
Date: Wednesday, August 29, 2007 6:22 pm
Subject: Re: [sqlite] BestMatch and SqliteStatment Clash
> On 29/08/2007 10:37 PM, RaghavendraK 70574 wrote:
> > Thx. I have modifed it to ?, but
> > Sqlite fails to get records for the below
sqlite] BestMatch and SqliteStatment Clash
> On 29/08/2007, RaghavendraK 70574 <[EMAIL PROTECTED]> wrote:
> >
> > select * from test where '982' like t || '%' order by t desc
> limit 1;
> >
> > This works but will not work for earlier data(9854002656).So
&g
Clash
> RaghavendraK 70574
> <[EMAIL PROTECTED]> wrote:
> > Best match is "9854002656" among the 2 records.
> >
> > Pls try this simple one.It will make it clear,
> >
> > create table test(t text);
> >
> > insert into test values ('9'
I am not sure how to make it any clearer.
>
> Rgds,
> Simon
>
> On 29/08/2007, RaghavendraK 70574 <[EMAIL PROTECTED]> wrote:
> >
> > Hi,
> >
> > Pls see my last post, hope it is clear.
> >
> > regards
> > ragha
> >
> **
it!
*
- Original Message -
From: Dennis Cote <[EMAIL PROTECTED]>
Date: Wednesday, August 29, 2007 8:45 pm
Subject: Re: [sqlite] BestMatch and SqliteStatment Clash
> RaghavendraK 70574 wrote:
> > Hi,
> >
> > There are 2 testcases one works
Pls see if u hv an open sqlite3 terminal.sometimes this can also cause a prob
with begin tx and just kept it open.
regrads
ragha
**
This email and its attachments contain confidential information from
Hi,
Do we have tool(similar to Sqlite3) by which Prepare statement can be tested as
easily as normal sql ?
As most of the time we have use Prepare statement hence we need a RAD tool
to verify it.This will be useful to the Sqlite grp.Share your opinion.
Or if any have an idea how to do it,let me
Convert your sql to sql Statements.
run sqliteanalz and see which all columns are to be indexed.
regards
ragha
**
This email and its attachments contain confidential information from HUAWEI,
which is
Hi,
In one of the slides "http://www.sqlite.org/php2004/page-052.html;
it is stated "OR will make DBEngine not to use Indexes" and IN will
make DBEngine use
indexes"
I could not understand the rationale abt this. Can u explain?
regards
ragha
nd
delete it!
*
- Original Message -
From: Dan Kennedy <[EMAIL PROTECTED]>
Date: Saturday, September 1, 2007 11:59 am
Subject: Re: [sqlite] Difference between OR and IN
[http://www.sqlite.org/php2004/page-052.html]
> On Sat, 2007-09-01 at 11:58
Hi,
If i have 2 sqlite files is there a way to merge
the files without corruption? assuming the schema
of both the files are same.
I don't want to use sql statements rather want to do
using file api's.
Similar to diffdb.c
pls help.
regards
ragha
te.org/php2004/page-052.html]
> On 9/1/07, RaghavendraK 70574 <[EMAIL PROTECTED]> wrote:
>
> > I want to know why
> > prepareStatement: select * from xxx where IN (?);
> > stmt.bind("abc,xyz,123"); is not supported for multiple
> > values.
&
Talbot <[EMAIL PROTECTED]>
Date: Sunday, September 2, 2007 1:03 am
Subject: Re: [sqlite] Difference between OR and IN
[http://www.sqlite.org/php2004/page-052.html]
> On 9/1/07, RaghavendraK 70574 <[EMAIL PROTECTED]> wrote:
>
> > I want to know why
> > prepareStat
statement. You
> control
> the execution and how many rows you want back.
>
>
> RaghavendraK 70574 uttered:
>
> > Hi,
> >
> > Ok.
> >
> > Is there any way to tell the VDBE to stop execution moment it
> gets a record
> > from the IN list rather t
How to plug custom search algorthim within Sqlite?
For example,
select * from table where a = 'xxx';
Instead of using normal inbuilt search can it be userDefined function?
regards
ragha
-
To unsubscribe, send email
nesday, September 5, 2007 6:20 pm
Subject: [sqlite] Re: Re: Merging two tables
> RaghavendraK 70574
> <[EMAIL PROTECTED]> wrote:
> >> How to plug custom search algorthim within Sqlite?
> >> For example,
> >>
> >> select * from table where a = 'xxx'
May be,
first want to try with the same impl as any other operator like =.
If successful, then try providing some mech which make use of indexes
where they are actually not allowed for.
If u see one of my earlier post, related to pread,Made use of custom pread
to avoid disk access, now in 3.5 it
it!
*
- Original Message -
From: Dwight Ingersoll <[EMAIL PROTECTED]>
Date: Friday, September 7, 2007 10:48 pm
Subject: Re: [sqlite] New Operator Support
> On 9/7/07, RaghavendraK 70574 <[EMAIL PROTE
Hi,
Its to get involved in the development of sqlite.If possible
move to contrib section.a dream.
regards
ragha
**
This email and its attachments contain confidential information from HUAWEI,
which is
Hi,
Just to get more hands on Sqlite i want to
write a custom operator. Pls suggest how i can do it.
For example
select * from tablex where column1 ~ '123';
I want implement it similar to '='. Can anyone help me
what all steps,files i need to change?
regards
ragha
AIL PROTECTED]>
Date: Wednesday, September 5, 2007 8:50 pm
Subject: [sqlite] Re: Re: Merging two tables
> RaghavendraK 70574
> <[EMAIL PROTECTED]> wrote:
> >> How to plug custom search algorthim within Sqlite?
> >> For example,
> >>
> >> select *
immediately and
delete it!
*
- Original Message -
From: Dennis Cote <[EMAIL PROTECTED]>
Date: Tuesday, September 11, 2007 4:39 am
Subject: Re: [sqlite] Re: Re: Merging two tables
> RaghavendraK 70
Hi,
Can anyone explain how does the below sql statmenent work,
select * from tbl1 where a=xx and b=yy; when a is indexed and b is indexed
seperately? I tried explain could not make out.
Does it select all records with t=111 and then do a search for b=222 with
in that set or other way?
>From
it!
*
- Original Message -
From: Igor Tandetnik <[EMAIL PROTECTED]>
Date: Wednesday, September 12, 2007 8:10 pm
Subject: [sqlite] Re: multi column select
> RaghavendraK 70574
> <[EMAIL PROTECTED]> wro
>env | grep LD_LIBRARY_PATH;
it will display path list. Now use that path and see if your lib is present
there or not in that paht.
If not add the path(where fst2 exists) to LD_LIBRARY_PATH
R u new to unix/linux, i suggest to go through
basic of them.
regrads
But with large cache there is too much fragmentation observered (pgin/pgout)
over a period of time. Do u see this behaviour?
My CacheSize: 32KB, PgSize: 8KB (to the limits)
regards
ragha
**
This email and
Sqlite insertion performance
> --- RaghavendraK 70574 <[EMAIL PROTECTED]> wrote:
> > But with large cache there is too much fragmentation observered
> (pgin/pgout)> over a period of time. Do u see this behaviour?
>
> Fragmentation is not a function of page
Why is the CPU shot up by 100% (relative)when the full db is in mem? This can
be a concern.I understand there is not much i/o but 100% is too high.
regards
ragha
**
This email and its attachments contain
Hi,
Is the performance same,better or
regards
ragha
**
This email and its attachments contain confidential information from HUAWEI,
which is intended only for the person or entity whose address is
Hi,
Can any suggest a way to notify collation function "end of search" from
control algorthim?
regards
ragha
**
This email and its attachments contain confidential information from HUAWEI,
which is
;
Date: Sunday, September 30, 2007 6:41 pm
Subject: [sqlite] Re: End of Search Notification to Collation function
> RaghavendraK 70574
> <[EMAIL PROTECTED]> wrote:
> > Can any suggest a way to notify collation function "end of search"
> > from
> > control
Can cache and page size configuration improve or worsen the situation?
regards
ragha
**
This email and its attachments contain confidential information from HUAWEI,
which is intended only for the person or
Try this, use mmap
(i assume u'r db is readonly)this is much faster and
better than ifstream read. Also ifstream read can keep the data in cache as
long as no other serious
i/o occurs.
U need to accept it as we work with Virtual Mem or
write your own FileSystem which is mem based and short
Ram Drive involve a context switch(from user to Kernel) and hence there is loss
of performance!!!
Check this factor also.
regards
ragha
**
This email and its attachments contain confidential information
There is no association of this flag and code in sqlite3.5.2,
does this really have any impact while building sqlite?
regards
ragha
**
This email and its attachments contain confidential information from
Overall sqlite is losing its credits.
Now when i run sqlite in multithread & multi process
mode system experiences a infinite loop in the
busy handler. Also there is no log which tell
which process/thread has acquired the lock,hence needing complete system halt
and restart. This observed in
Sqlite has Big names. May be this should be
showcased at the sidebar on the front page.
Does it need any other brand building activity?
Atleast we got a better with those names.
regards
ragha
**
This email
I would suggest you to go for thread specific sqlite handles.
This would make each thread operate in its own db connection handle.
threadID = getthreadID();
if(threadIDHashList.find( threadID)== false)
{
//new thread,just add this threadID to list
//create new DB connection and save it
Kudos!!! to Dan/DRH.
We always get responses from Dan/DRH which is logical,meaningful and on dot.
regards
ragha
**
This email and its attachments contain confidential information from HUAWEI,
which is
Hi Stephen Woodbridge,
> (although it might
> > be an interesting academic exercise to make a VFS port of SQLite
> that uses
> > memory arrays for read/write ops.)
Do u have any ref impl of this kind?
regards
ragha
BDB 4.3.28 has other facet like
a) crash when zero diskspace.
b) if there is abrupt crash then recovery can fail.
also the time taken during recover is very long.
c) foot print is too big.
d) Performance is very good on most platforms.
e) Support is not very responsive.
f) Cannot copy the
sqlite has edge over BDB:
1) code readability is not as clean and clear as sqlite
2) forums not sure if they are responsive as sqlite
3) sqlite is stable and does not crash on-restarts, bdb make uses of
mmap and its behaviour is undefined under diskful condition.
get sigbus
85 matches
Mail list logo